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

0
votes

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

2
phpmysql

2 Answers

0
votes

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;
}
0
votes

Another approach can be achieved using GROUP_CONCAT() function, you will get customer details only once, and other order details in separate column separated by comma.

Considering Your table structure I wrote following query, change the column names as per your requirement.

SELECT a.customer_id, a.cust_name, GROUP_CONCAT(b.item_name) FROM customers a 
INNER JOIN orders b ON a.customer_id = b.customer_id
GROUP BY a.customer_id