Trouble differentiating return value from mysqli udpate query

[we5inelgr]

Hi all,

I'm testing an UPDATE query, which works fine when Col1 is not already equal to $var1 and WHERE Col2 = $var2 AND Col3 = $var3.

If I test where Col2 != $var2 and/or Col3 != $var3...I get affected_rows = 0.
If I test with Col1 already equal to $var1...I get affected_rows = 0.

Which of course makes sense.

However, what I'd like to do, is tell the difference between a Col1 already equal to $var1 update attempt...and a Col2 != $var2 and/or Col3 != $var3.
In this case, the WHERE clause failing is a much more critical issue (actually, viewed as an error) than simply Col1 already equal to $var1.

How do I differentiate between those two failed update attempts? 😕
I realize that the actual mysqli_query was sucessful, but the update wasn't (i.e. no update occured - for different reasons).

Note: the results for mysqli_error($link) is always blank in each case, and mysqli_errno($link) is always 0 (zero) in each case. Also, the echo "here" doesn't occur.

$sqlUpdate = mysqli_query($link, "UPDATE table SET Col1 = '$var1' WHERE Col2 = '$var2' AND Col3 = '$var3' LIMIT 1") or exit ("Error updating."); //This or exit is only for testing

if (mysqli_affected_rows($link) == 0) {
    $result = "<font color=red><strong>Error: Update was not successful.  Col1 was already same as $var1</strong></font><br>" ;
} elseif (mysqli_affected_rows($link) == -1) {  //This doesn't ever seem to happen
    $result = "<font color=red><strong>Error: Update was not successful.  Mismatch between Col2 and $var2 and/or Col3 and $var3</strong></font><br>" ;
} else {
    $result = "<font color=#006600><strong>Success Updating.</strong></font><br>" ;    

}

echo "error: ".mysqli_error($link)."<br>";
echo "error no: ".mysqli_errno($link)."<br>";
$errorList = mysqli_error_list($link);
echo "error list: ".$errorList."<br>";
if ($sqlUpdate === "false") {
	echo "here";
}

Edit: Also, when I try to echo "error list: " I get this error

Call to undefined function mysqli_error_list()

[Weedpacket]

The question that arises is "why should changing the value of Col1 from 'foo' to 'foo' be considered an error?" If it's because the value of Col1 should not have equalled 'foo' to begin with, then perhaps that should have been checked.

[we5inelgr]

Thanks for the reply, and actually...it's the other way around. I do see where in my testing code, the $result message:

Error: Update was not successful. Col1 was already same as $var1

should be changed to something like

Warning: Update was not successful. Col1 was already same as $var1

.

However, when Col2 != $var2 and/or Col3 != $var3...that's the problem that I'm trying to isolate. Trying to update Col1 from 'foo' to 'foo' isn't an error...but the result from the update query is the same (at this point for me).

[Weedpacket]

we5inelgr wrote:

However, when Col2 != $var2 and/or Col3 != $var3...that's the problem that I'm trying to isolate

You want the update to happen when Col2 != $var2 OR Col3 != $var3? In case you weren't aware, "condition1 OR condition2" is satisfied the case where both conditions are satisfied, which I presume the "and" in "and/or" is intended to convey.

[we5inelgr]

No. If Col2 != $var2 and/or Col3 !=$var3 should not update. Meaning, if either or both are not equal, it should not update. And, it isn't. As expected.

What I'm trying to do, is make a distinction between these two scenarios where the update dosen't happen (as expected), so that I can take action accordingly:

No Update scenario 1:

Col 1 = "123" in the table already, and $var1 = "123" (ie. same). And Col2 = "abc" and Col3 = "def" in the table already, and $var2 = "abc" (ie. same) and $var3 = "def" (ie. same)

UPDATE table SET Col1 = '$var1' WHERE Col2 = '$var2' AND Col3 = '$var3' LIMIT 1") 

^ This update will not update the record of course, and isn't an "error" but I will treat as a "warning" or "info" to the user that the udpate didn't work because Col1 was already equal to $var1. The important part in this case...the WHERE conditions were meet.

No Update scenario 2:

Col 1 = "123" in the table already, and $var1 = "456" (ie. different and OK to update). And Col2 = "abc" and Col3 = "def" in the table already, and $var2 = "ghi" and $var3 = "def" (and/or $var3 doesn't match either)

UPDATE table SET Col1 = '$var1' WHERE Col2 = '$var2' AND Col3 = '$var3' LIMIT 1") 

^ This update will not update the record of course, and I want to treat this is an "error". The important part in this case...the WERE conditions were not meet.

At this point, I don't know how to differentiate between the two no update scenarios.

[sneakyimp]

What I think weedpacket is saying is that you should check whether you are actually going to change any values before you try to run an update query. I.e., is there anyway you can check the new value against the current one before you bother running a query?

Also, if you read the documentation on [man]mysqli_affected_rows[/man] you will see that it only returns -1 if there is an ERROR:

php manual wrote:

An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause in the query or that no query has yet been executed. -1 indicates that the query returned an error.

Likewise it should be clear that mysqli_affected_rows will return a zero in all of these cases:
no records were updated (e.g., the new values were identical to the existing ones)
no rows matched the WHERE clause
* no query has yet been executed

It kinda seems to me that if you want your script to know the difference between a non-update of an existing record or a non-update because no records match, then you will need to take extra steps to understand what is in the database. It might be just a matter of comparing new values to old ones. If you don't have the old values available, you might need to run another query to fetch them.

[we5inelgr]

okay, I see. Thanks. run a select prior to doing an update (as long as there was a result from the select). Or....

I've since come across mysqli_info ($link).

and am using it this way with intended outcomes from testing so far:

$query_info = mysqli_info ($link); 
list($matched, $changed, $warnings) = sscanf($query_info, "Rows matched: %d Changed: %d Warnings: %d");

if ($matched == "1" && $changed == "1" && $warnings == "0") { 
    $result = "Success" ; 
} elseif ($matched == "1" && $changed == "0" && $warnings == "0") { 
    $result = "Warning: Update was not successful.  Col1 was already set to $var1" ;
 } elseif ($matched == "0" && $changed == "0" && $warnings == "0") { 
    $result = "Error: Update was not successful.  Data provided did not match the record." ;
 } elseif ($warnings != "0") { 
    $result = "Warning ($warnings): Update was not successful." ; 
} else { 
    $result = "Error: Update was not successful." ; 
} 

Thoughts on the use of mysqli_info ($link) for this type of purpose?

[sneakyimp]

I haven't used mysqli_info, but it sounds pretty handy. Looks to me like what you are doing is on the right path, but you'll have to decide for yourself if it's doing what you want. I'm not sure what data type $warnings will be but expect that you might not want to compare it to a string containing a single zero. Perhaps check if it is [man]empty[man] or equal to FALSE or if it's zero (not a string) instead.

[we5inelgr]

ah, good idea. thanks!