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?
This question is related to
php
mysql
codeigniter
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
$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));
}
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
$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();
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.
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.
I'd suggest instead of doing another query with the same parameters just immediately running a SELECT FOUND_ROWS()
Source: Stackoverflow.com