PDO绑定参数/值的问题 - Problems with PDO bindParam/Value

Problems with PDO bindParam/Value

本文关键字：问题绑定参数 PDO | 更新日期: 2023-09-27

我正在尝试从数据库中获取查询，但是，当我回显$sql并print_r($stmtTwo) WHERE 子句声明 WHERE :name 而不是来自$wherefinal的字符串时，我为 bindParam (:name) 的值没有绑定。

我拥有的代码是：

$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();

$wherefinal在 SQL 语句之前定义，并定义为：

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

当回显时，$wherefinal显示：

(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来自通过不同的SQL语句生成的表单中的单选按钮的值，并且很聪明。

首先，您需要构造占位符部分

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

然后使用它来构造一个完整的 SQL 查询

$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";

之后，您可以将值绑定到每个占位符

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