[eluser]vincej[/eluser]
Hi - I'm new to CI so please forgive what might be a silly question:
I have a table ( sample below) with Order ID, Product ID's and Ordervalues. I want to be able to sum the order value for each Product ID which are repeated by OrderID so that I get a single result which I can pass through my controller into my view which might look like: Total Order Value: Chickens: $5000.
Sample table:
Code:
orderid prodid ordervalue
DEA2806 25 25.00
DEA3216 23 16.00
BRO0289 22 35.00
BRO0289 17 18.00
BRO0289 18 43.00
DEA7759 28 62.00
JAC5354 17 240.00
DOE7762 17 240.00
So far my approach has been to first run a query which pulls the ProdID and Ordervalue. Put that into an array.
then run a second query, where I take the ProdID's from the array, and then do a sum on each one by ProdID.
Needless to say, my code does not do what I want it to do hence why I'm here. The first section, pulls to the ProdID's fine but does not give the order values - I don't understand why, similar queries have given multiple results.
My section 2 is probabaly completely wrong as all I am getting is the sum total of all products sold.
Model:
Code:
function ProductTotal(){
$result = array(); // Section one
$this->db->select('ProdID','ordervalue');
$this->db->from('order');
$this->db->where('status', 'open');
$Q = $this->db->get();
foreach ($Q->result_array() as $row){
$result[] = $row;
}
foreach ($result as $value){ //Section two
$this->db->select_sum('ordervalue');
$this->db->from('order');
$this->db->where('status', 'open');
$Q = $this->db->get();
foreach ($Q->result_array() as $row){
$productorder[] = $row;
}
return $productorder;}
}
If you help me out - I'd be super happy and super greatfull !
Many thanks !