Multi keywords search with SQL

Question

I'm using a search bar on my website based on keyup binding and ajax requests. It works fine but I would like my search engine to be able to have finner result with multi keywords management.

However I could not find any simple method to set up this kind of search method.

Does anyone knows how to set this up ?

Here is the actual SQL request that's being made:

if ($recherche !=""){
            $req = $this->bdd->prepare("SELECT * FROM videos WHERE titre LIKE :recherche OR auteur LIKE :recherche UNION SELECT videos.id_video, videos.titre, videos.lien, videos.auteur, videos.date_upload FROM videos RIGHT JOIN mots_clefs ON videos.id_video = mots_clefs.id_video AND mots_clefs.mot_clef LIKE :recherche ORDER BY date_upload DESC LIMIT ".$start.", ".$limit);
            $req->execute(array('recherche' => "%".$recherche."%"));
            $result = json_encode($req->fetchAll(PDO::FETCH_ASSOC));
        }

Request example:

SELECT * FROM videos WHERE titre LIKE '%word 1 word 2%' OR auteur LIKE '%word 1 word 2%' UNION SELECT videos.id_video, videos.titre, videos.lien, videos.auteur, videos.date_upload FROM videos RIGHT JOIN mots_clefs ON videos.id_video = mots_clefs.id_video AND mots_clefs.mot_clef LIKE '%word 1 word 2%' ORDER BY date_upload DESC LIMIT 0, 20);

        

Answer 1

You can execute the above query in a loop by passing one keyword at a time.

1. get the entire keyword list provided by the user into a sting.

2. Use string.Split() method by passing comma(,) as delimiter to get the list of the keywords into an array list.

3. loop through the array list and pass to the query. Make sure you append the data fetched from the SQL into a data table or dataset and Not rewrite it.

   string select = "SELECT * FROM [MyTable] WHERE [Title] LIKE '%" + strSearch.Replace(",", "%' OR [Title] LIKE '%") + "%'";