MySQL Forums
Forum List  »  PHP

Improving Ranking of search results in a product search script
Posted by: fatjoe slim
Date: December 12, 2005 09:45PM

Hey there. Spent about 3 hours on this so far reading the manual on full text search and other additional articles but as of yet have not had much luck...

I am trying to improve a search script on my website so that when someone searches for a product, rows matched with Title column are returned above rows matched with Description. Because if you search for Windows XP, the product Windows XP should return above the product Brand New Laptop which has "Bundled with Windows XP" in the description. Get my drift?

http://www.cmsmadesimple.org/pastebin/211

As seen on line 173 I have attempted to edit the SELECT from database to reflect this ( $query = 'select *, MATCH(`title`,`description`) AGAINST ("$word") AS relevancy FROM '.$prefix.'store_search ORDER BY relevancy DESC'; )

As of yet I have not had much luck.. Have tried various suggested methods throughout the articles I have read and none seem to really give give correct results. With the current Select query above, I get a product returned that doesn't even have Windows in the title or description!!!

I am sure the inventory table is setup correctly & the structure is as below:


CREATE TABLE `cubecartstore_inventory` (
`product` varchar(60) collate latin1_general_ci NOT NULL default '',
`quantity` int(16) NOT NULL default '1',
`description` text collate latin1_general_ci NOT NULL,
`image` varchar(250) collate latin1_general_ci NOT NULL default '',
`price` decimal(30,2) NOT NULL default '0.00',
`title` varchar(250) collate latin1_general_ci NOT NULL default '0',
`cat_id` int(16) NOT NULL default '0',
`popularity` bigint(64) NOT NULL default '0',
`sale_price` decimal(30,2) NOT NULL default '0.00',
`supplier` varchar(30) collate latin1_general_ci default NULL,
`front` int(30) NOT NULL default '0',
FULLTEXT KEY `title` (`title`,`description`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;


Would appreciate any tips / feedback.

Cheers

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.