Mysqli:mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement

Question

I am creating dynamic mysqli query with the help of @chris85. I am able to created this.

<?php
require_once 'include/db.php';    
$firstname = 'Alpha';
$lastname = 'Romeo';

$query = "SELECT * FROM users";

$cond = array();
$params = array();

if (!empty($firstname)) {
    $cond[] = "fname = ?";
    $params[] = $firstname;
}

if (!empty($lastname)) {
    $cond[] = "lname = ?";
    $params[] = $lastname;
}

if (count($cond)) {
    $query .= ' WHERE ' . implode(' AND ', $cond);
}
echo $query;
$stmt = $mysqli->prepare($query);
if(!empty($params)) {
     foreach($params as $param) {
         $stmt->bind_param("s", $param);
        echo $param;
     }
}
$stmt->execute();

?>

When i execute this i got this.

SELECT * FROM users WHERE fname = ? AND lname = ?
Warning: mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement in /home/u983213557/public_html/test.php on line 32
AlphaRomeo

I am not sure why it is failing. please advise me to fix this issue.

You can't do `$stmt->bind_param()` in a loop one param at a time. You need to do 1 `$stmt->bind_param()` with **all** the params at the same time. — Sean, Apr 28 '16 at 03:17
no, it doesn;t work that way, looping `->bind_param` just results into just one bound type all in all, so when you used `foreach` it didn't stacked those two `s`. there's already tons of workaround regarding that issue, http://php.net/manual/en/mysqli-stmt.bind-param.php, check out the manual entries there — Kevin, Apr 28 '16 at 03:17
@Sean rodrigo thanks for your comment. it you guys can advise me how to fix it then it would be very helpful. I am creating dynamic query that why i can't simply hard code bind param — Roxx, Apr 28 '16 at 03:46
Use the code that @YourCommonSense linked to. Remove the code inside the `if(!empty($params)){ ... }` block and put the code [`$bind = array(); foreach($params as $key => $val){ $bind[$key] = &$params[$key];} $types = str_repeat("s", count($params)); array_unshift($bind, $types); call_user_func_array(array($stmt, 'bind_param'), $bind);`](https://phpdelusions.net/pdo/mysqli_comparison#in) — Sean, Apr 28 '16 at 05:05
@Sean I am getting below error. E_WARNING : type 2 -- call_user_func_array() expects parameter 1 to be a valid callback, first array member is not a valid class name or object -- at line 70 E_ERROR : type 1 -- Call to a member function execute() on a non-object -- at line 72 — Roxx, Apr 28 '16 at 07:51

Your Common Sense · Accepted Answer · 2016-04-28T03:56:47.037

2

It is failing because mysqli is not PDO and you cannot bind in a loop. Thus you have to use tricks to bind an array in mysqli. Luckily, if your PHP version is 5.6 or 7, you can use this code:

$stmt = $db->prepare($query);
$types = str_repeat('s', count($param));
$statement->bind_param($types, ...$param);
$statement->execute();

if not, then you are bound to use call_user_func()-based solution

edited Apr 28 '16 at 03:56

answered Apr 28 '16 at 03:17

Your Common Sense

156,878
40
214
345

if you explain bit more then it would be great. i am using php 5.5+ – Roxx Apr 28 '16 at 03:44
You will find the 5.5-friendly code following the link. – Your Common Sense Apr 28 '16 at 03:48
I checked that link but it is bit hard for me to understand. – Roxx Apr 28 '16 at 03:59
You don't really need to understand, you can just copy-paste and adapt it for your query and data. By the way, any reason you are not using PDO? – Your Common Sense Apr 28 '16 at 04:01
I agree with you i need to understand it. Actually my whole code is created in sqli (prepared). It is not possible to make changes in more than 100 files. – Roxx Apr 28 '16 at 04:04
I didn't say you need to understand. I just said you can take the code and change the query and the data array to your own ones. – Your Common Sense Apr 28 '16 at 04:08
I am not able to use it with my code. If you have some time then please show example with my code. I tried multiple times but not able to manage it. Thanks – Roxx Apr 28 '16 at 04:39
Here is the error which i am getting now. E_WARNING : type 2 -- call_user_func_array() expects parameter 1 to be a valid callback, first array member is not a valid class name or object -- at line 70 E_ERROR : type 1 -- Call to a member function execute() on a non-object -- at line 72 – Roxx Apr 28 '16 at 07:51
Set up the error reporting like explained in this answer and tell me what is the error http://stackoverflow.com/a/22662582/285587 – Your Common Sense Apr 28 '16 at 08:14

Mysqli:mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement

1 Answers1