Error with bindParam overwriting in PHP

Question

This is a bit of a weird one, and I could well be coding this completely wrong - hence why I've hit the same error twice in two days, in completely different parts of a script. The code I'm using is below:

    public function findAll( $constraints = array() ) {

     // Select all records
     $SQL = 'SELECT * FROM ' . $this->tableName;

     // See if there's any constraints
     if( count( $constraints ) > 0 ) {
      $SQL .= ' WHERE ';

      foreach( $constraints as $field => $value ) {
       $SQL .= $field . ' = :' . $field . ' AND ';
      }

     }

     // Remove the final AND and prepare the statement
     $SQL = substr( $SQL, 0, -5 );  
     $PDOStatement = $this->PDO->prepare( $SQL );

     // Loop through constraints and bind parameters
     foreach( $constraints as $field => $value ) {
      print 'Binding ' . $field . ' to ' . $value . ' 
';
      $PDOStatement->bindParam( $field, $value );
     }

     $PDOStatement->execute();
     var_dump($PDOStatement);  

     while ( $results = $PDOStatement->fetch( PDO::FETCH_ASSOC ) ) {

      var_dump($results);

     }

    }

I'm pretty new to using PDO, but basically I'm attempting to pass an array of constraints e.g.

array( 'active' => 1, 'name' => 'James' )

and return all rows from the table

WHERE active = 1 AND name = 'James'

If I use this array, the SQL executed from the first

var_dump( )

is

SELECT * FROM {table} WHERE active = :active AND name = 'James'

- exactly as I expect. The bound parameters prints 'Binding active to 1' and 'Binding name to James' - exactly as expected. The rows exist in the database, and yet the second

var_dump()

call for $results outputs nothing - i.e. no rows are returned.

If I pass an array of a single constraint, e.g.

array( 'active' => 1 )

, this works perfectly fine. It appears to be whenever multiple constraints are passed that it stops working.

Answer 1

That's because bindParam works by binding to a variable, and you are re-using the variable ($value) for multiple values. Try with bindValue instead.

Or even better yet; Pass the values as an array to execute instead. This makes the statement stateless, which is generally a good thing in programming.

Answer 2

Thanks very much!