PDO BindParam won't bind on while loop every time

Question

I am connecting to an SQL Server DB via PDO in PHP. My question is why can I not bind parameters to named place markers every time a while loop runs? Here's the code:

    public function search($search, $field)
{
    $c = new PDO("sqlsrv:Server=localhost;Database=$this->_db", $this->_user, $this->_pass);

    $sql = "
    SELECT Title 
    FROM Table WHERE 1=1"; 
    //form array of search terms stored in serachArray variable
    $searchArray = explode(' ', $search );
    //count objects in array
    $num_search_terms = count( $searchArray );
    $i = 0;//for search term array (starts at 0)
    $x = 1;//for parameter incrementin (starts at 1)
    while( $i <= ( $num_search_terms -1 ) )
    {
        $sql .= "
        AND $field LIKE :s".$x;
        $stmt = $c->prepare( $sql );

        $currTerm = "%" . $searchArray[ $i ] . "%";

        $stmt->bindParam( ':s'.$x, $currTerm );         

        $i++;
        $x++;
    }

    $stmt->execute();

    while ( $row = $stmt->fetch( PDO::FETCH_OBJ ) )
    {
       print $row->Title;
    }
}

The result set is returning empty with no errors. There should definitely be data returned from this search. When I only enter one search term it works fine. However when I enter more than one I always get no results.

you're building it wrong. you should build the query string in its entirety, THEN bind the parameters. — Marc B, Dec 04 '12 at 15:53

Álvaro González · Accepted Answer · 2012-12-04T15:59:06.063

6

You are preparing each incomplete query fragment inside the loop. You should only prepare once and you should prepare the complete SQL query.

Update: Not sure where you looked for documentation, but the bindParam() manual page has several examples:

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();
?>

You won't get anything useful if you do this inside the loop:

$stmt->bindParam( ':s'.$x, $currTerm );

... but you remove and create a new $stmt object right before.

edited Dec 04 '12 at 15:59

answered Dec 04 '12 at 15:53

Álvaro González

142,137
41
261
360

Thanks. So can I then bind multiple parameters at one time? I'm struggling to find documentation on this... – Kelsey Thorpe Dec 04 '12 at 15:56
Oh right I saw that yes, but I didn't want to write out every one individually. Is there anyway to loop it to find all params and do it automatically? so it could automatically do ':s1',$s1 etc.? – Kelsey Thorpe Dec 04 '12 at 16:01
You have to call `PDO::prepare` *once* to create *one* `PDOStatement` object and them bind *all* your parameters to that only object. I have the impression that I'm not being understood. Is there any specific part in my explanation I can improve? – Álvaro González Dec 04 '12 at 16:17
1

You can bind in a loop. Take a look here: http://stackoverflow.com/questions/13333006/insert-large-amount-of-variables-into-table-using-pdo/13333344#13333344 and here: http://stackoverflow.com/questions/13667807/pdo-multiple-named-placeholders-doesnt-retrieve-data/13668536#13668536 – Jonathan Spiller Dec 04 '12 at 16:18
Thanks Alvaro your explanation was good. Jonathan those links are exactly what I was looking for, thanks. – Kelsey Thorpe Dec 04 '12 at 16:39

PDO BindParam won't bind on while loop every time

1 Answers1