php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #31037 MySQLi - bind_param
Submitted: 2004-12-09 11:14 UTC Modified: 2006-02-13 07:09 UTC
Votes:44
Avg. Score:4.5 ± 0.8
Reproduced:38 of 38 (100.0%)
Same Version:16 (42.1%)
Same OS:13 (34.2%)
From: php at trafex dot nl Assigned: georg (profile)
Status: No Feedback Package: MySQLi related
PHP Version: 5CVS-2005-04-26 OS: Fedora core 1
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2004-12-09 11:14 UTC] php at trafex dot nl
Description:
------------
I was trying to execute the code that you see below.
But i got the error 
"Warning: mysqli_stmt::bind_param() [function.bind-param]: Number of variables doesn't match number of parameters in prepared statement in..."

So i've tried everything, but still got the same error.

I think this is a bug, and i hope it can be fixed.

I've tried the code with PHP 5.0.2 and PHP 5.0.3 RC1
But still got the same error.

For more info please mail me, thanx!

Reproduce code:
---------------
/* create a prepared statement */
$stmt = $mysqli->prepare("SELECT username FROM pm_users WHERE user_id = ?")

   /* bind parameters for markers */
   $stmt->bind_param('i', $user_id);

    $user_id = 1;

   /* execute query */
   $stmt->execute();


Expected result:
----------------
Nothing at the moment, just NO error.

Actual result:
--------------
Warning: mysqli_stmt::bind_param() [function.bind-param]: Number of variables doesn't match number of parameters in prepared statement in /home/projects/pacman/_public_html/test.php on line 16

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2004-12-09 12:08 UTC] georg@php.net
Thank you for this bug report. To properly diagnose the problem, we
need a short but complete example script to be able to reproduce
this bug ourselves. 

A proper reproducing script starts with <?php and ends with ?>,
is max. 10-20 lines long and does not require any external 
resources such as databases, etc.

If possible, make the script source available online and provide
an URL to it here. Try avoid embedding huge scripts into the report.

Can't reproduce: 
 
<?php 
 
$mysql = new mysqli("localhost", "root", "", "test"); 
 
printf("Client version: %s\n", $mysql->client_version); 
printf("Server version: %s\n", $mysql->server_version); 
 
$mysql->query("DROP TABLE IF EXISTS pm_users"); 
 
$mysql->query("CREATE TABLE pm_users(username varchar(20), 
user_id int)"); 
$mysql->query("INSERT INTO pm_users VALUES ('foo', 1)"); 
 
