Perform a MySQL search by relevance in PHP

If you have a database with multiple entries that are similar and you'd like to do a search, you're going to need a way to sort the results. Sorting by the number of times a keyword appears in a block of text is one way, and it's called search relevence. Here is a PHP functions for performing a MySQL database search and ording the results by relevence:

  1. function relevance_search($keyword_raw, $page, $perpage)
  2. {
  3.         $return = NULL;
  4.         $page = ereg_replace("[^0-9]", "", $page);
  5.         $perpage = ereg_replace("[^0-9]", "", $perpage);
  6.         $offset = ' OFFSET '.(($page-1)*$perpage)-1;
  7.         if ($page == 1) {
  8.                 $offset = '';
  9.         }
  10.         $keywords = explode(' ', $keyword_raw);
  11.         if (is_array($keywords)) {
  12.                 $i = 0;
  13.                 foreach($keywords as $value) {
  14.                         $chunk = mysql_real_escape_string($value);
  15.  
  16.                         if ($i > 0) {
  17.                                 $plus = ' + ';
  18.                                 $or = ' OR ';
  19.                         }
  20.                         $keyword_string = $keyword_string.$plus."(CASE WHEN body LIKE '%$chunk%' THEN 1 ELSE 0 END) + (CASE WHEN title LIKE '%$chunk%' THEN 1 ELSE 0 END)";
  21.                         $where_string = $where_string.$or."body LIKE '%$chunk%' OR title LIKE '%$chunk%'";
  22.                         $i++;
  23.                 }
  24.                 $sql = "SELECT *, (".$keyword_string.") AS relevance FROM table_name WHERE (".$where_string.") ORDER BY relevance DESC LIMIT ".$perpage.$offset;
  25.                 $query = mysql_query($sql);
  26.                 if (mysql_num_rows($query)) {
  27.                         $i = 0;
  28.                         while ($row = mysql_fetch_array($query)) {
  29.                                 $return[$i] = $row;
  30.                                 $i++;
  31.                         }
  32.                         mysql_free_result($query);
  33.                 }
  34.         }
  35.         return $return;
  36. }

This function returns all the data matching to inputted keywords, and return an array of results sorted beginning with most relevent (the highest count of keyword matches).

Share/Save/Bookmark

June 20, 2008 | Filed Under MySQL, PHP

 
Advertisment

Comments

2 Responses to “Perform a MySQL search by relevance in PHP”

  1. EllisGL on September 25th, 2009 6:35 pm

    Here’s how I did a weighted results:
    SELECT *,
    IF(`name` LIKE “searchterm%”, 20, IF(`name` LIKE “%searchterm%”, 10, 0)) +
    IF(`description` LIKE “%searchterm%”, 5, 0) +
    IF(`url` LIKE “%searchterm%”, 1, 0) AS `weight`
    FROM `myTable`
    WHERE (`name` LIKE “%searchterm%” OR
    `description` LIKE “%searchterm%” OR
    `url` LIKE “%searchterm%”)
    ORDER BY `weight` DESC
    LIMIT 20

  2. EllisGL on September 25th, 2009 6:37 pm

    I actually did more weighting with if the term started with, then it got another value.. stuff like that..

Leave a Reply