loading...
Cover image for Why is affectedRows method returning 0 in CodeIgniter?

Why is affectedRows method returning 0 in CodeIgniter?

dmahely profile image Doaa ・2 min read

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?

Gif of confused man

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'!

Gif of Rachel Anniston rubbing her chin with 'Hmm interesting' text at the bottom

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.

Gif of Andy Samberg nodding and saying Aha!

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

Posted on by:

dmahely profile

Doaa

@dmahely

Software engineer and full stack developer. She/her.

Discussion

markdown guide