November 15th, 2006
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[$d])));
}
}
if ($relevancy>0)
$res["$mypage"] = $relevancy;
if (count($res)>0)
arsort ($res);
return $res;
}
$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[$d])));
}
}
if ($relevancy>0)
$res["$mypage"] = $relevancy;
if (count($res)>0)
arsort ($res);
return $res;
}
Digg this
post to del.icio.us
on February 23rd, 2007 at 4:46 pm
well how do you call the function?
on February 23rd, 2007 at 5:44 pm
like you would any other function? I don't understand the question
on September 12th, 2007 at 9:33 pm
You wouldn't happen to have a copy of this in ASP would you?
on September 13th, 2007 at 9:01 am
Sorry, no. But I'm building an inventory system in ASP shortly, if I have to adapt this code I'll let you know