[SOLVED] Sorting mysql query results by total matches.

[rcorlew]

I have a search script that I have been working on for a while. It works very well, sort of like google, displays the correct text from the DB that it has matched, highlights the search phrase just fine.

 

I am however having trouble getting it to order it's results by the number of matches it has made from the DB.

 

My script searches through a table that holds the title, link and the entire text for every page on my site. It is not too big since I currently only have 10 or so pages, but that number will end up at around 100 or so.

 

My table is like this:

 

id = self explanatory

title = page title

link = page location

p_text = the text from the page

 

I am having no luck sorting the results by relevance, although I can see the relevance. I don't know if this is possible with sql or not.

 

Here is the basis of my code:

 

<?php
$result = mysql_query("SELECT * FROM pages WHERE page_text LIKE '%$cs_word%'");
$count = mysql_num_rows($result); 

if($count == 0) {
	echo "<b>Sorry, no results found.</b>"; }
if($count > 0) {

while ($row = mysql_fetch_array ($result)) {

$string = "$row[page_text]";	
$searchit = "$cs_word";
$pos = stripos("$string", "$searchit");
$ncount = substr_count($string, $searchit);
$ls = $pos - 60;
echo "$ncount";	
if ($pos > 60) { $lsr = $ls; }
else { $lsr = 0; }

$purl = "$row[location]";


	$replacement = "<span id='sText'>$searchit</span>";
	$searchout = substr($string, $lsr, 200);
	$search_results = eregi_replace($searchit, $replacement, $searchout);

	echo "<div id='sResult'>";
	echo "<a href='$purl$ltype'>$row[title]</a>";
	echo "</div>";
		echo "<div id='sShow'>";
		echo "$search_results";
		echo "</div>";
		echo "<div id='sLink'>http://www.mycrdisorder.org/$purl$ltype</div>";
	$s_count = str_word_count($search_results);
		echo "<br />";
	}
}echo "See results in the <a href='forum$ltype?function=search&searchterm=$cs_word'>forum</a>";

?>

 

Like I said, the script is great except for the sorting by total matches in the text.

[Illusion]

Use FULLTEXT index on p_text column and make use of MATCH(), AGAINST() and REGEXP() to search the content.

[rcorlew]

Ok, here is the search that I have now, and as yet to have developed a way to weigh the total number of matches that are produced by each search word. The query returns results just fine, but I would like to know if there is a way to ORDER by SUM(matches('$expanded[0], page_text)) like you can do with php itself.

 

Here is the query:

 

<?php
$query = "SELECT id, title, location, page_text, MATCH(title,page_text) AGAINST('+(>$expanded[0]) +(>$expanded[1]) +(>$expanded[2]) +(>$expanded[3])' IN BOOLEAN MODE) AS score FROM pages WHERE MATCH (title,page_text) AGAINST ('+$expanded[0] +$expanded[1] +$expanded[2] +$expanded[3]' IN BOOLEAN MODE) > 0 HAVING page_text LIKE '%$cs_word%' ORDER BY score DESC";
?>

 

I have not seen a way to do this yet, if there is please let me know.

[rcorlew]

Ok I finally got this done, since I have not seen or read of anything that will truly accomplish any sort of true scoring, I will post my code so all the others who may be looking for the answer for this problem will have at least a good starting point.

 

If you would like further clarification, message me and I will help you out since this seems to be a situation that most people tend to give up on.

 

Here is the full script, I have taken time to ensure that this works, first of all you have to have the table set up as MyIsam, took me a while to realize that I have it as InnoDB, which is not FULLTEXT search able.

 

<?php
echo "<div align='center'>";
echo"<form action='$PHP_SELF?function=search' method='POST'>
Search for : <input type='text' name='s_word' size='20' value='$cs_word'> 
<input type='submit' name='submit' value='Search' />
</form></div><br />";

	$searchit = "$cs_word";		
	$expanded = preg_split("/[\s,]+/", $cs_word);
		$nx = count($expanded);

$query = "SELECT id, title, location, page_text, MATCH(title,page_text) AGAINST('+$expanded[0] ~$expanded[1] ~$expanded[2] ~$expanded[3]' IN BOOLEAN MODE) AS score FROM pages WHERE MATCH (title,page_text) AGAINST ('+$expanded[0] +$expanded[1] +$expanded[2] +$expanded[3]' IN BOOLEAN MODE)  ORDER BY score DESC";
$result = mysql_query($query);
$count = mysql_num_rows($result);
$result2 = mysql_query("SELECT * FROM pages");
$page_count = mysql_num_rows($result2);
$result3 = mysql_query("SELECT * FROM pages WHERE title LIKE '%$cs_word%' OR page_text LIKE '%$cs_word%'");

	if($count < 2) $rc = "result";
	if($count > 1) $rc = "results";

echo "<div id='searchHeader'>We found $count $rc on $page_count pages</div>";
	echo "<br />";

if($count == 0) {
	echo "<b>Sorry, no results found.</b> <br />"; }
if($count > 0) {

while ($row = mysql_fetch_assoc($result)) {

$string = "$row[page_text]";
$pos = stripos("$string", "$expanded[0]");
$ncount = substr_count($string, $expanded[0]);

	$i = 1;			
	foreach($expanded as $nval) {		
		$pos1 = strripos("$string", "$nval");
		if($pos1 != null) { $i++; }
	}

	if($ncount < 2) { $tc = "match"; }
	if($ncount > 1) { $tc = "matches"; }

$ls = $pos - 60;

if ($pos > 60) { $lsr = $ls; } else { $lsr = 0; }

$purl = "$row[location]";

	$replacement = "<span id='sText'>$expanded[0]</span>";
	$replacement2 = "<span id='sText'>$expanded[1]</span>";
	$searchout = substr($string, $lsr, 200);
	$search_results = eregi_replace($expanded[0], $replacement, $searchout);

	$p = ceil($nx / $i);
		if(($i - 1) == $nx) { $p = 1; }
	$my_score = ceil(100 / $p);
	$l_id = $ncount + $my_score;

	$r_output[$ncount] = "
		<b>Score $my_score%</b><br />
		<span id='sResult'>
		<a href='$purl$ltype'>$row[title]</a>
		</span> - <span id = 'myForm'>$ncount $tc on this page</span><br />
			<div id='sShow'>
			$search_results
			</div>
		<div id='sLink'>http://www.mycrdisorder.org/$purl$ltype</div>
		<br />";
	}
	krsort($r_output);
//Now we will print out the sorted results by true relevance		
		foreach($r_output as $val) {
			echo "$val";
		}

	}
?>