Calling a query within a view... i know it's bad, so i need help!

[eluser]include[/eluser]
Hello!

I'm trying to get my head around displaying a nice "My orders" page in an app - the page list's each order in a box, with all the items in that order next to it. It's done by joining three tables (Orders, Items, Products), The items table has a product_id and an order_id etc.

So that's great and working, but the trouble i've hit is that query i'm using...

Code:

function get_all_orders() {
  $this->db->select('o.*,p.*,i.*,o.order_id AS order_id,p.product_id AS product_id, i.item_id as item_id');
  $this->db->from('Orders o');
  $this->db->join('Items i','o.order_id = i.order_id','left');
  $this->db->join('Products p','p.product_id = i.product_id','left');
  $this->db->order_by('o.order_id', 'desc');  
  $query = $this->db->get();
  return $query->result();                  
}

Returns each row as a separate result, so then i'm using something like this in the view to separate the orders...

Code:

$currentParent = false;
foreach ($orders as $o) {
    if ($currentParent != $o->order_id) {
        echo '<h1>ORDER NUMBER ' . $o->order_id . '</h1>';
        $currentParent = $o->order_id;
    }
    echo '<p>' . htmlentities($o->product_name) . '</p>';
}

But now i'm running into problems with trying to use the Pagination function, as one Order box could be 5 records (1 order record then 4 items) so it doesn't work.

I'm thinking it would be super easy (but bad) to just call the items query from within the view... but i don't want to do that. I'm thinking there must be a way to get the SQL result into a more suitable array structure where each order contains a sub-array with the items... but i've no idea how to do that in MYSQL or how to return it in the correct way in the view.

Bit stuck really - anybody been here/done that before and fancy showing me the error of my ways?

Thanks in advance :-)

[eluser]EyeAmN8[/eluser]
You might need to approach this in a different way. If you have multiple items for one order number, and all of them are separate db entries you may want to build a new array or arrays from the query results.
So, you could run a query to get all of the order numbers and store it in an array, then run another query to get the orders for each of the order numbers. Then you could use the two arrays to build another 2 dim that would be easier to loop through in the view.

so by the time your array gets to the view it could be like :

Code:

$orders['order_number'][array of orders for this order number];

As far as pagination goes it would be the same as paging through multiple blog posts.
Also, this all depends on how you keep your data in the db.