Searching multiple values in a Comma-Separated MySQL column in a single query

Question

I am fully aware of how bad it is to have Delimited Lists in a Table Column, instead of normalizing data into multiple tables.

Although, I do have a table structure designed years ago, which I will re-design soon.
Simplified, it looks like this:

Table: Newsletters
+------------+---------------+
| subject    | mailing_lists |
+------------+---------------+
| A Test     | 1,2           |
| More Tests | 2,3           |
+------------+---------------+

You can see it in this SQLFiddle, if you prefer to.

Recently, I gave my users the option to write a delimited list of mailing list ids (like 1,3), as a feature to select which newsletters to show in a view.
(e.g. Only show newsletters that were sent to lists with id 1 or 3)

So: A table column with a delimited list of ids, and delimited ids as an input.

This would obviously be much easier if the tables were normalized.

So, I solved this in PHP, by exploding the input ids and iterating them to create a query like the one in the fiddle mentioned above, which looks like this:

SELECT * FROM `newsletters`
  WHERE FIND_IN_SET("1", `mailing_lists`) > 0
     OR FIND_IN_SET("3", `mailing_lists`) > 0

This query perfectly fetches the data I want to get, but I can only create it programmatically, since I have to add a new condition for each id in my delimited list.

Question is: Out of pure curiosity: Is there a way to avoid the loop in PHP, and make a query without splitting the ids in code?

Try this link http://stackoverflow.com/questions/12317042/mysql-string-to-set-and-get-intersection — rakeshjain, Oct 14 '13 at 06:41

score 1 · Accepted Answer · answered Oct 14 '13 at 07:27

After the very useful post by rakeshjain, I managed to transform my query into this:

SELECT * FROM (SELECT *,
               `mailing_lists` REGEXP REPLACE("1,3", ',', '(\\,|$)|')
                  as haslists
               FROM `newsletters` B) A
  WHERE A.haslists = 1

In the above, I assume that "1,3" is the value provided by the user.
Here is the solved fiddle: http://sqlfiddle.com/#!2/4621b0/19

Thank you rakeshjain!

score 0 · Answer 2 · answered Oct 14 '13 at 06:21

0

Yes, there is a way to avoid the loop. Just normalize your database structure.

Table "Subjects" subjectID (INT(11), auto_increment)) | subject (varchar(255))

Table "MailingLists" listID (INT(11), auto_increment) | listName

Table "Subjects2Lists" (many to many) subjectID (index) | listID (index)

So you can get every single list ID by executing a simple select / join statement.

SELECT 
    list.listID, 
    names.listName 
FROM 
    Subject2Lists AS list 
LEFT JOIN 
    MailingLists AS names 
    ON (list.listID = names.listID) 
WHERE 
    subjectID = 1

answered Oct 14 '13 at 06:21

Marcel

4,854
1
14
24

1

I already posted this fact on my question, Marcel, but thank you for the effort. – mavrosxristoforos Oct 14 '13 at 06:23
Hm ... sorry for that. Thought it could be fine for others, to avoid the problem For your specific problem I 'd create a stored procedure with the ids of the mailing lists as a parameter. So you can iterate through the ids an create a complete statement and execute it. – Marcel Oct 14 '13 at 06:28
True, but I would rather do it in PHP in that case. I will normalize my table structure, though, since I'm planning a big update, but I thought it would be worth a shot to ask this question. – mavrosxristoforos Oct 14 '13 at 06:34

Searching multiple values in a Comma-Separated MySQL column in a single query

2 Answers2