Mysql/php - Query Question

Question

I have been trying to figure out a different way to complete this task on another question on this website, but think maybe I am making it too difficult.

Here is what I have: Table with, and ImageID, ImageName, GalleryID Another Table with Comments, Author, Date, ImageID

What I want to do is do a query where I find all of the Images that have a galleryID=42. In addition, I would like to grab all of the comments that are associated with each picture (via the ImageID) and concatenate them in a single value. For example:

ImageID: 1234, ImageName: IMG425, GalleryID: 42, Comments: Cool!|||John Smith|||2010-09-06~~Nice shot!|||Richard Clark|||2010-10-01~~I remember this run.|||Susan Edwards|||2010-10-04

I need to concatenate all of the results from the Comments table that are for each image and put them in as a single value, then I can parse them via PHP in the body of my page.

Answer 1

there's a function in mysql called GROUP_CONCAT i havent really tried it but i think it could help

Good Luck

EDITED:

The query might be something like

SELECT img.id,img.name,img.galleryID, 
   GROUP_CONCAT(com.author,comment.date,com.content 
                ORDER BY comm.date SEPARATOR '|||')
FROM images img JOIN comments com ON img.imageID=com.imageID
GROUP BY img.id,img.name,img.galleryID;

or something like that, but i dont know if group_concat works with joins

Answer 2

Why not just pull the comments data in a separate query?

While the extra trip to the database isn't ideal, it's probably going to be just as much overhead as concatenating and extracting that data, and separating the queries will make your code less jumbled.

Answer 3

GROUP_CONCAT() is the way to go, and the other answers are close. KISS.

SELECT
   ImageID, ImageName, GalleryID
   , GROUP_CONCAT(
      CONCAT_WS('|||', Comment.author, Comment.date, Comment.content)
      SEPARATOR '~~'
   ) as comments
FROM
   Images
   JOIN Galleries USING (GalleryID)
   JOIN Comments USING (ImageID)
WHERE
   GalleryID = 42
GROUP BY
   ImageID, ImageName, GalleryID

Note that GROUP_CONCAT() has a very short max length by default. You may have to run

SET group_concat_max_len = 65535;