Incorrect SQL Query Result with Active Record

[eluser]MattHirschfelt[/eluser]
Can anyone tell me why running a query through phpMyAdmin on my database gives me a different result than my active record styles query?

The query through phpMyAdmin is

Code:

SELECT * FROM `planets` GROUP BY `player`

which returns approximately 46,200 results.


The active record query is

Code:

$this->db->from('planets')->group_by('player')->count_all_results();

and it returns approximately 6,600 results.

[eluser]Aken[/eluser]
Put this after your active record query and see why:

Code:

$this->db->last_query();

[eluser]MattHirschfelt[/eluser]
It tells me the query is

Code:

SELECT COUNT(*) AS `numrows` FROM (`planets`) GROUP BY `player`

When I run through phpMyAdmin I get the same row count as when I run

Code:

SELECT * FROM `planets` GROUP BY `player`

However, the active record query is still echoing the wrong result on the page. Based on running the select is claims to running through phpMyAdmin, it appears the Active Record query is returning an array and echoing the first result in the array instead of an integer that contains the numbers of records returned. I guess I'm as a loss as to why it's doing this behavior.

[eluser]animatora[/eluser]
What is the output when you run the query

Code:

SELECT COUNT(*) AS `numrows` FROM (`planets`) GROUP BY `player`

from inside the framework, not going through active record ?

[eluser]MattHirschfelt[/eluser]
[quote author="animatora" date="1337951141"]What is the output when you run the query

Code:

SELECT COUNT(*) AS `numrows` FROM (`planets`) GROUP BY `player`

from inside the framework, not going through active record ?[/quote]

It returns approximately 46,200 results. The result is a list of numbers in a column 'numrows'. The rows appears to be a count of how often each 'player' appears in the database with one row per player. So one player appears to be in the database 6,600 times so that is the number appearing when I echo the data instead of the count of 46,200 rows.

I'm passing the data to the controller with

Code:

data['player_count'] = $this->Index_model->player_count();

and then echoing it in the view with

Code:

<?=$player_count?>

[eluser]Matalina[/eluser]
You want the number of rows? Because when you use group_by it will preform the aggraget function in this case count on each row it find.

If you did not use group_by you would get the answer you wanted (I understand this isn't what you wanted).

I'm assuming you want to get the count of all the different players in your database.

Instead of using group by do this:

Code:

SELECT DISTINCT player_id FROM planets

So the active record code to get what you want is:

Code:

$this->db->distinct()->select('player_id')->get('planets')->num_rows();

This will return the number of rows the query generated. count_all_results() will probably work as well.

You could use num_rows() instead of count_all_results() in your original query and probably get the answer you want.

[eluser]PhilTem[/eluser]
@Matalina is probably right and I would have suggested the same: From my understanding, count_all_results() won't work with a group_by() since there's another business logic implemented in count_all_results(). If you want to have the number for

Code:

SELECT * FROM `planets` GROUP BY `player`

with the AR-class, simply use

Code:

$query = $this-db->select('*')->from('planets')->group_by('player');
$results = $query->num_rows();

Just have a look at the count_all_results() method in file at system/database/DB_query_builder.php. You should see, why it's not working with what you want. But the num_rows()-version should work, though.

[eluser]MattHirschfelt[/eluser]
Thank you everyone for your help so far!

When I try:

Code:

$this->db->distinct()->select('player_id')->get('planets')->num_rows();

I get a count of every row in the table. I think that's expected because every row is distinct when you include the other data held in each row.

When I try:

Code:

$query = $this-db->select('*')->from('planets')->group_by('player');
$results = $query->num_rows();

I get the following error:

Fatal error: Call to undefined method CI_DB_mysql_driver::num_rows() in /home/matth41/public_html/openparser.info/application/models/index_model.php on line 31

The full function at line 31 is as follows:

Code:

function player_count()
{
  $query = $this->db->from('planets')->group_by('player');
  return $results = $query->num_rows();
}

I feel like I'm starting to doubt the benefit of using a PHP Framework when I could write the site in a flat PHP file structure in a fraction of the time.

[eluser]weboap[/eluser]
what's the output of

Code:

$this->db->distinct()->select('player')->get('planets')->num_rows();

can you post your db structure?

Note: the error at line 31 : need ->get()

[eluser]MattHirschfelt[/eluser]
I don't know what it didn't work the first time I tried it (I did change player_id to player when I put in my code), but it works now. Of course, that just means I'm on to another database query that isn't working. And this user guide says active record is supposed to make searching your database easier.