如何将多个查询合并为一个查询，并使用单个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'

你需要加入你的表。这是未经测试的，所以就像这样。

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

还要注意的是，order是mysql中的保留字，因此不是一个好的表名称-因此会有反引号。

Answer 2

SELECT 
    orders_assigned.delivery_status AS delivery_status,
    orders_assigned.username AS username,
    orders_assigned.order_id AS order_id,
    delboy_login.name AS del_name,
    delboy_login.lat AS del_lat,
    delboy_login.longi AS del_longi,
    order.address_id AS address_id,
    order.user_id AS user_id,
    customer_login.name AS cus_name,
    address.lat AS cus_lat,
    address.longi AS cus_longi
FROM
    orders_assigned
        LEFT JOIN
    delboy_login ON delboy_login.id = orders_assigned.username
        LEFT JOIN
    orders ON order.order_id = orders_assigned.order_id
        LEFT JOIN
    customer_login ON customer_login.cust_id = order.user_id
        LEFT JOIN
    address ON address.a_id = order.address_id

这是您想要的SQL。

请注意，您可以重命名查询返回的列，如下所示。

    delboy_login.name AS del_name,
    delboy_login.lat AS del_lat,
    delboy_login.longi AS del_longi,

    customer_login.name AS cus_name,
    address.lat AS cus_lat,
    address.longi AS cus_longi

连接表是非常重要的，我希望您将来能够选择查询模式。

祝你好运。

编辑：

您想要的输出可以通过简单地创建两个不同的temp_arrays，并将它们一个接一个地推入主数组来实现。

if ($count1 >= 1) {
    while ($row = $sql->fetch()) {
        $delivery_status = $row['delivery_status'];
        $deliveryboy_id  = $row['username'];
        $order_id        = $row['order_id'];
        $del_name  = $row['del_name'];//name
        $del_lat   = $row['del_lat'];//lat
        $del_longi = $row['del_longi'];//long
        $del_icon  = $row['del_icon'];//icon
        $address_id = $row['address_id'];
        $user_id    = $row['user_id'];
        $cus_name  = $row['cus_name'];//name
        $cus_lat   = $row['cus_lat'];//lat
        $cus_longi = $row['cus_longi'];//long
        $cus_icon  = $row['cus_icon'];//icon

        $tmp_del                 = array();
        $tmp_del['del_lat']      = $del_lat;
        $tmp_del['del_name']     = $del_name;
        $tmp_del['del_longi']    = $del_longi;
        $tmp_del['del_icon'] = $del_icon;

        $tmp_cus               = array();
        $tmp_cus['cus_lat']    = $cus_lat;
        $tmp_cus['cus_name']   = $cus_name;
        $tmp_cus['cus_longi']  = $cus_longi;
        $tmp_cus['cus_icon']   = $cus_icon;

        array_push($response, $tmp_del);
        array_push($response, $tmp_cus);
    }

Answer 3

试试这个:它会起作用的！

SELECT oa.delivery_status, oa.username oa.order_id, dl.name, dl.lat, dl.longi, o.address_id, o,user_id, cl.name, a.lat, a.longi
FROM orders_assigned as oa 
JOIN delboy_login as dl ON oa.username = dl.id 
JOIN order as o ON oa.order_id = o.order_id
JOIN customer_login as cl ON  = o.user_id = cl.cust_id
JOIN address as a ON  = o.address_id = a.a_id
WHERE oa.delivery_status != 'Cancelled' && oa.order_status='Picked'