Single MySQL field with comma separated values

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?

you might need 2 queries. 1 to get the indexes then loop those for ImageID — t q, Jul 13 '12 at 01:56
Here is your problem. This is bad design. Use atomic values for Gallery. — Roman Newaza, Jul 13 '12 at 01:57
possible duplicate of [mysql is array in multiple columns](http://stackoverflow.com/questions/3971622/mysql-is-array-in-multiple-columns). Also: http://stackoverflow.com/questions/4037145/mysql-how-to-select-rows-where-value-is-in-array — OMG Ponies, Jul 13 '12 at 02:01
"I know my design stinks, but it's easier that way" is a poor excuse for bad workmanship, especially if there's any chance whatsoever another developer will ever have to work on things. — Ken White, Jul 13 '12 at 02:08

Zane Bien · Accepted Answer · 2012-07-13T02:24:18.433

26

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

edited Jul 13 '12 at 02:24

answered Jul 13 '12 at 01:57

Zane Bien

22,685
6
45
57

2

+1 for the GROUP_CONCAT option to still let him get the Gallery data listed the way he needs it for jQuery. – davidethell Jul 13 '12 at 02:04
Good answer with GROUP_CONCAT. This is not covered in the possible duplicate question. – djskinner Oct 24 '12 at 13:01
"However, you should really normalize your design and use a cross-reference table between posts and images. " What exactly does that look like? – Chris Nielsen Oct 05 '18 at 19:21

score 1 · Answer 2 · answered Jul 13 '12 at 02:03

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))

score 1 · Answer 3 · edited Jul 14 '15 at 13:59

1

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.

edited Jul 14 '15 at 13:59

kamal pal

4,187
5
25
40

answered Jul 13 '12 at 02:04

Roman Newaza

11,405
11
58
89

"Turn to atomic values for Gallery - normalize it." What does this mean? – Chris Nielsen Oct 05 '18 at 19:18

Single MySQL field with comma separated values

3 Answers3

Linked

Related