使用PDO准备好的语句从搜索字段中使用多个关键字进行LIKE查询 [英] LIKE query using multiple keywords from search field using PDO prepared statement
问题描述
站点用户使用搜索表单来查询产品数据库.输入的关键字在数据库中搜索产品的标题.
Site users use a search form to query a database of products. The keywords entered search the titles for the products in the database.
public function startSearch($keywords){
$keywords = preg_split('/[\s]+/', $keywords);
$totalKeywords = count($keywords);
foreach($keywords as $key => $keyword){
$search .= '%'.$keyword.'%';
if($key != ($totalKeywords)-1){
$search .= ' AND itemTitle LIKE ';
}
}
$sql=$this->db->prepare("SELECT * FROM prodsTable WHERE itemTitle LIKE ?");
$sql->bindParam(1, $search);
$sql->execute ();
$sql->fetchALL(PDO::FETCH_ASSOC);
如果用户输入单个关键字,则搜索有效,但是如果使用多个关键字,则查询不会执行.
The search works if a user enters a single keyword, but if multiple keywords are used the query does not execute.
如果: $ keywords ='苹果ipod'; $ search ='%apple%AND itemTitle Like%ipod%';
if: $keywords = 'apple ipod'; $search = '%apple% AND itemTitle LIKE %ipod%';
因此,准备好的语句应如下所示:
So the prepared statement should look like this:
从prodsTable中选择*的itemTitle Like%apple%和itemTitle Like%ipod%"
"SELECT * FROM prodsTable WHERE itemTitle LIKE %apple% AND itemTitle LIKE %ipod%"
如果两个产品的标题中同时包含"apple"和"ipod",则没有结果返回.
No results return when two products should return having both "apple" and "ipod" in their titles.
我在做什么错了?
推荐答案
准备好的语句可以防止SQL注入,因此不会解释参数中的sql代码.在调用prepare()之前,您将必须使用正确的AND itemTitle LIKE ?
编号构建sql查询.
Prepared statements protect you from sql injection, so sql code in the parameters will not be interpreted. You will have to build a sql query with the correct number of AND itemTitle LIKE ?
before calling prepare().
$keywords = preg_split('/[\s]+/', $keywords);
$totalKeywords = count($keywords);
$query = "SELECT * FROM prodsTable WHERE itemTitle LIKE ?";
for($i=1 ; $i < $totalKeywords; $i++){
$query .= " AND itemTitle LIKE ? ";
}
$sql=$this->db->prepare($query);
foreach($keywords as $key => $keyword){
$sql->bindParam($key+1, '%'.$keyword.'%');
}
$sql->execute ();
这篇关于使用PDO准备好的语句从搜索字段中使用多个关键字进行LIKE查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!