Dheeraj R S Dheeraj R S - 4 months ago 8
SQL Question

How can I change the grouping when displaying data from two joined tables?

I have joined two tables, customers and orders, on the customer id value.
There are multiple entries in orders table with same customer id.

When I display the entries of orders table, each order is followed by the customer details.
Instead, I want the customer details to be displayed once followed by all the orders with that customer's id.

How do I do that using PHP and MySQL?

I want the output to be like

Customer1_name
Item 1 Quantity
Item 2 Quantity

Customer2_name
Item1 Quantity
Item2 Quantity


But what I'm getting is

Customer1_name
item1 quantity
Customer1_name
item2 quantity


if he has ordered 2 items

Answer

In general, like this:

Execute your query, ordering first by customer ID, then by order ID, (or whatever you want to sort your orders by within each customer section). It is important to ORDER BY customer_id first; if you do not, this method will not work.

$stmt = $pdo->query('SELECT * FROM customers 
    INNER JOIN orders ON customers.id = orders.customer_id ORDER BY customer_id, order_id');

As you fetch the results, keep track of the customer, and when it changes, output the new customer information and replace the current customer with the new customer.

$customer_id = null;                          // current customer - initialize to null
while ($row = $stmt->fetchObject()) {
    if ($row->customer_id != $customer_id) {
        // customer is different, so start a new customer section
        echo $row->customer_name;
        $customer_id = $row->customer_id;    // reset the current customer
    }
    // always output your order information regardless of whether the customer has changed
    echo $row->order_info;
}
Comments