多次使用绑定参数 [英] 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替换每个:term参数(x表示term = n ++),必须有一个更好的解决方案?
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?
编辑:为此发布我的解决方案:
Edit Posting my solution to this:
$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屋!