Query where_in comma separated list

Question

I have a 'list_table' table looks like:

id :  list 
1  :  1,2,44,5    
2  :  4,3,5,2,56,66

Is it possible to check if '44' is in List column in mysql database? I'm using codeigniter and my code looks like:

$this->db->select('*'); 
$this->db->from("list_table");
$this->db->where("find_in_set('44', 'list')");
$query = $this->db->get();
return $query->result();

I also tried with WHERE_IN but didn't get correct result.

This is what query I get when I enable_profile:

SELECT *
FROM `poslovi`
LEFT JOIN `firme` ON `firme`.`f_id` = `poslovi`.`po_firma_id`
LEFT JOIN `kategorije` ON `kategorije`.`k_id` = `poslovi`.`po_category`
WHERE `po_date_istek` > '2022-03-21 10:37:25'
AND   (`po_naziv_oglasa` LIKE '%Radnik u ćevabdžinici%' ESCAPE '!' OR  `f_name` 
LIKE '%Radnik u ćevabdžinici%' ESCAPE '!')
AND find_in_set("61", po_category) <> 0
AND `po_status` = '1'
ORDER BY `po_date_istek` DESC
LIMIT 10

This is what I have in my database:

Just to mention, if I remove 'find_in_set' I get correct result so the rest of the query is good as I noticed

well, the main problem here is [database normalization](https://en.wikipedia.org/wiki/Database_normalization) e.a. What is if you have in your table values like 44, 444 and 4444, how often would 44 be found, erroneously most likely. Try to restructure your database. — Vickel, Mar 19 '22 at 23:45
Actually, I tried to put these values in another table but database is kind big and I have few join tables as well, I have issue with memory, website crash — Pecooou, Mar 20 '22 at 00:03
^ this is most likely due to a non optimal database setup, missing indexes, incorrect order of executing joins and many more. *memory issue, website crash*: that is a very vast description of what you have running is going wrong. — Vickel, Mar 20 '22 at 00:17
maybe you need json format in DB? look at https://stackoverflow.com/questions/56951528/how-to-insert-data-in-json-format-in-codeigniter — Pavlo Mezhevikin, Mar 21 '22 at 09:53

score 3 · Answer 1 · answered Mar 19 '22 at 23:49

3

You need a true or false condition in the WHERE clause, so a comparison

$this->db->select('*'); 
$this->db->from("list_table");
$this->db->where("find_in_set('44', 'list') <> 0");
$query = $this->db->get();
return $query->result();

But it ot recomended to store data this way . Read mor in Is storing a delimited list in a database column really that bad?

answered Mar 19 '22 at 23:49

nbk

45,398
8
30
47

Doesn't work, anyway thanks for advice, I will reconstruct database. Cheers – Pecooou Mar 20 '22 at 00:05
i am wondering why this doesn't work there are many example that ot works just fine https://stackoverflow.com/questions/46509723/how-to-use-find-in-set-in-codeigniter-query – nbk Mar 20 '22 at 00:11
I get the result only if '44' is in the first place in comma sep list – Pecooou Mar 20 '22 at 00:39
1

that is strange run the raw query in workbench or so and try it. FIND_IN_SET works on every position. Maybe a bug in your version – nbk Mar 20 '22 at 00:49
Try removing the single quotes from the 'list' in the where statement: `$this->db->where("find_in_set('44', list) <> 0");` – Kobus Myburgh Mar 20 '22 at 15:51
@KobusMyburgh he said it works only if 44 is at the start, so the column is recognized – nbk Mar 20 '22 at 16:00
I have tested out the code I posted above, with the data he used, and it is working for me as I did it. I also want to refer you to this page: https://www.mysqltutorial.org/mysql-find_in_set/. I am not sure it is getting picked up this way, as 44 is literally checked against a string with value 'list' – Kobus Myburgh Mar 20 '22 at 16:05

score 0 · Answer 2 · answered Mar 20 '22 at 15:54

As nbk said, you need the true/false condition, however, the answer is not working for the OP. You need to remove the single quotes around list in that answer:

$this->db->where("find_in_set('44', 'list') <> 0");

Rewrite the code as below, minus the quotes around list:

$this->db->select('*'); 
$this->db->from("list_table");
$this->db->where("find_in_set('44', list) <> 0");
$query = $this->db->get();
return $query->result();

That should solve the issue for you.

score 0 · Accepted Answer · answered Mar 21 '22 at 10:20

In a screenshot you posted of your data, its possible to see that you include a space after each comma.

The value of po_category is 2, 7, 61, not 2,7,61—and find_in_set does not ignore those spaces!

You've noticed that find_in_set works when you search for the first entry, this is because that does not have a leading space; 7 and 61 do.

find_in_set(" 61", po_category) would match, in this case, but then it wouldn't match if it is the first entry. While you could do (find_in_set("61", po_category) <> 0 || find_in_set(" 61", po_category)) <> 0 to support both cases, that is unnecessarily slow and taxing. Just save your data without spaces. Or, better yet, not as a comma separated list.

In nbk's answer there's a link that explains why doing this is not optimal. One way to save a list of IDs is making a separate table for them and using JOINs. This will be better for performance. Another option that is slightly more complex to implement, if you are using MySQL 8.0.17 or higher, is to save it as a JSON array & index that array.

This was a problem, now it's OK, thanks. On the other way I will consider reorganizing database structure — Pecooou, Mar 21 '22 at 11:02

Query where_in comma separated list

3 Answers3