Efficient way to list multiple items, all of which have multiple line items

[versatilewt]

So, basically I need to list multiple orders on a page, along with their line items. There is an orders table, and a line items table - basic set up. This is the way I'm doing it now, and I'm wondering if there's not a more efficient way to do this. I've simplified the code a bit for my question here, but it gets the point across

$order = $db->getAssoc(SELECT * FROM orders);

foreach($order as $k=>$v) {
	$line_item = $db->getAssoc("SELECT * FROM order_line_item WHERE order_id = $k");
            $order[$k]['shipping'] = $line_item;
}

And then I just operate on the order array to get the info. Is there a better way to do this?

[sneakyimp]

It seems efficient enough to me. What exactly seems inefficient about it to you?

[etully]

This hits mysql with an N+1 query. That is, one query to find out the ids of the orders and then N queries to get the products for the orders. N+1 queries are fine if you only have a few thousand visitors per day but they will crush a server once you get any kind of load. And it's just a bad habit.

Look into inner joins.

Sort of like this:

select line_id,user_id,product_name from line_items inner join orders on line_items.order_id = orders.id;

[sneakyimp]

Ah I overlooked that. You would also need to order your query results by order_id. something like this:

$data = $db->getAssoc("SELECT O.id, O.name AS order_name, O.whatever, L.* FROM orders O, order_line_item L WHERE L..order_id = O.id ORDER BY O.id");
$last_order_id = NULL;
foreach($data as $key => $value) {
  if ($last_order_id != $value['order_id']) {
    echo "new order:" . $value['order_name'] . "<BR>";
    // output any additional order-related info here.
  }
  // output a line item here

  $last_order_id = $value['order_id'];
}

The trade off will be that your query result is larger because you have a lot of redundant order data.

EDIT: forgot very important last line of the loop