Re: searching and sorting
| From: | Richard Lynch | Date: | Wed, 19 Jan 2005 17:16:04 +0000 |
| Subject: | Re: searching and sorting | ||
| References: | 1 2 3 4 5 6 7 8 9 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-206767@lists.php.net to get a copy of this message | ||
> I agree. That is a wise statement, and I have tried to do this with
> regular
> expressions but this doesn't deal with relevance at all, or is there
> something that I am missing? I suppose I could do multiple searches on my
> data to look for "sports cars" first, "cars" second, and "sports"
> last.
> Then
> I could deal with the separate issue of the text files.
Your database may provide a "full text" search system you can use, which
would be Good in terms of performance, but probably Bad in terms of
portability.
If you need portability, something like this:
<?php
$words = split('/\\s/', $_POST['search']);
//Ignore multiple inputs of same word:
$words = array_flip(array_flip($words));
$query = " select fields, you, need, ";
//This is for real:
$query .= " (description like '%$search%') ";
reset($words);
while (list(, $word) = each($words)){
$query .= " + (description like '%$word%') ";
}
$query .= " as score, ";
//This is just for debugging, so you can see where points are garnered:
$query .= " (description like '%$search%') as perfect, ";
reset($words);
while (list(, $word) = each($words)){
$query .= " (description like '%$word%') as 'word_$word', ";
}
$query .= " 'filler' "; // Just for the extra comma at the end of the loop
$query .= " from whatever ";
$query .= " order by score desc ";
$matches = mysql_query($query) or trigger_error(mysql_error() . "
$query", E_USER_ERROR);
while ($row = mysql_fetch_array($matches)){
list($fields, $you, $want, $score) = $row;
//For debugging only:
echo "Scoring<br />\n";
echo "Perfect: $row[perfect]<br />\n";
reset($row);
for ($i = 0; $i < 4; $i++) each($row); //Ignore stuff we already printed
for ($i = 4; $i < count($row); $i++){
list($word, $points) = each($row);
echo "Word match ($word): $points<br />\n";
}
}
?>
You'll need to convince MySQL to convert boolean to 0/1.
You can also apply scaling factors so that a perfect match gets, say, 10
points, intead of just 1:
$query .= " 10 * (description like '%$search%') as perfect ";
This is crude, but effective, and can easily be ported to any SQL engine
-- Except, of course, for the data-type conversion from a boolean result
to int, which you need to tweak, but that should be fairly easy to work
out a hack for all SQL engines, perhaps by using a variable function name:
$query .= " 10 * $sql_bool_to_int(description like '%$search%') as perfect ";
You then define $sql_bool_to_int for each SQL engine you need, and Bob's
your uncle.
--
Like Music?
http://l-i-e.com/artists.htm