0

Now I should explain briefly what I want to do.

I have a table of "containers" (transmittals) and another table of "items" (documents) within each container.

I want to output a table listing all the containers with a column at the end of each row with a comma-space list of the items in the container. The purpose is to implement a simplistic search of the items in each container (using the datatables plugin).

I could of course query my containers table and during the "PHP while loop" I could secondary query the matching container id in the items table. Then I concatenate the items for each row, but I want to avoid multiple SQL calls.

So I created a right join (I write a simplified version below)...

SELECT transmittal.*, transmittal_documents.*
FROM transmittal RIGHT JOIN
     transmittal_documents
     ON transmittal_documents.transmittal_id = transmittal.transmittal_id

What is the best way to code my PHP while loop to only gather the unique "container" values in the table whilst concatenating the "items" using something like PHP join.

Or, perhaps I can do this in SQL before processing in PHP?

Thanks in advance for your assistance.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
James Pitt
  • 451
  • 2
  • 8
  • 19

2 Answers2

1

The best way is to do this in SQL using group_concat:

SELECT t.*, group_concat(td.item) as items
FROM transmittal t LEFT JOIN
     transmittal_documents td
     ON td.transmittal_id = t.transmittal_id
GROUP BY td.transmittal_id;

I think you mean left join rather than right join. The left join will keep everything in the transmittal table along with all matches in the other table.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks. That led me in the right direction. I ended up needing to add GROUP BY as well. Also, it didn't concatenate id's well. I think that was due to being INT not a string. I added additional joins for the string table data I really wanted and that resolved it. – James Pitt Jan 14 '14 at 14:47
  • I fixed the group by. `group_concat()` should work with integers; it just produces an integer list. – Gordon Linoff Jan 14 '14 at 14:51
  • If it is INT I end up with a BLOB. I show contents and get something like 31352c31372c31342c3136. – James Pitt Jan 14 '14 at 14:58
  • Do you know if there is a size limit on the GROUP_CONCAT column we have created? – James Pitt Jan 14 '14 at 14:59
  • @JamesPitt . . . Yes, it is controlled by `group_concat_max_len` (see http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_group_concat_max_len). – Gordon Linoff Jan 14 '14 at 15:44
0

Thanks to the answer above from Gordon Linoff, I managed to get this to work for me...

SELECT t., td., group_concat(td.item) as items FROM transmittal t LEFT JOIN transmittal_documents td ON td.transmittal_id = t.transmittal_id GROUP BY t.transmittal_id

I'm not sure why I needed the additional GROUP BY but it worked. Without it I only received one row of data.

I actually had to JOIN in my additional table with the document information but that would not be an issue if the "td.item" value was a string (it was an INT in the tables I had here which caused a BLOB to be created which didn't contain the right information).

It appears there may be a limit to the GROUP_CONCAT field without using additional parameter settings so I might be better handling this with Arrays in PHP.

James Pitt
  • 451
  • 2
  • 8
  • 19