CODEWITHSUNDEEP
phpcodeigniteractiverecordsql-updatequery-builder
Zulkifli Said
Zulkifli Said

Reputation: 631

CodeIgniter UPDATE query with multiple SET assignments and multiple WHERE conditions

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

Upvotes: 35

Views: 186511

Answers (8)

mickmackusa
mickmackusa

Reputation: 47864

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.

Upvotes: 0

Waseem shah
Waseem shah

Reputation: 450

$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.

Upvotes: 2

NDBoost
NDBoost

Reputation: 10634

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

Upvotes: 81

IsisCode
IsisCode

Reputation: 2490

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

Upvotes: 18

Ks Sjkjs
Ks Sjkjs

Reputation: 91

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.

Upvotes: 1

GabouhSk8
GabouhSk8

Reputation: 169

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();
        }

    }

Upvotes: 1

Nikunj Dhimar
Nikunj Dhimar

Reputation: 2386

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;
}

}

Upvotes: 2

Moyed Ansari
Moyed Ansari

Reputation: 8461

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

Upvotes: 1

Related Questions