Jump to content

mysqli prepared statements UPDATE query trouble


Recommended Posts

I am working on converting legacy queries to mysqli prepared statements. My SELECT queries are working fine, but ADD/DELETE/UPDATE are giving me problems. I have found many online examples showing bind statements for ADD/DELETE/SELECT, including the use of call_user_func_array() but others claim that bindings are not necessary when writing records (as opposed to reading them). So I am confused about this issue, and when I use bind statements I get the following error message:


Warning: mysqli_stmt::bind_result(): Number of bind variables doesn't match number of fields in prepared statement in /srv/www/htdocs/PNS/Complex/writeRec5.php on line 177 bind_param()error :


The following are snippets of code using dummy values to simplify the code for testing along with output. I reaIize that call_user_func_array() is not necessary in this simple example, but I will need it for the  project. I don't see the error, and I have compared it to many online forum examples which claim to be functioning. Does anything stand out?  


$prepareString = "Address=?,Permit_Rate=?";
echo "<b>PrepareString: </b>",$prepareString,"<br /><br />";
$Address="123 Somewhere Street";$Permit_Rate=25; 
$sql="UPDATE table1 SET " . $prepareString . " WHERE Key_ID = " . $keyid;
echo $sql,"<br /><br />";


PrepareString: Address=?,Permit_Rate=?
UPDATE table1 SET Address=?,Permit_Rate=? WHERE Key_ID = 2664

$stmt = $mysqli->prepare($sql); 
  if (false === $stmt) {die('Query Prep Failed' . $mysqli->error);}

echo "<b>Length of dataTypes: </b> ",strlen($dataTypes),": <b>Contents of dataTypes: </b>",$dataTypes,"<br />";
echo "<b>Count of bindParams: </b>",count($bindParams)," <b>Contents of bindParams: </B>"; var_dump($bindParams); echo "<br /><br />"; 
Length of dataTypes: 2: Contents of dataTypes: si
Count of bindParams: Contents of bindParams: array(2) { [0]=> &string(20) "123 Somewhere Street" [1]=> &int(25) } 
$x = call_user_func_array(array($stmt, 'bind_result'), &$bindParams);
  if (false === $x) {die('bind_param()error :' . $mysqli->error);}


Warning: mysqli_stmt::bind_result(): Number of bind variables doesn't match number of fields in prepared statement in /srv/www/htdocs/PNS/Complex/writeRec5.php on line 177 bind_param()error :



Link to comment
Share on other sites

UPDATE queries don't return result sets. why are you trying to use bind_result at all. bind_result is used for a SELECT/SHOW/EXPLAIN query.


after you correct bind_result to be bind_parm, do you understand that the array you pass as the second parameter to call_user_func_array() contains the parameters you are trying to supply to the bind_parm() statement? the first element of that array would be the 'si' string. the following array elements would be references to the actual data. this is the only place you use references.


however, since you are NOT dynamically binding an arbitrary number of parameters to the query, why are you even using call_user_func_array()? you would only use call_user_func_array() when the number of bound parameters is dynamic.

Link to comment
Share on other sites



Thanks so much for clearing up the issue of result_bindings for written data. There is a huge amount of conflicting information out there... I was following an online example (grossly in error) which showed result bindings for UPDATE, but noticed multiple comments elsewhere stating that bindings aren't used -- Now things make more sense, and I think the proverbial light is shining a bit brighter!


Answering your question about call_user_func_array, I pared down the code to simplify the snippet as much as possible. I threw a few static values in there but the real code is dynamic and the fields used will vary.


In the context of call_user_func_array and their related variations, can you give an explanation what callbacks do and how they make the field list variable? It is working for my SELECT query, but I copied an online example and have no idea what it does or how it works.  



Link to comment
Share on other sites

callback functions are something you pass as an argument to one function, and once that function has finished doing something, or needs to do something it does it will execute the passed function. Exactly what a callback does is going to depend on what function is using the callback.


In the specific case of call_user_func_array the callback is just the function you would like to execute, the array of params will be passed into that function as individual parameters. For instance:

call_user_func_array(array($stmt, 'bind_param'), array('ii', 123, 456));
will execute as if you had instead typed

$stmt->bind_param('ii', 123, 456);
on that line. By using call_user_func_array, the parameter list given to bind_params can be built up dynamically during your code, where as if you had typed that line directly you'd only be able to provide a static list. Edited by kicken
Link to comment
Share on other sites

kicken, Your explanation and code example just solved my problem. I did not realize that the data type string must be part of the bind_parameter array for call_user_func_array(). I thought the data types were passed as a string outside of the array, which is why it threw the error. I used array_unshift() to add the string and it flew! The sample data was properly UPDATED to the database, and all is well! I will apply the change to my ADD/DELETE modules and the last weeks of frustration will have come to a close.


My sincere thanks to you and mac_gyver for your thoughtful analysis and suggestions.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.