Problems with PDO bindParam/Value

Question

I am trying to get a query from a database, however, the value I have for bindParam (:name) is not being bound, when I echo $sql and print_r($stmtTwo) the WHERE clause states WHERE :name instead of the string from $wherefinal.

The code I have is:

$sql= "SELECT Species.Species_ID 
    FROM Species 
    JOIN ( 
        SELECT Species.Species_ID, COUNT(*) AS mynum 
        FROM Species_Opt LEFT JOIN Species ON (Species.Species_ID = Species_Opt.SO_Species_ID) 
        WHERE :name
        GROUP BY SO_Species_ID HAVING mynum = 6 
        ) AS mytable ON Species.Species_ID = mytable.Species_ID";
$stmtTwo = $pdo->prepare($sql);
$stmtTwo->bindParam(':name', $wherefinal);
$stmtTwo->execute();

with $wherefinal being defined before the sql statement and being defined as:

$where = "";
foreach ($_POST as $k => $v){
    $where .= "(Species_Opt.SO_Option_ID = $v) OR "; 
};
$wherefinal = substr($where, 0, strrpos($where, " OR "));

And when echoed, $wherefinal displays:

(Species_Opt.SO_Option_ID = 4) OR (Species_Opt.SO_Option_ID = 12) OR (Species_Opt.SO_Option_ID = 17) OR (Species_Opt.SO_Option_ID = 20) OR (Species_Opt.SO_Option_ID = 21) OR (Species_Opt.SO_Option_ID = 32)

$v is from the value of a radio button from a form that is generated via a different SQL statement and smarty.

@Fred-ii-, I believe that OP at least know that it does not work that's why he asked and your duplicated link does not help solving his problem. This link should be a better suggestion: http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition, because his problem can be shorten to `Species_Opt.SO_Option_ID IN (?, ?, ?, ....)` — invisal, Dec 02 '14 at 02:46
@invisal I've reopened it, but if anyone else closes the question based [on your link](http://stackoverflow.com/q/920353/) as another duplicate, it will be out of my control. — Funk Forty Niner, Dec 02 '14 at 02:48
@invisal, your right I did know that what I had does not work, and thanks for the link, however, I am having trouble implementing what you linked. — user2861154, Dec 02 '14 at 03:49

score 0 · Accepted Answer · answered Dec 02 '14 at 04:37

First thing first, you need to construct your placeholder part

$placeholder = str_repeat('?,', count($_POST) - 1) . '?';

Then use it to construct a full SQL query

$sql= "SELECT Species.Species_ID 
       FROM Species 
       JOIN ( 
          SELECT Species.Species_ID, COUNT(*) AS mynum 
          FROM Species_Opt LEFT JOIN Species ON 
               (Species.Species_ID = Species_Opt.SO_Species_ID) 
          WHERE Species_Opt.SO_Option_ID IN ({$placeholder})
       GROUP BY SO_Species_ID HAVING mynum = 6 
       ) AS mytable ON Species.Species_ID = mytable.Species_ID";

After that, you can bind the value to each placeholder

$sh = $pdo->prepare($sql);

$i = 1;
foreach($_POST as $value) {
    $sh->bindValue($i, $value); $i++;
}
$sh->execute();

Problems with PDO bindParam/Value

1 Answers1