Lost in the middle of mysql and objects

[eluser]andriu[/eluser]
Hi guys.

First of all I want to say thanks for quick response and help recieved on this forum!

Ok, Im a bit lost.

I have 3 tables.

Code:

proforma
     proforma_products
             proforma_products_discounts

I would like to get results and pass it all as one object.

To do this im trying:

Code:

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

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

// now i want a query that add the discounts(if there is any) for the relevant product to the object
// but i'm a bit lost.
$row->products->discount = $query->result();

I'm not that used with working with objects.
My solution would be doing a for() with php and add it to an multidimensional array.

Thanks for any help!

[eluser]jedd[/eluser]
[quote author="andriu" date="1256502658"]
I'm not that used with working with objects.
[/quote]

Use arrays, then. The functions are similar. You're under no obligation to use objects to retrieve/access your data.

Can you better describe your three tables. Your indenting, and the code you presented, hints at a hierarchical relationship (which might mean you've misunderstood RDBMS theory), and I'm tempted to suggest two LEFT JOINS against your proforma selection would get you where you want to go ... but your requirements are a tad fuzzy.

[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

[eluser]jedd[/eluser]
[quote author="andriu" date="1256525558"]
Tables:
proforma
id
client_name
notes

proforma_products
id
proforma_id
qty
price

proforma_discounts
id
product_id
amount
type
[/quote]

Untested(!) but something like this:

Code:

SELECT *
FROM proforma
LEFT JOIN proforma_products ON proforma_products.proforma_id=proforma.id
LEFT JOIN proforma_discounts ON proforma_discounts.proforma_id=proforma.id
WHERE proforma.id = $id

You may have to modify the SELECT parameters to rename (using AS) any potential duplicates, or just narrow down the things you want (SELECT client_name, notes, qty, price, amount, type)

Left as an exercise to the reader to convert this to AR - not a big fan of that class, so don't use it. If you go the ->query approach instead, be sure to vet your input data - an is_numeric() test against $id should suffice here.

Btw I'd consider renaming your tables - unless you really need proforma_ in front of the second and third table names.