[El Forum]

[eluser]The Questioner[/eluser]
I've developed an ecommerce site in CI which allows the user to filter products by product attributes. Each product can have one or more product attribute. I have a model which builds the SQL directly to retrieve the appropriate rows from the MySQL table. This table is used to store attributes for each product. Its structure is like this:

Code:

CREATE TABLE IF NOT EXISTS `product_attributes` (
  `id` int(11) NOT NULL auto_increment,
  `id_product` int(11) default NULL,
  `id_attribute` int(11) default NULL,
  `ordinal_pos` tinyint(10) NOT NULL,
  `display_name` varchar(255) default NULL,
  `allowed_attribute_items` varchar(255) default NULL,
  `out_of_stock_attribute_items` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `index_product_id` (`id_product`),
  KEY `index_id_attribute` (`id_attribute`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

The id_attribute column contains an ID number, and the allowed_attribute_items column contains a string of comma seperated numbers.

Imagine if the table contained the following data:

Code:

row 1:
id_product=10
id_attribute=6
allowed_attribute_items=191,192,193,194,195,196,197

row 2:
id_product=21
id_attribute=6
allowed_attribute_items=191,192,193,194,195,196

row 3:
id_product=21
id_attribute=8
allowed_attribute_items=206

I want to retrieve distinct id_product row values where id_attribute is 6 and the associated allowed_attribute_items contains the number 194, AND where id_attribute is 8 and the associated allowed_attribute_items contains the number 206. In this case rows 2 and 3 should be retrieved.

The SQL needs to apply multiple combinations of the row pair id_attribute and allowed_attribute_items. The closest syntax I can get is:

Code:

SELECT DISTINCT id_product
FROM product_attributes
WHERE
(product_attributes.id_attribute=<value1> AND InStr(1,[allowed_attribute_items],"<value2>")>0)
AND
(product_attributes.id_attribute=<value3> AND InStr(1,[allowed_attribute_items],"<value4>")>0)

Using this as another example, I want to get hold of all the distinct id_product values where the id_attribute is <value1> and the associated allowed_attribute_items contains the value <value2> AND also where
id_attribute is <value3> and the associated allowed_attribute_items contains the value <value4>.

However the above SQL syntax doesn't work. I don't want to use an OR because the criteria need to match for each pair of columns.

I just can't get this to work.