getting count from mysql

[eluser]malcomhfc[/eluser]
Hi, i need some help with getting the number of rows where a certain value is true.

I have managed to do it for one call. However i need the where value to have different params.

For example.

if username = bob then count number of rows and return.
if password = banana then count number of rows and return.

This is what i have in the model so far

Code:

public function count_by($params = array())
{
  if ($params['status'] == 'open')
  {
   $this->db->where('status', $params['status']);
  }
  return $this->db->count_all_results('dev');
}

The above i need to check for status = draft and status = closed also.

the controller:

Code:

$status = $this->dev_m->count_by(array('status' => 'open'));

The view i just echo status. But im looking for a way to something along the lines of

Code:

echo status->open; echo status->closed;

I'm learning PHP and i don't want to make a variable for every count in the controller.

Any suggestions would be greatly appreciated.

[eluser]bgreene[/eluser]

Code:

select status, count(status) from tablename group by status

will give table like
status..count
open......2
closed....5
draft.....9
etc etc

[eluser]malcomhfc[/eluser]
still struggling on this issue. Sorry bgreene, your suggestion was of no help to me. i tried that query and the output was nothing.

[eluser]bgreene[/eluser]
nothing! hardly possible! if your table has a column "status" and there are some entries in it which have a value in the status column then

Code:

SELECT `status`, COUNT(`status`) FROM tablename GROUP BY `status`

will _definitely_ give you a result like the example I gave above

[eluser]CroNiX[/eluser]

Code:

$this->db
  ->select('status')
  ->select('COUNT(`status`) AS status_count', FALSE)
  ->group_by('status')
  ->get(tablename)
  ->result();

Should work fine. Basically same as bgreenes except uses AR and retrieves the count as status_count

[eluser]CroNiX[/eluser]
Edited as I had a brainfart and you can't write subqueries (yet) with AR.

A less efficient query, but would give you the format you originally asked for (echo $status->closed), would be something like:

Code:

$q = 'SELECT ';
$q .= '(SELECT COUNT(`status`) FROM tablename WHERE `status` = "open") AS open,';
$q .= '(SELECT COUNT(`status`) FROM tablename WHERE `status` = "closed") AS closed';

$status = $this->db->query($q)->result();
echo $status->open;
echo $status->closed;

Tested and works.