PHP PDO动态WHERE子句 [英] PHP PDO dynamic WHERE clause

问题描述

我有一个简单的函数，可以根据某些条件从数据库表返回计数.

I have a simple function that returns a count from a database table, based on some criteria.

function MyCount($strTable, $strCriteria) {
    $strSQL = "SELECT COUNT(*) FROM " . $strTable . " ";
    if (trim($strCriteria) != "") $strSQL .= "WHERE " . $strCriteria;
    $results = mysql_query($strSQL, $objConn);
    $row = mysql_fetch_array($results);
    return $row[0];
}

对于在一行代码中快速获取值非常有用，例如:

Its very useful for quickly getting a value in 1 line of code, e.g:

$Users = MyCount("Users", "Deleted = 0");

但是，我现在正尝试使用PDO，并且在将参数值作为参数传递时遇到了麻烦.我正在尝试执行以下操作(无效):

However, I'm now trying to move to PDO and am having trouble passing in the were as parametrized values. I'm trying to do something like the below (which doesn't work):

$objQuery=$objConn->prepare("SELECT count(*) as TheCount FROM :table_name WHERE :criteria");
$objQuery->bindParam(':table_name', $strTable);
$objQuery->bindParam(':criteria', $strCriteria);

我想显而易见的是:

$objQuery=$objConn->prepare("SELECT count(*) as TheCount FROM :table_name WHERE ".$strCriteria");
$objQuery->bindParam(':table_name', $strTable);

但是，这似乎与参数化价值观的精神背道而驰……还有人有其他建议吗?

But, this seems to go against the spirit of parametrized values... does anyone have any other suggestions?

谢谢

推荐答案

这是问题所在:

$objQuery->bindParam(':table_name', $strTable);

您只能绑定PDO中的值(field= :value)，而不能绑定表名称或列名称或自定义动态where子句.

You can only bind values ( field= :value) in PDO you cannot bind table names or column names or custom dynamic where clause.

因此，您只需手动构建查询:

So you just build the query manually:

SELECT count(*) as TheCount FROM `$strTable` WHERE $strCriteria

---

function my_count($strTable, $strCriteria, $objConn)
{
    $sql ="SELECT count(*) as TheCount FROM $strTable WHERE $strCriteria";
    $objQuery=$objConn->query($sql);
    $row =$objQuery->fetch();

    return $row['TheCount'];
}


$Users = my_count("Users", "Deleted = 0",  $objConn);

这篇关于PHP PDO动态WHERE子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！