PHP PDO 准备语句——MySQL LIKE 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1786436/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PHP PDO prepared statement -- MySQL LIKE query
提问by TIm
I am trying to do a search through php's PDO class (mysql driver). I have the following query working with the MySQL client (table names changed to protect the innocent):
我正在尝试通过 php 的 PDO 类（mysql 驱动程序）进行搜索。我有以下与 MySQL 客户端一起使用的查询（表名已更改以保护无辜者）：
SELECT hs.hs_pk,
hs.hs_text,
hs.hs_did,
hd.hd_did,
hd.hd_text,
hv.hv_text,
hc.hc_text
FROM hs
LEFT JOIN hd
ON hs.hs_did = hd.hd_did
LEFT JOIN hd
ON hd.hd_vid = hv.hv_id
LEFT JOIN hc
ON hd.hd_pclass = hc.hc_id
WHERE hs.hs_text LIKE "%searchTerm%"
LIMIT 25;
This works like a charm regardless of the search term that I use. However, when I move to php, I can't get it to return anything. I have tried several different syntaxes that seem logical to work, but nothing I have tried works. here's my existing code:
无论我使用什么搜索词，这就像一个魅力。但是，当我转向 php 时，我无法让它返回任何东西。我尝试了几种似乎合乎逻辑的不同语法，但我尝试过的任何语法都不起作用。这是我现有的代码：
$handle = fopen('/foo/bar/test.log', 'w+');
fwrite($handle, "doSearch, with search term: $searchTerm\n");
$sql =
'SELECT hs.hs_pk,
hs.hs_text,
hs.hs_did,
hd.hd_did,
hd.hd_text,
hv.hv_text,
hc.hc_text
FROM hs
LEFT JOIN hd
ON hs.hs_did = hd.hd_did
LEFT JOIN hd
ON hd.hd_vid = hv.hv_id
LEFT JOIN hc
ON hd.hd_pclass = hc.hc_id
WHERE hs.hs_text LIKE :searchTerm
LIMIT 25';
try {
$dbh = new PDO('mysql:host=localhost;dbname=awdb', "user", "password");
fwrite($handle, "connected to DB\n");
$prep = $dbh->prepare($sql);
$ret = $prep->execute(array(':searchTerm' => '"%'.$searchTerm.'%"'));
while ($row = $prep->fetch(PDO::FETCH_ASSOC)) {
$i++;
$result[$i]['subText'] = $row['hs_pk'];
$result[$i]['subText'] = $row['hs_text'];
$result[$i]['subDid'] = $row['hs_did'];
$result[$i]['devDid'] = $row['hd_did'];
$result[$i]['devText'] = $row['hd_text'];
$result[$i]['vendorText'] = $row['hv_text'];
$result[$i]['classText'] = $row['hc_text'];
}
$dbh = null;
}
catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
I've tried the following as well (SQL WHERE clause & prep->execute lines are all that change):
我也尝试了以下操作（SQL WHERE 子句和 prep->execute 行都发生了变化）：
WHERE hs.hs_text LIKE CONCAT(\'%\', ?, \'%\')
$ret = $prep->execute(array($searchTerm));
WHERE hs.hs_text LIKE "%:searchTerm%"
$ret = $prep->execute(array(':searchTerm' => $searchTerm));
WHERE hs.hs_text LIKE ":searchTerm"
$ret = $prep->execute(array(':searchTerm' => '%'.$searchTerm.'%'));
etc...
等等...
回答by troelskn
$ret = $prep->execute(array(':searchTerm' => '"%'.$searchTerm.'%"'));
This is wrong. You don't need the double quotes.
这是错误的。你不需要双引号。
WHERE hs.hs_text LIKE ":searchTerm"
$ret = $prep->execute(array(':searchTerm' => '%'.$searchTerm.'%'));
This is also wrong. Try with:
这也是错误的。尝试：
$prep = $dbh->prepare($sql);
$ret = $prep->execute(array(':searchTerm' => '%'.$searchTerm.'%'));
Explanation: Prepared statements don't simply do a string-replace. They transport the data completely separate from the query. Quotes are only needed when embedding values into a query.
说明：准备好的语句不只是简单地进行字符串替换。它们传输与查询完全分开的数据。仅在将值嵌入到查询中时才需要引用。
回答by Micah
$prep = $dbh->prepare($sql);
$ret = $prep->execute(array('searchTerm' => $searchTerm));
回答by TIm
Well, I solved this one. And quite frankly, I'm an idiot... Thank you all for seeing this and giving good feedback. The problem was a typo in a table name (which I changed, so nobody here would be able to see my issue to begin with...). The suggestions did lead me to find the issue, though, so thank you adam, jkndrkn and troelskn.
嗯，我解决了这个问题。坦率地说，我是个白痴...感谢大家看到这一点并提供良好的反馈。问题是表名中的拼写错误（我更改了该名称，因此这里没有人能够从一开始就看到我的问题......）。不过，这些建议确实让我找到了问题所在，所以谢谢 adam、jkndrkn 和 troelskn。
For the record, the following combination works well:
作为记录，以下组合效果很好：
WHERE aw_hcl_subdevices.hs_text LIKE CONCAT(\'%\', ?, \'%\')
$ret = $prep->execute(array($searchTerm));
