Welcome Guest, Not a member yet? Register   Sign In
I need help with this SQL query
#4

[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 Smile We're always learning new things in here Smile

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

Hope that helps!
Cheers
Tim



Messages In This Thread
I need help with this SQL query - by El Forum - 07-30-2014, 04:37 AM
I need help with this SQL query - by El Forum - 07-30-2014, 01:16 PM
I need help with this SQL query - by El Forum - 07-30-2014, 08:37 PM
I need help with this SQL query - by El Forum - 07-31-2014, 08:18 AM



Theme © iAndrew 2016 - Forum software by © MyBB