PDOException thrown in PHP

Question

I'm getting the error "SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens" when I try to run the below function:

    public function find_products($string = '', $fields = array(), $sort_by = '', $sort_dir = 'ASC') {

    $fields = empty($fields) ? '*' : ('' . implode(',', $fields) . '?');
    $bindings = array('%' . $string . '%','%' . $string . '%','%' . $string . '%');
    $and_where_checks = array('series','material');
    $AND = '';

    // Loop through the POST variables to see what is safe to play with
    $allowed = array();
    foreach ($and_where_checks as $awc)
        if ( ! empty($_POST[$awc]))
            $allowed = $awc;

    if ( ! empty($allowed)) {
        $tmp = array();
        foreach ($allowed as $v)
            $tmp = '' . $v . ' IN (' . str_pad('', count($v) * 2 - 1, '?,') . ')';

        $AND = 'AND (' . implode(' AND ', $tmp) . ') ';

        foreach ($allowed as $k)
            foreach ($_POST[$k] as $v)
                $bindings = $v;
    }

    $query = 
        "SELECT " . $fields . " FROM " . $this->product_table . " " . 
        "WHERE (" . $this->primary_key . " LIKE ? " .
        $AND . 
        "ORDER BY " . $sort_by . " " . $sort_dir;

    $sth = $this->$dbh->prepare($query);

    $sth->execute($bindings);

    return $sth->fetchAll(PDO::FETCH_ASSOC);
}

The $POST[$awc] variables are filled by checkboxes on this page http://ladd-dev.bitstormweb.com/products/interactive-product-finder/. When I choose one of each checkbox group (e.g. 1 Series and 1 Material) the results are fine, but when I choose multiple boxes in the same group, I get the PDOException.

Does anyone know why? I'm still learning this code so any help would be appreciated!

Devon Bessemer · Answer 1 · 2014-04-21T17:07:25.680

0

In your query, you only have one variable to be bound (the ?):

$query = 
    "SELECT " . $fields . " FROM " . $this->product_table . " " . 
    "WHERE (" . $this->primary_key . " LIKE ? " .
    $AND . 
    "ORDER BY " . $sort_by . " " . $sort_dir;

Here, you must be either binding 0 or binding more than 1. Check how many values are in $bindings.

$sth = $this->$dbh->prepare($query);
$sth->execute($bindings);

You can check how many values are in $bindings by using print_r($bindings);

Update: Without knowing what your input is, your code seems to be using $bindings twice. It is set at the top with 3 values that are the same thing: $bindings = array('%' . $string . '%','%' . $string . '%','%' . $string . '%'); then at the bottom you have a foreach where you are not using an array at all:

foreach ($_POST[$k] as $v)
            $bindings = $v;

edited Apr 21 '14 at 17:07

answered Apr 21 '14 at 17:02

Devon Bessemer

34,461
9
69
95

Thanks for the help. Once I managed to get everything to print correctly for debugging, here is what I'm getting: For $bindings: `Array ( [0] => %% [1] => %% [2] => %% [3] => EEC [4] => Plastic )` For $query: 'SELECT `part_number`,`photo_a` FROM `products` WHERE (`part_number` LIKE ? OR `series` LIKE ? OR `material` LIKE ?) AND (`series` IN (?) AND `material` IN (?)) ORDER BY `part_number` ASC` I'm not sure if the ? are filling in correctly. – user3557469 Apr 21 '14 at 18:34
is there a reason you are using WHERE IN for series and material? If they are just variables, then it seems to make more sense to use `series = ? AND material = ?`. Also you can see from your array that the likes aren't being filled correctly, %%. Unless you want it like that. Test the query independently using predefined values, then try to use the prepared with variables. – Devon Bessemer Apr 21 '14 at 18:47
Thanks for the help Devon. I figured out that the culprit was that no matter what inputs I was using I was only building IN (?) with one array value. So I tweaked the $tmp array to fill out based on the number of checkboxes checked (from the original input). `$tmp = array(); $c = count($allowed); // Number of variables with content $i = 0; // Starting array placement foreach ($allowed as $v) { $tmp[] = "" . $v . " IN (" . str_pad('', count($_POST[$allowed[$i]]) * 2 - 1, '?,') . ")"; $i++; }` – user3557469 Apr 21 '14 at 20:24

PDOException thrown in PHP

1 Answers1