How to filter data via query

[Tapan]

Hi,

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.

Thanks.

[r937]

@Tapan

Surely I can use if color = red or size = small but then it disturbs the pagination I am doing.

could you show this query please

[Tapan]

Hi,

SELECT * FROM shop_catgs_prods WHERE cgpr_catg_id = '1' AND cgpr_prod_status = '1'

Using above say i got 100 products and I was doing pagination in the above query, now i will loop through those and display products to user using the following query

SELECT * FROM shop_products WHERE prod_id = '123' LIMIT 1

Where 123 is/are the prod_id’s I got from the first query. Now if i have to search colour or size then I am not able to do it as the first query is returning 100 products on the basis of selected category, and if i use if then else after the second query then if only 30 products match the color or size, the pagination becomes wrong , coz pagination is working on the basis of 100 products that i got in the first query.

I hope i explained myself, please help me sort this up.

Thanks.

[r937]

replace your two queries with this one query –

SELECT p.* FROM shop_catgs_prods AS cp INNER JOIN shop_products AS p ON p.prod_id = cp.prod_id AND p.color = 'red' AND p.size = 'small' WHERE cp.cgpr_catg_id = '1' AND cp.cgpr_prod_status = '1' ORDER BY p.prod_id LIMIT 25,25 -- pagination

[Tapan]

Hi,

It worked great.

Thanks