Reputation:

How To Create Sortable Table THEAD Links for Codeigniter Table

I can not seem to make my thead th links be able to sort out my data. I have tried many different methods now not sure what to do.

For example if user clicks on the user id thead link it will sort users by user id asc or desc and so on. Same for username, date added links. I have tried it with out sourced scripts but because it is controlled with controller does not work.

Using My Code

What I am trying to achieve is with the user list table head area there are 5 thead that I would like to make it so when I click on either one it sorts out the list according to that field by asc and or or dsc. Trying to make it work with current pagination the pagination works fine.

Some Library's are auto-loaded.

Codeigniter User Controller

protected function getList() {
    $data['heading_title'] = $this->lang->line('heading_title');

    $data['breadcrumbs'] = array();

    $data['breadcrumbs'][] = array(
        'text' => '<i class="fa fa-home"></i>' .' '.  $this->lang->line('text_home'),
        'href' => site_url('admin/dashboard')
    );

    $data['breadcrumbs'][] = array(
        'text'      => $this->lang->line('heading_title'),
        'href'      => site_url('admin/users')
    );

    $data['text_enabled'] = $this->lang->line('text_enabled');
    $data['text_disabled'] = $this->lang->line('text_disabled');

    $data['column_user_id'] = $this->lang->line('column_user_id');
    $data['column_name'] = $this->lang->line('column_name');
    $data['column_status'] = $this->lang->line('column_status');
    $data['column_last_updated'] = $this->lang->line('column_last_updated');
    $data['column_date_added'] = $this->lang->line('column_date_added');
    $data['column_action'] = $this->lang->line('column_action');

    $data['delete'] = site_url('admin/users/delete');
    $data['insert'] = site_url('admin/users/add');

    $data['text_confirm'] = $this->lang->line('text_confirm');

    $data['button_insert'] = $this->lang->line('button_insert');
    $data['button_edit'] = $this->lang->line('button_edit');
    $data['button_delete'] = $this->lang->line('button_delete');

    $this->load->library('setting'); 

    $this->load->library('pagination'); 

    $config = array(); 
    $config["base_url"] = base_url('admin/users'); 
    $config['total_rows'] = $this->db->get('user')->num_rows(); 
    $config["per_page"] =  $this->setting->get('config_limit_admin');
    $config["uri_segment"] = 3;  

    $config['full_tag_open'] = "<ul class='pagination'>";
    $config['full_tag_close'] ="</ul>";
    $config['num_tag_open'] = '<li>';
    $config['num_tag_close'] = '</li>';
    $config['cur_tag_open'] = "<li class='disabled'><li class='active'><a href='#'>";
    $config['cur_tag_close'] = "<span class='sr-only'></span></a></li>";
    $config['next_tag_open'] = "<li>";
    $config['next_tagl_close'] = "</li>";
    $config['prev_tag_open'] = "<li>";
    $config['prev_tagl_close'] = "</li>";
    $config['first_tag_open'] = "<li>";
    $config['first_tagl_close'] = "</li>";
    $config['last_tag_open'] = "<li>";
    $config['last_tagl_close'] = "</li>";

    $this->pagination->initialize($config);

    $data['users'] = $this->db->get('user', $config["per_page"], $this->uri->segment(3));

    if (isset($this->request->post['selected'])) {
        $data['selected'] = (array)$this->request->post['selected'];
    } else {
        $data['selected'] = array();
    }

    return $this->load->view('user/users_list', $data);
}

View

<form action="<?php echo $delete; ?>" method="post" enctype="multipart/form-data" id="form-user">

