strange issues with get_where..

[eluser]awpti[/eluser]
mysql> SELECT * FROM (`jobs`) WHERE `times_reported` > 0;
Empty set (0.00 sec)

But..

Code:

function fetch_reported_jobs()
{
   $this->db->get_where('jobs', 'times_reported > 0');
   return $this->db->count_all_results();
}

Returns a result of 1

Result of db->last_query: SELECT * FROM (`jobs`) WHERE `times_reported` > 0

For some reason, count_all_results() is returning 1 even though it should return 0. I can't see any issue with the code in DB_active_rec.php

Thoughts?

[eluser]awpti[/eluser]
The only way to get around this seems to be to manually issue a query.

Even $this->db->where(..) returns one when it should return 0.

The query it returns via last_query returns an empty result set when run against the DB directly, but CI sees it as a result of 1. Weird!

[eluser]adamfairholm[/eluser]
awpti - Just wanted to chime in that I've also had this issue - got around it with a manual query like you did. You're not alone!

[eluser]jeffpeck[/eluser]
Perhaps the DB is drawing from cache. Try turning caching off if you have it on and try again.

[eluser]Derek Allard[/eluser]
count_all_results() isn't intended to be used after results have been brought back... that would be num_rows(), this function is intended to simply return the number of results with some restrictions. So in this case the query would be

Code:

$this->db->where('jobs', 'times_reported > 0');
   return $this->db->count_all_results();

[eluser]rlopez[/eluser]
Whew. This was causing a *LOT* of pain. Changing count_all_results to num_rows helped. Perhaps Derek's explanation should be on the documentation?

Thanks for helping, Derek

[eluser]Derek Allard[/eluser]

Code:

echo $this->db->count_all_results('my_table');
// Produces an integer, like 25

Thanks for the feedback rlopez, how could I make this more clear in the future?

[eluser]rlopez[/eluser]
I would note on the count_all_results section, that this function is not to be used to verify if at least one row was returned, and point the reader to the num_rows function instead

[eluser]xwero[/eluser]
I think this is a non issue. If the idea is to get row count you use

Code:

$this->db->where('times_reported >',0);
$this->db->count_all_results();

If the idea is to get the rows

Code:

$this->db->get_where('jobs', 'times_reported > 0');

If the application requires the row count the method should return the query object instead of the result object.

I think the AR methods are descriptive enough to make it a one time mistake.