if ($stmt = $mysql->prepare("SELECT username FROM pm_users 
WHERE user_id = ?")) 
{ 
    $stmt->bind_param('i', $user_id); 
    $user_id = 1; 
  
    $stmt->execute(); 
	 
    $stmt->bind_result($name); 
    $stmt->fetch(); 
 
    printf("Name: %s\n", $name); 
    $stmt->close(); 
} 
 
$mysql->close(); 
?> 
 
 
Output: 
Client version: 40108 
Server version: 40108 
Name: foo 
 [2004-12-09 12:20 UTC] php at trafex dot nl
Oke i've edited the script you gave for my database.
This script do i use now:

Code:
----------------------------------------
<?php 
 
$mysql = new mysqli("localhost", "****", "****", "test");
 
printf("Client version: %s\n", $mysql->client_version); 
printf("Server version: %s\n", $mysql->server_version); 
 
$mysql->query("DROP TABLE IF EXISTS temp_table"); 
 
$mysql->query("CREATE TABLE temp_table(username varchar(20), user_id int)"); 
$mysql->query("INSERT INTO temp_table VALUES ('foo', 1)"); 
 
if ($stmt = $mysql->prepare("SELECT username FROM temp_table WHERE user_id = ?")) 
{ 
    $stmt->bind_param('i', $user_id);
    $user_id = 1; 
  
    $stmt->execute(); 
	 
    $stmt->bind_result($name); 
    $stmt->fetch(); 
 
    printf("Name: %s\n", $name); 
    $stmt->close(); 
} 
 
$mysql->close(); 
?>
----------------------------------------


This script outputs:
----------------------------------------
Client version: 40107 Server version: 40107
Warning: mysqli_stmt::bind_param() [function.bind-param]: Number of variables doesn't match number of parameters in prepared statement in /home/projects/pacman/_public_html/test.php on line 16
Name:

----------------------------------------

A online example can you find here:
http://testing.4worx.com/test.php

And the phpinfo() here:
http://testing.4worx.com/phpinfo.php
 [2005-02-04 16:25 UTC] georg@php.net
Please try using this CVS snapshot:

  http://snaps.php.net/php5-STABLE-latest.tar.gz
 
For Windows:
 
  http://snaps.php.net/win32/php5.0-win32-latest.zip

Please also upgrade your mysql server and client library 
to 4.1.9 
 [2005-03-04 17:06 UTC] php at trafex dot nl
I've tested it again with the these versions:
- PHP version 5.04-cvs (snapshot of 4 march 05)
- MySQL version 4.1.10

And again the same error.
 [2005-03-06 18:26 UTC] sniper@php.net
Assigning to the maintainer..

 [2005-04-26 15:11 UTC] andrey@php.net
Cannot reproduce with different combinations of client and server API as well as PHP versions

andrey@whirlpool:~/test> php 31037.php
Client version: 50004
Server version: 40112
Name: foo
andrey@whirlpool:~/test> php 31037.php
Client version: 50004
Server version: 40110
Name: foo
andrey@whirlpool:~/test> php 31037.php
Client version: 40110
Server version: 40110
Name: foo


Did you recompile PHP with 4.1.10 library or it is still using 4.1.7?
 [2005-05-04 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 [2005-10-06 19:09 UTC] pavel dot stratil-jun at fenix dot cz
I get this error with PHP 5.0.4 on Sun Solaris 9 with Mysql 5.07beta. Can you look into this again please?
 [2006-02-07 11:31 UTC] joey at alegria dot co dot jp
Very bizzar (and frustrating). But I am 100% sure on this. I had no errors from using mysqli_bind_param(); accross a number of scripts that input data to MySQL. However, As soon as I made a script that requested data from the database that also used mysqli_bind_result(), everything that had mysqli_bind_param() started throwing up 

"Warning: mysqli_stmt::bind_param() [function.bind-param]: Number of
variables doesn't match number of parameters in prepared statement
in..."

even on scripts that worked fine before! Because I can find no way to revert it to the working state, I also can't supply a sample script. Is it possible the fist use of mysqli_bind_result() corrupts part of PHP?

I used PHP5.0.3 with MySQL 4.1.19 on Fedora Core 4 (x86)
 [2006-02-07 13:56 UTC] joey at alegria dot co dot jp
Upgraded PHP to 5.0.4-10.5 and that got the mysqli_stmt_bind_param() function working again with my scripts that only had INSERT SQL queries in them.

The error still persisted when binding input parameters on SELECT queries, however this can be fixed by paying close attention to syntax. DO NOT use the same syntax you would for a normal query. When sending a prepared statement it is important to omit quotation marks ('?') around the placeholders. Prepared statement don't require them for strings.

EXAMPLE:

$username='foo';
$id=1;
$prepare="SELECT * FROM test WHERE user='?' and id=?";
$sql->stmt=mysqli_stmt_init($sql->db);
mysqli_stmt_prepare($sql->stmt,$prepare);
echo mysqli_stmt_param_count($sql->stmt); // returns 0
mysqli_stmt_bind_param($sql->stmt,'si',$username,$id); // throws an error

That throws an error however...

$username='foo';
$id=1;
$prepare='SELECT * FROM test WHERE user=? and id=?';
$sql->stmt=mysqli_stmt_init($sql->db);
mysqli_stmt_prepare($sql->stmt,$prepare);
echo mysqli_stmt_param_count($sql->stmt); // returns 2
mysqli_stmt_bind_param($sql->stmt,'si',$username,$id); // works OK

The PHP documentation is not mistaken in the examples it gives for prepared statements but perhaps it could be a little more explicit in pointing out this easy-to-make syntax error.
 [2006-02-13 01:03 UTC] mczub at newcomo dot pl
This script (with 1 variable):
------------------------------
<?php
$mysql = new mysqli("localhost", "***", "***", "test");
printf("Client version: %s\n", $mysql->client_version); 
printf("Server version: %s\n", $mysql->server_version); 
$mysql->query("DROP TABLE IF EXISTS temp_table"); 
$mysql->query("CREATE TABLE temp_table(username varchar(20), user_id int)"); 
$mysql->query("INSERT INTO temp_table VALUES ('foo', 1)"); 
if ($stmt = $mysql->prepare("SELECT username FROM temp_table WHERE user_id = ?")) 
{ 
    $stmt->bind_param('i', $user_id);
    $user_id = 1;
    $stmt->execute(); 
    $stmt->bind_result($name); 
    $stmt->fetch(); 
    printf("Name: %s\n", $name); 
    $stmt->close(); 
} 
$mysql->close(); 
?>

Output:
-------
Client version: 40107 Server version: 50018 Name:


This script (with 2 variables):
------------------------------
<?php
$mysql = new mysqli("localhost", "***", "***", "test");
printf("Client version: %s\n", $mysql->client_version); 
printf("Server version: %s\n", $mysql->server_version); 
$mysql->query("DROP TABLE IF EXISTS temp_table"); 
$mysql->query("CREATE TABLE temp_table(username varchar(20), user_id int, user_id2 int)"); 
$mysql->query("INSERT INTO temp_table VALUES ('foo', 1, 2)"); 
if ($stmt = $mysql->prepare("SELECT username FROM temp_table WHERE user_id = ? AND user_id2 = ?")) 
{ 
    $stmt->bind_param('ii', $user_id, $user_id2);
    $user_id = 1;
    $user_id2 = 2; 
    $stmt->execute(); 
    $stmt->bind_result($name); 
    $stmt->fetch(); 
    printf("Name: %s\n", $name); 
    $stmt->close(); 
} 
$mysql->close(); 
?>

Output:
-------
Client version: 40107 Server version: 50018
Warning: mysqli_stmt::bind_param() [function.bind-param]: Number of variables doesn't match number of parameters in prepared statement in C:\wamp\www\learn\mysql.php on line 12
Name:


It's weird...
 [2006-02-13 07:09 UTC] georg@php.net
> Client version: 40107 Server version: 50018

Why don't upgrade your client version?
Recompile your PHP extensions or download an actual version from http://dev.mysql.com/downloads/connector/php/
 [2006-02-14 01:28 UTC] skotch at online dot stack dot net
After a couple of nights I've found the solution. Upgrading to newest versions of PHP and MySQL don't solve the problem.Both PHP and MySQL have a lib named libmysql but those libs are not the same. Make PHP to use php/libmysql and everythig will be OK. For ensure the result just rename the libmysql library from MySQL install and execute your php script correct!
 [2006-03-28 13:05 UTC] putu dot dondo at gmail dot com
i get this error too!
i use WAMP5 on windowsXP
 [2006-03-30 20:56 UTC] droppse at wjh dot harvard dot edu
Updating the mysql client lib's resolved this problem on XP Pro, WAMP.
 [2006-04-28 06:33 UTC] cythrawll at cythnet dot com
I reproduced this (on accident) with php 5.1.2 on MySQL 4.1.14 Slackware 10.2

Client version: 40114 Server version: 40114
 [2006-05-16 07:36 UTC] aman dot tur at gmail dot com
I am using WAMP 5 (MySQL 5.0.21 Client 40107, Apache 2 , Windows Server 2003). When i try to execute followint script i get error: Number of
variables doesn't match number of parameters in prepared statement

<?php
     $capital=1;
     $mysqli=new mysqli('192.168.0.1','root','*','world');

     //INIT statement
     $stmt=$mysqli->stmt_init();
     if (mysqli_connect_errno())
     {
	   printf("Connect failed: %s\n", mysqli_connect_error());
	   exit();
     }
      //Create statement for Procedure
       $stmt=$mysqli->stmt_init();
       if(!$stmt)
       {
          printf("Error creating Statement: $s\n",$mysqli->error);
	      exit();
       }
		$stmt=$mysqli->prepare("Select name from country where Capital = ?");
		if(!$stmt)
		{
			printf("Error creating Statement: %s\n",$mysqli->errno);
			printf("Error creating Statement: %s\n",$mysqli->error);
			printf("Statement Error: %s\n",$stmt->error);
			exit();
		}
		printf("No of Parameters in Statement: %d \n",$stmt->param_count);
		printf("Client version: %s\n", $mysqli->client_version);
		printf("Server version: %s\n", $mysqli->server_version);
	    //Bind the paremeter values that are to be passed to stored procedure
                //This line gives error
	 	$stmt->bind_param("i",$capital);

	 	//execute the stored procedure
	 	$stmt->execute();
	 	$stmt->bind_result($name);
        $stmt->fetch();

        printf("Country Name: %s\n", $name);

	 	$stmt->close();
	 	$mysqli->close();
?>
 [2006-09-11 15:19 UTC] xpertindia at yahoo dot com
I got it workig as follows -

$vsql1 = "select userid, email from re_users u where email = ? and password = ?";

$preparedstatement1 = $gvdblink->prepare($vsql1);
mysqli_stmt_bind_param($preparedstatement1, "ss", $bv1, $bv2)

It is funny that when I put an * (asterix) I get a  warning/error. It is sad that we have to specify every column we need to retreive.

This fails -

$vsql1 = "select * from re_users u where email = ? and password = ?";
 [2006-09-11 15:21 UTC] xpertindia at yahoo dot com
oops correction - 

I got it workig as follows -

$vsql1 = "select userid, email from re_users u where email = ? and
password = ?";

$preparedstatement1 = $gvdblink->prepare($vsql1);
$preparedstatement1->bind_param('ss', $bv1, $bv2);

It is funny that when I put an * (asterix) I get a  warning/error. It is
sad that we have to specify every column we need to retreive.

This fails -

$vsql1 = "select * from re_users u where email = ? and password = ?";
 [2007-11-28 15:51 UTC] catalin dot tanasescu at filipnet dot ro
I know this is old but ...I had the same problem.
resolved it by adding after each    $stmt->execute(); this
   $stmt->store_result();

very strange ...
hope this helps someone
 [2011-04-03 13:20 UTC] noadress at noadress dot com
I've had the same problem.. and my libs were new.

I had: 
$sqlquery = "UPDATE $table SET title='?', msg='?', date='?' WHERE ID='?'";
.......
$stmt->bind_param('sssi', $_SESSION['tit'], $_SESSION['tex'], $_SESSION['dat'], 
$_SESSION['id']); 

--> ERROR - Number of variables doesn't match number of parameters in prepared 
statement


--> SOLVE
Remove the '' before and after the ?
-> "UPDATE $table SET title=?, msg=?, date=? WHERE ID=?"
and now he did the update ;)

have fun
 [2011-04-03 13:24 UTC] solve at solve dot com
Probleme SOLVED !
Look at my post above (from noadress at noadress dot com)
 [2014-05-13 20:32 UTC] rui dot calsaverini at gmail dot com
This bug is rendering mysqli unusable to me! I can't even get it to accept something like this:

$stmt = $link->prepare("SELECT ?, ?");		
								  
$stmt->bind_param("i", $this->ckey);
$stmt->bind_param("i", $this->ckey);
		  	
$stmt->execute();


I get the same  "Number of variables doesn't match number of parameters in prepared statement in..." warning.
Anybody found a solution? Is this API marginally usable?
 
PHP Copyright © 2001-2019 The PHP Group
All rights reserved.
Last updated: Sun Aug 18 09:01:27 2019 UTC