mysqli prepared statements UPDATE query trouble

[geno11x11]

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 />";

Output:

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);}

$dataTypes="si";$bindParams=array(&$Address,&$Permit_Rate);
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 />"; 

Output:

Length of dataTypes: 2: Contents of dataTypes: si
Count of bindParams: 2 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);}
$stmt->execute();$stmt->close();

Output:

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 :

 

 

[mac_gyver]

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.

[geno11x11]

mac_gyver,

 

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.  

 

Thanks,

[kicken]

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 August 17, 2013 by kicken

[geno11x11]

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.