PHP Code, SQL Code

Build a search query with relevance in PHP & MySQL

Do you have a lot of content or data from your website in a MySQL database, but no search feature to surface it? This is a simple function that performs searches better than just using the LIKE operator in your SELECT statement.

This is a handy function that takes that name of the table that you want to search, the number of words in the query as well the words that user entered (the query itself as an array of word). It runs a select query on all the rows in that table. The num_results tells us the number of the results we got out of that select statement, and then the for loop determines the relevancy of each result and ranks them accordingly.


function query_table ($tablename, $q_num, $q_array) {
       $query = "SELECT number, article from $tablename";
       $result = mysql_query($query);
       $num_results = mysql_num_rows($result);
       for ($c = 0;$c < $num_results; $c++) {
              $relevancy = 0;
              $row = mysql_fetch_object($result);
              $mypage = $row->number;
              $mycontent = strtolower(strip_tags($row->article));
              for ($d = 0; $d < $q_num; $d++) {
                     $relevancy += substr_count($mycontent, strtolower(strip_tags($q_array&#91;$d&#93;)));
              }
       }
       if ($relevancy>0)
            $res["$mypage"] = $relevancy;
       if (count($res)>0)
            arsort ($res);
       return $res;
}
Share

6 Comments

  1. well how do you call the function?

  2. like you would any other function? I don't understand the question

  3. You wouldn't happen to have a copy of this in ASP would you?

  4. Sorry, no. But I'm building an inventory system in ASP shortly, if I have to adapt this code I'll let you know

  5. Black background + dark blue text=poke my eyes out.

  6. Dude, you can also used FULL TEXT SEARCHING

Leave a Reply

Your email address will not be published. Required fields are marked *

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Anti-spam image