CODEWITHSUNDEEP
mysql
Lelio Faieta
Lelio Faieta

Reputation: 6684

How to identify if on update query executed with no changes

Maybe the title is not that clear but assume this situation: I have a form to update a specific row of a table. When I submit the form an UPDATE query is performed.

I count the number of affected rows to determine if the query went right or not.

$row->execute();
$count = $row->rowCount();
if($count==0){
    http_response_code(500);
}else{
    http_response_code(200);
}

This works well if the user submit the form by changing any data. If the form is left unchanged the query will affect 0 results and my code will return 500. But the point is that the query was properly executed.

So my question is: is there a way mysql will tell me that 0 row where affected because nothing changed in the data instead of any other case (eg. wrong value in a field or so on?). So that my if can become something like this:

if($count==0||$no_field_changed==false){

Upvotes: 0

Views: 214

Answers (1)

RiggsFolly
RiggsFolly

Reputation: 94682

Gordon already gave you the answer in his comment,

You should check the error conditions to see if the query succeeded

but just to make it obvious I will expand upon it for you. 

$status = $row->execute();
if ( ! $status ) {
    // the query errored
    $arr = $row->errorInfo();
    error_log(print_r($arr,1), 3, 'db_error.log'));
    http_response_code(500);
    exit;
}

// So if we get here the query ran successfully
if($row->rowCount() == 0){
    // Nothing was changed by our query this time but it ran successfully
    http_response_code(200);
}else{
    // Something was changed by our query
    http_response_code(200);
}

Upvotes: 1

Related Questions