mysqli prepared statements UPDATE throwing error message

[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?

Code: Select all

$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

Code: Select all

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

Code: Select all

$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 :

[johnj]

Looks like you are violating the basic idea behind "bind" function. If you create place holders for 4 variables and if you supply or bind ONLY 3 or less than 3 then you will get the warning saying that "Number of bind...".

[johnj]

Try to rearrange the code like this:

Code: Select all

$stmt->execute();
$x = call_user_func_array(array($stmt, 'bind_result'), &$bindParams);
  if (false === $x) {die('bind_param()error :' . $mysqli->error);}
$stmt->close();


[johnj]

The other thing that you can try is to change:

Code: Select all

$x = call_user_func_array(array($stmt, 'bind_result'), &$bindParams);

to

Code: Select all

$stmt->bind_result(&$Address, &$Permit_Rate);

Make a direct call using bind_result instead of using call_user_func()

[mash]

The problem is that there is actually missing one parameter.
The array containing your bind params needs to have the first element set to the type of the variables in question.
so: UPDATE customers set name=? where id=?
requires an array of three fields for putting to bind_params
a[0]='si';//for string and integer
a[1]=$newName;
a[2]=$customerID;

You don't have to worry bout the field-types and can set everything to string (='s') and the rest will be done automagically.
Easier: If you have 5 fields with params you just set:
a[0]=str_repeat('s',5);