29 Dec

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.

5 Dec

CRUD Using Codeigniter

Db operations using codeigniter is explained briefly with codes.

Add a new row
A row can be inserted as shown below. To get the unique id of the row inserted, we have to use the function: $this->db->insert_id().

$data['user_gid'] = $user_gid;
$data['username'] = $user_name;
$data['email'] = $email;
$this->db->insert("mytable", $data);
$user_id = $this->db->insert_id();

Update a row
To update a row, we have to pass a where condition.
mutiple where conditions are possible. Below sample code just used one where condition.

$data['user_gid'] = $user_gid;
$data['username'] = $user_name;
$data['email'] = $email;
$this->db->where('user_id', $user_id);
$this->db->update('mytable', $data);

Select a row
To select a row that matches a where condition, we have to pass a
where condition just like we passed for update.
$this->db->select(“*”) will select all fields of the table.
To select just one field or specific fields we can specify the field names.

//get all fields
$this->db->select("*");
//to get  email and username
//$this->db->select("username,email");
$this->db->from("mytable");
$this->db->where('user_id', $user_id);
$query = $this->db->get();
$row = $query->row_array();
return $row;

Select all rows

$this->db->select("*");
$this->db->from("mytable");
$query = $this->db->get();
$rows = $query->result();
return $rows;

Delete a row

$where = array(
    'user_id' => $user_id
);
$this->db->delete("mytable", $where);