2 replies to this topic

[berthz]

I am trying to develop a filter for the product sort. I want to create the possibility for three selection:

1: Brand/manufacturer
2: Colour (its a product option)
3: Size (also a product option)

Customers see three dropdowns and with those filter the product in a category.

I managed to combine Brand with either Colour or Size but not the three filters together. The reason is that Colour and Size are both attributes from the same table. Therefore I need a little help with the following sql statement to have all three filters work together

$where = 'pa.products_id = p.products_id and (pa.options_values_id = \''.$var1.'\' where pa.options_id = \'1\') and pa.options_values_id = \''.$var2.'\' where pa.options_id = \'2\')';

The $var1 and $var2 work I just need to know how to double select on pa.options_values_id. So first of all $var1 corresponds with pa.options_values_id=1 and $var2 corresponds with pa.options_values_id=2. My where statement doesnt work because it involves more than one 'where' but how do I get this to work?

Thanks in advance.

[MrPhil]

I haven't actually tried this, but I think you're going to have to involve a JOIN between the p and pa tables:

TABLE_PRODUCTS p LEFT JOIN TABLE_PRODUCTS_ATTRIBUTES pa ON (p.products_id=pa.products_id)

and then select multiple rows from the combined table:

WHERE (pa.options_id='1' AND pa.options_values_id='$var1') OR
	 (pa.options_id='2' AND pa.options_values_id='$var2')

I suspect at that point you'll have to go through the returned rows and check that you got two matches for any given item (both color and size) before presenting the choices. Some selected items may match only one attribute; none should match zero attributes.

In other words, I can't tell you how to do the entire operation in a single SQL statement, as it involves matching multiple rows out of one table. Maybe someone else out there is more of an SQL expert than me, and could tell you how.

[berthz]

Hi Phil,

I managed to do a workaround using a modified version of http://addons.oscommerce.com/info/5997 it did the sql statements correct already. I just added the manufacturers to it and added in on top of the product listing page rather than in a infobox.