I have two tables created for and online ordering system that look as such:
Table 1 (orders)
15 | joe | joe@s.com
16 | ron | ron@this.com
17 | tim | tim@nice.com
18 | sue | sue@rad.com
and: Table 2 (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!!
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