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

Question

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?

I should also mention to use the CodeIgniter Active Record class to help organize your code a bit better. — Steven Lu
– Steven Lu, Commented Jul 6, 2012 at 1:48

Steven Lu · Accepted Answer · 2012-07-06 01:42:56Z

14

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 Jul 6, 2012 at 1:42

Steven Lu

2,1801 gold badge24 silver badges33 bronze badges

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

4 Comments

Corbin Over a year ago

+1, but it might be worth mentioning that you can alias the column and then use that. Like SELECT COUNT(*) AS cnt ... and then use $result['cnt'].

user967451 Over a year ago

@Corbin yea I had to do it like that, COUNT(*) as the key wasn't working for some reason.

Frug Over a year ago

Skip a step and refer directly to the result without setting it as an array first: COUNT(*) AS cnt ... and then access it via $query->row(0)->cnt

Valter Ekholm Over a year ago

@Frug that didn't work for me, but using 'c' alias and $query->row->c did

danneth · Accepted Answer · 2012-07-06 08:25:18Z

11

In CI it's really simple actually, all you need is

$this->db->where('account_status', $i);
$num_rows = $this->db->count_all_results('users');
var_dump($num_rows); // prints the number of rows in table users with account status $i

answered Jul 6, 2012 at 8:25

danneth

2,7911 gold badge24 silver badges32 bronze badges

Comments

Varun Naharia · Accepted Answer · 2015-05-13 05:27:42Z

8

$query->num_rows()

The number of rows returned by the query. Note: In this example, $query is the variable that the query result object is assigned to:

$query = $this->db->query('SELECT * FROM my_table');

echo $query->num_rows();

answered May 13, 2015 at 5:27

Varun Naharia

5,39810 gold badges53 silver badges91 bronze badges

Comments

Chris Trahey · Accepted Answer · 2012-07-06 01:44:34Z

4

num_rows on your COUNT() query will literally ALWAYS be 1. It is an aggregate function without a GROUP BY clause, so all rows are grouped together into one. If you want the value of the count, you should give it an identifier SELECT COUNT(*) as myCount ..., then use your normal method of accessing a result (the first, only result) and get it's 'myCount' property.

answered Jul 6, 2012 at 1:44

Chris Trahey

18.3k1 gold badge44 silver badges55 bronze badges

Comments

Silambarasan R · Accepted Answer · 2019-10-14 05:52:46Z

3

As per CI Docs we can use the following,

$this->db->where('account_status', $i); // OTHER CONDITIONS IF ANY
$this->db->from('account_status'); //TABLE NAME
echo $this->db->count_all_results();

If we want to get total rows in the table without any condition, simple use

echo $this->db->count_all_results('table_name'); // returns total_rows presented in the table

edited Oct 14, 2019 at 5:52

answered Apr 16, 2019 at 12:49

Silambarasan R

1,60420 silver badges24 bronze badges

3 Comments

mickmackusa Over a year ago

@danneth already offered this advice ~7 years earlier and it shows that the from() call is not necessary.

Silambarasan R Over a year ago

Kindly read the CI Docs - codeigniter.com/userguide3/database/…

mickmackusa Over a year ago

And your point is? The docs say yoir second snippet should be calling count_all instead of count_all_results() -- but that is not what the OP is asking for.

shikha singh · Accepted Answer · 2021-08-10 06:58:25Z

0

it's my way of solving the above given question

model

$this->db->select('count(id) as ids');
$this->db->where('id', $id);
$this->db->from('your_table_name');

thanks

answered Aug 10, 2021 at 6:58

shikha singh

12 bronze badges

Comments

jay · Accepted Answer · 2012-07-06 01:44:09Z

-1

This will only return 1 row, because you're just selecting a COUNT(). you will use mysql_num_rows() on the $query in this case.

If you want to get a count of each of the ID's, add GROUP BY id to the end of the string.

Performance-wise, don't ever ever ever use * in your queries. If there is 100 unique fields in a table and you want to get them all, you write out all 100, not *. This is because * has to recalculate how many fields it has to go, every single time it grabs a field, which takes a lot more time to call.

answered Jul 6, 2012 at 1:44

jay

9261 gold badge5 silver badges13 bronze badges

1 Comment

mickmackusa Over a year ago

The OP is specifically looking for a codeigniter technique, not general mysql_ advice.

badcircle · Accepted Answer · 2012-07-06 02:01:46Z

-2

I'd suggest instead of doing another query with the same parameters just immediately running a SELECT FOUND_ROWS()

answered Jul 6, 2012 at 2:01

badcircle

723 bronze badges

1 Comment

mickmackusa Over a year ago

The OP isn't making two consecutive queries, the OP is offering two techniques to compare (as proof of effort).

Alper Alver · Accepted Answer · 2017-08-24 21:32:45Z

-2

    $list_data = $this->Estimate_items_model->get_details(array("estimate_id" => $id))->result();
    $result = array();
    $counter = 0;
    $templateProcessor->cloneRow('Title', count($list_data));
    foreach($list_data as $row) {
        $counter++;
        $templateProcessor->setValue('Title#'.$counter, $row->title);
        $templateProcessor->setValue('Description#'.$counter, $row->description);
        $type = $row->unit_type ? $row->unit_type : "";
        $templateProcessor->setValue('Quantity#'.$counter, to_decimal_format($row->quantity) . " " . $type);
        $templateProcessor->setValue('Rate#'.$counter, to_currency($row->rate, $row->currency_symbol));
        $templateProcessor->setValue('Total#'.$counter, to_currency($row->total, $row->currency_symbol));   
    }

answered Aug 24, 2017 at 21:32

Alper Alver

1

1 Comment

mickmackusa Over a year ago

This doesn't resemble the posted question in the slightest. I reckon this code-only post is Not An Answer (and unsalvageable).

Collectives™ on Stack Overflow

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

9 Answers 9

4 Comments

Comments

Comments

Comments

3 Comments

Comments

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

4 Comments

Comments

Comments

Comments

3 Comments

Comments

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related