dtwy2858
2017-07-27 19:32
浏览 56
已采纳

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

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

orderid | customername | email

15 | joe | joe@s.com
16 | ron | ron@this.com
17 | tim | tim@nice.com
18 | sue | sue@rad.com

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!!

图片转代码服务由CSDN问答提供 功能建议

我有两个为在线订购系统创建的表,如下所示:
表1(订单)< / p>

orderid | 客户名| 电子邮件

15 | 乔| joe@s.com
16 | ron | ron@this.com
17 | 蒂姆| tim@nice.com
18 | 起诉| sue@rad.com

和:表2(orderitems)

id | orderid | orderitems

1 | 15 | 酵母
2 | 15 | 四谷
3 | 16 | 原创水果
4 | 16 | 芬兰黑麦
5 | 16 | 酵母
6 | 17 | 酵母
7 | 18 | 原始水果

两个表上的orderid是相同的,我试图找出如何循环遍历orderitems表中的所有orderid以打印每个客户的收据。

到目前为止,我有一个mysqli语句:

  $ sql ='SELECT a.orderid,a.customername,a.email  ,b.orderid,b.menuitem FROM命令a,orderitems b WHERE a.orderid = b.orderid'; 
 
 $ result = mysqli_query($ conn,$ sql); 
while($ row = mysqli_fetch_array($ result)  )){
 echo“&lt; td&gt;”  。  $ row ['customername']。  “&lt; / td&gt;”; 
 echo“&lt; td&gt;”  。  $ row ['phone']。  “&lt; / td&gt;”; 
 echo“&lt; td&gt;”  。  $ row ['email']。  “&lt; / td&gt;”; 
 echo“&lt; td&gt;”  。  $ row ['menuitem']。  “&lt; / td&gt;”; 
} 
   
 
 

这可以获得我需要的所有东西,除了我想单独分开每个订单,这样我就可以给每个客户发送一个 收据。 我不知道如何实现这一点,我已经用尽所有搜索条件,试图找到我要找的东西。
任何帮助将不胜感激。
提前谢谢!!

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dti3914 2017-07-27 19:42
    已采纳

    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

    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题