Why is affectedRows method returning 0 in CodeIgniter?

After a database update or insert function, I usually use return ($this->db->affected_rows() > 0) and assume that true means success and false means failure. This has been going pretty well, until the other day when I was working on a reset pin functionality.

For simplicity, we were resetting all pins to a default pin, say pin1234, which is also the same pin that is assigned when users are first created.

function resetPin($id) {
    $this->db->set('pin', 'pin1234');
    $this->db->where('id', $id);
    $this->db->update('users');

    return ($this->db->affected_rows() > 0);
}

All goes well and I'm happy with my code. But wait, why am I always getting false as the result?

I made sure that the query syntax was correct, and even used $this->db->get_last_query() to get the query that was being run and pasted it into phpMyAdmin (yes, I still use phpMyAdmin). Curiously, when I ran it there, the result was '0 rows affected'!

After a bit more tinkering, I found out that this happens when the pin's value is already pin1234. Because we're trying to update it to pin1234, it's going to say that 0 rows were affected.

So, how to refactor the function so we can handle this? Easy, first we get the user's current pin, and if it's equal to pin1234, then we can return true, because there will be no need to reset the pin. However, if the pin is not equal to pin1234, we will update it and return the number of affected rows.

function resetPin($id) {
    $this->db->select('pin');
    $this->db->where('id', $id);
    $currentPin = $this->db->get('users')->result()[0]->pin;


    if($currentPin == 'pin1234') {
        return true;
    } else {
        $this->db->set('pin', 'pin1234');
        $this->db->where('id', $id);
        $this->db->update('users');
        return ($this->db->affected_rows() > 0);
    }
}

Thank you for reading. Until next time 👋

Note: I got the cover image from Flickr

Comments

[majeo]

got here when surfing for some issue , read yer solution , simple good clean .. TQ for sharing ..

i'm some how not fimilar yet with CI , I couldn't get this peace of code or let's say didn't know why need it ..
if($result->num_rows() == 1){

return $result->row(0)->id; //this one why row(0) ??!!

[Doaa Mahely]

Hey, glad I could help.
Can you post a full snippet of code?

[majeo]

thnx for you ..
here you re a simple user_model (Models file = typically deal with DB as I learnt) ..
it is just typical method of comparing what user enters with what data we have in db ..
you won't need to see the (View file) I guess .. :)
it's only one row comes back from db, let's say one result comes back , why need to involve that Zero :
row(0)->id; ?!!
to my simple knowledge there is no need for Zero in that bracket .. right ?!

[majeo]

posting screenshot image , has not been uploaded for some reason but here you re : the code :

<?php
class User_model extends CI_Model{
public function register(){
//data array to get all data from the form
$data = array(
'first_name' => $this->input->post('first_name'),
'last_name' => $this->input->post('last_name'),
'email' => $this->input->post('email'),
'username' => $this->input->post('user_name'),
'password' => md5($this->input->post('password'))
);
$insert = $this->db->insert('users',$data);
return $insert;
/if this all above go then it will retrun true if not then it will return false, that's why we
testing it there on user.php by if() statement/
}

    //login method: is logic funtion that it's able to work there on users.php if($user_id)
    public function login($user_name, $password){
        //validate with the table
        $this->db->where('username',$user_name);
        $this->db->where('password',$password);
        //restoring the row of the matching in a var
        $result = $this->db->get('users');

        if($result->num_rows() == 1){    //if there is an actual result 
            return $result->row(0)->id;   //basiclly return the only id from the row , but why the zero !!!
        }else{    // if not then do next 
            return false; 
        }
    }
}

?>