How do I test if my MySQL update query was success

Question

I have a model function that updates a user in my CodeIgniter application:

// updates first of a user, return true if successful, false if not.
public function updateFirstName($userId, $newFirstName) {
    $this->db->query("UPDATE users SET firstName='$newFirstName' WHERE id=$userId");
    return // whether request was successful?
}

How do I return a boolean value that ensures the user of ID $userId has been updated? For instance, it should return false if no user was found with ID $userId.

Answer 1

public function updateInfo($newinfo) {
    $this->db->update("some_table", $newinfo);
    return ($this->db->affected_rows() > 0);
}

This will either return true or false

Answer 2

Check this for more information. Active Records

public function updateFirstName($userId, $newFirstName) {

   return $this->db
               ->where('id', $userId)
               ->update("users", array('firstName' => $newFirstName));
}

With this way you will also avoid sql injection that you had before

Answer 3

Use a stored procedure, you can check the result.

Below is a stored procedure example :

CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_UpdateInfo`(tableId int,tableName varchar(100) charset utf8,description varchar(400) charset utf8)

BEGIN

    declare exit handler for sqlexception select 0 as `result`;

    update table
    set `name` = tableName,
        description = description
    where id = tableId;
    select 1 as `result` ;
END

PHP example code :

$this->load->database();

$rs = $this->db->query('call usp_UpdateInfo(?,?,?)',array($tableId,$tableName,$description));
$this->db->close();     
return $rs->result_array();

Answer 4

As commented, have you tried $this->db->affected_rows()?

That will tell you how many rows were updated.

Answer 5

I have use this code for checking update query.

$status = $this->db->query("UPDATE users SET firstName='$newFirstName' WHERE id=$userId"); 
if($status)
   return true;
else
    return false;

Answer 6

A better solution I've found is to manage the difference between an ERROR and 0 affected rows. 0 affected rows is not necessarily a bad thing, but an error is something you do want to know about:

if ($this->db->_error_message()) {
    return FALSE; // Or do whatever you gotta do here to raise an error
} else {
    return $this->db->affected_rows();
}

Now your function can differentiate...

if ($result === FALSE) {
    $this->errors[] = 'ERROR: Did not update, some error occurred.';
} else if ($result == 0) {
    $this->oks[] = 'No error, but no rows were updated.';
} else {
    $this->oks[] = 'Updated the rows.';
}

Just some quick hacking there - you should obviously make the code far more verbose if you have other people using it.

The point is, consider using _error_message to differentiate between 0 updated rows and a real problem.