Hello,
i need to update php/mysql to sort resulsts by relevance.
In current search you can select options to filter search result.
If one or more options do not match, search returns "no result".
What you have to do now is to filter options and sort by relevance (matched options)
Search filter options are saved in 3 mysql tables: categories, services, languages.
Selected options of comapny are saved in company table, field: categories, services, languages.
Example value: "1,2" (means that company has selected cat1, cat2) see mySql below.
Result example: (note: current script selects/sort stores by distance radius)
Suppose you have 3 categoy values, user selects to search company value 1 and 2
Company 1 is 3 miles away and has 2 category matches
Company 2 is 2 miles away and has 2 category matches
company 3 is 1 mile away and has 1 category matched
company 4 is 1 mile away and has 0 category matches
Correct result order is:
Company2 (best relevance, 2 miles)
Company1 (best relevance, 3 miles)
Company3 (good relevance, 1 miles)
Company4 (no relevance, 1 miles)
Note; you have to count all matched options of categories, services, languages. to get the relevance
--------------------------------------------------------------------
Code used in script for categories, same for others.
//Fetch categories
$sql = "select * from categories order by Title asc";
$result = $mysql->exSql($sql) or die($mysql->debugPrint());
while($row = mysql_fetch_assoc($result)){
if(DEFAULTLANG!=CURRENTLANG){
$translatedText = fetchTranslation(CURRENTLANG,DEFAULTLANG, sprintf("cat-%s", $row['id']), $row['Title']);
$row['Title'] = $translatedText;
}
if(!empty($_REQUEST['category']) && is_array($_REQUEST['category'])){
if(in_array($row['id'],$_REQUEST['category'])){
$row['selected'] = 'SELECTED';
$showOptions=true;
}
}
$xtpl->assign('category', $row);
$xtpl->parse('[login to view URL]');
$xtpl->parse('[login to view URL]');
}
//Filter Categories
if(!empty($_REQUEST['category']) && is_array($_REQUEST['category'])){
$cSQL = array();
for($i=0;$i<sizeof($_REQUEST['category']);$i++){
$cSQL[] = sprintf(" FIND_IN_SET('%s',categories) > 0 ", intval($_REQUEST['category'][$i]));
}
$whereSql[] = sprintf(" ( %s ) ", implode(" OR ", $cSQL));
}
------------------------------------------------
mysql used in db for option tables
CREATE TABLE IF NOT EXISTS `categories` (
`id` int(11) NOT NULL auto_increment,
`Title` char(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
INSERT INTO `categories` (`id`, `Title`) VALUES
(1, 'cat1'),
(2, 'cat2'),
...
------------------------------------------------
check attached script for updates.