How to easily split results in PHP with a LEFT JOIN mysql query?

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?

If you want two arrays, take 2 trips to the database. Once for the orders, once for the details of those orders. By left joining your causing the split problem you're trying to fix. — xQbert, Apr 03 '16 at 13:47
Well, I guessed that performance-wise letting PHP and MySQL handle one query instead of 2 is better than 2 queries? — PIDZB, Apr 03 '16 at 13:48
Personally, I find getting the data set I need to work with, no more is easier. Not knowing the full use case here I can't say which approach is better. But given that you're trying to populate 2 arrays, I'd take two trips. But then, why do you need two arrays. If one set of data contains everything you need to render, than why split into two arrays? you can use control break logic and iterate though the "orders" and then when it "changes" write out a new header and then iterate though the details. (basically ignoring the "order" info until it changes) — xQbert, Apr 03 '16 at 13:55

score 1 · Accepted Answer · edited May 23 '17 at 12:23

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.

How to easily split results in PHP with a LEFT JOIN mysql query?

1 Answers1