"number of bound variables does not match number of tokens" -- but it does

Question

I’ve seen many people get the error SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens, but as far as I can tell, none of their problems helped or applied to me. I’m convinced it’s something really stupid on my part...

When I echo out $ps and $ps_val (both in my db class and on the page), they have the same number of values. I’ll even echo out the SQL and count and double check spelling of the placeholders and it all adds up. I even went into phpMyAdmin, copy-pasted my SQL, and copy-pasted all placeholders with their values, and that worked, so I know it’s not SQL syntax.

$ps[] = ":category";
$ps_val[] = $_GET['cat'];

if (isset($_GET['color'])){
    // resort_color converts hex color into comma delimited text colors
    // e.g. resort_color("#000000") returns "black, black with white trim, etc."
    $colors = resort_color($_GET['color']);

    if (strpos($colors, ",") >= 0){
        $colors = explode(",", $colors);

        $i = 0;
        foreach ($colors as $color){
            $color = trim($color);
            if ($i == 0){
                $sql_color = " AND `Color` LIKE \"%:color{$i}%\"";
                $ps[] = ":color{$i}";
                $ps_val[] = $color;
            } else {
                $sql_color = $sql_color . " OR `Color` LIKE \"%:color{$i}%\"";
                $ps[] = ":color{$i}";
                $ps_val[] = $color;
            }
            $i++;
        }
    } else {
        $sql_color = 'AND `Color` LIKE \"%:color%\"';
        $ps[] = ":color";
        $ps_val[] = $colors;
    }
} else {
    $sql_color = "";
}

$sql[] = 'SELECT * FROM `bullet_catalog` WHERE `Category` = :category ' . $sql_color;
$sql[] = 'SELECT * FROM `leeds_catalog` WHERE `Category` = :category ' .  $sql_color;
$sql[] = 'SELECT * FROM `trimark_catalog` WHERE `Category` = :category ' .  $sql_color;

if ($db->query_all($sql, $ps, $ps_val)){
    $products = $db->query_all($sql, $ps, $ps_val);
} else {
    $products = "";
}

And my query_all function:

public function query_all($sql, $ps="", $ps_val=""){
    if (!$sql){
        return false;
    } else {
        foreach ($sql as $ind_sql){
            $query = $this->pdo->prepare($ind_sql);
            if ($ps){
                if (is_array($ps)){
                    $i = 0;
                    foreach ($ps as $pss){
                        $query->bindValue($pss, $ps_val[$i]);
                        $i++;
                    }
                } else {
                    $query->bindValue($ps, $ps_val);
                }
            }

            try {
                $query->execute();

                $result = $query->fetchAll(PDO::FETCH_ASSOC);
                foreach ($result as $res){
                    $results[] = $res;
                }   
            } catch (PDOException $error){
                echo "<div><pre>" . print_r($error->getMessage(), true) . "</pre></div>";
            }
        }
        if (isset($results)){
            return $results;
        } else {
            return false;
        }
    }
}

PDOStatement::debugDumpParams returns

SQL: [295] SELECT * 
FROM `table` 
WHERE `Category` = ":category" 
AND `Color` LIKE "%:color0%" 
OR `Color` LIKE "%:color1%" 
OR `Color` LIKE "%:color2%" 
OR `Color` LIKE "%:color3%" 
OR `Color` LIKE "%:color4%" 
OR `Color` LIKE "%:color5%" 
OR `Color` LIKE "%:color6%" 

Params: 8 

Key: Name: [9] :category 
paramno=-1 
name=[9] ":category" 
is_param=1 param_type=2 

Key: Name: [7] :color0 
paramno=-1 
name=[7] ":color0" 
is_param=1 param_type=2 

Key: Name: [7] :color1 
paramno=-1 
name=[7] ":color1" 
is_param=1 param_type=2 

Key: Name: [7] :color2 
paramno=-1 
name=[7] ":color2" 
is_param=1 param_type=2 

Key: Name: [7] :color3 
paramno=-1 
name=[7] ":color3" 
is_param=1 param_type=2 

Key: Name: [7] :color4 
paramno=-1 
name=[7] ":color4" 
is_param=1 param_type=2 

Key: Name: [7] :color5 
paramno=-1 
name=[7] ":color5" 
is_param=1 param_type=2 

Key: Name: [7] :color6 
paramno=-1 
name=[7] ":color6" 
is_param=1 param_type=2 

Answer 1

If you use PDO bindValue to do a search with a LIKE condition you cannot put the percentages and quotes to the param placeholder '%:color0%'.

This is WRONG:

SELECT * FROM `bullet_catalog` WHERE `color` LIKE '%:color0%'

The CORRECT solution is to use just the placeholder like this:

SELECT * FROM `bullet_catalog` WHERE `color` LIKE :color0

And then add the percentages to the php variable where you store the keyword:

$query->bindValue(':color0', "%" . $color0 . "%");

Also as % and _ are not escaped in $color0, so you should escape them yourself.

Also explained

• How do I create a PDO parameterized query with a LIKE statement?
• implement LIKE query in PDO