I need help with this SQL query

[eluser]Unknown[/eluser]
This does what I want:

SELECT distinct residency, count(*) as count
FROM users
WHERE (active = 1)
GROUP BY residency;

I am struggling to get this into my model. So far I have:

Code:

function get_residency()
{
        $this->db->select('residency');
        $this->db->distinct();
        $this->db->from('users');
        $this->db->where('active', '1');
        $query = $this->db->get();
        return $query->result();
}

How do I put the count as count part in??

[eluser]Tim Brownlaw[/eluser]
I just tried something similar and got this...

You can add in extra Select Statements and for your count - it's best to use a primary index if you have one, Try to avoid using * and be specific.

Code:

function get_residency()
{
        $this->db->select('residency');
        $this->db->distinct();
        $this->db->select('count(id) as count');  // New Bit
        $this->db->from('users');
        $this->db->where('active', '1');
        $this->db->group_by('residency);          // New Bit
        $query = $this->db->get();
        return $query->result();
}

Also you can loose the "From" statement and put the table name in the get

Code:

function get_residency()
{
        $this->db->select('residency');
        $this->db->distinct();
        $this->db->select('count(id) as count');      // New Bit
        $this->db->where('active', '1');
        $this->db->group_by('residency);
        $query = $this->db->get('users');             // Changed
        return $query->result();
}

Those are just some of the ways you can achieve what you want.

[eluser]Rolly1971[/eluser]
all you need to do is change what your function returns, eg:

Code:

function get_residency()
{
        $this->db->select('residency');
        $this->db->distinct();
        $this->db->where('active', '1');
        $this->db->group_by('residency);
        //$query = $this->db->get('users');
        //return $query->result();
        return $this->db->count_all_results();
}

[eluser]Tim Brownlaw[/eluser]
Time to crack this open and see whats what!

For testing purposes I'm using one of my existing tables called members that has a field called timezones.
Or I could have said "the names have been changed to protect the innocent...." but that's a bit lame!

Case 1

Code:

echo '<h4>Case 1</h4>';
$this->db->select('timezone');
$this->db->distinct();
$this->db->select('count(id) as count');
$this->db->group_by('timezone');
$query = $this->db->get('members');

// Display the generated query For Case 1
echo $this->db->last_query();

// Show the results For Case 1
var_dump($query->result());

The Results of this are

Code:

SELECT DISTINCT `timezone`, count(id) as count FROM (`members`) GROUP BY `timezone`

Code:

array (size=3)
  0 =>
    object(stdClass)[34]
      public 'timezone' => string '' (length=0)
      public 'count' => string '1' (length=1)
  1 =>
    object(stdClass)[35]
      public 'timezone' => string '-6' (length=2)
      public 'count' => string '2' (length=1)
  2 =>
    object(stdClass)[36]
      public 'timezone' => string '10' (length=2)
      public 'count' => string '2' (length=1)

So we get the Fieldname's values along with their respective counts

Case 2

Code:

echo '<h4>Case 2</h4>';
$this->db->select('timezone');
$this->db->distinct();
$this->db->group_by('timezone');
$this->db->order_by('timezone desc'); // Added in for testing what count_all_results was seeing.

// Show the results For Case 2
var_dump($this->db->count_all_results('members'));

// Display the generated query For Case 2
$query = $this->db->last_query();
echo $query;
// Run the query generated by count_all_results in Case 2
$result = $this->db->query($query);
// and show its results
var_dump($result->result());

The results are

Code:

int 2

Code:

SELECT COUNT(*) AS `numrows` FROM (`members`) GROUP BY `timezone` ORDER BY `timezone` desc

Code:

array (size=3)
  0 =>
    object(stdClass)[36]
      public 'numrows' => string '2' (length=1)
  1 =>
    object(stdClass)[35]
      public 'numrows' => string '2' (length=1)
  2 =>
    object(stdClass)[34]
      public 'numrows' => string '1' (length=1)

So Case 1 gives the required results but interestingly in Case 2 - count_all_results is creating the correct SQL BUT it only returns the result of the first row.

If you change the desc to asc in the order_by in Case 2 - you'll get a result of 1... Also, the code in DB_active_rec.php shows this to be the case. It's expecting a single row result.

So in this situation, count_all_results isn't the way to go!

@rolly - you also forgot the tell it what table to use! So what you suggested kind of went BANG! it intrigued me enough to test it out as I've never used it before We're always learning new things in here

So when in doubt, knock up some test code and see what is actually happening!

Hope that helps!
Cheers
Tim