How to do a num_rows() on COUNT query in codeigniter?

Issue

This works:

        $sql = "SELECT id
                FROM `users`
                WHERE `account_status` = '" . $i . "'"; 
        $query = $this->db->query($sql);
        var_dump($query->num_rows());

But this doesn’t:

        $sql = "SELECT COUNT(*)
                FROM `users`
                WHERE `account_status` = '" . $i . "'"; 
        $query = $this->db->query($sql);
        var_dump($query->num_rows());

How to do a num_rows on a COUNT(*) query? Also is doing it the 2nd way any better performance wise?

Solution

Doing a COUNT(*) will only give you a singular row containing the number of rows and not the results themselves.

To access COUNT(*) you would need to do

$result = $query->row_array();
$count = $result['COUNT(*)'];

The second option performs much better since it does not need to return a dataset to PHP but instead just a count and therefore is much more optimized.

Answered By – Steven Lu

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0