select all products from child categories in parent category

Question

I have the following 'categories' table:

+--------+---------------+----------------------------------------+
| ID     | Parent ID     | Name                                   |
+--------+---------------+----------------------------------------+
| 1      | 0             | Computers                              |
| 2      | 1             | Apple                                  |
| 3      | 1             | HP                                     |
| 4      | 2             | Macbook Air                            |
| 5      | 2             | Macbook Pro                            |
| 6      | 1             | Dell                                   |
| 7      | 6             | Inspiron                               |
| 8      | 6             | Alienware                              |
| 9      | 8             | Alienware 13                           |
| 10     | 8             | Alienware 15                           |
| 11     | 8             | Alienware 17                           |
| 12     | 0             | Smartphones                            |
| 13     | 12            | Apple                                  |
| 14     | 12            | Samsung                                |
| 15     | 12            | LG                                     |
+--------+---------------+----------------------------------------+

Let's say I have the following 'products' table:

+--------+---------------+----------------------------------------+
| ID     | Category ID   | Name                                   |
+--------+---------------+----------------------------------------+
| 1      | 13            | Apple iPhone 8                         |
| 2      | 13            | Apple iPhone 8 Plus                    |
| 3      | 14            | Samsung Galaxy S8                      |
+--------+---------------+----------------------------------------+

With the following query, I select all the products in a category:

SELECT
    id,
    name
FROM
    products
WHERE
    category_id = ?

Ok, my question:

The product 'Apple iPhone 8' is in the category Apple, this is a subcategory of the category Smartphones. If I replace the '?' in my query with 13 (the category ID of Apple), I get the product. When I replace the '?' in my query with 12 (the category ID of Smartphones), I don't get the product. I want to select all products that are in the category or in one of the child/grandchild/... categories. How can I do this with a single query (if possible)?

Answer 1

you can use join . your query should be like this

SELECT
    id,
    name
FROM
    products
JOIN 
    categories
ON 
    products.category_id = categories.id;

Answer 2

It can be achieved using join

SELECT id, name FROM products JOIN categories ON products.category_id = categories.id WHERE products.category_id = 13 OR categories.parent_id = 12

Answer 3

SELECT id, name FROM products LEFT JOIN categories ON products.category_id = categories.id

Answer 4

1) A QUERY. I'm taking a query from this answer. How to create a MySQL hierarchical recursive query Please read it for a full explanation of the query. The query assumes that the parent ID will be less than the child IDs (like 19 is less than 20,21,22).

select * from products where `Category ID` in 
    (select ID from 
    (select * from categories order by `Parent ID`, ID) categories_sorted, 
    (select @pv := '12') initialisation 
    where (find_in_set(`Parent ID`, @pv) > 0 
    and @pv := concat(@pv, ',', ID)) or ID = @pv)

You have to set the "12" to be whatever the parent category is.

2) Via two sections in PHP, one that loops until you have all category IDs. Then a second section that gets all products in those categories. This is far more verbose but I like how clear you can see what is happening.

$db = new mysqli(host, user, password, database);

$all_ids = []; // total ids found, starts empty
$new_ids = [12]; // put parent ID here

do {

    // master list of IDs
    $all_ids = array_merge($new_ids, $all_ids);

    // set up query
    $set = "(".implode($new_ids, ',').")";
    $sql = "select ID from categories where `Parent ID` in $set";

    // find any more parent IDs?
   $new_ids = []; // reset to nothing
   $rows = $db->query($sql) or die("DB error: (" . $db->errno . ") " . $db->error);
    while ($row = mysqli_fetch_assoc($rows)) {
        $new_ids[] = $row['ID'];
    }

} while (count($new_ids) > 0);    


// get products
$set = "(".implode($all_ids, ',').")";
$sql = "select * from products where `Category ID` in $set";
$rows = $db->query($sql) or die("DB error: (" . $db->errno . ") " . $db->error);
while ($row = mysqli_fetch_assoc($rows)) {
    echo "{$row['Name']}<br>\n";
}