CODEWITHSUNDEEP
phpmysqlcodeigniter
Davinder Kumar
Davinder Kumar

Reputation: 662

Search keywords in mysql and get data which have minimum 5 keywords

I have a large database in which various images are saved.

Each image has multiple keywords split by comma(,).

Now some images are showing on the front end when user click the image it will show images which have at least 5 common keywords with the selected keywords. I am confused how to do it.

Upvotes: 4

Views: 120

Answers (1)

Imran Qamer
Imran Qamer

Reputation: 2263

first of all get all keywords of the image to whome you want to search like you clicked on image one whose keywords are :

$keywords = "good,beautiful,nice,weather";

Now add loop to get all keywords in an array by explode.

$exploded = explode(",",$keywords);

Now use foreach loop on $exploded to match from db.

use your query like this

$matched = array();
foreach($exploded as $key => $searchValue) 
{   
    select * from cars where colors like '%,$searchValue,%';
    //you can use find in set instead like:
    select * from cars where find_in_set($searchValue,colors);

    //code here to check matched or not.
   //if result matched add 1 in count of same index in matched array like :
   $matchedimageid = //get image id from query and add that in array.
   $matched[$matchedimageid] = $matched[$matchedimageid]+1;
}

//code here to get all image ids from $matched array whose value greater than or equal to 5. and show them in list

Note: this logic is for your current situation but best solution is to have keywords in seperate table.

Upvotes: 2

Related Questions