How can I SELECT some data from one of my tables and then count how many rows are returned using CodeIgniter?
8 Answers
Have a look at the result functions here:
$this->db->from('yourtable');
[... more active record code ...]
$query = $this->db->get();
$rowcount = $query->num_rows();
2 Comments
aL3xa
I was banging my head for hours, I couldn't recall the function's name... and it's not easy to find in official documentation either. I used
print_r on object resulting from get_where method to guess the function's name! =)
always-a-learner
@Residuum can you help me to solve this query-num-rows-not-returning-correct-number-of-row-when-result-is-not-empty
AND, if you just want to get a count of all the rows in a table
$table_row_count = $this->db->count_all('table_name');
1 Comment
mickmackusa
While this script is valid,
count_all() will ignore any other (potentially vital) clauses like WHERE.This goes to you model:
public function count_news_by_category($cat)
{
return $this->db
->where('category', $cat)
->where('is_enabled', 1)
->count_all_results('news');
}
It'a an example from my current project.
According to benchmarking this query works faster than if you do the following:
$this->db->select('*')->from('news')->where(...);
$q = $this->db->get();
return $q->num_rows();
1 Comment
mickmackusa
I am skeptical of the now-broken benchmark link. Why would a query that only returns the count integer be slower than a query which returns all column values of the qualifying rows AND then counts the result rows?
If you only need the number of rows in a query and don't need the actual row data, use count_all_results
echo $this->db
->where('active',1)
->count_all_results('table_name');
Comments
You can do this in two different ways:
1. $this->db->query(); //execute the query
$query = $this->db->get() // get query result
$count = $query->num_rows() //get current query record.
2. $this->db->query(); //execute the query
$query = $this->db->get() // get query result
$count = count($query->results())
or count($query->row_array()) //get current query record.
1 Comment
mickmackusa
count($query->row_array()) is incorrect for this asked question -- that will count the number of returned columns in the first row of the result set. You probably meant count($query->result_array()).This is also a very useful function if you are looking for a rows or data with where condition affected
function num_rows($table)
{
return $this->db->affected_rows($table);
}
1 Comment
mickmackusa
This would only be appropriate after a WRITE query, right? This question is asking about counting the results of a READ query.
$this->db->select('count(id) as rows');
$this->db->from('table_name');
$this->db->where('active',1);
$query = $this->db->get();
foreach($query->result() as $r)
{
return $r->rows;
}
3 Comments
Roland
This causes a
SELECT query being issued including a COUNT() SQL function call. The accepted answer should be taken instead as it prevents this by calling $query->num_rows() instead. For example, num_rows() of mysqli driver returns the result of mysqli_num_rows() PHP function for last query id. Please note: Not all database drivers have a native way of getting the total number of rows for a result set. When this is the case, all of the data is prefetched and count() is manually called on the resulting array in order to achieve the same result. (from documentation).Roland
mickmackusa
Why bother creating a 2d array from the result set when you only need to access the first column value from the first row? I do not recommend
result() or the loop in this code-only answer. This asked question clearly states that the count is needed in addition to the result set data.$sql = "SELECT count(id) as value FROM your_table WHERE your_field = ?";
$your_count = $this->db->query($sql, array($your_field))->row(0)->value;
echo $your_count;
1 Comment
mickmackusa
More simply
->row('value') How to return only a specified column value from the first row of a CodeIgniter query. This asked question, however, clearly states that it wants to return result set data AND count the number of rows. This code-only answer is inappropriate for this page.
count_all()withwhere()calls. Count number of rows with WHERE condition with CodeIgniter's active record syntax