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.

SELECT GalleryData.ID, GalleryData.FileName, GalleryData.GalleryID, GalleryData.Description, Galleries.GalleryName, Galleries.GalleryFolder, Comments.ID, Comments.Comment, Comments.CommentAuthor, Comments.CommentDate FROM GalleryData INNER JOIN Galleries ON GalleryData.GalleryID = Galleries.GalleryID LEFT JOIN Comments ON GalleryData.ID = Comments.ID This yields duplicate image records when there are more than 1 comment associated with an image. — dragboatrandy
– dragboatrandy, Commented Oct 7, 2010 at 20:10

Explosion Pills · Accepted Answer · 2010-10-08 05:24:38Z

1

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;

answered Oct 8, 2010 at 5:24

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dragboatrandy Over a year ago

The concatenate part works, but it only yields the images that have comments. I want it to show all of the images, but include the comments with the ones that actually have comments. There are 400 or so images with that GalleryID and when I run the query, only 3 show, because those are the ones with comments.

Explosion Pills Over a year ago

Oh sorry. Left join on Comments then :)

pleasedontbelong · Accepted Answer · 2010-10-07 20:10:34Z

0

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

edited Oct 7, 2010 at 20:10

answered Oct 7, 2010 at 20:03

pleasedontbelong

20.1k12 gold badges55 silver badges79 bronze badges

2 Comments

dragboatrandy Over a year ago

I tried this, but it gave me only one result.SELECT GalleryData.ID, GalleryData.FileName, GalleryData.GalleryID, GalleryData.Description, Galleries.GalleryName, Galleries.GalleryFolder, GROUP_CONCAT(Comments.CommentAuthor,Comments.CommentDate, Comments.Comment ORDER BY Comments.CommentDate SEPARATOR '|||') FROM GalleryData INNER JOIN Galleries ON GalleryData.GalleryID = Galleries.GalleryID LEFT JOIN Comments ON GalleryData.ID = Comments.ID WHERE GalleryData.GalleryID=42

pleasedontbelong Over a year ago

have you forgot the last GROUP BY? or you run out of characters =P .. try a simple query to check if group_concat() is your answer, maybe just showing the imageID and the authors of the comments..

jwheron · Accepted Answer · 2010-10-07 20:15:52Z

0

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.

answered Oct 7, 2010 at 20:15

jwheron

2,5722 gold badges30 silver badges40 bronze badges

2 Comments

dragboatrandy Over a year ago

I tried that. See stackoverflow.com/questions/3884347/…

jwheron Over a year ago

That seems like a non-sequitur in my opinion. Just because your original code contains a flaw (and given the comments on that question, it looks like you have viable options to fixing the original problem). You even say in your question that you may be overcomplicating the issue. In my experience, rewriting straightforward code to brute force a solution just leads to all sorts of angst later on.

Collectives™ on Stack Overflow

Mysql/php - Query Question

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related