CodeIgniter Forums

Full Version: Outputting Select Sum
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]tecrik[/eluser]
Hey -

I am trying to output the sum of a database column. The calculations seem to work but the output is not a single number but an array which looks like this:

Array ( [SUM(summe)] => 592299 )

How do I get CI to only show '592299'? I pasted the relevant code below.

Thanks!


Model:
Code:
function revenue ()
    {
    $query = $this->db->select('SUM(summe)', FALSE)
    ->from('orders')
    ->where('hidden', 0)
    ->get();
                                            
    return $query->row_array();
    }

Controller:
Code:
...
$data['revenue'] = $this->m_bestellung->revenue();
        
    $content = $this->parser->parse('bestellungen/template', $data, true);
    $this->view_template($content, $data);
    }

View:
Code:
...
<?php
print_r ($revenue);
?>
..

El Forum

[eluser]Dam1an[/eluser]
The "other stuff" is just the key in the array
You could always alias it using 'AS' and then use that key in the array to get just the value

Code:
function revenue ()
    {
    $query = $this->db->select('SUM(summe) AS `total`', FALSE)
    ->from('orders')
    ->where('hidden', 0)
    ->get();
                                            
    $row = $query->row_array();
    return $row['total'];
    }

Obviously put a check to make sure it's set and what not before you convert query to row_array Wink

El Forum

[eluser]tecrik[/eluser]
Thanks, that worked! Knowing about "AS" would have helped a lot ;-) I find myself having to learn PHP and CI at the same time on a project that's just a bit to extensive for that purpose ;-)

I now know wrote the function like this:

Code:
function revenue ()
{
$query = $this->db->select('SUM(summe) AS `total`', FALSE)
    ->from('orders')
    ->where('hidden', 0)
    ->get()
    ->row();
                                            
    return $query->total;
            
        }

El Forum

[eluser]jedd[/eluser]
AS is a SQL construct.

If you're leaning PHP, CI and SQL all at the same time .. you'll find lots of surprises like this. Wink

El Forum

[eluser]tecrik[/eluser]
No kidding, I am having a blast Wink Thanks so much for the help here, though!

So I ran into a different problem with this part:

Code:
function order_value_average ()
{
  $this->db->select_avg('summe');
  $query = $this->db->get('orders');

  return $query->summe;
            
}

According to the user guide, this part

Code:
$this->db->select_avg('summe');
$query = $this->db->get('orders');

should produce something like "SELECT AVG(summe) as summe FROM members" which makes the entire snippit very similar to the one in my inital post. Still, I get the following error message "Undefined property: CI_DB_mysql_result::$summe" in my view.

In general, I am confused about the "return" part, meaning how to return data and in which way. Is there some kind of documentation for that?

El Forum

[eluser]Dam1an[/eluser]
you need the second paramter to give it an alias again
At the moment, it just averages the summe column

El Forum

[eluser]tecrik[/eluser]
What do you mean second parameter? I tried

Code:
$this->db->select_avg('summe', 'summe');

as well as

Code:
$this->db->select_avg('summe', $summe);

Thanks!

El Forum

[eluser]Dam1an[/eluser]
Taken from the user guide
Quote:Writes a "SELECT AVG(field)" portion for your query. As with select_max(), You can optionally include a second parameter to rename the resulting field.

Code:
$this->db->select_max('age', 'member_age');
$query = $this->db->get('members');
// Produces: SELECT MAX(age) as member_age FROM members

So in the same way you aliased the SUM operation to total, you can do the same here, and then use that name to access the variable in your row

El Forum

[eluser]tecrik[/eluser]
Thanks, don't know why it didn't work but I am using it like this now:

Code:
function order_value_average ()
       {

    $this->db->where('hidden', 0);
    $this->db->select_avg('summe', 'average', FALSE);
    $query = $this->db->get('orders')
    ->row();
                                            
        return $query->average;
            
    }