php - select all products from child categories in parent category

455

I have the following 'categories' table:

+--------+

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

{-code-2}

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

{-code-3}

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)?

910

Answer

-----+
781

Answer

+ | ID | Parent ID | Name | +--------+
393

Answer

-----+
784

Answer

+ | 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 | +--------+
27

Answer

-----+
590

Answer

+|||+--------+
49

Answer

-----+
760

Answer

+ | ID | Category ID | Name | +--------+
993

Answer

-----+
998

Answer

+ | 1 | 13 | Apple iPhone 8 | | 2 | 13 | Apple iPhone 8 Plus | | 3 | 14 | Samsung Galaxy S8 | +--------+
634

Answer

-----+
810

Answer

+|||SELECT id, name FROM products WHERE category_id = ?
836

Answer

Solution:

you can usejoin . yourquery should be like this

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

Answer

Solution:

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

493

Answer

Solution:

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

429

Answer

Solution:

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";
}

People are also looking for solutions to the problem: php - SQL MAX and same coupon

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.