I am so stumped on this one.
I have some code that generates update queries, prepares, them binds them, then executes. The first query runs successfully, but then prepare fails on the second one. The query is very simple. I am extra confused because the mysql logs show the query being prepared, and I am able to manually execute it in mysql. Consider the following code:
$set = null;
foreach($editable as $field => $value) {
if(!empty($value))
$set .= $field."=?,";
}
$set = substr($set, 0, -1);
// generate the update query based on object data, and execute it
$stmt = $this->dbh->stmt_init() or die("stmt_init: ".$this->dbh->error);
$stmt->prepare("UPDATE ".$this->table_name."
SET ".$set."
WHERE ".$whereclause) or die("prepare failed: ".$this->dbh->error." (".$stmt->sqlstate.")");
if($stmt->error) die($stmt->error);
$this->bindParameters($stmt, $editable);
$stmt->execute();
$stmt->close();
function bindParameters(&$statement, &$params) {
echo "<pre>";
var_dump($statement);
echo "</pre>";
$args = array();
// just assume every value is string type
$args[] = implode('', array_map(function($v) {
return "d";
},
array_values($params)));
foreach ($params as $paramName => $value) {
$args[] = &$params[$paramName];
//$params[$paramName] = null;
}
var_dump($args);
//die();
call_user_func_array(array(&$statement, 'bind_param'), $args);
}
The first part runs in a loop, where $editable is an array of field names and values. $whereClause is always "client_id=83". $this->dbh holds the connection object. This code works fine for the first update, but not the second. it is generating queries like this:
UPDATE client_rate SET rate=? WHERE client_id=83
And it is creating the following:
UPDATE client_rate SET rate=99 WHERE client_id=83
UPDATE client_rate SET rate=165 WHERE client_id=83
The second one is the one that fails with the syntax error. The mysql logs show these queries, and I can run them manually with no errors.
The error displayed exactly is:
prepare failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE client_id=83' at line 3 (42000)
This is what I see in the mysql query logs related to this:
19954 Query SELECT * FROM client_rate LIMIT 0
19954 Query SELECT * FROM client_rate WHERE client_id=83
19954 Prepare UPDATE client_rate
SET rate=?
WHERE client_id=83
19954 Execute UPDATE client_rate
SET rate=99
WHERE client_id=83
19954 Close stmt
19954 Query SELECT * FROM client_rate WHERE client_id=83
19954 Prepare UPDATE client_rate
SET rate=?
WHERE client_id=83
19954 Execute UPDATE client_rate
SET rate=165
WHERE client_id=83
19954 Close stmt
What in the hell could I be doing wrong that would cause prepare to detect an error?
UPDATE: When I remove all the error checking, I can see that the DB is actually running the queries fine. I just get the following a few times on my page:
Warning: mysqli_stmt::bind_param(): invalid object or resource mysqli_stmt
Anything obvious maybe I am missing?
Well, in the end I honestly do not know what caused this. I had noticed my debug code was really piling up trying to solve this, so I reverted my working copy. It then mysteriously started to work.
I am unsure what I did to break it in the first place, but now it works...