#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3

    Question 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:

    PHP 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).
    Last edited by we5inelgr; June 26th, 2013 at 09:26 PM.
  2. #2
  3. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,907
    Rep Power
    6351
    PHP Code:
    array($stringOfParms
    That creates an array with one element: the STRING of comma-delimited parameters. You want to use the bind array from earlier.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3
    Thanks for the reply. I've changed the execute line to (without changing anything else):

    PHP Code:
    $stmt->execute(array($bindArray)); 
    And get the same exact error. Ideas why?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,987
    Rep Power
    375
    maybe try this example on that page?

    PHP Code:

    <?php
    /* Execute a prepared statement by binding PHP variables */
    $calories 150;
    $colour 'red';
    $sth $dbh->prepare('SELECT name, colour, calories
        FROM fruit
        WHERE calories < ? AND colour = ?'
    );
    $sth->bindParam(1$calories);
    $sth->bindParam(2$colour);
    $sth->execute();
    ?>
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    your $bindArray already is an array. When you wrap it in another array, you end up with an array containing only one element:

    PHP Code:
    $stmt->execute(array($bindArray)); 
    You wanna pass $bindArray

    PHP Code:
    $stmt->execute($bindArray); 
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  10. #6
  11. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,907
    Rep Power
    6351
    You JUST want to use the bind array, don't wrap it in anything else.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3
    oh geez.

    PHP Code:
    $stmt->execute($bindArray); //no array($bindArray) 
    that works now. sorry for being so obtuse

    many thanks to all who helped!

    Comments on this post

    • Jacques1 disagrees : Please don't close your threads. Often times, other users have additional comments and remarks. They can post them when you lock the topic.
    • ManiacDan agrees : Balancing Jacques.

IMN logo majestic logo threadwatch logo seochat tools logo