CODEWITHSUNDEEP
phpmysqlsqlfull-text-search
Dani Queiroga
Dani Queiroga

Reputation: 59

Search table for keyword

I have a website that basicly consists on a video search engine, I have the follow code that will query mysql for the given input criteria:

$search=htmlspecialchars($_GET['load']); 
$say=mysql_query("SELECT * FROM madvideo WHERE MATCH (baslik) AGAINST ('*$search*' IN BOOLEAN MODE)");

Then I have the follow code that will display the results:

     $katala=mysql_query("SELECT * FROM madvideo WHERE MATCH (baslik) AGAINST ('*$search*' IN BOOLEAN MODE) order by id asc limit $s,$perpage");
            $i=0;
                    while ($sea=mysql_fetch_array($katala))  { 
            $i++;

$idd=$sea['id']; $seoo=$sea['seo']; $baslikk=$sea['baslik']; $resimm=$sea['resim']; $suree=$sea['sure'];
 $izlenmee=$sea['izlenme']; $tarihh=$sea['tarih']; $katt=$sea['kat'];

Let me try to explain what's the problem with this. Lets say you search on my website for "I like to eat strawberry"

And lets suppose I have on mysql table an entry with the very same name "I like to eat strawberry"

With my current code I will get the follow example results:

"I like to drive my car" "my car is super fast" "they eat to much" and so on, and the relevant result might appear but not at first like I would like to be.

How can I make it display the results in a more relevant way, like the google for example?

Upvotes: 1

Views: 109

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125224

Break the search term into words so you can build the order by condition dynamically using like expressions. Since the result of a logic expression in mysql is 1 or 0 you can add those expressions.

SELECT * 
FROM madvideo 
WHERE MATCH (baslik) AGAINST ('*$search*' IN BOOLEAN MODE) 
order by 
    (baslik like '%$word1%') 
    + (baslik like '%word2%')
    + (baslik like '%word3%')
    desc
limit $s,$perpage

Upvotes: 2

Related Questions