php - 查询按外来id输出一组值

Question

编码 JSON 格式的逻辑方法是什么，它将输出类似于此的内容

{
   "Student":{
      "studentId":"11-13555",
      "Orders":[
         {
            "transactionId":"20140310-3241-2135",
            "Transactions":[
               {
                  "dateOrdered":"2014-07-07 23:21:56",
                  "productId":12,
                  "quantity":3
               },
               {
                  "dateOrdered":"2014-07-07 23:22:26",
                  "productId":8,
                  "quantity":1
               }
            ]
         },
         {
            "transactionId":"20140310-1541-2134",
            "Transactions":[
               {
                  "dateOrdered":"2014-07-07 23:23:36",
                  "productId":12,
                  "quantity":1
               }
            ]
         }
      ]
   }
}

给定这张表

tblOrders

+==========================================================================================+ 
| id |    transactionId    |     dateOrdered     | studentId | quantity | productId | .... |
|====+=====================================================================================|
|  1 | 20140310-3241-2135  | 2014-07-07 23:21:56 |  11-13555 |        3 |        12 |      |
+----+---------------------+---------------------+-----------+----------+-----------+------+
|  2 | 20140310-3241-2135  | 2014-07-07 23:22:26 |  11-13555 |        1 |         8 |      |
+----+---------------------+---------------------+-----------+----------+-----------+------+
|  3 | 20140310-1541-2134  | 2014-07-07 23:23:36 |  11-13555 |        1 |        12 |      |
+----+---------------------+---------------------+-----------+----------+-----------+------+

不知何故，我想不出如何使用这段代码对其进行编码:

$result = $conn->query("SELECT * FROM tblOrders WHERE studentId=$studentId GROUP BY transactionId");

if ( $result && $result->num_rows > 0 ) {

    $orders = array();
    while( $row = $result->fetch_array() ) {
        $orders[] = $row;
    }

    $response["Orders"] = $orders;
    //$response["Student"] = "";
    $response["status"] = "success";

} else { /* some code... */ }

print json_encode( $response );

因为它给了我一个不一致的“键”，我无法确定它们中的哪一个是我正在寻找的，这是我第一次使用关键字 GROUP BY 查询数据库。

Answer 1

皮尤。吃点茶点后，我想出了一些解决这个问题的愚蠢方法。它不是很好，但它正在“工作”并且做我想做的事。所以我想我会发布我的解决方案:

我已经把它变成了两个不同的查询。虽然不是那么理想。

如果大家有更好的解决办法，欢迎批评指正，我会欣然采纳。

解决方案:

$orderList = array();

$result1 = $conn->query("SELECT DISTINCT transactionId FROM tblOrders WHERE studentId = $studentId");

if ($result1 && $result1->num_rows > 0) {

    $orders = array();
    while ( $row1 = $result1->fetch_array() ) {

        $transactionId = $row1["transactionId"];
        $result2 = $conn->query(" SELECT"
                                    .   "  e1.dateOrdered"
                                    .   ", e2.name as productName"
                                    .   ", e2.price as productPrice"
                                    .   ", e1.quantity"
                                    .   ", (e1.quantity * e2.price) as totalPrice"
                            .   " FROM tblOrders e1"
                            .   " INNER JOIN tblProducts e2"
                            .   " ON (e1.productId = e2.id)"
                            .   " WHERE transactionId = '$transactionId'"
                            .   " AND studentId = $studentId"
                            .   " ORDER BY dateOrdered");

        $transactionList = array();
        while( $row2 = $result2->fetch_array() ) {
            $transactions["dateOrdered"] = $row2["dateOrdered"];
            $transactions["productName"] = $row2["productName"];
            $transactions["productPrice"] = $row2["productPrice"];
            $transactions["quantity"] = $row2["quantity"];
            $transactions["totalPrice"] = $row2["totalPrice"];
            $transactionList[] = $transactions;
        }

        $orders["transactiondId"] = $transactionId;
        $orders["Transactions"] = $transactionList;
        $orderList[] = $orders;
    }

    $student["Orders"] = $orderList;

    $response["status"] = "success";
    $response["Student"] = $student;

} else {
    $response["status"] = "failure";
    $response["message"] = "No order history";
}

print json_encode( $response );

结果:

{
   "status":"success",
   "Student":{
      "Orders":[
         {
            "transactiondId":"20141028-9364-2677-9324",
            "Transactions":[
               {
                  "dateOrdered":"2014-10-28 01:25:55",
                  "productName":"Polo",
                  "productPrice":"400",
                  "quantity":"2",
                  "totalPrice":"800"
               },
               {
                  "dateOrdered":"2014-10-28 01:25:55",
                  "productName":"Polo",
                  "productPrice":"400",
                  "quantity":"1",
                  "totalPrice":"400"
               }
            ]
         },
         {
            "transactiondId":"20141028-2272-1336-5641",
            "Transactions":[
               {
                  "dateOrdered":"2014-10-28 00:13:17",
                  "productName":"Polo",
                  "productPrice":"400",
                  "quantity":"2",
                  "totalPrice":"800"
               }
            ]
         },
         {
            "transactiondId":"20141027-9409-8121-9023",
            "Transactions":[
               {
                  "dateOrdered":"2014-10-27 23:45:22",
                  "productName":"Polo",
                  "productPrice":"400",
                  "quantity":"12",
                  "totalPrice":"4800"
               }
            ]
         },
         {
            "transactiondId":"20141027-3100-8787-4934",
            "Transactions":[
               {
                  "dateOrdered":"2014-10-27 23:21:56",
                  "productName":"Polo",
                  "productPrice":"400",
                  "quantity":"2",
                  "totalPrice":"800"
               }
            ]
         },
         {
            "transactiondId":"20141027-6525-9465-5526",
            "Transactions":[
               {
                  "dateOrdered":"2014-10-27 23:16:13",
                  "productName":"CLUB SHIRT",
                  "productPrice":"200",
                  "quantity":"4",
                  "totalPrice":"800"
               },
               {
                  "dateOrdered":"2014-10-27 23:16:13",
                  "productName":"Polo",
                  "productPrice":"400",
                  "quantity":"10",
                  "totalPrice":"4000"
               },
               {
                  "dateOrdered":"2014-10-27 23:16:13",
                  "productName":"Polo",
                  "productPrice":"400",
                  "quantity":"6",
                  "totalPrice":"2400"
               },
               {
                  "dateOrdered":"2014-10-27 23:16:13",
                  "productName":"Polo",
                  "productPrice":"400",
                  "quantity":"2",
                  "totalPrice":"800"
               },
               {
                  "dateOrdered":"2014-10-27 23:16:13",
                  "productName":"CLUB SHIRT",
                  "productPrice":"200",
                  "quantity":"12",
                  "totalPrice":"2400"
               },
               {
                  "dateOrdered":"2014-10-27 23:16:13",
                  "productName":"CLUB SHIRT",
                  "productPrice":"200",
                  "quantity":"8",
                  "totalPrice":"1600"
               }
            ]
         }
      ]
   }
}

我已经删除了那些“不太有用”的字段，如果您注意到的话，我会带出必要的字段。