MySQL & PHP : Multiple Keyword Search

Question

I have a string containing comma separated keywords. For Example:

$keywords = 'keyword1, keyword2, keyword3';

My Table schema, named tbl_address is like this ( simplified ) :

id        INT(11)        PRIMARY KEY, AUTO INCREMENT
address   VARCHAR(250)   NOT NULL

Assume I have to use MySQLi in PHP ( not PDO ).

Here is my current approach:

$result = array();
$keyword_tokens = explode(',', $keywords);
foreach($keyword_tokens as $keyword) {
  $keyword = mysqli_real_escape_string(trim($keyword));
  $sql = "SELECT * FROM tbl_address WHERE address LIKE'%$keyword%'";
  // query and collect the result to $result
  // before inserting to $result, check if the id exists in $result.
  // if yes, skip.
}
return $result;

This approach works, but inefficient in performance. If there are a lot of keywords, it will make a lot queries.

My question is, is there a better way to achieve the same goal ? i.e. what is the simplest way to return all records with the address containing the ANY of the keywords ?

Answer 1

You only need an 'OR', nothing else...

<?php

$result = array();
$keyword_tokens = explode(',', $keywords);
$keyword_tokens = array_map('mysqli_real_escape_string', $keyword_tokens);

$sql = "SELECT * FROM tbl_address WHERE address LIKE'%";
$sql .= implode("%' or address LIKE '%", $keyword_tokens) . "'";

// query and collect the result to $result
// before inserting to $result, check if the id exists in $result.
// if yes, skip.

return $result;

edit: Just to be sure you also trim the keywords

<?php

$result = array();
$keyword_tokens = explode(',', $keywords);
$keyword_tokens = array_map(
    function($keyword) {
        return mysqli_real_escape_string(trim($keyword));
    }, 
    $keyword_tokens
);

$sql = "SELECT * FROM tbl_address WHERE address LIKE'%";
$sql .= implode("%' OR address LIKE '%", $keyword_tokens) . "'";

// query and collect the result to $result
// before inserting to $result, check if the id exists in $result.
// if yes, skip.

return $result;

Also, you should also pass the db resource link to the mysqli_real_escape_string() function...

Answer 2

Try WHERE IN clause:

$keyword = (array)explode(',', $keywords);
for($i=0;$i=count($keyword);$i++){
   $keyword[$i]=mysqli_real_escape_string(trim($keyword[$i]),'\'" ');
}
//This is what I suggest.
$query='SELECT * FROM tbl_address WHERE address IN ("'.implode('","',$keyword).'")';

Successfully tested on MySQL 5.1.