<div class="table-responsive">
<table class="table table-bordered table-hover">
<thead>
<tr>
<td style="width: 1px;" class="text-center"><input type="checkbox" onclick="$('input[name*=\'selected\']').prop('checked', this.checked);" /></td>
<td class="text-center" style="color: #1E91CF; font-size: 14px; font-weight: bold;"><?php echo $column_user_id; ?></td>
<td class="text-center" style="color: #1E91CF; font-size: 14px; font-weight: bold;"><?php echo $column_name; ?></td>
<td class="text-center" style="color: #1E91CF; font-size: 14px; font-weight: bold;"><?php echo $column_status; ?></td>
<td class="text-center" style="color: #1E91CF; font-size: 14px; font-weight: bold;"><?php echo $column_last_updated; ?></td>
<td class="text-center" style="color: #1E91CF; font-size: 14px; font-weight: bold;"><?php echo $column_date_added; ?></td>
<td class="text-center" style="color: #1E91CF; font-size: 14px; font-weight: bold;"><?php echo $column_action; ?></td>
</tr>
</thead>
<tbody>
<?php foreach ($users->result() as $user) { ?>  
<td class="text-center"><?php if (in_array($user->user_id, $selected)) { ?>
<input type="checkbox" name="selected[]" value="<?php echo $user->user_id; ?>" checked="checked" />
<?php } else { ?>
<input type="checkbox" name="selected[]" value="<?php echo $user->user_id ?>" />
<?php } ?>
</td>
<td class="text-center"><?php echo $user->user_id; ?></td>
<td class="text-center"><?php echo $user->username; ?></td>
<td class="text-center"><?php if ($user->status == TRUE) { echo $text_enabled; } else { echo $text_disabled ; } ?></td>
<td class="text-center"><?php echo $user->last_updated; ?></td>
<td class="text-center"><?php echo $user->date_added; ?></td>
<td class="text-center"><?php echo anchor("admin/users/edit/" . $user->user_id, '<div class="btn btn-primary text-right" role="button"><i class="fa fa-pencil"></i>
 Edit</div>');?></td>
</tr>
<?php } ?>
</tbody>
</table>
</div>
<div class="row">
<div class="col-sm-6 text-left">
<?php echo $this->pagination->create_links();?>
</div>
</div>
</form>

Model get users

public function getUsers() {
  $this->db->select('*');
  $this->db->from($this->db->dbprefix . 'user');
  $query = $this->db->get();

  if ($query->num_rows() > 0) {
      return $query->result();
      return true;
  } else {
      return false;
  }
}

Upvotes: 3

Answers (4)

Federico J.

Reputation: 15912

Ok, let's order and paginate!

First of all, how pagination work

When you paginate, you say:

"There are (let's say) 25 elements. I want to show page 2, so, if I'm (let's say) showing 5 elements per page, I have to order them and then show from element 6 to 10"

That way, if you wanted to show page 3, you'll order by (let's say) id, and then you show from elements 11 to 15. Summarizing, you must know:

