CodeIgniter Forums
Having issues with sumin data from an array - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Having issues with sumin data from an array (/showthread.php?tid=51350)

Having issues with sumin data from an array - El Forum - 04-30-2012

[eluser]The Revel[/eluser]
My table contains this data:

Code:
tid     email               amount     date
1       [email protected]     500.00     2012-04-12
2       [email protected]     -10.00     2012-04-12
3       [email protected]     -15.50     2012-04-14
4       [email protected]     -25.10     2012-04-16
5       [email protected]     100.00     2012-04-18

I am trying to sum the amounts based on email address (transaction details)

Here is what I have while trying to debug:

Code:
$q2 = $this->db->query('SELECT *, SUM(amount) FROM transactions WHERE email="'. $useremail .'" GROUP BY email');
          if ($q2->num_rows() > 0)
            {
            $row2 = $q2->row();
            $this->data['sum'] = $query->result();
            
            }
          echo $this->data['sum'];

When I echo $this->data['sum'] I get "Array" and not the ammount. I am new to CodeIgniter and shamelessly have no idea what I am doing wrong. I want to pass this variable to the view page as I have a list of all the transactions, and need a total (account balance).

Any help is much appreciated.
Thank you.

Having issues with sumin data from an array - El Forum - 04-30-2012

[eluser]The Revel[/eluser]
Ok, I modified the query a bit:
Code:
$q2 = $this->db->query('SELECT SUM(amount) FROM transactions WHERE email="'. $useremail .'" GROUP BY email');
          if ($q2->num_rows() > 0)
            {
            $row2 = $q2->row();
            $this->data['sum'] = $q2->result();
            
            }
          echo $this->data['sum'];

Now if I run the query in my MySQL manager I get the return of

Sum(amount)
$$$$.$$ <- the actual sum

But it still shows up as an array despire the fact I am getting back 1 item.

Having issues with sumin data from an array - El Forum - 04-30-2012

[eluser]Stefan Hueg[/eluser]
Try this
Code:
$q2 = $this->db->select('SUM(amount) as sum_amount', FALSE)
   ->from('transactions')
   ->where('email', $useremail)
   ->group_by('email')
   ->get();

  if ($q2->num_rows() > 0)
  {
   $this->data['sum'] = $q2->row()->sum_amount;  
  }
  echo $this->data['sum'];

I've also converted your query to ActiveRecord (you should get used to it Wink
http://ellislab.com/codeigniter/user-guide/database/active_record.html

Having issues with sumin data from an array - El Forum - 04-30-2012

[eluser]pbflash[/eluser]
result() will return an object regardless of how many entries it contains. You should use row() for a single result.

Try:
Code:
$q2 = $this->db->query('SELECT SUM(amount) as total FROM transactions WHERE email="'. $useremail .'" GROUP BY email');
          if ($q2->num_rows() > 0)
            {
            $row = $q2->row();
            $this->data['sum'] = $row->total;            
            }
          echo $this->data['sum'];


Having issues with sumin data from an array - El Forum - 04-30-2012

[eluser]The Revel[/eluser]
Thank you very much, worked like a charm.