我正在尝试构建逻辑,以创建一个用于PDO的多字LIKE语句。
这需要搜索字符串$str来构建LIKE部分的多个部分:
$str = $_POST['str'];
$keywords = preg_split('/['s]+/', $str);
$totalKeywords = count($keywords);
$search = "%$str%";
$sql_str = " AND post_content LIKE :search0 ";
for($i=1 ; $i < $totalKeywords; $i++){
$search_bit = ":search" . $i;
$sql_str .= " AND post_content LIKE $search_bit ";
}
这是SQL语句,$SQL_str插入正确的点:
$sql = "SELECT d.ID
, d.post_date
, d.post_content
, d.post_cat_id
, d.post_label
, c.fld_cat
FROM tbl_log_days d
, tbl_log_cats c
WHERE d.post_cat_id = c.fld_id " . $sql_str . "
ORDER BY post_date";
然后,为了绑定变量,我尝试了两种方法:
$stmt = $pdo->prepare($sql);
if (!empty($sql_str)) {
foreach ($keywords as $key => &$keyword){
$foo = '%'.$keyword.'%';
$stmt->bindParam(':search' . $key, $foo);
}
}
还有这个(foreach行中$关键字之前没有"与"符号):
$stmt = $pdo->prepare($sql);
if (!empty($sql_str)) {
foreach ($keywords as $key => $keyword){
$foo = '%'.$keyword.'%';
$stmt->bindParam(':search' . $key, $foo);
}
}
然而,当我搜索例如"过去的山丘"并检查实际运行的SQL时(我在MySQL中启用了查询日志记录),它只接受搜索字符串中的最后一个词:
SELECT d.ID
, d.post_date
, d.post_content
, d.post_cat_id
, d.post_label
, c.fld_cat
FROM tbl_log_days d
, tbl_log_cats c
WHERE d.post_cat_id = c.fld_id AND post_content LIKE '%past%' AND post_content LIKE '%past%'
ORDER BY post_date
我在运行搜索时对$keyword变量进行了var_dump,它返回:
string(4) "hill"
string(4) "past"
我解决不了这个问题。有可能做我想做的事吗?
我会做一些类似的事情
for($i=1 ; $i < $totalKeywords; $i++){
$search_num = "search" . $i;
$search_bit = ":" . $search_num;
$sql_str .= " AND post_content LIKE $search_bit ";
$foo = '%'.$keyword.'%';
$V[$search_bit] = $foo;
}
$query = $pdo->prepare($sql);
$query->execute($V);
(我还没有测试过这个代码,所以请原谅拼写错误。)
我在Sitepoint上问了同样的问题:https://www.sitepoint.com/community/t/multi-word-like-prepared-statement-for-pdo-query/223738/5
在那里得到了一个解决方案:
$stmt = $pdo->prepare($sql);
if (!empty($sql_str)) {
for ($x = 0; $x<$totalKeywords; $x++) {
// add the percent signs, or make a new copy of the array first if you want to keep the parameters
$keywords[$x] = "%" . $keywords[$x] . "%";
$stmt->bindParam(':search' . $x, $keywords[$x]);
}
}