Join 2 tables resulting in multidimensional array

Question

I have 2 (mysql) tables:

orders:
   order_number

deliveries:
   id, order_number, status

Orders can have multiple deliveries in the deliveries table. Right now I query for 50 orders, then loop them (php) and query the deliveries table for deliveries where order_number = orders.order_number and merge the arrays.

Ideally this is one query, not 51. I've tried 100 combinations of joins and groups but can't crack this seemingly simple query. Result should be:

[orders] => Array
    (
        [0] => Array
            (
                [order_number] => 16021322220558
                [deliveries] => Array
                    (
                        [0] => Array
                            (
                                [id] => 13332
                                [order_number] => 16021322220558
                                [status] => delivering
                            )
                        [1] => Array
                            (
                                [id] => 13333
                                [order_number] => 16021322220558
                                [status] => delivering
                            )
                    )

            )
        [1] => Array
            (
                [order_number] => 16021322220559
                [deliveries] => Array
                    (
                        [0] => Array
                            (
                                [id] => 13334
                                [order_number] => 16021322220559
                                [status] => delivering
                            )
                        [1] => Array
                            (
                                [id] => 13335
                                [order_number] => 16021322220559
                                [status] => delivering
                            )
                    )

            )
        ...
    )

Edit: Here's what I ended up with. IN was the insight that got this from 51 queries down to 2!

<?php
// Query for the last 50 orders
$q_orders['orders'] = // SELECT * FROM orders LIMIT 50

// Create array of order numbers
foreach($q_orders['orders'] as $order){
    $order_numbers[] = $order['order_number'];
}   

// Query for all the deliveries
$q_deliveries = // SELECT * FROM deliveries WHERE order_number IN ($order_numbers)

// For each order, append the deliveries with the same order_number
$i = 0;
foreach($q_orders['orders'] as $order){

    foreach($q_deliveries as $delivery){

        if($delivery['order_number'] == $order['order_number']){

            $q_orders['orders'][$i]['deliveries'][] = $delivery;

        }

    }

    $i++;

}

Answer 1

Ok, so from what I could gather, I'll say this:

SELECT id, orders.order_number, status
 FROM orders, deliveries
  WHERE orders.order_number = 'ordernumber'
   AND deliveries.order_number = orders.order_number 

However, I'm quite confused as to why you need the original order number in the first place. You could just select the deliveries.order_number right off the bat, couldn't you? like this:

SELECT id, order_number, status
     FROM deliveries
      WHERE order_number = 'ordernumber'

Now for some PHP magic (I believe this should make the difference, tho I am unable to test it at this time)

<?php
    $result = mysql_query($query);
    $result_array = array();

    while($row = mysql_fetch_array($result)){
      $result_array[] = $row;
    }
?>

I'm sorry, I believe I understand your question now. In that case, no, you cannot use mysql to return a natural php-based multidimensional array. (mysql can't format the results for a specific language)

You will always have to merge the arrays manually if you want to change how the results are sorted.

Edit: For the multiple orders query

SELECT id, order_number, status
     FROM deliveries
      WHERE order_number IN (2323, 34, 25, 44646, 24, 26)

You will want to use IN. This means the query will grab any of these order numbers. It's quite easy to fill IN with a php array like so:

<?php
 $ordernumbers = implode(",", $arrayWithOrderNumbers);
 $query = "SELECT id, order_number, status
     FROM deliveries
      WHERE order_number IN ($ordernumbers)";
?>

IN is basically short for this: WHERE order_number = 34 OR order_number = 35 OR order_number = 34

etc. etc.