"Narrow your search" or "Category Search" functionality

[subman]

Hi, I have a simple shopping-cart style page (self built) that simply queries a mysql database and returns a dataset of matching products.

The user can currently search for some text .. i.e. "red" and then my page returns all the products that contain "red".

At the moment, that is all it does.

I would like to offer the ability to narrow down the search results by price range/manufacturer/colour/size etc.

You can then go as far as select multiple 'attributes' to only show red shoes, priced between £50-£100 from manufacturer D&G. (and also then remove these criteria to broaden your search again)

(Some examples are ebay, Debenhams, Halfords etc etc).

I have found one solution, but it requires me to manually code in all the lists of colours/prices/manufacturers etc, but I am sure this could be dynamically created, i just don't know how.

Any help would be appreciated!

Thanks
Tom

[leatherback]

Ideally, you have lookup tables for all these items. If you dont, you can for most of them use 'select distinct' to create lists with unique entrires in specific field of your table, such as designer. For the prices you can just create your own ranges.

[subman]

all the data is actually in one table.... something like this...
(the data is pulled from a flat csv fille... if necessary i could modify the cron job script to create multiple tables, but i dont think it will help)

fields: item no | description | manufacturer | price | colour

so when i do a search for items which have 'red' in the description, i would like to be able to offer a way to narrow down their search i.e.

Narrow Search....

by manufacturer
- Nike (2)
- Reebok (10)
- K Swiss (1)

by price
- £1 to £5 (1)
- £6 to £19 (12)

does that make sense?

[leatherback]

Yes. Use select distinct count(), , from .. , rather than select.