CODEWITHSUNDEEP
phpdatabasearrayscodeigniter
Spookytheboy
Spookytheboy

Reputation: 238

Database query using CodeIgniter returning null values into array?

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?

Upvotes: 1

Views: 3536

Answers (2)

complex857
complex857

Reputation: 20753

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

Upvotes: 1

Dazz Knowles
Dazz Knowles

Reputation: 534

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

Upvotes: 3

Related Questions