count all results pagination problem

[eluser]Unknown[/eluser]
Hello everybody,

I have successfully implemented pagination on my site, but when I try to filter the results of my database with some where clauses, the pagination breaks down:

Code:

$this->load->database();

$this->db->where('startingprice >', 0);
$this->db->where('startingprice <', 30);

$this->db->order_by("freeorpaid", "desc");
$this->db->order_by("timeofauction", "asc");
        
$query = $this->db->get("liveauctions", $per_page, $offset);
$num_results = $this->db->count_all_results();

I have included a small snippet. In my view, I get the right records showing up, but still get too many number links in my pagination. I should have three records showing with two records per page, so a total of two pages, but for some reason I still get links to three pages as if the filtering hasnt worked. Any body know what the problem might be?

[eluser]Dave @ Exp:resso[/eluser]
I came across this while searching for how to count all results for a query excluding the limit.

Anyway, taking a compete stab in the dark here, but perhaps it is because you are specifying the table in the get() function? Have you tried

Code:

$this->db->from('liveauctions');
$this->db->limit($per_page, $offset);

as separate lines, and then just using

Code:

$this->db->get();
$this->db->count_all_results();

?

[eluser]web-johnny[/eluser]
[quote author="vicse1784" date="1307659568"]Hello everybody,

I have successfully implemented pagination on my site, but when I try to filter the results of my database with some where clauses, the pagination breaks down:

Code:

$this->load->database();

$this->db->where('startingprice >', 0);
$this->db->where('startingprice <', 30);

$this->db->order_by("freeorpaid", "desc");
$this->db->order_by("timeofauction", "asc");
        
$query = $this->db->get("liveauctions", $per_page, $offset);
$num_results = $this->db->count_all_results();

I have included a small snippet. In my view, I get the right records showing up, but still get too many number links in my pagination. I should have three records showing with two records per page, so a total of two pages, but for some reason I still get links to three pages as if the filtering hasnt worked. Any body know what the problem might be?[/quote]

You must not have the get function before the count_all_results . Replace your get with from and add the count_all_results below it. Codeigniter explains how it works with the example:

Code:

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

[eluser]Dave @ Exp:resso[/eluser]
Hmm, would be nice if count_all_results() had a parameter to prevent it from clearing the active record query. That would make pagination much easier, as you could call count_all_results() and then call get() with the limit parameter.

[eluser]web-johnny[/eluser]
There is a solution for this . Actually in my pagination function I do it like this:

Code:

$this->db->where(....mpla mpla...)->order_by(...)->limit(....)..etc;
$my_db_results  = $this->db->get();

$count_results = $my_db_results->num_rows();
$get_results = $my_db_results->result();

[eluser]Dave @ Exp:resso[/eluser]
Surely num_rows() will return the number of rows in your result, i.e. the limited number of results, rather than the full result count excluding the limit as needed for pagination?

[eluser]Ray G[/eluser]
Pursuing CrescendoNZ Idea, I looked into the active record class and added some code to not reset the selection.

Code is located in DB_active_rec.php file starting at line 1008. change the function count_all_results to this

Code:

function count_all_results($table = '', $reset=TRUE)
    {
        if ($table != '')
        {
            $this->_track_aliases($table);
            $this->from($table);
        }

        $sql = $this->_compile_select($this->_count_string . $this->_protect_identifiers('numrows'));

        $query = $this->query($sql);
        if($reset === TRUE){
      $this->_reset_select();
    }

        if ($query->num_rows() == 0)
        {
            return 0;
        }

        $row = $query->row();
        return (int) $row->numrows;
    }

now you can count the rows before the limit and offset portion of your query by adding the FALSE parameter to the function.
Example

Code:

$this->db->select(something);
$this->db->from('mytable');
$this->db->where('john', 'doe');
$num_rows = $this->db->count_all_results('', FALSE);
$query = $this->db->get();

I do not consider myself an expert so if someone see's a problem with this or a better way to achieve please post. Always up for constructive criticism. Maybe the powers that be can take this to the next level and include it in the release code since there seems to be a need for it.

Ray

[eluser]Dave @ Exp:resso[/eluser]
Great idea! Hopefully the higher powers actually read this forum and agree it would save hundreds of lines of duplicate code.

One thing though I think is that the parameter would be better if it was false by default, and setting it to true enabled the additional functionality. i.e.

function count_all_results($table = '', $preserve_ar_state = FALSE)

or something. Anyway just a thought. I certainly can't hack the CI core for my purposes (EE plugins / custom php in template code), but would be great if this made it into the base framework and trickled through into EE.

[eluser]Gilles_T[/eluser]
Hi,

I struggled a while on this last year with a 1.7 CI version, found a fix and forgot about it. I just upgraded to 2.02 yesterday and came back on the point since my fix in the DB_active_rec has gone with the upgrade. I looked again since I thought the new version would come with a solution for it. I fear it's not the case, so after some search, I have no other way than putting back my fix in the DB_active_rec.

My proposition is the following : mimic the SQL_CALC_FOUND_ROWS() with integrating a

Code:

$this->db->SQL_CALC_FOUND_ROWS();

called before the ->select() and so on

and then after the ->get(), call a :

Code:

$count = $this->db->query('SELECT FOUND_ROWS() as cnt');

as we would do with a standard PHP SQL request.

Fix goes in 3 parts of the DB_actice_rec :
1/ In the var definition as :

Code:

var $ar_SQL_CALC_FOUND_ROWS            = FALSE;

2/ In the _compile_select function :

Code:

function _compile_select($select_override = FALSE)
    {
        // Combine any cached components with the current statements
        $this->_merge_cache();

        // ----------------------------------------------------------------
        
        // Write the "select" portion of the query

        if ($select_override !== FALSE)
        {
            $sql = $select_override;
        }
        else
        {
            $sql = ( ! $this->ar_distinct) ? 'SELECT ' : 'SELECT DISTINCT ';
            //------- Patch #GT# --------
            $sql = ( ! $this->ar_SQL_CALC_FOUND_ROWS) ? 'SELECT ' : 'SELECT SQL_CALC_FOUND_ROWS ';        
            
            if (count($this->ar_select) == 0)
            {
                $sql .= '*';        
            }
            else
            {                
                // Cycle through the "select" portion of the query and prep each column name.
                // The reason we protect identifiers here rather then in the select() function
                // is because until the user calls the from() function we don't know if there are aliases
                foreach ($this->ar_select as $key => $val)
                {
                    $this->ar_select[$key] = $this->_protect_identifiers($val);
                }
                
                $sql .= implode(', ', $this->ar_select);
            }
        }

3/ The new SQL_CALC_FOUND_ROWS function for the DB_active_rec class

Code:

function SQL_CALC_FOUND_ROWS($val = TRUE)
    {
        $this->ar_SQL_CALC_FOUND_ROWS = (is_bool($val)) ? $val : TRUE;
        return $this;
    }

I hope this can help someone.

A message to the CI development team to integrate this in a next release, or may be to better explain the usage of the count_all_result function that sounds like doing such a thing. The previous answer to the topic is a clever one as well and could be part of a next release.

Have a good day