CODEWITHSUNDEEP
phpmysqlpdo
Moussa El-hallak
Moussa El-hallak

Reputation: 7

Searching Mysql database with multiple keywords

I am trying to search a database for one or multiple keywords passed in $_POST['search'], I am not able to loop through the keywords.

The code was modified from https://code-boxx.com/php-mysql-search/

My script is:

$str = $_POST['search'];
$keywords = preg_split('/[\s]+/', $str);
$totalKeywords = count($keywords);
$query = "";
$i = 0;
while($keywords[i] != null)
{
    $query .= " AND name LIKE %".$keywords[i]."%" ;
}
$stmt = $pdo->prepare("SELECT * FROM MYTABLE WHERE MATCH (name) AGAINST (?) ". $query ." ");
$stmt->execute([$_POST['search']]);
$results = $stmt->fetchAll();

Ideally the search should look like:

SELECT * FROM MYTABLE WHERE MATCH (name) AGAINST (Keyword1 Keyword2 Keyword3) AND name LIKE '%Keyword1%' AND name LIKE '%Keyword2%' AND name LIKE '%Keyword3%

The search page:

<?php
if (isset($_POST['search'])) {
  require "2.php";
}

?>
<!DOCTYPE html>
<html>
  <body>
    <!-- [SEARCH FORM] -->
    <form method="post">
      <input type="text" name="search" required/>
      <input type="submit" value="Search"/>
    </form>
    <!-- [SEARCH RESULTS] -->
    <?php
    if (isset($_POST['search'])) {
      if (count($results) > 0) {
        echo $sql;
        foreach ($results as $r) {
          printf("<div>%s</div>", $r['name']);
        }
      } else {
        echo "No results found";
      }
    }
    ?>
  </body>
</html>

Appreciate any help.

Upvotes: 0

Views: 243

Answers (1)

Kevin
Kevin

Reputation: 41885

When you use prepared statements, avoid directly injecting your values into the query statement. You'll defeat the purpose of having them prepared.

So, first you can treat you statement into two parts.

One is the match against, and two is the where like part.

So first build off the base query:

$sql = "SELECT * FROM MYTABLE WHERE 1=1"; // base query

From there you can append the match against and where like clauses as you go along with the construction.

Then, create the match against bit:

MATCH (name) AGAINST (Keyword1* Keyword2* Keyword3* IN BOOLEAN MODE) // the ideal query
MATCH (name) AGAINST (? ? ? IN BOOLEAN MODE) // convert to question mark placeholders

To create it, just simply implode (glue) the question marks with spaces according to the number of input:

$against_placeholder = implode(' ', array_fill(0, $totalKeywords, '?')); // = ? ? ?

And to create the where like clause:

(name LIKE ? OR name LIKE ? OR name LIKE ?) // = like keyword1 or like keyword2 or like keyword3

So in your code:

$like_placeholder = implode(' OR ', array_fill(0, $totalKeywords, 'name LIKE ?'));

So to piece them all together:

$against_placeholder = implode(' ', array_fill(0, $totalKeywords, '?'));
$like_placeholder = implode(' OR ', array_fill(0, $totalKeywords, 'name LIKE ?'));
$sql .= " AND MATCH (name) AGAINST ({$against_placeholder}) AND ({$like_placeholder})"; // build the query with placeholders

This will yield a complete query statement like this:

SELECT * FROM MYTABLE WHERE 1=1 AND (MATCH (name) AGAINST (? ? ? IN BOOLEAN MODE)) AND (name LIKE ? OR name LIKE ? OR name LIKE ?)

And then the rest is just building the actual payload in the execution. Here's the rest:

$sql = "SELECT * FROM MYTABLE WHERE 1=1"; // base query

$against_placeholder = implode(' ', array_fill(0, $totalKeywords, '?'));
$like_placeholder = implode(' OR ', array_fill(0, $totalKeywords, 'slug LIKE ?'));
$sql .= " AND (MATCH (slug) AGAINST ({$against_placeholder} IN BOOLEAN MODE)) OR ({$like_placeholder})"; // build the query with placeholders
// prep input
$against_keywords = array_map(function($value) {
    return "{$value}*";
}, $keywords);

$where_keywords = array_map(function($value) {
    return "%{$value}%";
}, $keywords);
$keywords = array_merge($keywords, $where_keywords);

$stmt = $pdo->prepare($sql);
$stmt->execute($keywords);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

Upvotes: 2

Related Questions