Posted by Jason Morehouse on 02/01/05 17:51

Hello,

I'm trying to make up for the lack full text searching in sqlite by
passing the search off to php with sqlite_create_function. The parsing
part works fine, but I'm a little lost with the scoring, if anyone
perhaps a little better @ math than I am, may be of assistance!

The parsing code below, basically allows you to use a php function
within a sqlite call. This pases matched content to a function, where
the function needs to pass back a score:

sqlite_create_function($sDB, 'fulltext', 'fulltext_step',2);

function fulltext_step($title, $content) {

$words = explode(' ', strtolower('php books'));
$content = explode(' ',strtolower($content));
$content = array_intersect($content,$words);
foreach($content as $wordpos => $word) {
print "$wordpos - $word<br>";
}
}


sqlite_create_function($sDB, 'fulltext', 'fulltext_step',1);
$rs = sqlite_query($sDB,"select title fulltext(content) score from page
where content like '%php%books%' order by score");

while($data = sqlite_fetch_array($rs)) {
print "$data[score] $data[title]<br>";
}

So basically... the fulltext step builds and array, of how many times a
word is matched, and at what word position (broken into an array for
each word)... eg:

array('php'=>2,'php'=>30,'php'=>55)
array('books'=>3,'books'=>130);

The word php is at position 2, and the word books is at position 3...
and so on.

What is the best way to return a single score for this page?

It's a bit confusing I suppose, but thanks!

As a side, on a 90meg text table, the parsing function above runs in
about 0.061858177185059 ms... mysql does in about 0.0039310455322266
ms. I'm fine by that.

-Jason

--
Jason Morehouse
Vendorama - Create your own online store
http://www.vendorama.com

[Back to original message]