MySql Table Search

[holmes]

Tweet

I'm after a PHP search script that will index a MySql table and return the results including an image. I've tried search scripts that spider the page but it's just not doing what i want. The table also has 30,000 rows so i'm a bit concerned about speed.

I'd like to the results to look simiar to how kelkoo return theres http://www.kelkoo.co.uk/sitesearch/s...ry=dvd+players

I've scoured the internet, tried modding similar scripts myself all to no avail. Any ideas?

David

[rogerlittin]

Originally posted by holmes
I'm after a PHP search script that will index a MySql table and return the results including an image. I've tried search scripts that spider the page but it's just not doing what i want. The table also has 30,000 rows so i'm a bit concerned about speed.

I'd like to the results to look simiar to how kelkoo return theres http://www.kelkoo.co.uk/sitesearch/s...ry=dvd+players

I've scoured the internet, tried modding similar scripts myself all to no avail. Any ideas?

David

Bit confused but I think I know what you need.

You should be able to set up a full text Index on the columns that contain the search terms that your after. This is done within MySQL. What it does is indexes each word that is 4 or more characters in length to provide a really fast search. The minimum word length can be changed but you will probibly have to get your host to do it. The variable is ft_min_word_len

You can then do a select from php to do the search.

$sql = sprintf("SELECT *, MATCH(name,brand,description,category)
AGAINST('%s') AS score from `products`
WHERE MATCH(name,brand,description,category) AGAINST('%s') LIMIT %s,%s", $search_term, $start, $limit); 

if $search_term = home +cinema , the returned recordset will contain all the rows containing the term home +cinema. It will also put them in order of relevance. I have added a limit to the sql statement. $start is the first record to fetch and $limit is the number of records to fetch. Use this for paging.

Look at http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html for more information.

I have a table with over 280,000 rows and speed isn't really an issue using this method.

[holmes]

Thanks for that Roger, i didn't realise it had that function.

David