問題描述

我正在嘗試使用 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 * 
"
        . "FROM `help_article` 
"
        . "WHERE `name` LIKE '%how%'
"
        . "";
$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 * 
"
        . "FROM `help_article` 
"
        . "WHERE `name` LIKE '%:term%'
"
        . "";
$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的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！