• PHP Help PHP Coding

  • [RESOLVED] Why this error: Invalid parameter number: number of bound variables does not match nu

Hi all,

I'm trying to create a prepared dynamic select statement using PDO, and it appears to me that I've got the right number of variables to parameters.

The input:

$var1 is not null
$var2 is not null
$var3 is not null
$var4 is not null
$var5 is null

The code:

//**** Begin building the query statement. ****//
$buildSTMT = "SELECT * FROM Table1 WHERE ";
$bindArray = array();  //**** Create an empty array to add to, for the execute statement, when variables are found.

if ($var1 != "") {
    $buildSTMT .= "Col1 = ? ";
    $bindArray[] = $var1;
}

if ($var2 != "") {  

    $buildSTMT .= "AND Col2 = ?";
    $bindArray[] = $var2;
}

if ($var3 != "") { 
    $buildSTMT .= " AND Col3 = ?";
    $bindArray[] = $var3;
}

if ($var4 != "") {
    $buildSTMT .= " AND Col4 = ?";
    $bindArray[] = $var4;
}

if ($var5 != "") {
    $buildSTMT .= " AND Col5 = ?";
    $bindArray[] = $var5;
}


$stringOfParms = implode (", ", $bindArray);

echo "bind array count: ".count($bindArray)."<p>";  //output shows 4.
echo "string: ".$stringOfParms."<p>";  //string echos 4 items separated by commas "string: apple, banana, carrot, dragonfruit"
echo "built: ".$buildSTMT."<p>"; //echos "built: SELECT * FROM Table1 WHERE Col1 = ? AND Col2 = ? AND Col3 = ? AND Col4 = ?

$stmt = $connection->prepare($buildSTMT);

$stmt->execute(array($stringOfParms));  //This is line #118 the error is occuring on.

The error:

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in [blah] on line 118

Why this error?

b.t.w. I'm trying to replicate Example #3 here: http://www.php.net/manual/en/pdostatement.execute.php

(which doesn't show a "bind" step).

    The example that you looked at shows a way to bind parameters to placeholders that are in an IN clause. Hence, the implode was used on the placeholders to form the SQL statement. What you did was use implode in the parameter variables instead.

    Rather, you can just write:

    $stmt = $connection->prepare($buildSTMT);

$stmt->execute($bindArray);

      Thanks for the reply. I've changed the execute line to (without changing anything else):

      $stmt->execute(array($bindArray));

      And get the same exact error. Ideas why?

        Because you ended up passing an array of a single element to execute, instead of an array of the desired number of elements.

          oh geez.

          $stmt->execute($bindArray); //no array($bindArray)

          that works now. sorry for being so obtuse :rolleyes:

          many thanks!

            Write a Reply...