Occurences

[goobers]

Hi,

I'm having an issue with searching by relevance. I am aware of the full text search mysql implementation but due to some restrictions on the table type I cannot do that. Does anyone know of a way to implement a simple algorithm that sorts results from a mysql query SOLEY on the number of occurences of a given word or words within a field(s)?

Any help is greatly appreciated.

Thanks

[NogDog]

I can't think of an easy way to do it with SQL, but you could do it in PHP with:

<?php
/**
*  Sort an array by number of occurrences of a string.
*  Sorts in descending order, using case-insensitive regular expression.
*/
class Occurrences
{
   var $search = "";
   var $array = array();

   function Occurrences($array, $search)
   {
      if(is_array($array))
      {
         $this->array = $array;
         $this->search = (string)$search;
         usort($this->array, array($this, 'search'));
      }
      else
      {
         user_error("Occurrences::Occurrences() - first arg not an array");
      }
   }

   function search($a, $b)
   {
      $regex = '/'.preg_quote($this->search).'/';
      preg_match_all($regex, $a, $matches_a);
      preg_match_all($regex, $b, $matches_b);
      return(count($matches_b[0]) - count($matches_a[0]));
   }
}

// Sample usage:

$test = array(
   "blah foobar blah foobar foobar blah blah foobar blah foobar",
   "blah blah foobar blah blah",
   "blah foobar blah foobar blah blah foobar blah"
);
$searchWord = 'foobar';

// Do it
$sorted = new Occurrences($test, $searchWord);

// show result:
echo "<pre>".print_r($sorted->array, 1)."</pre>";

[Lars_Berg]

select ... 
order by char_length(c) - char_length(replace(c,'word1','word')) +
char_length(c) - char_length(replace(c,'word2','word'))  ...
 desc

[goobers]

Lars Berg wrote:

select ... 
order by char_length(c) - char_length(replace(c,'word1','word')) +
char_length(c) - char_length(replace(c,'word2','word'))  ...
 desc

Hi,

Is it possible that you could explain exactly how that order works, I would like to understand what's going on with the query..

thanks

[Lars_Berg]

c denotes your column

char_length(c)

returns the number of characters in the column

char_length(replace(c,'word1','word'))

If there is one occurrence of the string word1 in the column c, the length of the replace expression will be one less than the original column length, as we replace 5 characters with 4. As the replace function will replace all occurences the length will be decreased correspondingly.

char_length(c) - char_length(replace(c,'word1','word'))

so this code calculates the number of occurrences. Adding these calculatin for all search words and columns will give the total number of occurrences and since you wish to get the column with the most hits first, we need order it descendingly.