Geek asks and answers

MySQL single field with comma separated values

Possible duplicate:
mysql - an array in several columns

I have two tables:

Message table

PostID | Gallery
  1    | 4,7,8,12,13
  2    | 1,2,3,4
  3    | 5,8,9
  4    | 3,6,11,14

The values ​​in the Gallery are the primary keys in the Images table:

Image table

ImageID | FileName
   1    | something.jpg
   2    | lorem.jpg
   3    | ipsum.jpg
   4    | what.jpg
   5    | why.jpg

The reason I am doing this rather than just adding the PostID key to the Images table is because these images can be associated with many different messages. I suppose I could add another table for the relationship, but a comma-separated value is easier to work using the jQuery script, which I use to add to it.

If I’m on a page that requires images related to PostID 3, what query can I run to display all the file names for it?

+5
3

:

SELECT b.filename
FROM posts a
INNER JOIN images b ON FIND_IN_SET(b.imageid, a.gallery) > 0
WHERE a.postid = 3

SQLFiddle

. N: M ( ). , .

... , , jQuery script, .

N: M , imageid CSV:

, posts_has_images (postid, imageid):

GROUP_CONCAT() CSV imageid postid:

SELECT postid, GROUP_CONCAT(imageid) AS gallery
FROM posts_has_images
GROUP BY postid
+22

SQL, , .

, :

SELECT * FROM Images i WHERE EXISTS (SELECT 1 FROM Posts p WHERE p.PostID = 3 AND i.ImageID IN (p.Gallery))
0

. , Gallery. FIND_IN_SET, . - .

0

More articles:


All Articles