CodeIgniter UPDATE query with WHERE and WHERE IN logic

Question

When I try this code:

$query = '';
foreach ($data as $row)
{
    $where = array(
        'community_id' => $row->id,
        'user_id' => $this->session->user_id,
        'status' => 'invited_to_join',
    );
    $update = array(
        'status' => 'joined',
    );
    $query .= $this->db->set($update)->where($where)->get_compiled_update('community_members') . '; ';
}
if ($query)
{   
    return $this->db->query($query);
}

it gives me the error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE `community_members` SET `status` = 'joined' WHERE `community_id` = '18' A' at line 4

this answer uses a straightforward query with CASE, but I don't know how to convert that into CodeIgniter's query builder.

mickmackusa · Accepted Answer · 2025-09-24 20:41:20Z

5

You can direct use array $data['id'] like this, but you will need to make looped UPDATE queries to access each $data['id'] value.

$this->db->set('status','joined');
$this->db->where('community_id',$data['id']);
$this->db->where('user_id',$this->session->user_id); 
$this->db->where('status','invited_to_join');
$this->db->update('community_members');

edited Sep 24 at 20:41

mickmackusa♦

49.3k13 gold badges98 silver badges165 bronze badges

answered Dec 15, 2016 at 10:55

Ankur Radadiya

2353 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ankur Radadiya · Accepted Answer · 2016-12-15 11:29:16Z

2

You can also use where clause this way:

    $where = "'community_id'=".$data['id']." AND user_id=".$this->session-user_id." AND status='invited_to_join'";

    $this->db->set('status','joined');
    $this->db->where($where);
    $this->db->update('community_members');

Thank You!!!

answered Dec 15, 2016 at 11:29

Ankur Radadiya

2353 silver badges11 bronze badges

2 Comments

dapidmini Over a year ago

I think your first answer was much better. this method requires manual string escaping..

mickmackusa Sep 24 at 20:43

This unexplained script does not enjoy the protections of CodeIgniter's auto-quoting/escaping of WHERE clause conditions.

mickmackusa · Accepted Answer · 2025-09-24 20:38:56Z

The only reason to iterate anything is to accumulate the community ids from the 2d $data array. Use array_column() to isolate the ids, then pass them into a where_in() call. Chain the update() call which includes the SET clause and the remaining WHERE clause conditions. This cleanly and safely executes the query in a single trip to the database.

$update = $this->db
    ->where_in('community_id', array_column($data, 'id'))
    ->update(
        'community_members',
        ['status' => 'joined'],
        [
            'status' => 'invited_to_join',
            'user_id' => $this->session->user_id,
        ]
    );
return $update ? $this->db->affected_rows() : 0;

As a general rule, I advise against directly accessing submission or session data directly in model methods. Writing simple unit tests and maximizing method utility is achieved by passing in all dynamic data from call sites.

Collectives™ on Stack Overflow

CodeIgniter UPDATE query with WHERE and WHERE IN logic

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related