問題描述

網站用戶使用搜索表單來查詢產品數據庫.輸入的關鍵字搜索數據庫中產品的標題.

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:

SELECT * FROM prodsTable WHERE itemTitle LIKE %apple% AND 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->bindValue($key+1, '%'.$keyword.'%');
  }
  $sql->execute ();

這篇關于使用 PDO 準備好的語句使用搜索字段中的多個關鍵字進行 LIKE 查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！