Mysqli error with bind_param(): Number of elements in type definition string doesn't match number of bind variables

Question

I tried what I could to solve this error without any success. I'm trying to dynamically build a select query with prepared statement :

I've got this code

$gm = $_GET['gm']; 
$ct = $_GET['ct']; 

$query = 'SELECT * FROM fchar';

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

if (!empty($gm)) {
    $cond[] = "gm = ?";
    $params[] = $gm;
    $type[] = "i";
}

if (!empty($ct)) {
    $cond[] = "ct = ?";
    $params[] = $ct;
    $type[] = "s";
}

if (count($cond)) {
    $query .= ' WHERE ' . implode(' AND ', $cond);
}

if (count($params)) {
    $paramok = implode(', ', $params);
}
if (count($type)) {
    $typeok = implode($type);
}

$stmt = $connection->prepare($query);
$stmt->bind_param(''.$typeok.'',$paramok);

$stmt->execute();


if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}
while ($stmt->fetch()) {

}

I've got this error :

Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables in C:\xampp\htdocs\cebusingles\search.php on line 42

But when I echo the types and params, I've got this :

echo "<br><br>Types : ".$typeok."<br>Param: ".$paramok."<br><br>Query:   ".$query."<br><br>";
// gives :
// Types : is
// Param: 1, USA
// Query: SELECT * FROM fchar WHERE gm = ? AND ct = ?

So I have 2 types : is, and 2 param : 1, USA.

I don't understand why it says that the number of types is not matching the number of params.

ya know, all of those if(count()) are redundant. you're building the 3 arrays in parallel. if one of the arrays has fields, then all of them will, and it's just wasting cpu cycles to test each one individually - especially since the query'd fail if there WAS a mis-match in sizes on any of them. — Marc B
Actually the fields are all optional. There might be one or two. But I agree I need to build this dynamically. I intend to do this when I solve my Params error problem described above. — user5063297

Jesper Jesper · Accepted Answer · 2015-07-02T14:07:25

$stmt->bind_param(''.$typeok.'',$paramok);

Is missing what type of variabled you're trying to insert on the ? places

$stmt->bind_param('ss', ''.$typeok.'', $paramok);

You can read here what characters should be used when using numbers, strings or such:
http://php.net/manual/en/mysqli-stmt.bind-param.php

types

A string that contains one or more characters which specify the types for the corresponding bind variables:

Mysqli error with bind_param(): Number of elements in type definition string doesn't match number of bind variables

1 Answers