Table/record sorting made easier for CodeIgniter

In a project I am currently working on, I needed a way to efficiently allow sorting of the records in an admin area while preserving the functionality of a search function (that passes the query in the url) and the pagination library. Initially it started off as a section of each method where sorting was allowed (record lists of things like countries, states, etc). My sorting routines finally ended up being put into their own helper.

Calling the function:

list($sort, $config['uri_segment'], $config['base_url']) = get_sort_vars(3, $config['base_url'], $this->sortable_fields);

File: application/helpers/sort_helper.php

<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

function get_sort_vars($uri_segment = 3, $base_url = '', $sortable_fields = array())
{
 $CI =& get_instance();

 $sort = NULL;
 if ($CI->uri->segment($uri_segment) == 'sort') {
  $field = $CI->uri->segment($uri_segment + 1);
  if ($field !== FALSE)
  {
   $sort_field_names = array_flip($sortable_fields);
   $sort = $sort_field_names[$field];
   $base_url .= "/sort/{$field}";
   $uri_segment++;
   $order = strtolower($CI->uri->segment($uri_segment + 1, 'asc'));
   if ($order == 'asc' || $order == 'desc')
   {
    $sort .= ' ' . $order;
    $base_url .= "/{$order}";
    $uri_segment += 2;
   }
   $sort = urldecode($sort);
  }
 }
 return array($sort, $uri_segment, $base_url);
}

/* End of file */

Complete Example:

<?php
class Sample extends Controller {
 // set common class vars
 var $limit = 10;

 var $sortable_fields = array(
  ''    => '',
  'sample_id'  => 'id',
  'sample_date' => 'date',
  'sample_name' => 'name'
 );

 function Sample()
 {

  parent::Controller();
  // every public method of this controller requires the model
  $this->load->model('m_sample');
  $this->load->helper('sort');

 }

function index()
{

  $class = strtolower(__CLASS__);
  $function = strtolower(__FUNCTION__);

  // use segment 1 (default to $class) in case custom routing was used
  $config['base_url'] = $this->uri->segment(1, $class) . '/' . $function;

  // make sure that the 4th segment contains a valid sortable field (empty = no sort)
  if (!in_array($this->uri->segment(4, ''), $this->sortable_fields)) redirect($config['base_url']);
  foreach ($this->sortable_fields as $key => $value) {
   // create sort urls for view eg: "sample/index/sort/name/asc"
   $data["sort_{$value}"] = "{$config['base_url']}/sort/{$value}/" . (($this->uri->segment(4) == $value && $this->uri->segment(5) == 'asc') ? 'desc' : 'asc');
  }

  // get_sort_vars():Array(3) checks url, updates base_url and uri_segment for pagination, and creates sort sql string for model
  // 3 = uri segment to look at for 'sort'; i have search functions that would usually start at 4 instead, eg: "sample/search/querystring/sort/name/asc"
  list($sort, $config['uri_segment'], $config['base_url']) = get_sort_vars(3, $config['base_url'], $this->sortable_fields);

  // args: limit(int), offset(int), sort(str)
  $data['samples'] = $this->m_sample->get_all_samples($this->limit, $this->uri->segment($config['uri_segment'], 0), $sort);

  $config['total_rows'] = $this->m_sample->get_all_samples_count();
  $config['per_page'] = $this->limit;
  $this->pagination->initialize($config);
  $data['pagination'] = $this->pagination->create_links();

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

 }

}
/* End of file */ 

The code has been trimmed down to include the general idea of what is being done. I also added all the comments in case it’s hard to follow what is going on. Feel free to comment if you have any questions.

