36

How do I tell when a MySQL UPDATE was successful versus actually updated data?

Example:

TABLE
id    city_name
1     Union
2     Marthasville

If I run the following:

$data = array('city_name', 'Marthasville');

//update record 2 from Marthasville to the same thing, Marthasville. 
$this->db->where('id', 2);
$this->db->update('table', $data);

if($this->db->affected_rows() > 0)
{
    //I need it to return TRUE when the MySQL was successful even if nothing was actually updated.
    return TRUE;
}else{
    return FALSE;
}

This will return TRUE every time the UPDATE statement is successful, but FALSE when no rows were actually updated.

I need it to return TRUE every time the UPDATE statement was successfully executed even if it doesn't actually change any records.

hakre
  • 193,403
  • 52
  • 435
  • 836
zechdc
  • 3,374
  • 9
  • 40
  • 52

2 Answers2

48

Have a look at mysql_affected_rows()

It should tell you if anything was actually updated as opposed to nothing was successfully updated resulting in a return of true.

php.net says:

mysql_affected_rows()

Returns the number of affected rows on success, and -1 if the last query failed.

You could use the following to achieve your desired results:

if($this->db->affected_rows() >= 0){ }
472084
  • 17,666
  • 10
  • 63
  • 81
  • I just updated my question again to more clearly state the issue I am having with using mysql_affected_rows(). Wish I wouldn't have posted my question so soon :S – zechdc Sep 09 '11 at 23:03
  • 1
    I believe JLeafgle's answer was still correct. mysql_affected_rows will return -1 if the last query failed, otherwise will return >= 0. Assuming that's what you're after, then just change your if statement to... "if($this->db->affected_rows() >= 0)" – Jonnix Sep 09 '11 at 23:08
  • You are correct @Jon Stirling. I updated Jleagle's answer to reflect your suggestion, however I can't approve the edit so if someone else could, that would be great. Thanks! – zechdc Sep 09 '11 at 23:33
  • 1
    If this can be useful for any, with the new mysqli extension the parentheses dissappear, so it would be $db->affected_rows; to get the number of rows updated (you will get a 500 Error if you write the () ). – Unapedra Jun 16 '14 at 15:19
  • Note that if the value to be updated with isn't compatible with the column property (e.g. updating an integer column with a string), affected_rows() will still give a positive result (as the column will be updated, but not with the value requested). You need to also check for any warnings e.g. using mysqli_warning_count(db)==0. – will Apr 16 '22 at 01:36
18

Then you would use mysql_query:

SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

Simple like this:

$result = $this->db->update('table', $data);

if($result)
{
     //without error even no row updated
} else {

}
lnguyen55
  • 737
  • 6
  • 16