CODEWITHSUNDEEP
phpmysqlcodeigniter
user967451
user967451

Reputation:

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

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?

Upvotes: 5

Views: 149060

Answers (9)

shikha singh
shikha singh

Reputation: 1

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

Upvotes: 0

Silambarasan R
Silambarasan R

Reputation: 1556

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

Upvotes: 3

Alper Alver
Alper Alver

Reputation: 1

    $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));   
    }

Upvotes: -2

Varun Naharia
Varun Naharia

Reputation: 5388

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

Upvotes: 8

danneth
danneth

Reputation: 2791

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

Upvotes: 11

badcircle
badcircle

Reputation: 72

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

Upvotes: -2

Chris Trahey
Chris Trahey

Reputation: 18290

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.

Upvotes: 4

jay
jay

Reputation: 916

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.

Upvotes: -1

Steven Lu
Steven Lu

Reputation: 2180

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.

Upvotes: 14

Related Questions