php - 从mysql中的多个表中检索值

Question

我正在使用 php，我必须从具有公共(public) id 的多个表中获取数据，但问题是在少数表中，公共(public) id 包含多条记录，使用内部联接为我提供了单独的数据行，例如

{"dish_id":"52","quantity":"1","STATUS":"pending","franchise_id":"5","order_type":"PickUp","extraId":"2"}
    {"dish_id":"52","quantity":"1","STATUS":"pending","franchise_id":"5","order_type":"PickUp","extraId":"3"}

extraId是dish_id:52的多条记录。我需要这样的结果。

{"dish_id":"52","quantity":"1","STATUS":"pending","franchise_id":"5","order_type":"PickUp","extraId"[{"id":"2"},{"id":"3"]}}

我的查询是:

 $orders =  "Select DISTINCT order_detail.dish_id,order_detail.quantity,order_detail.STATUS,
order_main.franchise_id,order_main.order_type,
order_extras.extra_id,order_extras.extra_title,
order_addons.addon_id,order_addons.addon_size 
from order_main 
INNER JOIN order_detail ON order_main.id=order_detail.order_id 
INNER JOIN order_extras ON order_main.id=order_extras.order_id 
INNER JOIN order_addons ON order_main.id=order_addons.order_id 
WHERE order_main.franchise_id='$storeId' 
and 
order_detail.STATUS!='$order_status'";

请帮忙。

Answer 1

使用group by和group_concat。像这样的事情:

Select d.dish_id, d.quantity, d.STATUS, m.franchise_id, m.order_type,
       group_concat(e.extra_id) as extraids
from order_main m INNER JOIN
     order_detail d
     ON m.id = d.order_id INNER JOIN
     order_extras e
     ON m.id = e.order_id INNER JOIN
     order_addons a
     ON m.id = a.order_id 
where m.franchise_id = '$storeId' and d.STATUS <> '$order_status'
group by d.dish_id, d.quantity, d.STATUS, m.franchise_id, m.order_type;

您想要的结果不包括这些列:

• e.extra_title
• a.addon_id
• a.addon_size

我还建议您删除对 order_addons 的联接。

请注意，表别名使查询更易于编写和读取。