PDO绑定参数取决于查询中是否存在 [英] PDO Bind Params depending on whether they exists in the query

问题描述

让我们说我有一个mysql查询，该查询是根据某些条件构建的:示例

Lets say I have a mysql query which gets built depending on certain conditions: example

$query = "SELECT * from `usertable` where users_active=:users_active";
if($mode=="archived") {
    $query .= " AND archived=:archived";
}

$stmt = $dbpdo->prepare($query);
$stmt->bindParam(':users_active', $users_active);
$stmt->bindParam(':archived', $archived);
$stmt->execute();

现在，如果我运行上述命令，则仅在$ mode =="archived"时才有效，否则命名的占位符:archived"将不会成为查询的一部分.

Now, if I run the above it will only work if $mode=="archived", as otherwise the named placeholder ":archived" will not be part of the query.

从某种意义上说，这对我来说很有意义，但是却引出了如何优雅地处理它的问题.我的应用程序中有很多查询是有条件构建的.我可以这样做，但这对我来说似乎是重复的:

This makes sense to me in one respect but begs the question of how to deal with it elegantly. I have lots of queries in my application which are built conditionally. I could do this, but this seems like duplication to me:

if($mode=="archived") {
    $stmt->bindParam(':archived', $archived);
}

这似乎很漫长，特别是如果有很多条件来构建查询.有没有人对如何做到这一点有更整洁的想法，而不必添加很多条件测试.

This seems long winded, particularly if there are lots of conditions to how the query is built. Does anyone have a neater idea on how to do this without having to add lots of conditional tests.

任何想法都会受到赞赏.

Any thoughts would be appreciated.

致谢

詹姆斯

推荐答案

您可以使用带有值的数组，并将其作为参数发送到

You can use an array with the values and send that as a parameter to the execute() method.

如果bindParam()提供的变量的类型转换不那么重要(您甚至没有使用它...)，这将使构建查询变得容易得多，因为在构建查询时可以填充数组字符串.

If the type casting of the variables that bindParam() offers is not that important (you're not even using it...), that makes building queries a lot easier as you can fill the array when you build the query string.

以您的示例为例:

$query = "SELECT * from `usertable` where users_active=:users_active";
$params = array(':users_active' => $users_active);

if($mode=="archived") {
    $query .= " AND archived=:archived";
    $params[':archived'] = $archived;
}

$stmt = $dbpdo->prepare($query);

$stmt->execute($params);

这篇关于PDO绑定参数取决于查询中是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！