Lost in the middle of mysql and objects

[eluser]andriu[/eluser]
Hi Jedd

I solved it using a for loop, but it would be great If you know a better way of doing by Joining tables.

Tables:
proforma(containing invoices)
id client_name notes


proforma_products
id proforma_id qty price


proforma_discounts
id product_id amount type



The proforma has products, and products can have discounts.

The code I use now:

Code:

// Get the Proforma
$query = $this->db->get_where('proforma', array('id' => $id));

$row = $query->row();
$query = $this->db->get_where('proforma_products', array('proforma_id' => $id));

// Add products to proforma        
$row->products = $query->result();
        
foreach($row->products as $key => $value){
     $query = $this->db->get_where('proforma_discounts', array('product_id' => $value->id));
     $row->products[$key]->discounts = $query->result();
        }

return $row

This way I can access the discount for products by:
$proforma->products->discount

But as I said, If you can think of more effective way of doing this, I would be grateful.

Hopefully I explained it better this time.

Thanks