SELECT COUNT() with WHERE clause using CodeIgniter's query builder [duplicate]

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.

raveren · Accepted Answer · 2011-05-11 13:14:53Z

5

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.

answered May 11, 2011 at 13:14

raveren

18.8k12 gold badges74 silver badges89 bronze badges

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

Comments

MJB · Accepted Answer · 2011-05-11 13:15:35Z

2

Not positive, but it seems that all you need is to give that column a name. For example,

... = $this->db->query("select count(*) as colname from tblusers where role = '1'")->result_array();

I suspect that will work.

answered May 11, 2011 at 13:15

MJB

7,6862 gold badges33 silver badges41 bronze badges

Comments

Prikkeldraad · Accepted Answer · 2011-05-11 13:16:21Z

2

For reading the count(*) index use the following to create a readable field:

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

Now you will have:

Array ( [0] => Array ( [cnt] => 2 ) )

answered May 11, 2011 at 13:16

Prikkeldraad

1,3959 silver badges15 bronze badges

Collectives™ on Stack Overflow

SELECT COUNT() with WHERE clause using CodeIgniter's query builder [duplicate]

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related