多次使用绑定参数 [英] Use bound parameter multiple times

问题描述

我正在尝试为我的数据库实现一个非常基本的搜索引擎，用户可能会在其中包含不同类型的信息.搜索本身由几个联合选择组成，其中结果总是合并到 3 列中.

I'm trying to implement a pretty basic search engine for my database where the user may include different kinds of information. The search itself consists of a couple of a union selects where the results are always merged into 3 columns.

然而，返回的数据是从不同的表中获取的.

The returning data however is being fetched from different tables.

每个查询都使用 $term 进行匹配，我将它绑定到:term"作为准备好的参数.

Each query uses $term for matchmaking, and I've bound it to ":term" as a prepared parameter.

现在，手册说:

当您调用 PDOStatement::execute() 时，您必须为希望传递给语句的每个值包含一个唯一的参数标记.您不能在准备好的语句中两次使用同名的命名参数标记.

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name twice in a prepared statement.

我认为不是用 :termX(x 表示 term = n++)替换每个 :term 参数，而是必须有更好的解决方案?

I figured that instead of replacing each :term parameter with :termX (x for term = n++) there must be a be a better solution?

或者我只需要绑定 X 个 :termX 吗?

Or do I just have to bind X number of :termX?

编辑发布我的解决方案:

$query = "SELECT ... FROM table WHERE name LIKE :term OR number LIKE :term";

$term = "hello world";
$termX = 0;
$query = preg_replace_callback("/\:term/", function ($matches) use (&$termX) { $termX++; return $matches[0] . ($termX - 1); }, $query);

$pdo->prepare($query);

for ($i = 0; $i < $termX; $i++)
    $pdo->bindValue(":term$i", "%$term%", PDO::PARAM_STR);

好的，这是一个示例.我没有时间做 sqlfiddle，但如果有必要，我稍后会添加一个.

Alright, here is a sample. I don't have time for sqlfiddle but I will add one later if it is necessary.

(
    SELECT
        t1.`name` AS resultText
    FROM table1 AS t1
    WHERE
        t1.parent = :userID
        AND
        (
            t1.`name` LIKE :term
            OR
            t1.`number` LIKE :term
            AND
            t1.`status` = :flagStatus
        )
)
UNION
(
    SELECT
        t2.`name` AS resultText
    FROM table2 AS t2
    WHERE
        t2.parent = :userParentID
        AND
        (
            t2.`name` LIKE :term
            OR
            t2.`ticket` LIKE :term
            AND
            t1.`state` = :flagTicket
        )
)

推荐答案

我已经遇到过几次相同的问题，我想我已经找到了一个非常简单且很好的解决方案.如果我想多次使用参数，我只需将它们存储到 MySQL User-Defined Variable.
这使代码更具可读性，并且您不需要 PHP 中的任何附加功能:

I have ran over the same problem a couple of times now and I think i have found a pretty simple and good solution. In case i want to use parameters multiple times, I just store them to a MySQL User-Defined Variable.
This makes the code much more readable and you don't need any additional functions in PHP:

$sql = "SET @term = :term";

try
{
    $stmt = $dbh->prepare($sql);
    $stmt->bindValue(":term", "%$term%", PDO::PARAM_STR);
    $stmt->execute();
}
catch(PDOException $e)
{
    // error handling
}


$sql = "SELECT ... FROM table WHERE name LIKE @term OR number LIKE @term";

try
{
    $stmt = $dbh->prepare($sql);
    $stmt->execute();
    $stmt->fetchAll();
}
catch(PDOException $e)
{
    //error handling
}

唯一的缺点可能是您需要执行额外的 MySQL 查询 - 但恕我直言，这是完全值得的.
由于 User-Defined Variables 在 MySQL 中是会话绑定的，因此也无需担心变量 @term 在多用户环境中引起副作用.

The only downside might be that you need to do an additional MySQL query - but imho it's totally worth it.
Since User-Defined Variables are session-bound in MySQL there is also no need to worry about the variable @term causing side-effects in multi-user environments.

这篇关于多次使用绑定参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！