Devam Ediyor

477605 php / mySql sort by relevance

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('[url removed, login to view]');

$xtpl->parse('[url removed, login to view]');

}

//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.

Beceriler: Her şey Kabul, MySQL, PHP, SQL

Daha fazlasını görün: mysql sort relevance, php sort relevance, sort sort sort, sort sort, sort array c, php script null, php mysql create, php create table, create table php, category php id, asc company, value stores, key value stores, best title company, relevance, php script create table, mysql to sql, filter sort, mysql code search, mysql select filter, mysql sort field, mysql sort, parse table, php table array, mysql create table sort

İşveren Hakkında:
( 10 değerlendirme ) Austria

Proje NO: #2223512

Seçilen:

rinsadsl

See PMB

2 gün içinde 80$ USD
(282 Değerlendirme)
7.0