I have tables as below. All I want is for how many orders the category. I tried so hard but I couldn't.

Categories:

Products:

Orders:

The result I want:

Shoes: 2 order.

Tshirt: 3 order.

CodePudding user response：

Here is an example on how to do it

<?php

// Connect to the database
$conn = mysqli_connect('host', 'username', 'password', 'database');

// Check the connection
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
    exit();
}

// Execute the SQL query
$result = mysqli_query($conn, "SELECT c.Name AS 'Category', COUNT(o.ProductID) AS 'Order Count'
FROM Categories c
JOIN Products p ON c.ID = p.Category
JOIN Orders o ON o.ProductID = p.ID
GROUP BY c.Name");

// Check if the query was successful
if (!$result) {
    echo "Error executing query: " . mysqli_error($conn);
    exit();
}

// Process the result set
while ($row = mysqli_fetch_assoc($result)) {
    echo $row['Category'] . ": " . $row['Order Count'] . " orders." . PHP_EOL;
}

// Close the connection
mysqli_close($conn);

The output for your case will be :

Shoes: 2 orders. Tshirt: 3 orders.