Jump to content


Photo

Sort SQL Results by count of string occurance?


  • Please log in to reply
3 replies to this topic

#1 intrik

intrik
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 20 September 2006 - 03:40 AM

Hey guys,

I made this code to count how many times the search string occurs in the results data
$desc_lower = strtolower($results['desc']);
$title_lower = strtolower($results['title']);
$desc_count = substr_count($desc_lower, $searched);
$title_count = substr_count($title_lower, $searched);
$count_searches = ($title_count+$desc_count);
echo $count_searches;

Idealisticly what I'd like to do is order the sql results by the frequency of the occurance, so obviously if $count_searches had a value of 1234 it would order before a value of 1233.

So you get the most relevant search results at the top.

Any ideas?

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 20 September 2006 - 10:01 AM

I'm confused.. if you're doing this type of "scoring" in PHP, why not sort the results in PHP?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 intrik

intrik
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 20 September 2006 - 10:58 PM

Well, the problem is that I can't

SORT BY relevance DESC

In my version of MySQL... So it's going to have to be done with some PHP, how do I sort the results in PHP? Any ideas? I didn't even know you could do that...

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 21 September 2006 - 12:41 AM

I assume that you can write a custom sort routine, though I never have in PHP.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users