[Paul Boreham]

Hi all,

Got a problem thats a real headscratcher.

Client has a large ecom site that I am building and they would like the 'attribute searching' that is popular now on sites like eBay (also Halfords, Amazon etc). So the user clicks on 'Books' for example and then a sub list opens at the side that could say "Hardback", "Paperback" etc with a number in brackets of the amount in that attribute.

So the user is basically 'drilling down' to a smaller range of products.

I've got 1 level working fine - clicking on an attribute of 'Red' for example shows all my 'red' products.

But adding a second level in (and obviously further levels) is proving really hard. I've got the links and code working so when a user clicks on another attribute it adds it to the mysql statement, but I'm not sure how to code the search.

I've got a few tables involved here:-

1) Products in category (called ProdInCat and holds values of product Ids and Category Ids)

2) The main product table with all the details in of the product

3) Product attributes (called ProductAttributes, which holds product Id and Attribute Id).


So the problem is finding a product in CATEGORY X which has the ATTRIBUTE Y AND ATTRIBUTE Z.

Soon as I put the AND in, it returns no results.

Phew...

Has anyone done anythign similar and how did you get around it?

Many thanks!

====

Just to kickstart anyone, this is my current statement...

SELECT tabProdInCat.catId, tabProdInCat.prodId, tabProdAttributes.apAttId,tabProdAttributes.apProdId  FROM tabProdInCat LEFT JOIN tabProdAttributes ON tabProdAttributes.apProdId = tabProdInCat.prodId WHERE tabProdInCat.catId = %s AND (tabProdAttributes.apAttId = 21 AND tabProdAttributes.apAttId = 27)