I have two mysql tables named 'categories' and 'products'.
These are data of that tables.
mysql> select * from categories;
+-------------+--------+----------------------+-------------+
| category_id | parent | name | description |
+-------------+--------+----------------------+-------------+
| 1 | NULL | Products | NULL |
| 2 | 1 | Computers | NULL |
| 3 | 2 | Laptops | NULL |
| 4 | 2 | Desktop Computers | NULL |
| 5 | 2 | Tab PCs | NULL |
| 6 | 2 | CRT Monitors | NULL |
| 7 | 2 | LCD Monitors | NULL |
| 8 | 2 | LED Monitors | NULL |
| 9 | 1 | Mobile Phones | NULL |
| 10 | 9 | LG Phone | NULL |
| 11 | 9 | Anroid Phone | NULL |
| 12 | 9 | Windows Mobile | NULL |
| 13 | 9 | iPad | NULL |
| 14 | 9 | Samsung Galaxy | NULL |
| 15 | 1 | Digital Cameras | NULL |
| 16 | 1 | Printers and Toners | NULL |
| 22 | 1 | Computer Accessaries | NULL |
| 23 | 22 | USB Cables | NULL |
| 24 | 22 | Network Cables | NULL |
+-------------+--------+----------------------+-------------+
24 rows in set (0.00 sec)
mysql> select product_id, category_id from products;
+------------+-------------+
| product_id | category_id |
+------------+-------------+
| 1 | 24 |
| 2 | 6 |
| 3 | 6 |
| 4 | 6 |
+------------+-------------+
4 rows in set (0.05 sec)
Now I need to create a select query to get products to each category. I have already have category_id.
This is how I tried it:
SELECT * FROM products
WHERE category_id = 6
ORDER BY added_date DESC
My question is when I creating this select query I need all the products if one category have its subcategories. That mean, if category_id is 2, then I need to get all the products including its sub categories.
Can anybody tell me how I create this select query?
Thank you.
try this.
select a.*,b.* from categories a inner join products b on a.category_id = b.category_id where a.category_id='$Category_id' or a.parent_id='$category_id'
You can do it using a sub-query to get category_id values whose parent is 2:
SELECT *
FROM products
WHERE category_id = 2 OR
category_id IN (SELECT category_id
FROM categories
WHERE parent = 2)
ORDER BY added_date DESC
SELECT *
FROM products
WHERE category_id = 2 OR
category_id IN (SELECT category_id
FROM categories
WHERE parent = 2)
ORDER BY added_date DESC
All of the above answers will work but only for one level deep of categories. If you need sub-sub-categories then I'd suggest using something called MPTT. This will require 2 new fields in your categories database table though (typically called left and right).