Codegenesis markup: run a query twice?

Question

I am using codeigniter and the pagination class. This is such a basic question, but I need to make sure that I'm not missing something. In order to get the configuration elements necessary to obtain paginated results that retrieve them from the MySQL database, you basically need to run the query twice:

In other words, you need to run a query to determine the total number of records before you can paginate. So I do it like this:

Make this query to get the number of results.

$this->db->where('something', $something); $query = $this->db->get('the_table_name'); $num_rows = $query->num_rows(); 

Then I will need to do this again to get the results with limit and offset. Sort of:

 $this->db->where('something', $something); $this->db->limit($limit, $offset); $query = $this->db->get('the_table_name'); if($query->num_rows()){ foreach($query->result_array() as $row){ ## get the results here } } 

I'm just wondering if I'm really doing this right because the query always needs to be run twice? The queries I use are much more complicated than the above.

Answer 1

Unfortunately, in order to paginate, you need to know how many elements you paginate.

You can always cache the result for the total number of elements if it is too computationally expensive.

Answer 2

Yes, you need to run two queries, but $this->db->count_all('table_name'); - one, and the string is much cleaner.

Answer 3

To paginate, you need to read a set of records twice:

• As soon as you read the whole set, so that it can count the general records of numbers
• Then, to read the record window to display

Here is an example that I used for the project. In the table "banner" there is a list of banners that I want to show on a paginated page:

• Using the public class property to store shared records (public $ total_records)
• Using a private function to build a query (which is common to both activities). The ($ isCount) parameter that we pass to this function reduces the amount of data that the query generates, because we only need one field to count the rows, but when we read the data window, we need all the required fields.
• The get_list () function first calls the database to find the total and stores it in $ total_records, and then reads the data window to return to the caller.
• Remember that we cannot access $ total_records without first calling the get_list () method!

---

 class Banner_model extends CI_Model { public $total_records; //holds total records for get_list() public function get_list($count = 10, $start = 0) { $this->build_query(); $query = $this->db->get(); $result = $query->result(); $this->total_records = count($result); //store the count $this->build_query(); $this->db->limit($count, $start); $query = $this->db->get(); $result = $query->result(); return $result; } private function build_query($isCount = FALSE) { $this->db->select('*, b.id as banner_id, b.status as banner_status'); if ($isCount) { $this->db->select('b.id'); } $this->db->from('banner b'); $this->db->join('company c', 'c.id = b.company_id'); $this->db->order_by("b.id", "desc"); //latest ones first } 

And now from the controller we call:

 $data['banner_list'] = $this->banner_model->get_list(); $config['total_rows'] = $this->banner_model->total_records; 

Everything becomes more complicated when you start using JOINs, for example, in my example, where you want to show banners from a certain company! You can read my blog post on this subject further:

http://www.azmeer.info/pagination-hitting-the-database-twise/