从两个表循环访问mysqli查询以打印每个客户的逐项收据

Question

I have two tables created for and online ordering system that look as such:
Table 1 (orders)

orderid | customername | email

15 | joe | [e-mail]
16 | ron | [e-mail]
17 | tim | [e-mail]
18 | sue | [e-mail]

and: Table 2 (orderitems)

id | orderid | orderitems

1 | 15 | sourdough
2 | 15 | four grain
3 | 16 | original fruit
4 | 16 | finnish rye
5 | 16 | sourdough
6 | 17 | sourdough
7 | 18 | original fruit

The orderid's are the same on both tables and I am trying to figure out how I can loop through all orderid's on the orderitems table to to print receipts for each customer.

so far i have a the mysqli statement:

$sql = 'SELECT a.orderid, a.customername, a.email, b.orderid, b.menuitem FROM orders a, orderitems b WHERE a.orderid = b.orderid';

$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($result)){
            echo "<td>" . $row['customername'] . "</td>";
            echo "<td>" . $row['phone'] . "</td>";
            echo "<td>" . $row['email'] . "</td>";
            echo "<td>" . $row['menuitem'] . "</td>";
}

this gets everything thing I need except I would like to separate each order individually so I can send each customer a receipt. I am not sure how to achieve this and I have exhausted all search terms here to try to find what I am looking for.
Any help would be appreciated.
Thanks in advance!!

Answer 1

You should use an explicit join with an on (or using in this case where the two columns are exact match) clause. You then can group by the orders by their id and concatenate the items of the order with group_concat.

select o.customername as name, o.email as email, group_concat(oi.orderitems) as items
from orders as o
join orderitems as oi using (orderid)
group by orderid

the on syntax would be:

select o.customername as name, o.email as email, group_concat(oi.orderitems) as items
from orders as o
join orderitems as oi 
on o.orderid = oi.orderid
group by orderid

You were using a loose join but the explicit is cleaner and more accurate.

The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, the ON clause serves for conditions that specify how to join tables, and the WHERE clause restricts which rows to include in the result set