Fix for Codeigniter WHERE IN query issue due to comma

Fix for Codeigniter WHERE IN query issue due to comma

In the below code we have an array of ids named $list_ids. We would like to mark the status of rows with ‘my_id’ = 1, my_id = 2 and my_id = 3 as Completed.

            $data['status'] = "Completed";
            $list_ids = array(1,2,3);
            $this->db->where_in('my_id', $list_ids);
            $this->db->update('my_table', $data);
            echo $this->db->last_query();

But after executing the above query, you might be surprised. Only row with ‘my_id’ = 1 gets updated. You might notice no change to the other two rows? Why?

Look at the generated query below.

UPDATE `my_table` SET `status` = ‘Completed’ WHERE `my_id` IN(‘1,2,3’)

There is a comma before 1 and after 3. The query should be
UPDATE `my_table` SET `status` = ‘Completed’ WHERE `my_id` IN(1,2,3)
to work it as expected.

For that we need to pass an extra parameter to the where_in function. The working code is given below.

            $data['status'] = "Completed";
            $list_ids = array(1,2,3);
            $this->db->where_in('my_id', 
                   $list_ids, FALSE);
            $this->db->update('my_table', $data);
            echo $this->db->last_query();

You can see that, we changed the where_in code from
$this->db->where_in(‘my_id’, $list_ids) to
$this->db->where_in(‘my_id’, $list_ids, FALSE);

Hope it’s helpful.