Geek Answers Handbook

PHP MySQL concatenates cells from different rows with the same value

I have a database with identifier numbers, names, class period and teacher name. Each row includes a student identifier, name, period and teacher, which means that for each student there are several rows. It's not my problem. My problem is that in some classes there are two teachers that are listed ... which means that for one period of the class there are two separate lines, one for each teacher. Unfortunately, I do not control the formatting of the data, since it is exported from a system that does not have a large number of formatting options.

Here's an example, note how lines 23 and 24 relate to the same period period and student, but the only difference is the names of the teachers.

pk id last first period teacher 14 12345 Smith John 3 HARRIS 15 12345 Smith John 8 LEAL 17 12345 Smith John 1 HOUSTON 23 56789 Doe Jane 8 MERCER 24 56789 Doe Jane 8 RUIZ 25 56789 Doe Jane 3 BECK 26 56789 Doe Jane 1 STEED

I would like to combine two lines with the same period number and student name in one line. All information will remain the same, except that two different teacher names will be combined into something like "Mercer and Ruiz." Ideally, the end result would look something like this:

 24 56789 Doe Jane 8 MERCER & RUIZ

Is this possible using PHP and / or MySQL? I'm not looking for anyone to write all the code or anything else. I just can't imagine how to do this. I would welcome any guidance / guidance on how to do this.

As always, thanks for your time and help.

+4
source share
2 answers
 SELECT MAX(pk), ID, `last`, `first`, GROUP_CONCAT(teacher SEPARATOR ' & ') teachers FROM tableName // WHERE clause here... GROUP BY `last`, `first`, `Period` // ORDER BY clause here....

SOOURCE

+5
source 
 SELECT pk,id,last,first,period GROUP_CONCAT(teacher) FROM table GROUP BY id, first,last,period

Must do what you need.

+1
source

More articles:


All Articles