How many elements you're showing
The page you want to list (than later you'll transform in an offset, keep reading)
The order you're using to list.

Example: I'll use the following table, insert it in a DDBB and run the 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's paginate!

Imagine you want to show page 3, 2 elements per page, ordering by ascendent id. You'll have to show the elements with id 5 and 6, as page 1 has ids 1 and 2, and page 2 has ids 3 and 4. So, our offset will be:

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

And know, to order by id and show the elements from the page you'll have to run:

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

And you'll get, in this order: (5,'f') and (6, 'e')

And if you wanted order them by letter, you'd run

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

wich gives you, in this order: (6, 'e') and (5, 'f')

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

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

Well, now, you know how the query you have to build, so, lets build it with CI.

Building pagination with CI

Your model will need to know what you want to show: The order, the page you want to show, and the number of elements, so, it will have to 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 code above you have a model class base to start sending values from the database to the controller, but you also have to play with the controller. It'll have to handle the pages to make sure you're in range, to make sure the values you want to order by are fields it really exists... Well, controller work, 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'd put all the table values in a 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>

Well, this is TO START ANSWERING your question. Now you can paginate, but is needed to add automatically the pagination links, wich could look like BASE_URL/users/?page=1&order_field=id&order_direction=asc. And, you'll have to write in JS an AJAX call triggered via anchors in your html. If you understood all above code, next steps are:

Write an AJAX call with its anchors in the header of the table.
Reload the content based on values of the page selected and those values: For this, you'll need a controller method wich takes the AJAX parameters and returns you the tbody, 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 view 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'll need a "partial view" to load the data of only the tbody:

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 will have to be modified to show the partial view if you are sending an AJAX request:

CONTROLLER: Modified to give back 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 above could or could not work properly if just copy/pasted. The code is for clarification and to give you a path to follow: You MUST understand first and code after. All above is written in the hope it will be useful, if you had any doubt, write a comment. Have fun coding!

Upvotes: 2

user3300065

Reputation:

I have found the best program so far that does everything I am after It is called https://datatables.net/examples/styling/bootstrap.html

I have had to use this because coeigniter pagination does not work with sorting methods.

Upvotes: 0

Tomasz

Reputation: 336

First of all put page you are on in parameters that you are passing to function in controller:

protected function getList($offset = 0, $order_by = 'id', $order_direction = 'asc') {

as in https://ellislab.com/codeigniter/user-guide/general/controllers.html Remember to put default values to those so that link can still be accessed without need to provide them.

Second thing is pass this data to view, add it inside of data variable.

Next pass $order_by and $order_direction to model as parameters public function getUsers($order_by, $order_direction) { don't need to set default values as they will always be set by controller.

After that in model you should make array of things that user can sort, just a good practice. You will sort using active record with following line $this->db->order_by($order_by, $order_direction); inside of controller, also don't forget to limit $order_direction to asc or desc so user cannot break application (just put default if not recognised).

After that preparation in view just do

site_url('admin/users/getList/'.$offset.'/user_id/'.(($order_direction==='asc')?'desc':'asc'));

change user_id to whatever other column you have and create links using that, you can create nice function for creating those links.

site_url() is from url_helper (https://ellislab.com/codeigniter/user-guide/helpers/url_helper.html)

Also don't forget to use offset that you passed from url in your model and to modify pagination module if needed.

It is general overview of how this can be achieved in codeigniter, I am using something similar for my application so I know that it works. You will need to write your own code but you get the idea where to go from here.

Upvotes: 0

jogesh_pi

Reputation: 9782

Take a look on this example:

View:

<thead>
  <tr>
    <td class="text-center"><input type="checkbox" onclick="$('input[name*=\'selected\']').prop('checked', this.checked);" /></td>
    <td class="text-center asc" data-orderby="column_user_id"><?php echo $column_user_id; ?></td>
    <td class="text-center asc" data-orderby="column_name"><?php echo $column_name; ?></td>
    <td class="text-center asc" data-orderby="column_status"><?php echo $column_status; ?></td>
    <td class="text-center asc" data-orderby="column_last_updated"><?php echo $column_last_updated; ?></td>
    <td class="text-center asc" data-orderby="column_date_added"><?php echo $column_date_added; ?></td>
    <td class="text-center asc" data-orderby="column_action"><?php echo $column_action; ?></td>

Lets say you have page that display the data :

controller/model/page/1

Lets find the url for Javascript:

$url = $this->input->server('REQUEST_URI');
if( preg_match('/\?/', $url) ) 
    $url = strstr($url, "?", true);

JQuery Code to change the URL:

$('.table thead td').click(function(e){
    var $this = $(this), 
        order_by = $this.data('orderby'), 
        myURL = '<?php echo $url ?>', 
        order='';

    // Handle Asc and Desc
    if( $this.hasClass('asc') ) {
        $this.removeClass('asc');
        $this.addClass('desc');
        order = 'desc';
    }
    else {
       $this.removeClass('desc');
        $this.addClass('asc');
        order = 'asc';
    }


    // change url on click
    document.location.href = myURL + '?orderby='+order_by+'&order='+order;
});

Now you will get the URL :

controller/model/page/1?orderby={data_element}&order={asc|desc}

Now handle the QUERY STRING in controller and pass it to the Model.

Controller:

// Return Query String else return empty
$QS = $this->input->server('QUERY_STRING');
$Page = $this->uri->segment(3);

$Start = ! empty($Page) ? $Page : 0;

// Pass this string to your model
$data['users'] = $this->mymodel->getUsers($Start, $config["per_page"], $QS);

Model

public function getUsers($Start=0, $Limit=10, $QS=null) {


  $this->db->select('*');
  $this->db->from('users');

  // Handle Order By
  if( ! is_null($QS) ) {
      parse_str($QS);

      if( isset($order_by) && ! empty($order_by) && isset($order) && ! empty($order) ) { 
          $this->db->order_by($order_by, $order);
      }

  }

  $this->db->limit($Start, $Limit);
  $query = $this->db->get();

  return $query->num_rows() > 0 ? $query->result_array() : false;

}

For active records help please follow this link. Hope this help you to understand and implement.

Upvotes: 0

How To Create Sortable Table THEAD Links for Codeigniter Table

Answers (4)

First of all, how pagination work

Building pagination with CI

View:

Controller:

Model

Related Questions