使用PHP PDO进行动态查询 [英] Dynamic queries with PHP PDO

问题描述

我试图弄清楚如何将我的历史记录脚本从mysql_query()转换为PDO.我有一个带有4个输入字段的表格，您可以随机选择.这意味着可以根据要获取的信息选择0、1、2、3、4个字段.

I'm trying to figure out how to convert my history script from mysql_query() to PDO. I have a form with 4 input fields which you can randomly select. Which means there can be 0, 1, 2, 3, 4 fields selected depending on which info you're trying to get.

我试图像这样查询数据库:

I've tried to query db like this:

$q = $db->prepare('SELECT date,
                          name,
                          action
                   FROM history
                   WHERE name = :name
                   AND action = :action');

$q->bindParam(':name', $Name, PDO::PARAM_STR, 20);
$q->bindParam(':action', $Action, $PDO::PARAM_STR, 20);
$q->execute();

但是如果我没有选择任何字段并且想要显示整个历史记录，这将不起作用.

But this doesn't work if I don't have any fields selected and want the whole history shown.

使用mysql_query()我可以做到这一点:

With mysql_query() I'd just do this:

mysql_query('SELECT date,
                    name,
                    action
             FROM history
             $Name
             $Action');

这意味着如果没有$ Name或$ Action，它们根本就不会包含在查询中.

Which means if there's no $Name or $Action they're simply not included in the query.

我应该只将旧查询复制/粘贴到$ q = $ db-query('')吗?但这违反了使用PDO的目的.

Should I just copy/paste the old query into $q = $db-query('')? But that kind of defeats the purpose of using PDO.

推荐答案

您始终可以将默认值分配给符合列名称的参数.

You could always assign default values to the params which conform to the column names.

这样，您的查询在默认情况下将以where column = column结尾，并且当存在值时将为where column = value.

That way your query will in the default case end up as where column = column and when there is a value present it will be where column = value.

当然，我的逻辑略有缺陷，因为bindParam不能那样工作.相反，您应该根据设置的参数逐步构建语句.

Of course, my logic was slightly flawed, since bindParam does not work that way. Instead, you should incrementally build your statement according to the params set.

/* Start with the most general case for the sql query.
 * The where part always evaluates to true and will thus
 * always return all rows and exists only to make appending
 * further conditions easier.
 */

$q = 'SELECT date, name, action FROM history WHERE 1';

/* Prepare a params array in any way you wish. A loop might be more
 * efficient if it is possible, but since in this example you have
 * only 2 variables, it didn't seem necessary 
 */

$params = array();
if (! empty($Name)) {
    $params['name'] = $Name;
}

if (! empty($Action)) {
    $params['action'] = $Action;
}

/* When the params array is populated, complete the sql statement by
 * appending the param names joined with ANDs 
 */

foreach ($params as $key => $value) {
    $q .= sprintf(' AND `%s` = :%s', $key, $key);
}

/* When the query is complete, we can prepare it */
$stmt = $db->prepare($q);

/* Then bind the values to the prepared statement 
 */

foreach ($params as $key => $value) {
    // Using bindValue because bindParam binds a reference, which is
    // only evaluated at the point of execute
    $stmt->bindValue(':'.$key, $value);
}

/* Now we're ready to execute */
$stmt->execute();

在此示例中，可以在完成sql语句的循环中完成empty检查，但这将为您提供一个不太通用的示例.

In this example, the empty check could've been done in the loop where we complete the sql statement, but that would've given you a less general example.

此示例还省略了bindValue的类型param，但这将很容易实现，例如通过将数组值更改为具有成员类型的对象或数组，或者通过在赋值循环内进行鸭子输入.

This example also leaves out the type param to bindValue, but that would be easily implemented, e.g. by changing the array value to an object or array having the type as a member, or by duck typing inside the assigning loop.

只要您提供初始查询(一般情况下)以及params数组，就可以轻松地将查询构建以这种形式放入可以满足所有数据库查询需求的函数中.

The query building could in this form easily be put in a function that would work for all your database querying needs, as long as you provide it the initial (general case) query along with the params array.

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