[SOLVED] Query where_in comma separated list

Issue

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:
enter image description here

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

Solution

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.

Answered By – RickN

Answer Checked By – Mildred Charles (BugsFixing Admin)

Leave a Reply

Your email address will not be published.