6 thoughts on “Table/record sorting made easier for CodeIgniter

  1. csotelo

    Interesting helper. I have used in my project and it worked very well … even, you can create another helper function that will return the pagination configuration (if a structure is maintained in the URI of the project, applying a little DRY).

    What we fail in this tutorial, is the model example:

    function get_all_count()
    {
    return $this->db->select(‘sample_id’)->get(‘table_name’)->num_rows();
    }

    function get_all($limit, $offset, $sort = ”)
    {
    $this->db->limit($limit, $offset);
    if($sort) $this->db->order_by($sort);
    $query = $this->db->get(‘table_name’);
    return $query->result();
    }

    And how it would apply in the view (with dropdowns or anchors)

    Bye!

    Reply
  2. priya

    load->library('pagination');
    $limit = 8;
    $this->load->model('Contry_model');
    $totalRows = $this->Contry_model->getTotalPosts();
    $data['posts'] = $this->Contry_model->getPosts($offset, $limit);

    $config['base_url'] = $this->config->item('base_url') . 'index.php/Contry/bloglist/';
    $config['total_rows'] = $totalRows;
    $config['per_page'] = $limit;
    $config['full_tag_open'] = '';
    $config['full_tag_close'] = '';
    $this->pagination->initialize($config);

    $jsFunction['name'] = 'show';
    $jsFunction['params'] = array();
    $this->pagination->initialize_js_function($jsFunction);

    $data['base_url'] = $config['base_url'];
    $data['page_link'] = $this->pagination->create_js_links();
    //$this->load->view('header_view',$data);
    $this->load->view('con_view', $data);
    //$this->load->view('footer_view',$data);

    }

    function bloglist($offset = 0)
    {
    $this->load->library('pagination');
    $limit = 8;
    $this->load->model('Contry_model');
    $totalRows = $this->Contry_model->getTotalPosts();
    $data['posts'] = $this->Contry_model->getPosts($offset, $limit);

    $config['base_url'] = $this->config->item('base_url') . 'index.php/Contry/bloglist/';
    $config['total_rows'] = $totalRows;
    $config['per_page'] = $limit;
    $config['full_tag_open'] = '';
    $config['full_tag_close'] = '';
    $this->pagination->initialize($config);

    $jsFunction['name'] = 'show';
    $jsFunction['params'] = array();
    $this->pagination->initialize_js_function($jsFunction);

    $data['base_url'] = $config['base_url'];
    $data['page_link'] = $this->pagination->create_js_links();
    //$this->load->view('header_view',$data);
    $this->load->view('data_view', $data);
    //$this->load->view('footer_view',$data);

    }
    function con_delete($id)
    {
    //$this->load->helper('URL')
    $this->load->library('table');
    $this->load->helper('html');
    $this->load->model('contry_model');
    if((int)$id > 0)
    {
    $this->contry_model->mod_delete($id);
    }
    redirect('index.php/contry/bloglist/');
    }
    }
    ?>

    model code

    load->database();
    $query = $this->db->query("SELECT count(*) as total_rows FROM register_page");
    if ($query->num_rows() > 0) {
    $row = $query->row();
    return $row->total_rows;
    }
    return 0;
    }
    function getPosts($offset, $limit)
    {
    $this->load->database();
    $query = $this->db->query("SELECT * FROM register_page LIMIT $offset, $limit");
    if ($query->num_rows() > 0)
    {
    return $query->result();
    }
    }
    function mod_delete($id)
    {
    $this->load->database();
    $this->db->delete('register_page',array('id'=>$id));
    }
    }
    ?>

    Ajax code

    function show(offset)
    {
    //alert(""+offset);
    if (window.XMLHttpRequest)
    {
    xmlhttp=new XMLHttpRequest();
    }
    else
    {
    xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
    }
    xmlhttp.onreadystatechange=function()
    {
    if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("data_view").innerHTML=xmlhttp.responseText;
    }
    }
    xmlhttp.open("GET","" + offset,true);
    xmlhttp.send();
    }

    load->view('data_view', $data);
    ?>

    view code

    ID
    FristName
    LastName
    Email
    RE-Mail
    Password
    Action

    id; ?>
    firstname; ?>
    lastname; ?>
    email; ?>
    remail; ?>
    password; ?>
    <a href="http://localhost/Codeigniter/index.php/contry/con_delete/id;?>">Delete

    plz help me….
    i have need sorting same code in codeigniter

    Reply
    1. admin Post author

      I don’t see where you have tried to achieve the desired result. You’re just showing me what you have, and saying what you want. There’s help, and then there’s doing it all for you.

      Suggestion: Opt for a session-based search/sort instead of how I did it in this post 😉

      PS: Paid services available to those who don’t put forth an effort 😛

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *