View Single Post
  #152  
Old 09-29-2005, 02:28 PM
 
leighj leighj is offline
 

Member
  
Join Date: Feb 2005
Posts: 15
 

Default

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)
  1. If the phrase matches add 5 to the score
  2. If the first word is found matches add 4 to the score
  3. If the second word is found add 4-(4/Number of search words) to the score
  4. If the third word is found add 4-(2*(4/Number of search words)) to the score
  5. If the fourth word is found add 4-(3*(4/Number of search words)) to the score
  6. 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...
__________________
  • X-Cart Version 4.1
  • PHP Version 4.4.x eAccelerator
  • MySQL 4.1
  • Fedora Core 1
  • Dual Xeon 2.4Ghz Hyperthreading enabled 2GB Ram
Reply With Quote