I'm finding that a prepared update using mysqli is updating all the rows in my table instead of the one referenced in the WHERE clause. I'm stumped as for the reason this is happening. I used simplified code below to run as a test, and it still happens. I'm using PHP 5.3.18 and the Client API library version 5.0.96.
$mysqli = new mysqli("localhost", "user", "pass", "db");
if (mysqli_connect_errno()) {
printf("Connect failed: %s
", mysqli_connect_error());
exit();
}
$query = "UPDATE test SET first_name = ? WHERE last_name = ?";
if(!$stmt = $mysqli->prepare($query)) {
die("Unable to prepare<br />");
}
$first = 'test';
$second = 'something';
$stmt->bind_param("ss", $first, $second);
if (!$stmt->execute()) {
die("Could not execute<br />");
}
echo "done";
The query executes, but the first_name is updated for all rows in the table. Yes, there is a last_name with "something." No, the three other test rows do not have the last_name = something. Is this something wrong with the library? My PHP config? Perhaps not enough sleep? What?
Oh... in addition... if I use a query like "UPDATE test SET first_name = ? WHERE id = ?" and update the params to use an id, it works. It only updates the single row. So why can't I use the last_name column in the WHERE clause?
I had the server techs update my server to MySql 5.5 and the issue is now fixed. I'm guessing it had something to do with the library or MySql install.