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