php - 如何将多个查询合并为一个并使用单个 while 循环获取数据？

Question

我有这段代码，如何将多个查询及其结果合并为一个？

<?php
$obj = new stdClass();
include('pdoConfig.php');
$response = array();
$sql1     = $dbh->prepare("select * from orders_assigned where delivery_status != 'Cancelled' && order_status='Picked'");
$sql1->execute();
$count1 = $sql1->rowCount();
if ($count1 >= 1) {
    while ($row1 = $sql1->fetch()) {
        $delivery_status = $row1['delivery_status'];
        $deliveryboy_id  = $row1['username'];
        $order_id        = $row1['order_id'];
        $sql2            = $dbh->prepare("select * from delboy_login where id = ?");
        $sql2->bindParam(1, $deliveryboy_id);
        $sql2->execute();
        $row2      = $sql2->fetch();
        $del_name  = $row2['name'];//name
        $del_lat   = $row2['lat'];//lat
        $del_longi = $row2['longi'];//long
        $del_icon  = $row2['icon'];//icon
        $sql3      = $dbh->prepare("select * from `order` where `order_id` = ?");
        $sql3->bindParam(1, $order_id);
        $sql3->execute();
        $row3       = $sql3->fetch();
        $address_id = $row3['address_id'];
        $user_id    = $row3['user_id'];
        $sql4       = $dbh->prepare("select * from customer_login where cust_id = ?");
        $sql4->bindParam(1, $user_id);
        $sql4->execute();
        $row4 = $sql4->fetch();
        $cus_name  = $row4['name'];//name
        $sql5 = $dbh->prepare("select * from address where a_id = ?");
        $sql5->bindParam(1, $address_id);
        $sql5->execute();
        $row5      = $sql5->fetch();
        $cus_lat   = $row5['lat'];//lat
        $cus_longi = $row5['longi'];//long
        $cus_icon  = $row5['icon'];//icon

        $tmp            = array();
        $tmp['lat']     = $del_lat;//i want use $cus_lat here too
        $tmp['content'] = $del_name;//i want use $cus_name here too
        $tmp['lng']     = $del_longi;//i want use $cus_longi here too
        $tmp['icon']    = $del_icon;//i want use $cus_icon here too
        array_push($response, $tmp);
    }
}
echo json_encode($response, JSON_NUMERIC_CHECK);

使用这个我只能获取 $del_ 数据或 $cus_ 数据，我想同时使用两者。我有多个表用于相同的数据，但我无法合并表，因为它们是不同类型的用户。由于我不擅长连接，所以我不知道如何在这里使用连接。

通过使用下面提供的查询之一，我得到了这个

{
"delivery_status" : Delivered,
"username" : 1,
"order_id" : 5,
"del_name" : Boy One,
"del_lat" : 26.8808383,
"del_longi" : 75.7503407,
"address_id" : 31,
"user_id" : 1,
"cus_name" : Roylee Wheels,
"cus_lat" : 20.593684,
"cus_longi" : 78.96288
},
{
"delivery_status" : Processing,
"username" : 1,
"order_id" : 6,
"del_name" : Boy One,
"del_lat" : 26.8808383,
"del_longi" : 75.7503407,
"address_id" : 30,
"user_id" : 1,
"cus_name" : Roylee Wheels,
"cus_lat" : 20.594725,
"cus_longi" : 78.963407
},

等等...

我想要这样的最终输出

{
"icon" : "icon path here",
"del_name" : "Boy One",
"del_lat" : 26.8808383,
"del_longi" : 75.7503407,
},
{
"icon" : "icon path here",
"cus_name" : "Roylee Wheels",
"cus_lat" : 20.594725,
"cus_longi" : 78.963407
},

等等...

Answer 1

select orders_assigned.*, order.*, address.* 
from orders_assigned
inner join delboy_login on delboy_login.id = orders_assigned.delboy_login
inner join `order` on `order`.`id` = orders_assigned.order_id
inner join customer_login on customer_login.cust_id = order.user_id
inner join address on address.a_id = order.address_id
where delivery_status != 'Cancelled' && order_status='Picked'

您需要加入您的 table 。这是未经测试的，所以类似这样。

从表中选择 * 是不好的做法，您实际上应该只定义您需要的字段。

另请注意，order 是 mysql 中的保留字，因此对于表来说不是一个好名称 - 因此需要反引号。