PDO - the search asks for the terms as a whole, and not each term separately

Question

I currently have a simple search engine that searches for a column in my database based on user input:

$search = $_GET['search']; $terms = explode(" ", $search); $sql = "SELECT * FROM people WHERE lname LIKE :search"; $q = $conn->prepare($sql) or die("failed!"); $q->bindValue(':search',"%".$search."%",PDO::PARAM_STR); $q->execute(); if ($q){ // do something } 

He is currently looking for terms in general, for example, a “red table” returns a result, but a “red table” returns nothing

Any ideas? Any help is much appreciated!

EDIT:

Since then I changed it to this ...

  $search = $_GET['search']; $terms = explode(" ", $search); $sql = "SELECT * FROM people WHERE MATCH (lname,fname) AGAINST (:search IN BOOLEAN MODE)"; $q = $conn->prepare($sql) or die("failed!"); $q->bindValue(':search',"%".$search."%",PDO::PARAM_STR); $q->execute(); 

it seems to be working fine now, if someone can offer a better solution, I would be very grateful!

Answer 1

This may not be the best way, but I think something like this can do the job. I would really like to know if there is a smarter way to do this!

 $search = $_GET['search']; $terms = explode(" ", $search); $sql = "SELECT * FROM people" if(count($terms) > 0) { $sql .= " WHERE (lname LIKE '%:term_0%' OR fname LIKE '%:term_0%')"; for($i = 1; $i < count($terms); $i++) $sql .= " AND (lname LIKE '%:term_" . $i . "%' OR fname LIKE '%:term_" . $i . "%')"; } $q = $conn->prepare($sql) or die("failed!"); for($i = 0; $i < count($terms); $i++) $q->bindValue(':term_' . $i, $terms[$i]); $q->execute();