MySQL search for multiple keywords in same column

Question

I am facing some problems in MySQL query in PHP.

I have the below Bengali words in a column named 'keywords' and they are assigned to 'গৌরিপ্রসন্ন মজুমদার' value of another column named 'name' in a table named 'lyricist'

সয়না,রয়না,কেমন,তাকে,আমার,তুমি,কৃষ্ণ,রাধে,দিতে,চাই,আরো,কাছে,তোমার,আমার,তুমি,থাকো,কাছে,ডাকো,আমি,পড়েছে

When I'm searching for some consecutive words like 'তোমার,আমার,তুমি' I get the correct answer 'গৌরিপ্রসন্ন মজুমদার' But when i'm searching for random words like 'তোমার,তুমি' it's not giving any answer.

$query = "SELECT name FROM `lyricist` WHERE keywords LIKE '%$val%'";

Here $val has 'তোমার,আমার,তুমি' or 'তোমার,তুমি' these type of values.

Why not it's treating the item randomly? Please HELP.

George Pant · Accepted Answer · 2016-05-14 17:29:21Z

3

$query = "SELECT name FROM `lyricist` WHERE keywords LIKE '%$val%'";

Will get you all the matching columns that start with some text having your value and then end with some text.So if you put some random words and they are not in the correct order obviously this query will not match.

I think what you want to do is when you have multiple values is put them in different variable val1,val2,... and using AND in your query

$query = "SELECT name FROM `lyricist` WHERE keywords LIKE '%$val1%' AND keywords LIKE '%$val2%'";

So you would have to construct your query dynamically something like this would work:

<?php
$val="keyword1,keyword2,keyword3";
$keywords=explode(',',$val);
if(!empty($keywords)){
     $query="SELECT name FROM `lyricist` WHERE keywords LIKE '%$keywords[0]%' ";  
        if(count($keywords)>1){
        for($i=1;$i<count($keywords);$i++){
         $query.="AND keywords LIKE '%$keywords[$i]%' ";
            }
       }        
}
?>

edited May 14, 2016 at 17:29

answered May 14, 2016 at 16:57

George Pant

2,1171 gold badge11 silver badges15 bronze badges

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

1 Comment

Nihar Over a year ago

I have edited the query to something like following because i'm looking for any match from like words, don't need to match all like words - SELECT name FROM lyricist WHERE keywords LIKE '%আমরা%' or keywords LIKE '%সোনার%' or keywords LIKE '%সয়না%' But it gives two rows because আমরা and সোনার these two words are from row1 and সয়না is from row2. But I want to get the one row that matches the maximum words from like words. And if two rows have same number of match words than it may return the first one. How can I do that? I tried with max, regex, match but didn't get correct result

Collectives™ on Stack Overflow

MySQL search for multiple keywords in same column

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related