count_all()

[eluser]Kemik[/eluser]
Hello,

Does anyone know if it's possible to use count_all() and a where statement?

E.g.
echo $this->db->count_all('comments');

But where news_id = $news_id?

I'm guessing it would be:

$this->db->where('news_id', $news_id);
$this->db->count_all('comments');

Would that work?

[eluser]Phil Sturgeon[/eluser]
Nope, its just a straight query thats not part of the ActiveRecord classes. You can get just as good results with:

Code:

$this->db->where(’news_id’, $news_id);
echo count($this->db->get(’comments’));

[eluser]moonbeetle[/eluser]

$this->db->where(’news_id’, $news_id);
echo count($this->db->get(’comments’));

This doesn't work for me as it doesn't return the correct number!

This does work and is the fastest according to my benchmarks:

Code:

$query = $this->db->query("SELECT COUNT(ID) AS total FROM comments WHERE news_id = $news_id");
$row = $query->row();
echo $row->total;

This one works as well, with less code, but it's slower:

$query = $this->db->getwhere('comments', array('news_id'=>'1'));
$num_rows = $query->num_rows();
echo $num_rows;

[eluser]Michael Wales[/eluser]

Code:

$query = $this->db->getwhere('comments', array('news_id'=>'1'));
$num_rows = $query->num_rows();

[eluser]andregufc[/eluser]
it is possible?

$query = $this->db->getwhere('comments', array('news_id'=>'1'));
$query = $this->db->getwhere('comments', array('titulo'=>'teste'));
$query = $this->db->getwhere('comments', array('post'=>'teste'));
$query = $this->db->getwhere('comments', array('author'=>'andre'));
$num_rows = $query->num_rows();

[eluser]Michael Wales[/eluser]
No - each would be a separate query - therefore, the example you gave would only return the number of rows for the last database call.

The best way to do what you are referring to is to perform the get, then assign the num_rows() to a variable, and move on to the next query.

[eluser]xwero[/eluser]
[quote author="andregufc" date="1187767333"]it is possible?

$query = $this->db->getwhere('comments', array('news_id'=>'1'));
$query = $this->db->getwhere('comments', array('titulo'=>'teste'));
$query = $this->db->getwhere('comments', array('post'=>'teste'));
$query = $this->db->getwhere('comments', array('author'=>'andre'));
$num_rows = $query->num_rows();[/quote]

If you want to get te number of rows with those values you can do

Code:

$query = $this->db->getwhere('comments', array('news_id'=>'1','titulo'=>'teste','post'=>'teste','author'=>'andre'));
$num_rows = $query->num_rows();

which gives following sql

SELECT * FROM comments WHERE news_id= 1 AND titulo = 'teste' AND post = 'teste' AND author = 'andre'

[eluser]Derek Allard[/eluser]
I know this thread is a few months old, but just wanted to mention that count_all_results() has been added into the svn. You'll need to update your system/database folder (and all files) and maybe also the userguide if you want that.

$this->db->count_all_results();

Permits you to determine the number of rows in a particular Active Record query. Queries will accept Active Record restrictors such as where(), or_where(), like(), or_like(), etc. Example:

Code:

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

$this->db->like('title', 'match');
$this->db->from('my_table');
echo $this->db->count_all_results();
// Produces an integer, like 17

I'd love to have you beat it up a bit, particularly if you are using a database besides MySQL.

[eluser]johnwbaxter[/eluser]
I got this from someone on the forums that i think does that but super quickly.

Does the following do the same thing as your new function or is it different?

Code:

function getNumberOfRows($table = '', $limit = null, $offset = null)
    {
        if ($table != '')
        {
            $this->from($table);
        }
        
        if ( ! is_null($limit))
        {
            $this->limit($limit, $offset);
        }
            
        $sql = $this->_compile_select();
        $this->_reset_select();
        
        // replace the fields list for something less complicated
        $sql = 'SELECT COUNT(1) AS rows ' . strstr($sql, 'FROM');  
        
        // return the count value
        $query = $this->query($sql);
        $result = $query->result_array();
        if (is_array($result)) {
            $result = $result[0]['rows'];        
        }
        $this->_reset_select();
        return $result;

[eluser]Derek Allard[/eluser]
Very similar. The one in the svn has the added advantage of working is all CI supported dbs, and being a bit more optimized.