0

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.

Cody Gray - on strike
  • 239,200
  • 50
  • 490
  • 574
Prabhu
  • 178
  • 2
  • 12

2 Answers2

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

  • thanks for response. some queries: is fulltext work in innodb database engine? while using match...Against am i get the result having all words first, any two on second and any one on third order? will u please show some helpful example which are best as per your practice . thanks again. – Prabhu Jan 27 '12 at 06:18
  • i got error while querying on DB :**The used table type doesn't support FULLTEXT indexes** – Prabhu Jan 27 '12 at 06:22
  • as I give note at end (you should have FULLTEXT indexing on the columns subject, message,reference,textbody) Apply full text index ion these colums like this query: ALTER TABLE tablename ADD FULLTEXT (subject, message,reference,textbody) –  Jan 27 '12 at 06:26
  • You can do with like also. see my another answer. –  Jan 27 '12 at 06:35
0

You can use the AND or OR operators with like keyword, depending on what you want the search to return.

SELECT * FROM table WHERE subject LIKE %$param1% AND another_col LIKE %$param2% .......;

OR

SELECT * FROM table WHERE subject LIKE %$param1% OR another_col LIKE %$param2% .......;
  • but in this case i cant change DB engine type innodb to myism. so i cant use fulltext search feature. any alternative for keyword ranking? i want something better than http://r937.com/keyword_relevance.html – Prabhu Jan 27 '12 at 06:45