CodeIgniter UPDATE query with multiple SET assignments and multiple WHERE conditions

Question

How can I convert this query to active record?

"UPDATE table_user 
 SET email = '$email', last_ip = '$last_ip' 
 where username = '$username' and status = '$status'";

I tried to convert the query above to:

$data = array('email' => $email, 'last_ip' => $ip);
$this->db->where('username',$username);
$this->db->update('table_user',$data);

How about using the where clausa status?

# must I write db->where two times like this?
$this->db->where('username', $username);
$this->db->where('status', $status);

I also tried this:

$this->db->where('username', $username, 'status', $status);

TV-C-1-5 · Accepted Answer · 2017-09-29 06:45:10Z

81

you can use an array and pass the array.

Associative array method:
$array = array('name' => $name, 'title' => $title, 'status' => $status);

$this->db->where($array); 

// Produces: WHERE name = 'Joe' AND title = 'boss' AND status = 'active'

Or if you want to do something other than = comparison

$array = array('name !=' => $name, 'id <' => $id, 'date >' => $date);

$this->db->where($array);

edited Sep 29, 2017 at 6:45

TV-C-1-5

7322 gold badges13 silver badges19 bronze badges

answered May 10, 2012 at 16:31

NDBoost

10.7k6 gold badges55 silver badges75 bronze badges

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

Comments

NazS2 · Accepted Answer · 2016-08-11 22:08:57Z

18

Yes, multiple calls to where() is a perfectly valid way to achieve this.

$this->db->where('username',$username);
$this->db->where('status',$status);

http://www.codeigniter.com/user_guide/database/query_builder.html

edited Aug 11, 2016 at 22:08

NazS2

397 bronze badges

answered May 10, 2012 at 16:33

IsisCode

2,49018 silver badges20 bronze badges

3 Comments

NDBoost Over a year ago

although this is ONE way to achieve this. Its much easier to use an array of key,values

AndFisher Over a year ago

Useful to know, since you might need to extend a where clause after evaluating some condition.

JWC May Over a year ago

This works perfectly in join also: $this->db->where('TRIPSHEET_MAIN.id', $id); $this->db->where("CASH.tripsheet_id", $id);

Nikunj Dhimar · Accepted Answer · 2013-08-03 10:46:28Z

2

you can try this function for multi-purpose

function ManageData($table_name='',$condition=array(),$udata=array(),$is_insert=false){
$resultArr = array();
$ci = & get_instance();
if($condition && count($condition))
    $ci->db->where($condition);
if($is_insert)
{
    $ci->db->insert($table_name,$udata);
    return 0;
}
else
{
    $ci->db->update($table_name,$udata);
    return 1;
}

}

answered Aug 3, 2013 at 10:46

Nikunj Dhimar

2,38620 silver badges24 bronze badges

Comments

Waseem shah · Accepted Answer · 2020-01-08 06:38:59Z

2

$wherecond = "( ( ( username ='" . $username . "' OR status='" . $status . "') AND (id='" . $id . "') ) )"; $this->db->where($wherecond);

If you want to add AND and OR conditions at a time. this will work.

answered Jan 8, 2020 at 6:38

Waseem shah

4407 silver badges14 bronze badges

Comments

Moyed Ansari · Accepted Answer · 2012-05-11 08:59:18Z

1

Try this

$data = array(
               'email' =>$email,
               'last_ip' => $last_ip
            );

$where = array('username ' => $username , 'status ' => $status);
$this->db->where($where);
$this->db->update('table_user ', $data);

answered May 11, 2012 at 8:59

Moyed Ansari

8,4612 gold badges38 silver badges57 bronze badges

1 Comment

rahim.nagori Over a year ago

Any idea how can I add a OR condition in this/

GabouhSk8 · Accepted Answer · 2014-08-12 17:45:52Z

1

it's late for this answer but i think maybe still can help, i try the both methods above, using two where conditions and the method with the array, none of those work for me i did several test and the condition was never getting executed, so i did a workaround, here is my code:

public function validateLogin($email, $password){
        $password = md5($password);
        $this->db->select("userID,email,password");
        $query = $this->db->get_where("users", array("email" => $email));
        $p = $query->row_array();

        if($query->num_rows() == 1 && $password == $p['password']){  
            return $query->row();
        }

    }

answered Aug 12, 2014 at 17:45

GabouhSk8

1691 silver badge13 bronze badges

Comments

Ks Sjkjs · Accepted Answer · 2016-05-27 08:31:55Z

1

you can use both use array like :

$array = array('tlb_account.crid' =>$value , 'tlb_request.sign'=> 'FALSE' );

and direct assign like:

$this->db->where('tlb_account.crid' =>$value , 'tlb_request.sign'=> 'FALSE');

I wish help you.

answered May 27, 2016 at 8:31

Ks Sjkjs

911 silver badge2 bronze badges

Comments

mickmackusa · Accepted Answer · 2024-09-01 01:49:47Z

0

Most elegantly, this task can be done in just one query builder method call because the 3rd parameter of CodeIgniter's update() method receives an associative array of WHERE clause conditions.

$this->db->update(
    'table_user',
    ['email' => $email, 'last_ip' => $ip],
    ['username' => $username, 'status' => $status]
);

The method signature is basically:

function update($table, $set, $where)

I strongly recommend that your model method which performs this action should return an int-type value which is populated by $this->db->affected_rows() to indicate that the targeted row(s) where actually changed in some way.

answered Sep 1, 2024 at 1:49

mickmackusa♦

49.3k13 gold badges98 silver badges165 bronze badges

Collectives™ on Stack Overflow

CodeIgniter UPDATE query with multiple SET assignments and multiple WHERE conditions

8 Answers 8

Comments

3 Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

Comments

3 Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related