PHP PDO准备的语句-MySQL LIKE查询 [英] PHP PDO prepared statement -- MySQL LIKE query
问题描述
我正在尝试通过php的PDO类(mysql驱动程序)进行搜索.我有以下与MySQL客户端一起使用的查询(更改了表名以保护无辜的人):
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):
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;
无论我使用什么搜索词,它都像一个符咒一样工作.但是,当我转到php时,无法获得返回任何内容的信息.我尝试了几种看起来合乎逻辑的语法,但是没有尝试过.这是我现有的代码:
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:
$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();
}
我也尝试了以下操作(SQL WHERE子句& prep->执行行已全部更改):
I've tried the following as well (SQL WHERE clause & prep->execute lines are all that change):
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.'%'));
等...
推荐答案
$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.
这篇关于PHP PDO准备的语句-MySQL LIKE查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!