Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

mysqli prepared statements UPDATE throwing error message

Codes here !

Moderators: macek, egami, gesf

mysqli prepared statements UPDATE throwing error message

Postby geno11x11 » Fri Aug 16, 2013 10:55 am

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 :
geno11x11
New php-forum User
New php-forum User
 
Posts: 4
Joined: Wed Jan 30, 2013 5:27 pm

Re: mysqli prepared statements UPDATE throwing error message

Postby johnj » Fri Aug 16, 2013 9:16 pm

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
php-forum Super User
php-forum Super User
 
Posts: 1805
Joined: Thu Mar 10, 2011 5:07 pm

Re: mysqli prepared statements UPDATE throwing error message

Postby johnj » Fri Aug 16, 2013 9:24 pm

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
php-forum Super User
php-forum Super User
 
Posts: 1805
Joined: Thu Mar 10, 2011 5:07 pm

Re: mysqli prepared statements UPDATE throwing error message

Postby johnj » Fri Aug 16, 2013 9:33 pm

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()
johnj
php-forum Super User
php-forum Super User
 
Posts: 1805
Joined: Thu Mar 10, 2011 5:07 pm

Re: mysqli prepared statements UPDATE throwing error message

Postby mash » Sun Oct 13, 2013 8:01 am

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);
mash
New php-forum User
New php-forum User
 
Posts: 7
Joined: Sat Jan 14, 2012 1:36 pm


Return to mySQL & php coding

Who is online

Users browsing this forum: Bing [Bot] and 2 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.