Like Query Using Multiple Keywords from Search Field Using Pdo Prepared Statement

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

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

How to perform a LIKE query using multiple keywords from search field using mysqli prepared statement

As user3783243 states in the comments above, my placeholders and parameters where not matching. So in order to solve that, I did the following (this will be sloppy as I'm new to PHP but if someone can clean it up for me I'll award the answer to that).

First you have to create a string for the type parameter (mine are all strings so this was easy, you could run a conditional statement if you have different types). Since I use two placeholders for each entry in my SQL, each iteration will include two s's.

$typeparam='';
foreach($word as $key => $value){
  $typeparam.='ss';
}

Then create a new array to put the types and the values all together (again, since there are two placeholders for each parameter, I just add the $word twice to the array):

$bindpars=array();

$bindpars[]=&$typeparam;
foreach($word as $key => $value){
  $bindpars[]=&$word[$key];
  $bindpars[]=&$word[$key];
}

Finally, bind the parameters using call_user_func_array:

call_user_func_array(array($stmt,'bind_param'),$bindpars);

So the code in my question now looks like this:

$word=preg_split('/[\s]+/',$terms);
$totalwords=count($word);

$sql="SELECT title,content FROM articles WHERE (title LIKE CONCAT('%',?,'%') OR (content LIKE CONCAT('%',?,'%'))";

for(i=1;$i<$totalwords;$i++){
  $sql.=" AND (title LIKE CONCAT('%',?,'%') OR (content LIKE CONCAT('%',?,'%'))";
}

$stmt=$conn->prepare($sql);

$typeparam='';
foreach($word as $key => $value){
  $typeparam.='ss';
}

$bindpars=array();

$bindpars[]=&$typeparam;
foreach($word as $key => $value){
  $bindpars[]=&$word[$key];
  $bindpars[]=&$word[$key];
}

call_user_func_array(array($stmt,'bind_param'),$bindpars);

$stmt->execute;
$stmt->store_result;

PDO Search same keyword in multiple columns

I solved my own problem like this:

$keywordfromform = $_GET["keyword"];
$keyword = "%$keywordfromform%";
$sql = 'SELECT * FROM table
WHERE author LIKE ? OR title ? OR text LIKE ?';
$stmt = $pdo->prepare($sql);
$stmt->execute(array($keyword, $keyword, $keyword));
$entries = $stmt->fetchAll();

Explanation: I think the problem was, that for each ? I needed to bind a $keyword. And I did know how to combine them. Then I looked it up on php.net and realised that I may just need to add array().

How to make a search form work with multiple fields using PDO prepared statement

You can do it like you did before:

$fields = array('first_name', 'last_name', 'email', 'job', 'country', 'city');
$inputParameters = array();

foreach ($fields as $field) {
    // don't forget to validate the fields values from $_POST
    if (!empty($_POST[$field])) {
        $inputParameters[$field] = '%' . $_POST[$field] . '%';
    }
}

$where = implode(' OR ', array_map(function($item) {
    return "`$item` LIKE :$item";
}, array_keys($inputParameters)));

$search = $db->prepare("SELECT `id`, `name` FROM `users` WHERE $where");
$search->execute($inputParameters);

foreach ($search->fetchAll(PDO::FETCH_ASSOC) as $row) {
    var_dump($row);
}

Using PDO query, without prepared statements, with multiple LIKE statements from multiple HTML input fields

You've forgotten quotes around the $_POST values that you're directly inserting into your queries:

$conditions[] = "$field LIKE CONCAT ('%', '$_POST[$field]', '%')";
                                          ^--            ^--

so while this will fix your immediate problem, you'll still be wide open to sql injection attacks.

How do I create a PDO parameterized query with a LIKE statement?

Figured it out right after I posted:

$query = $database->prepare('SELECT * FROM table WHERE column LIKE ?');
$query->execute(array('value%'));

while ($results = $query->fetch())
{
    echo $results['column'];
}

---

Related Topics