CODEWITHSUNDEEP
phpmysql
Bibhudatta Sahoo
Bibhudatta Sahoo

Reputation: 4904

how to filter data according to key words in MySQL?

I am facing an problem to retrieve data with respect to filter key words .I have message table like this

My filter table:-

+--+-------+-----------+---------+---------------+
|id|user_id|for_page_id|key_words|    message    |
+--+-------+-----------+---------+---------------+
| 1|     12|        2  |he,you   |You are awesome|
| 2|     12|        2  |the,book |this is good   |
+--+-------+-----------+---------+---------------+

I have a string of key words and i want to find the corresponding message for that key word.
I tried this:-

$string='he is good';
$keyWords=explode(' ',$string);
$query="SELECT * FROM `filter` WHERE `key_words` like '".$keyWords[0]."' or`key_words` like 
        '".$keyWords[1]."' or`key_words` like '".$keyWords[2]."'";
echo ($query);

The query look likes

SELECT * FROM `filter` WHERE `key_words` like 'he' or`key_words` like 'is' or`key_words` like 'good'

It gives output as

| 1|     12|        2  |he,you   |You are awesome|
| 2|     12|        2  |the,book |this is good   |

Accepted output is

| 1|     12|        2  |he,you   |You are awesome|

Any thoughts would be appreciated.

Upvotes: 1

Views: 1580

Answers (2)

Shams Reza
Shams Reza

Reputation: 1097

Try this. Hope will help you 

$string='he is good';
$keyWords=explode(' ',$string);
$searchQuery='';
foreach ($keyWords as $word) {
    $word = trim($word);
    if ($word) {
        $searchQuery = $searchQuery . "find_in_set('$word', trim(key_words)) > 0 or ";
    }
}
$searchQuery = chop($searchQuery, ' or ');
$query="SELECT * FROM filter WHERE ".$searchQuery;
echo ($query);

Query will be like 

SELECT * FROM filter WHERE find_in_set('he', trim(key_words)) > 0 or find_in_set('is', trim(key_words)) > 0 or find_in_set('good', trim(key_words)) > 0

Upvotes: 1

geof2832
geof2832

Reputation: 136

The FIND_IN_SET() function is probably what you are looking for.

SELECT FIND_IN_SET('b','a,b,c,d');
-> 2

It returns 0 if the string is not found in the list.

Here is a link to the MySQL documentation

Upvotes: 3

Related Questions