Count rows in DB where

Question

I'm following a tutorial on Pagination with CI/jQuery. In the tutorial they get the total number of rows by doing:

$config['total_rows'] = $this->db->count_all('tblUsers');

They get the total number of users to define the pagination. However, they get ALL users. In my application, I only need the users with a certain value assigned to them, in this case 'role'.

I only need the users where role = 1 in my DB.

I've tried a couple of things with ->count() (from CI's Active Record DB class) or trying to count() to check how many 'rows' an array has, but I haven't been able to get the result I need. I also tried doing a regular query: select count(*) from tblusers where role = 1 and then somehow tried grabbing how many it returned, but alas.

Doing

$config['total_row'] = $this->db->query("select count(*) from tblusers where role = '1'")->result_array();

gives me the following array:

Array ( [0] => Array ( [count(*)] => 2 ) )

However, I can't seem to be able to read out the count(*) index..

Message: Undefined index: count(*)

I hope this makes some sense. Basically I'm trying to do something like..

$this->db->where('role', 1)->get('tblUsers')->count()

Sadly, this doesn't work :D

Thanks in advance. Any help is appreciated.

Answer 1

You could simply alias the count(*) result column:

select count(*) as number_of_entries from tblusers where role = '1'

You can now use number_of_entries as the key from the resulting array to access the needed value.