Don't worry I've been banging my head on X-Cart for 7 months now
Check the additional Additional Fields String section code
yes I did forget a line or two WHOOPS ...
Code:
$add_fields[]=$scorestr . " 0 as searchscore";
Which goes at the end of the Additional Fields String section code (I fixed my original post also)
Searchscore is calculated in MySQL not in PHP.
This is where the score is found
Code:
#
# Generate additional fields string
#
$add_fields_string = '';
$scorestr="";
// Add score to fields - LNJ
if (!empty($data["by_title"]) && count($search_words)>0) {
$scorestr.="if($sql_tbl[products].product like '%". $data["substring"] ."%',5,0) + "; //Add high score for exact match - LNJ
$topscore=4; // Yeah I know HARDCODED - LNJ
$scoredesc=$topscore/count($search_words); //Get decrementing number for scores - LNJ
foreach($search_words as $search_word){
$scorestr.="if($sql_tbl[products].product like '%". $search_word ."%',$topscore,0) + "; //add individual word score - LNJ
$topscore=$topscore-$scoredesc; //Descrement individual score for word - LNJ
}
}
SNIP CODE FOR Short and Long Description
Code:
$add_fields[]=$scorestr . " 0 as searchscore";
The create an additional SINGLE field that consists of several if statements that follow this logic (for a 5 word search)
- If the phrase matches add 5 to the score
- If the first word is found matches add 4 to the score
- If the second word is found add 4-(4/Number of search words) to the score
- If the third word is found add 4-(2*(4/Number of search words)) to the score
- If the fourth word is found add 4-(3*(4/Number of search words)) to the score
- If the fifth word is found add 4-(4*(4/Number of search words)) to the score
This lets the database do the filter for the records and then sort and limit the data sent back to PHP. So PHP has only 10 records in the result set an not 1000 or so to calculate and sort.
Granted this approach does increase the load on the database engine and is contained in a sql statement but it provides the following benefits over having PHP do the calculation
- Reduces the database connection load (only returns 10 rows instead of 1000)
- Sorting is a (hopefully optimized) database function rather than several PHP functions
- Less code (sorting this result in PHP would be cumbersome at best)
I hope this helps...