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 ?

Combining “LIKE” and “IN”: stackoverflow.com/questions/1865353/… — Chris
– Chris, Commented Apr 16, 2013 at 10:18
If the keyword contains regular expression keywords, the query will fail, or generate unexpected result, e.g. keyword = '|=|=|' — Raptor
– Raptor, Commented May 20, 2013 at 2:55

Jimbo · Accepted Answer · 2016-11-30 10:06:45Z

30

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';

edited Nov 30, 2016 at 10:06

answered Apr 16, 2013 at 10:31

Jimbo

26.7k16 gold badges93 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andrew Fox Over a year ago

+1, this was exactly what I was looking for, I also learned a new MySQL string function!

Birdy Over a year ago

Excellent answer, this has helped me resolve a weeks worth of research and helped me jump over 30% of my issue :)

Strawberry · Accepted Answer · 2013-04-16 10:18:39Z

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     |
 +---------+----------+

answered Apr 16, 2013 at 10:18

Strawberry

34k14 gold badges43 silver badges57 bronze badges

1 Comment

arraintxo Over a year ago

Just a pair of caveats with this approach. Can't use the LIKE syntax and doesn't trim the keywords...

Jimbo · Accepted Answer · 2014-11-05 11:20:27Z

3

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...

edited Nov 5, 2014 at 11:20

Jimbo

26.7k16 gold badges93 silver badges135 bronze badges

answered Apr 16, 2013 at 10:22

arraintxo

4842 silver badges12 bronze badges

1 Comment

Raptor Over a year ago

you missed a trailing single quote for the $sql though.

Robert · Accepted Answer · 2013-04-16 10:16:56Z

1

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%'

answered Apr 16, 2013 at 10:16

Robert

20.3k6 gold badges58 silver badges85 bronze badges

1 Comment

Nilupul Heshan Kodikara Over a year ago

If you have multiple keywords you can make a string like ::-> SELECT * FROM tbl_address WHERE address LIKE "%key1%key2%key3%"

B.F. · Accepted Answer · 2015-01-11 15:28:28Z

1

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.

answered Jan 11, 2015 at 15:28

B.F.

4776 silver badges9 bronze badges

Comments

Vaishu · Accepted Answer · 2015-05-14 07:26:00Z

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;

edited May 14, 2015 at 7:26

answered Apr 16, 2013 at 10:28

Vaishu

2,3733 gold badges23 silver badges25 bronze badges

1 Comment

TheCarver Over a year ago

Should this not be $other .=" instead of $other =" inside your loop? Otherwise, you're cancelling out the or clause in each loop.

mukund · Accepted Answer · 2013-04-16 10:21:12Z

0

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;

answered Apr 16, 2013 at 10:21

mukund

2,4431 gold badge21 silver badges32 bronze badges

Comments

Codesen · Accepted Answer · 2013-04-16 10:22:08Z

0

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.

answered Apr 16, 2013 at 10:22

Codesen

7,8425 gold badges31 silver badges31 bronze badges

Collectives™ on Stack Overflow

MySQL & PHP : Multiple Keyword Search

8 Answers 8

2 Comments

1 Comment

1 Comment

1 Comment

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

2 Comments

1 Comment

1 Comment

1 Comment

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related