current community

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm pulling numerical data from the database based on a date selected by the user. For some reason, when I select my function to pull all of today's data (there is none), it still forms an array of null values.

I'm thinking the problem lies in either the select_sum() or where() functions. Here's my CI query: 

    $this->db   
            ->select_sum('column1')
            ->select_sum('column2')
            ->select_sum('column3')
                ->where('created_at >=', date('Y-m-d'));
            $query = $this->db->get('table_name');

And here is my foreach loop that pulls all of the selected data into an array to be used throughout the page:

$popular_items_array = array();
        foreach ($query->result() as $key => $row) 
        {   
            if ($row == NULL) {
                echo "Error populating table.";
            } else {
             $popular_items_array = $row;
            }
        }

To take a look at the data, I then did:

echo json_encode($popular_items_array);

Which turns out showing this: 

{"column1":null,"column2":null,"column3":null}

If I select a different time frame (where data actually exists by the set date) that same JSON echo will display the existing data. The thing I'm not understanding is, why is the query returning anything at all? Why isn't it just failing? And how can I run a check / loop that will catch this problem, and display an error message letting the user know that no data exists on that date?

share|improve this question
 
What does $this->db->last_query(); return after the $query = $this->db->get('table_name'); line? I'd be interesting in seeing what the actual query looks like. –  Dazz Knowles Jul 1 '13 at 18:33
 
@DazzKnowles the query looks like this SUM(column1) AS column1, SUM(column2) AS column2, SUM(column3) AS column3 FROM (table_name) WHERE created_at >= '2013-07-01' –  PrinceEnder Jul 1 '13 at 18:40
add comment

2 Answers

up vote 2 down vote accepted

If you'd prefer to get no record back at all you could amend your code to be:

$this->db   
    ->select_sum('column1')
    ->select_sum('column2')
    ->select_sum('column3')
        ->where('created_at >=', date('Y-m-d'))
        ->having('count(*) > 0');
$query = $this->db->get('table_name');
share|improve this answer
 
Hmm, so adding "having()" will stop the query from going through, but how am I able to check that it failed? That's the real problem - displaying whether or not these sum queries are actually doing anything relevant. –  PrinceEnder Jul 1 '13 at 19:11
 
Fixed that, I added an if statement saying if(empty($popular_items_array) –  PrinceEnder Jul 1 '13 at 19:13
add comment
up vote 1 down vote

This is how the aggregate functions (like sum(), avg(), max() and others) work, they will return the aggregated value from the result set. If the result set is an empty one, they will aggregate that. This cause a lot of confusion and bugreports but this is how this should work, a more detailed explanation can be found at dba.stackexchange.com

You can use COALESCE to substitute the NULL values to something more useful, or you can add a count() so you can tell how many rows was used to generate the sum()s.

Strangely enough, if you add a group by it will work as you would expect (at least with mysql):

SELECT sum(id) sum_id FROM `users` WHERE 0 # => array( array('sum_id' => null) )

But:

SELECT sum(id) FROM `users` WHERE 0 GROUP BY null # => array()
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.