Okay, let it order and paginate!

First of all, how pagination works

When you paginate you say:

"There are (say) 25 items. I want to show page 2, so if I am (say) showing 5 items per page, I have to order them and then show from item 6 to 10."

So if you want to show page 3, you order (let's say) the id and then you see from items 11 to 15. To summarize, you should know:

• How many items are you showing
• The page you want to list (the later you convert to offset, keep reading)
• The order you are using to display.

Example: I will use the following table, insert it into DDBB and run queries to see what I mean:

CREATE TABLE IF NOT EXISTS `elements` (
  `id` int(11) DEFAULT NULL,
  `letter` char(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `elements` (`id`, `letter`) VALUES
    (10, 'a'),(9, 'b'),(8, 'c'),(7, 'd'),   (6, 'e'),   (5, 'f'),   (4, 'g'),   (3, 'h'),   (2, 'i'),   (1, 'j');

      

        
        
        
      

    

And now, let the paginate!

Imagine you want to show page 3, 2 elements on the page, ordering along the ascendent axis. You will need to show elements with IDs 5 and 6, since page 1 has IDs 1 and 2, and page 2 has IDs 3 and 4. So our offset would be:

$offset = ($elements_per_page) * ($page – 1);
// In this example: $offset = 2 * (3 – 1 ) = 4.

      

        
        
        
      

    

And know to order by id and show items from the page you have to run:

SELECT *
FROM elements
ORDER BY id ASC
LIMIT 4, 2

      

        
        
        
      

    

And you get in the following order: (5,'f') and (6, 'e')

And if you want to order them by letter, you run

SELECT *
FROM elements
ORDER BY letter ASC
LIMIT 4, 2

      

        
        
        
      

    

which gives you in the following order: (6, 'e') and (5, 'f')

Explanation: The limit clause allows you to specify the offset and number of items you want to show.

From http://dev.mysql.com/doc/refman/5.0/en/select.html , "With two arguments, the first argument indicates the offset of the first line to return, and the second indicates the maximum number of lines to return. The starting line offset is 0 (not 1) "

Well, now you know how to execute the query, so let's build it using CI.

Creating pagination with CI

Your model needs to know what you want to show: the order, the page you want to show, and the number of elements, so it should be something like:

MODEL users_model.php

public function getUsers( $offset, $elements_per_page, $order_field, $order_direction ) 
{    
    $this->db->select('*');
    $this->db->from($this->db->dbprefix . 'user');

    $this->db->order_by( $order_field, $order_direction )

    // Be careful, the order is differente here than in real MySQL query LIMIT: LIMIT $OFFSET, $ELEMENTS_PER_PAGE
    $this->db->limit($elements_per_page, $offset );  


    $query = $this->db->get();

    if ($query->num_rows() > 0) {
        return $query->result();
    } else {
        // This just to keep allways the same kind of data returned, ;D
        return array();
    }
}

// We'll use this to know how many users there are.
public function countUsers() 
{
    return $this->db->count_all_results( $this->db->dbprefix . 'user' );
}

      

        
        
        
      

    

With the above code, you have a model class base to start sending values ​​from the database to the controller, but you also need to play with the controller. It will have to process the pages to make sure you are in range, to make sure the values ​​you want to order are fields that actually exist ... Well, the controller works, you know?

CONTROLLER: users.php

public function user_results()
{
    // To control the page you're sending is ok, you have to count first the number of elements
    $page   = $this->input->get( $page );

    $elements_per_page  = 3; // You could also send it in your request

    $this->load->model('users_model');  
    $total      = $this->users_model->countUsers();
    $get_offset =  ( $page – 1 ) * $elements_per_page;
    // And you have to control the offset:
    $min_offset = 0; 
    $max_offset = floor( $total / $elements_per_page ); 
    $offset = ( ( get_offset > $max_offset ) ? $max_offset : ( ( $get_offset < $min_offset ) ? $min_offset : $get_offset ) ); 

    // Then, you have to control the fields to order. I'd recommend you white list filter:
    $get_order_field    = $this->input->get('order_field');
    $order_fields   = array( 'id', 'letter');
    $order_field    = in_array( $get_order_field, $order_fields ) ? $get_order_field : 'id';

    $get_order_direction    = $this->input->get('order_direction');
    $order_directions   = array( 'asc', 'desc');
    $order_field        = in_array( $get_order_direction, $order_directions ) ? $get_order_direction : 'asc';

    // And now, you can show a list of the elements you want to show from the page:       
    $data['users'] = $this->users_model->getUsers( $offset, $elements_per_page, $order_field, $order_direction );

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

      

        
        
        
      

    

And to finish with the backend, I would put all the table values ​​in the view:

VIEW: table_users_view.php

<table>
  <tr>
    <th>Id</th>
    <th>Name</th>
  </tr>
  <?php if ( !empty( $users ) : ?>
      <?php foreach ( $users as $user ) : ?> 
  <tr>
    <td><?=$user->id?></td>
    <td><?=$user->name?></td>   
  </tr>
      <?php endforeach; ?> 
  <?php endif; ?>
</table>

      

        
        
        
      

    

Ok, this is START ANSWER your question. You can now paginate, but you need to automatically add links to pages that might look like BASE_URL/users/?page=1&order_field=id&order_direction=asc

. And you will have to write in JS an AJAX call called with anchors in your html. If you all understand the code, follow these steps:

• Write an AJAX call with its anchors in the table header.
• Reload the content based on the values ​​of the selected page and these values: for this you need a controller method that takes AJAX parameters and returns a tbody to you, not the whole table.

More or less:

$(document).ready(function({
    $('.order').click( function(){
        var field = $(this).data('field');
        var dir   = $(this).data('dir');       
        var page  = $('.pagination active').data('page');
        $.ajax({
            type: 'get',
            dataType: 'json'
            url:  BASE_URL + 'users/ajax/?page=' + page + '&order_direction=' + dir + '&order_field=' + field,
            success: function(data) {
                $('table.users tbody').html(data.tbody);
            }
        });
    })   
});

      

        
        
        
      

    

Your opinion should be changed to:

<div class="pagination">
    <span class="active" data="1">1</span>
    <span data="2">2</span>
    <span data="3">3</span>
</div>

<table>
  <tr>
    <th>
      <span class="order" data-field="id" data-dir="asc">Up</span>
      <span class="order" data-field="id" data-dir="desc">Down</span>
      ID
    </th>
    <th>
      <span class="order" data-field="name" data-dir="asc">Up</span>
      <span class="order" data-field="name" data-dir="desc">Down</span>
      Name
    </th>
  </tr>
  <tbody>
  <?php if ( !empty( $users ) : ?>
      <?php foreach ( $users as $user ) : ?> 
  <tr>
    <td><?=$user->id?></td>
    <td><?=$user->name?></td>   
  </tr>
      <?php endforeach; ?> 
  <?php endif; ?>
  </tbody>
</table>

      

        
        
        
      

    

and you need a "partial view" to load only body data:

VIEW: table_users_partial_view.php

      <?php if ( !empty( $users ) : ?>
          <?php foreach ( $users as $user ) : ?> 
      <tr>
        <td><?=$user->id?></td>
        <td><?=$user->name?></td>   
      </tr>
          <?php endforeach; ?> 
      <?php endif; ?>

      

        
        
        
      

    

Your controller must be changed to display the partial view if you are sending an AJAX request:

CONTROLLER: Changed to return only partial data:

public function user_results()
{
    // To control the page you're sending is ok, you have to count first the number of elements
    $page  = $this->input->get( $page );

    $elements_per_page = 3; // You could also send it in your request

    $get_offset    =  ( $page – 1 ) * $elements_per_page;
    // And you have to control the offset:
    $min_offset    = 0; 
    $max_offset    = floor( $total / $elements_per_page ); 
    $offset    = ( ( get_offset > $max_offset ) ? $max_offset : ( ( $get_offset < $min_offset ) ? $min_offset : $get_offset ) ); 

    // Then, you have to control the fields to order. I'd recommend you white list filter:
    $get_order_field   = $this->input->get('order_field');
    $order_fields  = array( 'id', 'letter');
    $order_field   = in_array( $get_order_field, $order_fields ) ? $get_order_field : 'id';

    $get_order_direction   = $this->input->get('order_direction');
    $order_directions  = array( 'asc', 'desc');
    $order_field       = in_array( $get_order_direction, $order_directions ) ? $get_order_direction : 'asc';

    // And now, you can show a list of the elements you want to show from the page:
    $this->load->model('users_model');

    $data['users'] = $this->users_model->getUsers( $offset, $elements_per_page, $order_field, $order_direction );
    if ( !$this->input->is_ajax_request() ) {
        $this->load->view('table_users_view', $data );
    } else {
        $return = array( 
            'tbody' => $this->load->view('table_users_partial_view
        );  
        // This is to give back the value as a JSON the JS will understand. In fact, in the AJAX call we indicated it.
        echo json_encode( $return );
    }
}

      

        
        
        
      

    

DISCLAIMER: Only SQL from the beginning is tested code, all of the above may or may not work as expected if just copy / paste. The code is for clarification and gives you the following path: you must understand and code first. Everything above is written in the hope that it will be helpful, if you have any doubts, please post a comment. Have fun coding!