11 replies to this topic

[Braunson]

Hello, Im trying to order my serch results by relevance but i dont know how, or im to tired (its 5:40am here).

I have found out the relevance of each result successfully, but dont seem to know how to order them by it. Heres the code.

		$highestRelevance = 0;
		while($row = mysql_fetch_assoc($result))
		{
		$subject = $row['subject'];
		$content = $row['content'];
		$search = strtolower($search);
		$subject = strtolower($subject);
   		$theRelevanceOfTheResult1 = substr_count($content, $search);
		$theRelevanceOfTheResult = $theRelevanceOfTheResult1 + substr_count($subject, $search);

				  if ($theRelevanceOfTheResult > $highestRelevance){
   					$highestRelevance = $theRelevanceOfTheResult;
				  } 

 		}

	if($highestRelevance == 0){
		$highestRelevance = 1;
	}

		$search = str_replace(array('%', '_'), array('\%', '\_'), $search);
		$result = mysql_query("
			SELECT *
			FROM `articles`
			WHERE `subject` LIKE '%$search%'
				OR `content` LIKE '%$search%'
				OR `postdate` LIKE '%$search%'
		ORDER BY '$highestRelevance' ASC
		") or die(mysql_error()); 


			while($row = mysql_fetch_assoc($result))
			{
				$aid = $row['aid'];
				$subject = $row['subject'];
				$postdate = $row['postdate'];
				$content = bbcode($row['content']);

		$search = strtolower($search);
		$subject2 = strtolower($subject);
		$theRelevanceOfTheResult1 = substr_count($content, $search);
		$theRelevanceOfTheResult = $theRelevanceOfTheResult1 + substr_count($subject2, $search);
		$theRelevanceOfTheResult = round(($theRelevanceOfTheResult / $highestRelevance) * 100, 2); // the percentage relevance

				echo "				<li><a href=\"./view_article/$aid/\">$subject</a><br /><i>Relevance: $theRelevanceOfTheResult%</i></li>
";
			}

Also a friend said to do it this way, but I dont understand..

		$relevanceArray[$aid] = $relevancePercentage;
		sort($relevanceArray);
		foreach($relevanceArray as $r => $v){
   			$get = "SELECT * FROM `articles` WHERE $r";
		}

Any help is appreciated, thank you.

Edited by Braunson, 13 July 2007 - 01:04 PM.

[Braunson]

Offical Bump, I need an reply urgently please.

[Demonslay]

I've never tried working with relevance searches, so I can't help all that much other than with some logic maybe.

So far, it looks good to me (from what I can understand), expect a few points.

One, you wouldn't place $highestRelevance into your searching SQL. You can't order something by a number; it has to be a column (or an expression dealing with the column value).

To be honest, I can only think of one solution to ordering like this. Kind of messy, but it would atleast work until you could figure something better out (what I usually do, hack something apart and make something messy atleast work until I learn more about it and can clean it up).

First of all, simply take your results, and find each's relevance, like you have with the first loop. However, in this loop, make a new multi-dimensional array, with the first index containing the relevance factor, and the second index containing the result. Then, you'd have to make your own sorting function for sorting a multi-dimensional array like this.

Here's something of what I'm talking about, can't guarantee it will work.

$search = strtolower($search);
$highestRelevance = 0;
while($row = mysql_fetch_assoc($result)){
  $relevance = substr_count(bbcode($row['content']), $search) + substr_count(strtolower($row['subject']), $search);
  if($relevance > $highestRelevance)
	$highestRelevance = $relevance;

  $results[] = array($relevance, $row);
}
if($highestRelevance == 0)
  $highestRelevance = 1;

// Custom multi-dimensional array sorting, see PHP manual example for usort()
function cmp($a, $b){
  return strcmp($a[0], $b[0]);
}
usort($results, 'cmp');

foreach($results as $r){
  $row = $r[1];

  $relevance = round(($r[0] / $highestRelevance) * 100, 2); // the percentage relevance

  echo "<li><a href=\"./view_article/{$row['aid']}/\">{$row['subject']}</a><br /><i>Relevance: {$relevance}%</i></li>";
}

Logically, that should work just fine, and now that I type it out it doesn't look quite as ugly as I thought it would.

The reason you can't simply use the $relevance as the key for the array, is that there can easily be the same relevance for several results, meaning it would over-write itself all the time.

Also a note on your old code, you cannot iterate over a MySQL result more than once without resetting the pointer. To do that, you would use the mysql_data_seek() function after each iteration. Something I just learned myself the other day, lol.

Edited by Demonslay, 12 July 2007 - 01:05 PM.

[Braunson]

Uhhh... You've lost me, I tryed that I dunno if I added it correctly, mind adding that to the code I've posted above. Cause I've totally *** myself...

[birdbrain24]

What he is saying the you have to ORDER BY a column that exists in the database!

[Demonslay]

Erm, no, that's not really what I'm telling him in relation to my solution birdbrain.

The code I posted IS the full code and should replace all of what you posted.

Actually, add this as your $result variable at the top.

$result = mysql_query("SELECT * FROM `articles` WHERE `subject` LIKE '%$search%' OR `content` LIKE '%$search%' OR `postdate` LIKE '%$search%'") or die(mysql_error());

Edited by Demonslay, 12 July 2007 - 10:16 PM.

[Braunson]

Still doesn't rank from most relevant to least relevant which is what im trying to do if I didnt explain it best...

[Demonslay]

Possibly my mistake in using strcmp() for comparing numbers, lol.
Try this then as your full code.

$search = strtolower($search);
$result = mysql_query("SELECT * FROM `articles` WHERE `subject` LIKE '%$search%' OR `content` LIKE '%$search%' OR `postdate` LIKE '%$search%'") or die(mysql_error());

$highestRelevance = 0;
while($row = mysql_fetch_assoc($result)){
  $relevance = substr_count(bbcode($row['content']), $search) + substr_count(strtolower($row['subject']), $search);
  if($relevance > $highestRelevance)
	$highestRelevance = $relevance;

  $results[] = array($relevance, $row);
}
if($highestRelevance == 0)
  $highestRelevance = 1;

// Custom multi-dimensional array sorting, see PHP manual example for usort()
function cmp($a, $b){
  if($a[0] == $b[0])
	return 0;
  return $a[0] > $b[0] ? 1 : -1;
}
usort($results, 'cmp');

foreach($results as $r){
  $row = $r[1];

  $relevance = round(($r[0] / $highestRelevance) * 100, 2); // the percentage relevance

  echo "<li><a href=\"./view_article/{$row['aid']}/\">{$row['subject']}</a><br /><i>Relevance: {$relevance}%</i></li>";
}

[Braunson]

That works, but It displays from least relevant to most relevant, i want it the other way around

[Demonslay]

Ok then, two options.

Reverse the logic of the cmp() function and change the inequality sign.
Or, use array_reverse() and reverse the array after sorting it.

Would be easiest to just do option one and reverse the logic of the sorting function.

[Braunson]

Awsum thank you

[Demonslay]

Glad it worked. Turned out to be easier than I had ever thought this sort of sorting would be.

Thanks for the basis, now I finally understand how the full thing works, and I just may use it myself in the future.