Database query using CodeIgniter returning null values into array?

Question

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?

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
– Dazz Knowles, Commented Jul 1, 2013 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' — Spookytheboy
– Spookytheboy, Commented Jul 1, 2013 at 18:40

Dazz Knowles · Accepted Answer · 2013-07-01 18:54:23Z

3

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');

answered Jul 1, 2013 at 18:54

Dazz Knowles

5342 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Spookytheboy Over a year ago

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.

Spookytheboy Over a year ago

Fixed that, I added an if statement saying if(empty($popular_items_array)

Community · Accepted Answer · 2017-05-23 12:32:32Z

1

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()

edited May 23, 2017 at 12:32

CommunityBot

11 silver badge

answered Jul 1, 2013 at 18:40

complex857

20.9k6 gold badges54 silver badges56 bronze badges

Collectives™ on Stack Overflow

Database query using CodeIgniter returning null values into array?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related