05-02-2012, 07:06 AM
[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:
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:
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:
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.
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.