+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Need help finishing off crafting an SQL query for matching pairs of columns (/showthread.php?tid=51393)
Need help finishing off crafting an SQL query for matching pairs of columns - El Forum - 05-02-2012
[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=206I 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.