如何使用LEFT JOIN mysql查询轻松地在PHP中拆分结果？

Question

I have a query in which I am tring to put the results in an array. The query returns all data of the two tables: order and orderdetails:

SELECT orders.*, order_details.* FROM `webshop_orders` 
        LEFT JOIN `order_details` 
        ON `orders`.`order_id` = `order_details`.`f_order_id` 
        WHERE `orders`.`f_site_id` = $iSite_id AND `orders`.`order_id` = $iOrder_id;";

I am trying to found out how to return this data an put them in an array of the following format:

$aOrders = array(
0=>array(Orders.parameter1=>value, orders.parameter2=>value, orders.parameter3=>value, 'orderdetails'=>array(
    array(Orderdetails.parameter1=>value, orderdetails.parameter2=>value)));

I currently return every result as an associate array and manually split every variable based on its name using 2 key-arrays, but this seems very 'labor-intensive'?

while($aResults = mysql_fetch_assoc($aResult)) { 
    $i++;
    foreach($aResults as $sKey=>$mValue){
        if(in_array($sKey, $aOrderKeys){
            $aOrder[$i][$sKey] = $mValue;
        } else {
            $aOrder[$i]['orderdetails'][$sKey] = $mValue;
        }
    }
}

EDIT: the function above does not take multiple order-details into consideration, but the function is meant as an example!

Is there an easier way or can I use a better query for this?

Answer 1

You can use the following while loop to fill your array:

$data = array();
while ($row = mysql_fetch_assoc($result)) {
    if (!isset($data[$row['order_id']])) {
        $order = array('order_id' => $row['order_id'],
                       'order_date' => $row['order_date'],
                       /* ... */
                       'orderdetails' => array());
        $data[$row['order_id']] = $order;
    }
    if (isset($row['order_details_id'])) { // or is it "!= null"? don't know...
        $details = array('id' => $row['order_details_id'],
                         'whatever' => $row['order_details_whatever']);
        $data[$row['order_id']]['orderdetails'][] = $details;
    }
}

This way you can have multiple orderdetails for one order, they get all added to the ['orderdetails'] field.

Additional notes:

• Do not use SELECT *, see the question What is the reason not to use select *? and any other website about this topic.
• Do not use the mysql_*() functions (even though I did above for showing the while loop), they are deprecated. Use PDO instead.