LIKE query using multiple keywords from search field using PDO prepared statement

Question

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.

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%"

No results return when two products should return having both "apple" and "ipod" in their titles.

What am I doing wrong?

`"SELECT * FROM prodsTable WHERE itemTitle LIKE '%apple%' AND itemTitle LIKE '%ipod%'"`. Is your like pattern quoted? — Reflective, Nov 04 '12 at 00:13
in the foreach statement it is '%'.$keyword.'%' which returns the prepared statement of: SELECT * FROM prodsTable WHERE itemTitle LIKE %apple% That works. However, when adding a second or third keyword where the $search variable becomes: '%'.$keyword.'%' AND itemTitle LIKE --> giving the prepared statement of: SELECT * FROM prodsTable WHERE itemTitle LIKE %apple% AND itemTitle LIKE %ipod% Doesn't work. Adding more than one keyword isn't providing results. I've tried it as: itemTitle LIKE '%aple%' but that doesn't work either. My original prepared without the quotes works. — Mike Jones, Nov 04 '12 at 00:46
Once keyword search works. More than one doesn't. This is the exact statement that is executed, but no results. SELECT * FROM prodsTable WHERE itemTitle LIKE %apple% AND itemTitle LIKE %ipod% — Mike Jones, Nov 04 '12 at 00:56

score 8 · Accepted Answer · edited Feb 02 '21 at 18:18

8

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 ();

edited Feb 02 '21 at 18:18

Your Common Sense

156,878
40
214
345

answered Nov 04 '12 at 00:54

Terje D.

6,250
1
22
30

1

Thank you! I had to make the following changes since it wouldn't let me pass by reference. foreach($keywords as $key => $keyword){ $search = '%'.$keyword.'%'; $sql->bindParam($key+1, $search); } Very thankful for your help and I appreciate your explanation too. – Mike Jones Nov 04 '12 at 01:18
This answer deserves more credit... Saved my life! :) – ArabianMaiden Mar 16 '19 at 21:49

LIKE query using multiple keywords from search field using PDO prepared statement

1 Answers1

Linked

Related