I have 2 tables in mysql which are following:
CREATE TABLE IF NOT EXISTS `shop_catgs_prods` (
`cgpr_catg_id` mediumint(9) NOT NULL,
`cgpr_prod_id` mediumint(9) NOT NULL,
`cgpr_prod_status` tinyint(1) NOT NULL DEFAULT '1'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `shop_products` (
`prod_id` mediumint(9) NOT NULL AUTO_INCREMENT,
`prod_code` varchar(30) NOT NULL DEFAULT '',
`prod_name` varchar(200) NOT NULL DEFAULT '',
`prod_description` text NOT NULL,
`prod_retail_price` float(10,2) NOT NULL DEFAULT '0.00',
`prod_section` varchar(255) NOT NULL,
`prod_colour` varchar(255) NOT NULL,
`prod_size` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`prod_id`),
UNIQUE KEY `prod_url` (`prod_url`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
The table shop_catgs_prods is meant to store product id which could be in multiple categories / subcategories. The second table is the products table itself.
Earlier I was doing 2 queries in which when a user clicked a category I got the category ID and using a custom php function I got subcategories IDs in that category and then I would search the shop_catgs_prods table for available products.
Once I had the product ID numbers using above query, i would then do another query to get the product details and display all products to user.
But now I am in a problem, I have to filter the products that I have got in the first query by colour or size. Surely I can use if color = red or size = small but then it disturbs the pagination I am doing.
Can I do a single query to get the products in the category / subcategories and also filter them by colour or size as required ???
Please help, it will be highly appreciated.