php mysql多关键词搜索,MySQL和PHP：多个关键字搜索

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 ?

解决方案

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