Pagination: Can this be improved?

[eluser]bobbytb[/eluser]
Im retrieving data from a database and then displaying it in a table with pagination.

The only way i can get this to work is with two queries to the database: the first is to fetch the total number of rows (in my case, a call to getTodayRows()), and the second to get the data to be displayed(in this case getToday()).

Controller:

Code:

function today(){
        $config['per_page']=10;
        $config['num_links']=10;
        $config['base_url']='http://localhost:8888/ci/index.php/data/today';
        $data['records'] = $this->data_model->getToday($config['per_page'], $this->uri->segment(3));
        // $data['records'] = $this->data_model->getToday();
        $rows = $this->data_model->getTodayRows();
        $config['total_rows']=$rows;
        $this->pagination->initialize($config);    

        $this->load->view('today_view', $data);    
    }

Models:

Code:

// Retrieve data for todays log
    function getToday($row_limit, $url) {
        $now = date("Y-m-d H:i:s");
        $startOfToday = date("Y-m-d ")."00:00:00";
        $this->db->where('datetime > ', $startOfToday);
        $this->db->where('datetime <= ', $now);
        $q = $this->db->get('data', $row_limit, $url);
        if ($q->num_rows() > 0) {
            foreach ($q->result() as $row) {
                $data[] = $row;
            }
            return $data;
        }
    }
    
    // Get the number of items logged today
    function getTodayRows() {
        $now = date("Y-m-d H:i:s");
        $startOfToday = date("Y-m-d ")."00:00:00";
        $this->db->where('datetime > ', $startOfToday);
        $this->db->where('datetime <= ', $now);
        $q = $this->db->get('data');
        if ($q->num_rows() > 0) {
            return $q->num_rows();
        }
    }

Essentially, Im querying the database twice just to retrieve the same data. Isn't there a better way of doing this?

[eluser]OES[/eluser]
You could change this a number of ways but a good way would be to add an additional parameter to getToday function ie,

Code:

// Retrieve data for todays log
    function getToday($row_limit, $url, $count=FALSE) {
        $now = date("Y-m-d H:i:s");
        $startOfToday = date("Y-m-d ")."00:00:00";
        $this->db->where('datetime > ', $startOfToday);
        $this->db->where('datetime <= ', $now);
        $q = $this->db->get('data', $row_limit, $url);
        if ($q->num_rows() > 0) {
            if($count === TRUE){
               return $q->num_rows();
            }else{
              foreach ($q->result() as $row) {
                $data[] = $row;
              }
              return $data;
            }
        }
    }

Hope this helps.

[eluser]bobbytb[/eluser]
Nice one OES. Thanks for that, it makes the code much more simpler, but would still have two queries - I was just wondering if there is any way where we could actually avoid a second query. I think for now i will stick with your suggestion though

[eluser]OES[/eluser]
Well if you are collecting the same data you can always just do a count on the returned array ie.

$data['records'] = $this->data_model->getToday($config['per_page'], $this->uri->segment(3));

$config['total_rows'] = count($data['records']);

Another way !

[eluser]bobbytb[/eluser]
Thanks OES, Appreciate your help!

[eluser]mcr_rm[/eluser]
I do it like this in my models...doesn't save you from overhead of calls to the db but it works for me. I then pass [results] to my view and [total] to the pagination class through my controller. Rather than making two calls from the controller I make one, however say you didn't want the total that is extra overhead that could be avoided doing as above functions.

Code:

public function _getAll($limit=10, $offset=0)
    {
        
        $this->db->flush_cache();
        $this->db->start_cache();
        $this->db->from($this->db_table);

        
        $this->db->stop_cache();
        
        $return=array();
        $return['total']=$this->db->count_all_results();
        
        $this->db->limit($limit, $offset);
        $this->db->order_by('id', 'DESC');
        $query = $this->db->get();
        $return['results']= $query->result();
        
        
        return $return;

    }

PS clearly you might want to pass in what you want to order results by also!

[eluser]bobbytb[/eluser]
Now that is elegant!
With regards to the order-by, its sequential data that comes in to the db regularly in every few minutes and so its naturally sorted when i retrieve it.
Thanks for sharing mcr-rm, it is quite clever!
/*going to read up on db caching now*/

[eluser]mcr_rm[/eluser]
Yeah remove that statement I just copied it out of a model I am actually working with right now.

As for db caching it's a way of caching arguments for a shared call here for me. So we actually do make two calls count all results then the additional calls are added to the cache arguments and give us the second call. This actually had far more in the cache before in another model. Not even sure we need it here because you could simply call count_all_results($this->db_table) then $this->db->get($this->db_table).

Which I am sure someone will point out the db caching is just left over from an earlier iteration where it was needed. In fact having put that here I am ging to change it as I just said. No reason for the active record caching.

http://ellislab.com/codeigniter/user-gui...ml#caching

Code:

public function _getAll($limit=10, $offset=0)
    {
        
        $return=array();
        $return['total']=$this->db->count_all_results($this->db_table);
    
        $this->db->limit($limit, $offset);
        $query = $this->db->get($this->db_table);
        $return['results']= $query->result();
        
        
        return $return;

    }

And you could change count_all_results to simply count_all.

Sometimes showing it off here makes you see things that need sorting.

[eluser]bobbytb[/eluser]
Well, this looks good enough for me - thanks everyone...
and i also learned something new