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