...

View Full Version : PHP - How to sort the results of this code?



1bellb
08-04-2012, 04:04 PM
im creating a search feature that will allow a user to type in a question, my code will then match as many words as possible with the questions already in my MySQL database and display the top 5 results depending on the amount of words that are matched in the question. I use a count() function which counts the number of matching words, however at the moment the results shown are displayed as the first 5 results in the database that have a 50% word match or more. I want the results to be shown as the highest match first and work its way down for every result in the database but only show the top 5. Here is the code I have...

<?php
include("config.php");
$search_term = filter_var($_GET["s"], FILTER_SANITIZE_STRING); //User enetered data
$search_term = str_replace ("?", "", $search_term); //remove any question marks from string
$search_count = str_word_count($search_term); //count words of string entered by user
$array = explode(" ", $search_term); //Seperate user enterd data

foreach ($array as $key=>$word) {
$array[$key] = " title LIKE '%".$word."%' "; //creates condition for MySQL query
}

$q = "SELECT * FROM posts WHERE " . implode(' OR ', $array); //Query to select data with word matches
$r = mysql_query($q);
$count = 0; //counter to limit results shown
while($row = mysql_fetch_assoc($r)){
$thetitle = $row['title']; //result from query
$thetitle = str_replace ("?", "", $thetitle); //remove any question marks from string
$title_array[] = $thetitle; //creating array for query results
$newarray = explode(" ", $search_term); //Seperate user enterd data again
foreach($title_array as $key => $value) {
$thenewarray = explode(" ", $value); //Seperate each result from query
$wordmatch = array_diff_key($thenewarray, array_flip($newarray));
$result = array_intersect($newarray, $wordmatch);
$matchingwords = count($result); //Count the number of matching words from
//user entered data and the database query
}

if(mysql_num_rows($r)==0)//no result found
{
echo "<div id='search-status'>No result found!</div>";
}
else //result found
{
echo "<ul>";
$title = $row['title'];
$percentage = '.5'; //percentage to take of search word count
$percent = $search_count - ($search_count * $percentage); //take percentage off word count
if ($matchingwords >= $percent){

?>
<li><a href='<?php echo $row['url']; ?>'><?php echo $title ?><i> &nbsp; No. matching words: <?php echo $matchingwords; ?></i></a></li>
<?php

$count++;
if ($count == 5) {break;
}
}else{
}
}
echo "</ul>";
}
?>

The image below shows the what happens when I search "How to make my own website" in the search bar. I already have several questions in the database for testing which are all similar questions and one the last entry is an exact match to the question I asked, but as its currently showing them as the first 5 mathing results, it ignores the full match. Here is the results from that search.
http://i1193.photobucket.com/albums/aa354/1bellb/results.jpg
I have added a bit of code which shows how many word matches there are in each question just so you can see it working a bit better. Also its a coincidence that its in ascending order, it is showing the first 5 matching results in the database.
What code do I need to add to this to arrange it so that it shows the closest match from the entire database first then the second best match, third etc...?

Fou-Lu
08-04-2012, 05:55 PM
I'm not sure if there's an easy way to count the words in a string that match a given value within MySQL itself. I'm sure it can be done, but I'd expect it to be messy.
PHP wise sorting is easy. PHP will need to be given an array of this data, and easiest to use a usort for it.


$aSearchTerms = explode(' ', $search_term); // You've sorta done this already, but overwritten it in a foreach.
$aRecords = array();

function sortByKeywordsASC(array $a, array $b)
{
$iResult = 0;
if (isset($a['_keywords'], $b['_keywords']))
{
$iResult = $a['_keywords'] - $b['_keywords'];
}
return $iResult;
}

function sortByKeywordsDESC(array $a, array $b)
{
return sortByKeywordsASC($b, $a);
}

if (mysql_num_rows($r) > 0)
{
while ($row = mysql_fetch_assoc($r))
{
$thetitle = str_replace('?', '', $row['title']);
$aTitleParts = explode(' ', $thetitle);
$aTitleParts = array_unique($aTitleParts);
$row['_keywords'] = count(array_intersect($aTitleParts, $aSearchTerms));
$aRecords[] = $row;
}

usort($aRecords, 'sortByKeywordsASC');
// here you issue a foreach on $aRecords to pull what you want to display.
}
else
{
// no results.
}


You should check in the SQL forum as well to see what it would take to do this in SQL instead. You'd be better off pulling only 5 records from your database based on criteria and sorted, than using PHP to pull an undefined number of records and then iterate only the top 5.


In hindsight, it would actually be better to count the number of keywords in the while instead of within the callback. Otherwise each comparison need to call a count which will grow very quickly in count calls versus one per record in the while.

1bellb
08-04-2012, 07:30 PM
I'm not sure if there's an easy way to count the words in a string that match a given value within MySQL itself. I'm sure it can be done, but I'd expect it to be messy.
PHP wise sorting is easy. PHP will need to be given an array of this data, and easiest to use a usort for it.


$aSearchTerms = explode(' ', $search_term); // You've sorta done this already, but overwritten it in a foreach.
$aRecords = array();

function sortByKeywordsASC(array $a, array $b)
{
$iResult = 0;
if (isset($a['_keywords'], $b['_keywords']))
{
$iResult = $a['_keywords'] - $b['_keywords'];
}
return $iResult;
}

function sortByKeywordsDESC(array $a, array $b)
{
return sortByKeywordsASC($b, $a);
}

if (mysql_num_rows($r) > 0)
{
while ($row = mysql_fetch_assoc($r))
{
$thetitle = str_replace('?', '', $row['title']);
$aTitleParts = explode(' ', $thetitle);
$aTitleParts = array_unique($aTitleParts);
$row['_keywords'] = count(array_intersect($aTitleParts, $aSearchTerms));
$aRecords[] = $row;
}

usort($aRecords, 'sortByKeywordsASC');
// here you issue a foreach on $aRecords to pull what you want to display.
}
else
{
// no results.
}


You should check in the SQL forum as well to see what it would take to do this in SQL instead. You'd be better off pulling only 5 records from your database based on criteria and sorted, than using PHP to pull an undefined number of records and then iterate only the top 5.


In hindsight, it would actually be better to count the number of keywords in the while instead of within the callback. Otherwise each comparison need to call a count which will grow very quickly in count calls versus one per record in the while.


Hi, thanks for sending me this but ill be honest and say im not sure where to put this and what to replace with this. I'll keep trying but there is always an error wherever I put it.
But I will keep trying
Thanks

AndrewGSW
08-04-2012, 09:08 PM
It would be worth investing FULLTEXT (http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html) searches; this requires the table(s) to be:


ENGINE=MyISAM

which may already be the case in Windows. phpMyAdmin can confirm this.

Perhaps not of immediate assistance though ;) but something for the future. It will be easier to count the matches, and perform partial/fuzzy matching.

It will Edit:CAN be faster as well - once the appropriate indexes have been created.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum