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?
Advertisement
Answer
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 ();