Single MySQL field with comma separated values [duplicate]

Question

Possible Duplicate:
mysql is array in multiple columns

I have two tables:

Posts 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 Gallery are the primary keys in the Images table:

Images Table

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

The reason I do this instead of just adding a PostID key to the Images table is because those images can be associated with a lot of different posts. I suppose I could add another table for the relationships, but the comma-separated value is easier to work with as far as the jQuery script I am using to add to it.

If I'm on a page that requires the images associated with PostID 3, what kind of query can I run to output all of the FileNames for it?

Answer 1

You can use this solution:

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

SQLFiddle

However, you should really normalize your design and use a cross-reference table between posts and images. This would be the best and most efficient way of representing N:M (many-to-many) relationships. Not only is it much more efficient for retrieval, but it will vastly simplify updating and deleting image associations.

---

...but the comma-separated value is easier to work with as far as the jQuery script I am using to add to it.

Even if you properly represented the N:M relationship with a cross-reference table, you can still get the imageid's in CSV format:

Suppose you have a posts_has_images table with primary key fields (postid, imageid):

You can use GROUP_CONCAT() to get a CSV of the imageid's for each postid:

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

Answer 2

In terms of proper SQL, you definitely should have another table to relate the two rather than the delimited column.

That said, here's how you could do it:

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

Answer 3

Here is your problem. This is bad design as you need to search for specific values of Gallery field. You can use FIND_IN_SET, but your query will be slow. Turn to atomic values for Gallery - normalize it.