将命名参数与PDO一起用于LIKE [英] Using named parameters with PDO for LIKE
问题描述
我正在尝试使用LIKE
在数据库中搜索name
字段.如果我这样手工制作SQL:
I am trying to search the name
field in my database using LIKE
. If I craft the SQL 'by hand` like this:
$query = "SELECT * \n"
. "FROM `help_article` \n"
. "WHERE `name` LIKE '%how%'\n"
. "";
$sql = $db->prepare($query);
$sql->setFetchMode(PDO::FETCH_ASSOC);
$sql->execute();
然后它将返回有关'how'的相关结果.
但是,当我将其转换为准备好的语句时:
Then it will return relevant results for 'how'.
However, when I turn it into a prepared statement:
$query = "SELECT * \n"
. "FROM `help_article` \n"
. "WHERE `name` LIKE '%:term%'\n"
. "";
$sql->execute(array(":term" => $_GET["search"]));
$sql->setFetchMode(PDO::FETCH_ASSOC);
$sql->execute();
我总是得到零结果.
我做错了什么?我在代码的其他地方使用了准备好的语句,它们可以正常工作.
What am I doing wrong? I am using prepared statements in other places in my code and they work fine.
推荐答案
绑定的:placeholders
不能用单引号引起来.这样,它们就不会被解释,而是被当作原始字符串处理.
The bound :placeholders
are not to be enclosed in single quotes. That way they won't get interpreted, but treated as raw strings.
当您要使用一个作为LIKE
模式时,然后将%
与值一起传递:
When you want to use one as LIKE
pattern, then pass the %
together with the value:
$query = "SELECT *
FROM `help_article`
WHERE `name` LIKE :term ";
$sql->execute(array(":term" => "%" . $_GET["search"] . "%"));
哦,实际上,您首先需要在此处清除输入字符串(addcslashes).如果用户在参数内提供任何多余的%
字符,则它们将成为LIKE匹配模式的一部分.请记住,整个:term
参数都作为字符串值传递,并且该字符串中的所有%
都成为LIKE子句的占位符.
Oh, and actually you need to clean the input string here first (addcslashes). If the user supplies any extraneous %
chars within the parameter, then they become part of the LIKE match pattern. Remember that the whole of the :term
parameter is passed as string value, and all %
s within that string become placeholders for the LIKE clause.
这篇关于将命名参数与PDO一起用于LIKE的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!