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

Make single query

$keywordary = explode(',', $keywords);
foreach($keywordary as $keyword) {
  $keys = trim($keyword);
    $other .=" or address like '%$keys%'";
}
$sql = "SELECT * FROM tbl_address WHERE address LIKE'%$keyword%' $other";

execute query;
return $result;

Answer 2

The best way is to use fulltext search.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

if you don't want to use fulltext you can use OR in your WHERE condition

SELECT * FROM tbl_address WHERE address LIKE '%$keyword%' OR adress LIKE '%$keyword2%'

Answer 3

Best way is just create search string WHERE clause and append it to query and run it once.

$result = array();
$keyword_tokens = explode(',', $keywords);
$where = '';$i=0
foreach($keyword_tokens as $keyword) {
  $where.= " address LIKE'%".mysqli_real_escape_string(trim($keyword))."%' OR ";
}
  // trim last OR with substr_replace
  substr_replace($where, "OR", -1, 1);
  $sql = "SELECT * FROM tbl_address WHERE $where";

return $result;

Answer 4

 SELECT * FROM user;
 +---------+----------+
 | user_id | username |
 +---------+----------+
 |     101 | Adam     |
 |     102 | Ben      |
 |     103 | Charlie  |
 |     104 | Dave     |
 +---------+----------+

 SELECT * 
   FROM user 
  WHERE FIND_IN_SET(username,'adam,ben,dave') > 0;
 +---------+----------+
 | user_id | username |
 +---------+----------+
 |     101 | Adam     |
 |     102 | Ben      |
 |     104 | Dave     |
 +---------+----------+

Answer 5

Hi create a query with union and execute in the end of the loop

$result = array();
$keyword_tokens = explode(',', $keywords);
$sql = '';
foreach($keyword_tokens as $keyword) {
  $keyword = mysqli_real_escape_string(trim($keyword));
  if (!empty($sql)) $sql .= " UNION "; 
  $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.
}

Execute the query here.

Answer 6

A simple REGEXP might be what you're after. You'd have to check how efficient it is for yourself.

SELECT * FROM tbl_address WHERE field REGEXP 'keyword1|keyword2|keyword3';