mysql - php search engine key words ranking

Question

I am new to php and mysql. In my project I have to develop search engine for multiple keywords on multiple columns and also require the keyword ranking and relevency.

For example if I have three words hi, hello, hey and want to search on fields like subject, message, reference, textbody. in this case the row who has more keywords ranking will come first and lowest ranker row goes last. will any one guide me how to implement such search engine. my database is about 4 million and is growing rapidly.

Answer 1

Use Full text search :

<?PHP

    //SET THE SEARCH TERM
    $term = "Search Term";


    $sql = "SELECT *, MATCH(subject, message,reference,textbody) AGAINST('". $term ."') as score FROM pages WHERE MATCH (subject, message,reference,textbody) AGAINST('". $term ."') ORDER BY score DESC";
    $query = mysql_query($sql);

    //BUILD A LIST OF THE RESULTS
    while($result = mysql_fetch_assoc($query)) {
       // your stuff for showing result
    }

?>

Note: you should have FULLTEXT indexing on the columns subject, message,reference,textbody