php - 如何使用 mysql PDO 和 OR 表达式计算字段数量？

Question

无法计算结果数来统计 mySQL 表(php/mysqlPDO)中的字段。

我面临的挑战是“旋转”字段可以有多个值。我会将“rotation”字段中的所有结果加在一起，对于名为“assignedRad”的特定用户，这些结果等于 29、30、31 或 32。

这是我的代码。错误是

Invalid parameter number: number of bound variables does not match number of tokens

$assignedRad=8; // this is the USER ID
// count vacation tally
$rotation=29;
$rotationoff=30;
$rotationvacay=31;
$rotationvacaytwo=32;                                       
$sql="SELECT COUNT(id) FROM my_table WHERE (rotation = :rotation OR rotation = :rotationoff OR rotation = :rotationvacay OR rotation = :rotationvacaytwo AND assignedRad = :assignedRad)";
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':rotation', $rotation);
$stmt->bindParam(':rotation', $rotationoff);
$stmt->bindParam(':rotation', $rotationvacay);
$stmt->bindParam(':rotation', $rotationvacaytwo);                                       
$stmt->bindParam(':assignedRad', $assignedRad);                                     
$stmt->execute();
$number_of_rows = $stmt->fetchColumn();
echo $number_of_rows;   // this value should be the SUM of the total number of times 'rotation' has an occurrence when it equals 29, 30, 31 or 32

SELECT 语句显示字段数与变量数相同。查询此内容的最有效的代码方式是什么？

Answer 1

您在 bindParam 语句中没有使用正确的参数名称。它们必须与查询中使用的相匹配。

我认为你在设置 OR 后错过了右括号

$sql="SELECT COUNT(id) 
        FROM my_table 
        WHERE (    rotation = :rotation 
                OR rotation = :rotationoff 
                OR rotation = :rotationvacay 
                OR rotation = :rotationvacaytwo
            )
           AND assignedRad = :assignedRad";
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':rotation', $rotation);
$stmt->bindParam(':rotationoff', $rotationoff);
$stmt->bindParam(':rotationvacay', $rotationvacay);
$stmt->bindParam(':rotationvacaytwo', $rotationvacaytwo);                                       
$stmt->bindParam(':assignedRad', $assignedRad);    

或者按照 @Chris85 的建议，稍微更改查询以使用 IN 子句

$sql="SELECT COUNT(id) 
        FROM my_table 
        WHERE rotation IN (:rotation,:rotationoff,:rotationvacay,:rotationvacaytwo)
           AND assignedRad = :assignedRad";

$stmt = $dbh->prepare($sql);
$stmt->bindParam(':rotation', $rotation);
$stmt->bindParam(':rotationoff', $rotationoff);
$stmt->bindParam(':rotationvacay', $rotationvacay);
$stmt->bindParam(':rotationvacaytwo', $rotationvacaytwo);                                       
$stmt->bindParam(':assignedRad', $assignedRad);