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?

[soak]

It's not really ideal because you're querying the order_line table for every order that you do. You could get all of that from one query if you need to.

 

Say you only need id and name from the main orders table and id, productname, cost from the line items table:

 

$order = $db->getAssoc('SELECT o.id, o.name, oli.id as line_id, oli.productname, oli.cost FROM orders AS o INNER JOIN order_line_item AS oli ON oli.order_id = o.id');

 

You may have a bit more work sorting it when it comes out though. Also, you'll have o.id and o.name for each of your order lines.

 

A couple of critiques:

 

Select * is "bad". If possible list just the fields that you need in your SQL query. This may make your results array smaller and will ensure that you get exactly the fields that you were expecting.

 

Do you use the whole array that is returned (i.e. do you display all of it at once)? If not then you ought to add a limit clause to your select query and just grab the data that you need. Add some pagination and incorporate that with your select and limit clause.

 

Another way to do it would be to keep your first query as is and get the data for your second query all at once, indexing it by order id. This way you could use just 2 queries and get the same data that you do at the moment. Saying that though I assume that you aren't getting all the data from the table as in your example query and that you are limiting it in some way so this may not be possible.