[OUTDATED] PHP Codeigniter full featrued JQuery datatables [Part 2]

Posted on

datatables2
Demo Source

Introduction
Continuing on from Part1, Part 2 of will show you how to add anchors, images to datatables and this is just for showing the idea and you may extend it as your needs

Catch up:
Check Part1

Step 1 : Setting up for the demo:
Create database with the below script (we have added new column “avatar”)

CREATE TABLE `subscriber` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first` varchar(100) DEFAULT NULL,
`last` varchar(100) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`avatar` varchar(200) DEFAULT NULL,
`date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1;
insert into `subscriber`(`id`,`first`,`last`,`email`,`date_created`) values (23,'Ahmedd','samy','ahmed.samy.cs@gmail.com','2013-03-11 23:11:40');
insert into `subscriber`(`id`,`first`,`last`,`email`,`date_created`) values (24,'John','Carter','test@test.com','2013-03-11 23:19:05');
insert into `subscriber`(`id`,`first`,`last`,`email`,`date_created`) values (25,'Lina','Khaled','lina@hotmail.com','2013-03-17 20:54:48');

-Modify style.css found at assets/css/style.css to the below

 body{font-family: Arial,Helvetica,sans-serif;background-color: #F0F0F0; font-size: 12px;}
h1{color:#072936;}
.wrapper { margin: 0 auto; width: 730px; }
footer { border-top: 1px solid #CCCCCC; float: right; font-size: 12px; margin: 40px 0 0; padding: 10px; width: 81%;}
/*Datatables style*/
#big_table_wrapper {background-color: #fff; }
#big_table_wrapper .dataTables_length {float:left;}
#big_table_wrapper .dataTables_filter {float:right;}
#big_Table_wrapper .ui-toolbar{padding:5px;}
#big_Table_wrapper .ui-toolbar{padding:5px;}
#big_table{width:730px; text-align: center;}
.dataTables_paginate .ui-button {    margin-right: -0.1em !important;}
.paging_full_numbers .ui-button {    color: #333333 !important;    cursor: pointer;    margin: 0;    padding: 2px 6px;}
.dataTables_info {    float: left;    width: 50%;}
.dataTables_info {    padding-top: 3px;}
.dataTables_paginate {    float: right;    text-align: right;}
.dataTables_paginate {    width: auto;}
.paging_full_numbers {    width: 350px !important;}
#big_table_processing > img {padding-left:20px;}
.actions{ display:block; padding:3px;  }
.actions a { margin-left:10px; margin-right: 10px; } 

Step 2 : Create the MVC:

subscriber controller in application/controllers/subscriber.php

<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
class Subscriber extends CI_Controller
{

    public function __construct()
    {
        parent::__construct();
        $this->load->library('Datatables');
        $this->load->library('table');
        $this->load->database();
    }

    function index()
    {

        //set table id in table open tag
        $tmpl = array('table_open' => '<table id="big_table" border="1" cellpadding="2" cellspacing="1" class="mytable">');
        $this->table->set_template($tmpl);

        $this->table->set_heading('First Name', 'Last Name', 'Email', 'Avatar', 'Actions');

        $this->load->view('subscriber_view');
    }

    //function to handle callbacks
    function datatable()
    {
        $this->datatables->select('id,avatar,first,last,email')
            ->unset_column('id')
            ->unset_column('avatar')
            ->add_column('avatar', '<img src="$1"/>', 'avatar')
            ->add_column('Actions', get_buttons('$1'), 'id')
            ->from('subscriber');

        echo $this->datatables->generate();
    }

    function edit($id)
    {
        //load some edit form
        redirect('subscriber');
    }

    function delete($id)
    {
        //add some delete code
        redirect('subscriber');
    }
}

Selecting a field is forced to be displayed in the table unless we have used unset_column as workaround and still have access on this column data

Create datatables_helper in application/helpers/datatables_helper

<?php
/*
 * function that generate the action buttons edit, delete
 * This is just showing the idea you can use it in different view or whatever fits your needs
 */
function get_buttons($id)
{
    $ci = & get_instance();
    $html = '<span class="actions">';
    $html .= '<a href="' . base_url() . 'subscriber/edit/' . $id . '"><img src="' . base_url() . 'assets/images/edit.png"/></a>';
    $html .= '<a href="' . base_url() . 'subscriber/delete/' . $id . '"><img src="' . base_url() . 'assets/images/delete.png"/></a>';
    $html .= '</span>';

    return $html;
}

This helper is helping to generate action buttons based on id, know that it’s not a good practice to generate HTML in helper function
but this is just a showing the idea, and you can use it separate view(s)

Conclusion:

  • You can add custom html elements in the datatables through helper function and using  the row data as parameters, that open endless possibilities , overlays, dialogs…etc almost any content
  • You can use any php native function or helper function on the selected data

Code Igniter: http://ellislab.com/codeigniter
DataTables: http://datatables.net/
IgnitedTables: https://github.com/IgnitedDatatables/Ignited-Datatables/

Tags: , , ,


  • Saavn Pro Apk

    Could you please tell me what it’s mean

    Thanks

    Reply

  • watch the unborn

    El alojamiento ideal para visitar GranadaPensión Veracruz …

    Reply

  • Freddy

    Hi Ahmed

    let say in your tutorial has one field salary then how can count it and put in the footer with colspan, Thanks

    Reply

  • block causes

    Search Results | N.C. Center for Nonprofits

    Reply

  • sandeep naskar

    First of all like to appreciate your work it helped me a lot but i need to get the data from the Subscriber_model to get the data from the database and not from the function datatable()

    Any help is apprecaited

    Reply

  • Himani

    please tell me how to pass id in sAjexsource ??

    Reply

  • anonymous

    thanks, very very help

    Reply

  • Arshad

    mysqli not working

    Reply

  • Ali Shoaib

    i am facing an issue my parameter is not passing as a id its considering as a string and give me error
    myhelper_helper.php

    function get_branch($branch)
    {
    $CI = &get_instance();
    $query = $CI->db->query(“select * from branches where branch_id = $branch”)->row();
    $return = $query->branch_name;
    return $return;

    }

    public function get_users_datatable()
    {
    $this->datatables->select(‘admin.admin_id,name,email,sex,birthday,religion,designation,address,phone,group_name,admin.branch_id,admin.user_id’)
    ->unset_column(‘admin.branch_id’)
    ->add_column(‘Branch’,get_branch(‘$1′),’admin.branch_id’)
    ->from(‘admin’)
    ->join(‘groups’,'groups.group_id=admin.group_id’,'left’)
    ->where(array(‘admin.group_id !=’=>0));

    echo $this->datatables->generate();
    //echo $this->db->last_query();
    }

    error is
    Error Number: 1054Unknown column ‘$1′ in ‘where clause’select * from admin where admin_id
    = $1Filename: D:\xamp\htdocs\dnspak\vivid\system\database\DB_driver.phpLine Number: 331

    plz help me asap

    Reply

  • Dean

    (how do I fix this error?)

    A PHP Error was encountered

    Severity: Notice

    Message: Only variable references should be returned by reference

    Filename: core/Common.php

    Line Number: 257

    A Database Error Occurred

    Unable to select the specified database: hype_news

    Filename: C:\xampp\htdocs\tutorial_datatables_part2\system\database\DB_driver.php

    Line Number: 140

    Reply

  • Nathan

    I know this is an old thread, however this would be great if I could get it to work! and very powerful integration, kudos to Ahmed.

    I’m on CI3 and keep getting

    Uncaught TypeError: Cannot read property ‘length’ of undefined

    fnAjaxUpdateDraw @ data-table.jquery.js:1907
    (anonymous function) @ data-table.jquery.js:1772
    f.Callbacks.n @ jquery-1.7.1.min.js:2
    f.Callbacks.o.fireWith @ jquery-1.7.1.min.js:
    2w @ jquery-1.7.1.min.js:
    4f.support.ajax.f.ajaxTransport.c.send.d @ jquery-1.7.1.min.js:4

    3 days now gong round in circles, 3 hours sleep, I think my eyes are about to bleed –

    Help welcomed :)

    Reply

  • carl

    nice tutorial, can you do a versión that uses a subscriber_model to get the ítems from the database? it seems there’s no use of a model

    Reply

  • Blin

    How to order by a column date format eu ?

    Reply

  • Anand

    this is my problem
    here when return a string if condition not working
    here my code
    function get_action($id,$j)
    {

    $ci= & get_instance();
    $ht=$j;
    $html=”;

    $html.=$j;

    //$html.=$GLOBALS['id'];
    $html.=’ ‘;

    $html.=’ ‘;

    $html.=’‘:’class=”tip” title=”">‘).’
    ‘;

    return $html;

    }

    I am Waiting For your Reply

    Reply

  • Dawood Khan

    Hi Ahmad!!
    Thanks for your good tutorials, I like it and used in my project, all going well except the actions column.

    I have bootstrap drop down menu as “options” column for every row. but its not working, the drop down is not open. Here is my code.

    function ajax_get_patients()
    {
    $this->load->library(‘datatables’);
    $this->datatables->select(‘p.id,p.reg_no,CONCAT_WS(” “,p.title,p.first_name,p.middle_name,p.last_name) AS patient_full_name,
    p.father_name,p.cnic,p.gender,p.customer_type,c.name as city_name,p.phone_1,cc.name as store_city_name,IF(p.active=1,”Active”,”Inactive”) as active’,FALSE);
    $this->datatables->from(‘patients as p’);
    $this->datatables->join(‘cities as c’,'c.id=p.city’);
    $this->datatables->join(‘cities as cc’,'cc.id=p.cons_city’);

    if($this->session->userdata(‘group_id’)==4)
    {
    // if its a SA
    $this->datatables->where(‘p.store_location_id’,$this->session->userdata(‘location’));
    $this->datatables->where(‘p.active’,1);
    }

    $link = $edit = “$1“;
    $this->datatables->edit_column(‘p.id’,$link,’p.id’);
    $this->datatables->add_column(‘Options’,$this->get_options(‘$1′),’p.id’);
    return $this->datatables->generate();

    }

    function get_options($id)
    {
    $html =”;
    $html.=’ Options ‘;
    $html.= ”;
    if($this->session->userdata(‘group_id’)!=3 && $this->session->userdata(‘group_id’)!=4){
    $html.=’Edit‘;
    $html.=’Add Prescription‘;
    }
    if($this->session->userdata(‘group_id’)!=5){
    $html.=’Create Invoice‘;
    }
    $html.=’View Deatils‘;
    $html.=”;
    return trim($html);
    }

    here is the view:

    PatientID
    Reg#
    Customer Name
    Father/Husband Name
    CNIC
    Gender
    Custormer Type
    City
    Phone#1
    Store City
    Active
    Options


    $(document).ready(function () {
    var oTable = $(‘#patient_table’).dataTable({
    “bProcessing”: true,
    “bServerSide”: true,
    “sAjaxSource”: ‘patient/datatable’,
    “bJQueryUI”: true,
    “sPaginationType”: “bootstrap”, // full_numbers
    “iDisplayStart “: 10,
    “iDisplayLength”: 10,
    “oLanguage”: {
    “sProcessing”: “<img src='img/loader.gif’>”
    },
    “fnInitComplete”: function () {
    oTable.fnAdjustColumnSizing();
    },
    “aaSorting”: [[ 1, "desc" ]],
    “aoColumns”: [
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    {
    "bSortable": false, "bSearchable": false }

    ],

    ‘fnServerData’: function (sSource, aoData, fnCallback) {
    $.ajax
    ({
    ‘dataType’: ‘json’,
    ‘type’: ‘POST’,
    ‘url’: sSource,
    ‘data’: aoData,
    ‘success’: fnCallback
    });
    }
    });
    });

    Kindly help me what is going wrong, I have done every thing but stuck here

    Thanks in advance.
    Dawood

    Reply

  • Red

    search not working on me..

    Reply

  • indra

    where i put the image photo

    Reply

  • Ade Permana

    Hello admin, can u help me please.. :(

    How can I make this AutoNumber in datatables like http://prntscr.com/66jiz3

    More or less like this https://datatables.net/examples/api/counter_columns.html

    Thanks before. :)

    Reply

  • Akmal Dirgantara

    Hello Admin, Help me Please..
    How can I make autonumber in CI in DataTable?
    Example : http://prntscr.com/66ahml

    Reply

  • meet

    my datatable is not updating after delete function.

    here is the code.. i pass post id ($_POST['postid']) from ajax to this function . and it deletes the data from database also. but after that redirect function not working or i don’t know what’s happening. but my data table is not updating after delete

    function delete()
    {
    $this->subscriber_model->delete_post($_POST['postid']);
    redirect(‘subscriber’, ‘refresh’);

    }

    Reply

  • vignesh

    Hi Ahmed,

    In my website, sorting and searching not working.

    Reply

  • ricero

    public function __construct() {
    parent::__construct();
    $this->load->library(‘Datatables’);
    $this->load->library(‘table’);
    $this->load->database();

    missed the line –> $this->load->helper(‘datatables_helper’);

    }

    Reply

  • Nidhin

    I got the error like this when Itried to add column:

    Fatal error: Call to undefined function get_buttons() in E:\xampp\htdocs\amc\application\controllers\state.php on line 27

    Reply

  • Azeem

    Hi Ahmed,

    Very nice work :), my question is, how can I add “checkboxes” column (For multi record deletion) in the beginning of the table containing record id in value attribute and “Edit” column in the end of the table as well.

    Reply

  • Jey

    Hi Ahmed, this very nice tutorials,
    i wondering how to add the row id using the id of database in each row
    can you help ?

    Reply

  • Farveen Hassan

    Hi Ahmed,

    First I Would Like to thank you for the code you have given for server side datatables.It is Working Fine and Clean ,and also you have saved my day….

    But now i like to know whether we can include sub query inside controller function (named :datatable )
    Is There any working demo or snippnet to show how it can be done ?

    hoping some solutions

    Thanks in advance :)

    Reply

  • rohit

    hi thanks for this nice tutorial but i dont know how to add a checkbox in the starting of table to delete multiple records

    THanks

    Reply

  • Sunarya Ade Permana

    Hi, I have a problem can u help me please..

    ” Why conditional no run in my helper check this : http://pho.to/7BKiU

    //Helper
    function table_status($id){
    $ci = & get_instance();
    if($id == ‘pending’):
    $html = $id.”a”;
    return $html;
    else:
    $html = $id.”b”;
    return $html;
    endif;
    }

    // Controller
    public function confirm_purchase(){
    $tmpl['table_open'] = ”;
    $this->table->set_template($tmpl);
    $this->table->set_heading(‘Purchase Date’, ‘Member Code’, ‘Name’, ‘Phone’, ‘Status’, ‘Proof of trans’, ‘Action’);

    $this->load->view(‘backend/template’,$data);
    }

    public function confirm_purchase_Data(){
    $this->datatables
    ->select(‘ stn_purchase.stn_purchase_date,
    member.member_code,
    member.firstname,
    member.phone,
    stn_purchase.stn_confirm as proof,
    stn_purchase.stn_purchase_status as status,
    stn_purchase.id_stn_purchase as id ‘)
    ->join(‘member’, ‘member.id_member = stn_purchase.id_member’, ‘left’)
    ->join(‘merchant_detil’, ‘merchant_detil.id_member = stn_purchase.id_member’, ‘left’)
    ->unset_column(‘status’)
    ->unset_column(‘proof’)
    ->unset_column(‘id’)
    ->add_column(‘Status’, table_status(‘$1′), ‘status’)
    ->add_column(‘Proof of trans’, table_act_lnk(‘$1′,site_url(“merchant/stn/view_proof”)), ‘id’)
    ->add_column(‘Actions’, table_act_btn_confirm(‘$1′,site_url(“merchant/stn/confirm_purchase/update”)), ‘id’)
    ->from(‘stn_purchase’);
    echo $this->datatables->generate();
    }

    //View

    $(document).ready(function() {
    var oTable = $(‘#ajaxDT’).dataTable( {
    “bProcessing”: true,
    “bServerSide”: true,
    “sAjaxSource”: ”,
    “sPaginationType”: “full_numbers”,
    //”iDisplayLength”: 10,

    “fnInitComplete”: function() {
    //oTable.fnAdjustColumnSizing();
    },
    ‘fnServerData’: function(sSource, aoData, fnCallback)
    {
    $.ajax
    ({
    ‘dataType’: ‘json’,
    ‘type’ : ‘POST’,
    ‘url’ : sSource,
    ‘data’ : aoData,
    ‘success’ : fnCallback
    });
    }
    } );

    } );

    Reply

    • juan carlos

      $(document).ready(function() {

      var oTable = $(‘#big_table’).dataTable({
      “bProcessing”: true,
      “bServerSide”: true,
      “sAjaxSource”: ‘subscriber/datatable’,
      “bJQueryUI”: true,
      “sPaginationType”: “full_numbers”,
      “iDisplayStart “: 20,
      “aoColumnDefs”: [
      {
      “sTitle”: “License”
      , “aTargets”: ["license"]
      },
      {
      “aTargets”: [1]
      , “bSortable”: true
      , “mRender”: function(lic, type, full) {
      if (lic == 0)
      return ‘Nej’;
      else
      return ‘Ja’;
      }
      },
      {
      “aTargets”: [2]
      , “bSortable”: true
      , “mRender”: function(liv, type, full) {

      if (liv == 0)
      return ‘Nej’;
      else
      return ‘Ja’;
      }
      },
      {
      “aTargets”: [6]
      , “bSortable”: true
      , “mRender”: function(status, type, full) {

      if (status == 0)
      return ‘Inaktivera’;
      else
      return ‘Aktiv’;
      }
      },
      {
      “aTargets”: [7]
      , “bSortable”: false
      , “mRender”: function(img, type, full) {
      if (img == ”)
      return “<img src='uploads/thumbs/default48x48.png’>”;
      else
      return ‘<img src="uploads/thumbs/’ + img + ‘”>’;
      }
      },
      ],
      “oLanguage”: {
      “sProcessing”: “<img src='images/ajax-loader_dark.gif’>”
      },
      “columns”: [
      {"data": "id", "title": "LIC"},
      ],
      “fnInitComplete”: function() {
      //oTable.fnAdjustColumnSizing();
      },
      ‘fnServerData’: function(sSource, aoData, fnCallback)
      {
      $.ajax
      ({
      ‘dataType’: ‘json’,
      ‘type’: ‘POST’,
      ‘url’: sSource,
      ‘data’: aoData,
      ‘success’: fnCallback
      });
      }
      });
      });

      Reply

  • rajapulau

    how to custom header with rowspan and colspan ?

    thanks

    Reply

  • Marc Ramzy

    Hello Ahmed,

    How are you?

    I am using your code to use server-side in datatables.
    My question is: I am using .columnfilter to filter resulted table using 4 filters, 2 text filters, 1 dropdown select filter and 1 date range filter. The text filters are working, dropdown filter and date range filter are not working; it keeps processing on server side when i select date range or list value and no results are returned.

    Can you help?

    Best regards,

    Marc

    Reply

  • monir

    If I add edit,delete hyperlink in each row, search cannot work properly. it is a big problem.

    If i want to add serial no in each row, how can i add this field?

    Reply

  • Aldo

    Hi , i have a problem with datatables
    I create a table:

    $tmpl = array('table_open' => '');
    $this->table->set_template($tmpl);
    $this->table->set_heading('Titolo', 'Data pubblicazione', 'Data scadenza', 'Pubblicato', 'Operazioni');

    Initialize Datatables

    var oTable = $('#datatables').dataTable({
    "bAutoWidth": false,
    "iDisplayLength": 20,
    "bProcessing": true,
    "bServerSide": false,
    "sAjaxSource": /news/ajax/getDataTables',
    "sPaginationType": "full_numbers",
    "iDisplayStart ": 20,
    "oLanguage": {
    "sUrl": "/css/datatables/italian.txt",
    },
    "aoColumns": [
    null,
    null,
    null,
    null,
    null,
    {
    "sClass": "center",
    "sWidth": "30px",
    "bSortable": false
    }
    ],
    "fnInitComplete": function () {
    $('#datatables').on('click', '.loadBottomPage', function () {
    loadBottomPage($(this), $('#windowsWrapper-sidebar-bottom'));
    return false;
    });
    },
    'fnServerData': function (sSource, aoData, fnCallback) {
    $.ajax({
    'dataType': 'json',
    'type': 'POST',
    'url': sSource,
    'data': aoData,
    'success': fnCallback
    });
    }
    // "bJQueryUI": true,
    /* "oLanguage": {
    "sProcessing": "<img src='assets/images/ajax-loader_dark.gif'>"
    },*/
    });

    Create my server side function

    $this->datatables->select('news.news_id,news_lingue_pubblicato,news_lingue_titolo,news_dataInizio,news_dataFine,news_pubblicato')
    ->unset_column('news_id')
    ->unset_column('news_pubblicato')
    ->unset_column('news_lingue_pubblicato')
    ->add_column('news_lingue_pubblicato',news_getPub( '$1','$2' ),'news.news_id,news_lingue_pubblicato')
    ->add_column('operazioni',news_getOpe( '$1' ),'news.news_id')
    ->from('news')
    ->join('news_lingue','news.news_id = news_lingue.news_id','inner');

    echo $this->datatables->generate();

    Helper functions:

    function news_getPub($id = 0,$value = 0)
    {
    $CI = & get_instance();
    $si = '/assets/images/online.png';
    $no = '/assets/images/offline.png';
    if ($value==1)
    return '';
    else
    return '';

    }
    function news_getOpe($id = 0)
    {
    $CI = & get_instance();
    $html = '';
    if (getPermessi( 'news', 'modify', $CI->session->userdata("gruppi_id") ) ) {
    $html .= '';
    }
    if (getPermessi( 'news', 'delete', $CI->session->userdata("gruppi_id") ) ) {
    $html .= '';
    }
    return $html;
    }

    the problem is that exec_replace does not perform the function.
    i don’t understand this line: if(preg_match(‘/(\w+::\w+|\w+)\((.*)\)/i’, $val, $matches) && is_callable($matches[1])) $matches not exist!!
    Can u help me?

    Reply

  • Anupal Sharma

    Everything seems good. But how to add a column at first or any in between position?
    I tried to put add_column in first but didn’t seems working. It display the column in last.

    $this->datatables
    ->add_column(‘Check’, get_check_button(‘$1′), ‘student_id’)
    ->select(‘student_id, student_name, student_email, student_mobile’)
    //->unset_column(‘user_id’)

    ->add_column(‘Actions’, get_buttons(‘$1′), ‘student_id’)
    ->from(‘students’);

    Any way to do it?

    Reply

  • Bourne Excellencia

    Does anyone know how to Add multi column sortable to Ignited Datatable?

    Reply

  • Bourne Excellencia

    Hello Ahmed,
    How can one add a doubleclick event to a datatable row, Say for instance, you want to go to a Customer Profile page?

    Reply

  • Numan

    What if i want to run php explode function on some column value e.g if avatar contains avatar.jpg value then i want to replace it with avatar_100_100.jpg

    Reply

  • iqbal ardiansyah

    i have this script in datatable_helper.php but the if condition work in my view i think because idx == $1 not $idx ==1
    this is datatable_helper.php
    function get_status($idx)
    {
    if($idx == 1){
    return $a =’STATUSNYA = ‘.$idx.’ ‘;
    }else{
    return $a =’STATUSNYA = ‘.$idx.’‘;
    }
    }

    controller script

    public function get_productss($id)
    {
    $this->datatables->select(‘p.tiny_image,p.id_product,p.title,p.price,c.name,p.status’)
    ->where( array(
    ‘p.price >’ => ’0′,
    ‘p.pckg_length !=’ => ’0′,
    ‘p.pckg_width !=’ => ’0′,
    ‘p.pckg_height !=’ => ’0′,
    ‘p.pckg_weight !=’ => ’0′,
    ‘p.status =’ => ’1′
    )
    )
    ->unset_column(‘p.id_product’)
    ->unset_column(‘p.status’)
    ->unset_column(‘p.tiny_image’)
    ->add_column(‘publish’, get_status(‘$1′),’p.status’)
    ->add_column(‘p.tiny_image’, ”,’p.tiny_image’)
    ->add_column(‘Actions’, get_buttons(‘$1′),’p.id_product’)
    ->from(‘hg_products as p’);
    if($id == 1){
    $this->datatables->where(‘p.id_store = ‘, 1);
    }else{
    $this->datatables->where(‘p.id_store != ‘, 1);
    }

    $this->datatables->join(‘hg_store as c’, ‘p.id_store = c.id_store’ , ‘inner’);
    echo $this->datatables->generate();
    }

    Reply

    • Aldo

      I have the sampe problem!!! how can i fix?

      Reply

    • Aldo

      try this:

      private function exec_replace($custom_val, $row_data)
      {
      $replace_string = '';
      $args = array();
      if(isset($custom_val['replacement']) && is_array($custom_val['replacement']))
      {
      foreach($custom_val['replacement'] as $key => $val)
      {
      $sval = preg_replace("/(?columns))
      $replace_string = $row_data[($this->check_mDataprop())? $sval : array_search($sval, $this->columns)];
      else
      $replace_string = $sval;

      $args[] = $replace_string;
      $custom_val['content'] = str_ireplace('$' . ($key + 1), $replace_string, $custom_val['content']);
      }
      }

      $func = preg_replace('/\([^\)]+\)\s?/', '', $custom_val['content']);
      if (is_callable($func))
      $custom_val['content'] = call_user_func_array($func,$args);

      return $custom_val['content'];
      }

      Reply

    • Farveen Hassan

      How did you solved That Problem of using if Condition inside The Datatable helper ?

      My code sample is this :- please have a look

      function get_budget($req_type,$req_land_outright_price,$req_total_lease_rent_amount)
      {
      $ci= & get_instance();

      if($req_type ==’Sell’){
      return “yes”;
      }else {
      return “wrong”;
      }
      }

      Here In above the if condition is not working
      Thank You in Advance

      Reply

  • Bourne Excellencia

    Hello Ahmed,
    This Tutorial is really nice, I have been able to Implement Datatable into my CodeIgniter, Including using Join Tables, However, It is not possible for me to place two tables on one page view as I have to load it from the controller, and I believe the the set_heading is conflicting, I also tried $this->table->clear(); on the view as well, but doesn’t work. What do you suggest please. Thanks

    Reply

    • Ahmed Samy Post author

      Thanks Borune for your comment, You will have to generate the table before sending it to the view,
      $this->table->set_template($tmpl);
      $this->table->set_heading(‘First Name’, ‘Last Name’, ‘Email’);
      $table1=$this->table->generate();

      $this->table->set_template($tmpl);
      $this->table->set_heading(‘name2′, ‘email2′,’anything else’);
      $table2=$this->table->generate();

      and in the view ,
      you will just < ?php echo $table1; ?> and < ?php echo $table2; ?>

      and note that you will need to initiate datatable JQuery plugin on second table in separate initialization and you need another “datatable” action in your controller to process the server side for table2

      Reply

      • Bourne Excellencia

        Thanks for your valuable response, You are such a blessing . . ., It is so helpful to me, I will work on that and see how it goes, my other question is how to do a dbclick on a row to take you to another page say customer profile page by ID in the role?

        Reply

      • Bourne Excellencia

        I just find my way around it by using links, which solves the problem for me.
        Thanks for the great tutorial.

        Reply

  • abdallah

    Action column not showing up :(
    $this->datatables->select(‘staff_id,firstname,lastname,email’)
    ->unset_column(‘staff_id’)
    ->add_column(‘Actions’, ‘test’)
    ->from(‘staff’);

    Reply

  • joyce

    hello good day, i just wanna know how can a change the table subscriber to another table name..cause when i change it, it does not display anything.please help me.thank you so much

    Reply

  • Rogert

    Hi everybody, how can I add the “edit column ” before the data obtained of Database?, that is to say, the “edit column” be the first.

    Reply

  • abdul

    Hi, Ahmed how to insert static counter , example Column No?thx

    Reply

  • pelukis

    How to insert function like this in datatables column?

    function getparent($id)
    {

    $sql = “select * from admin_menu where parentid=$id”;
    $query = mysql_query($sql);
    $r = mysql_fetch_array($query);
    $result = $r['menu_name'];

    return $result;

    }

    It is not working

    $this->datatables->add_column(‘Parent’,getparent(‘$1′),’parentid’);

    Reply

  • dealer

    hi ahmed,

    the code worked just fine after i added the $this->load->helper(‘datatables_helper’); command..
    is it because the version of CI i am using?

    anyway thanks alot..i enjoyed ur tutorials

    Reply

    • dealer

      i guess u have to add index.php in order for the buttons to be enabled in the datatables_helper.php

      for example:
      $html .=’‘;

      instead of ur code :
      $html .=’‘;

      thanks

      Reply

  • levent çelik

    hi ı would like to add where like that ->where(‘parent_id’,'”) and that’s not work how can control if it’s null parent_id it has to get
    thank u for your time

    Reply

  • Rogert

    Hi, I have a problem working with it, when DataTable shows the list, it shows an additional column with numbers than start in 1 and end in the number of records, Someone knows how can I hide this column?

    Reply

  • Kuro!

    I think the database insert code miss avatar field.

    and I think it will be helpfull if you continue this series with example on add and delete with jquery

    Reply

  • Kamil Kemal

    Hi Ahmed, thanks for good datatablesgrid. There is a problem. I want to send page name with id. Like subscriber. how can i do ?

    Reply

    • Ahmed Samy Post author

      Hello Kamil,
      you can do this by adding second parameter for get_buttons($id,$page) function
      and change

      ->add_column(‘Actions’, get_buttons(‘$1′),’id’)

      to

      ->add_column(‘Actions’, get_buttons(‘$1′,’$2′),’id,page’)

      and then you can easily add 2nd parameter in your url

      Reply

  • Vipul Sharma

    Can I use joins or not, i couldn’t find any documentation,
    $this->load->library(‘Datatables’);
    $this->datatables->from(‘events’);
    $this->datatables->select(“id,event_name”);
    echo $this->datatables->generate();

    Reply

    • Ahmed Samy Post author

      yes Vipul, you can use join as

      $this->datatables->join($table,$fk,$type)

      refer to application/libraries/datatables.php

      you can find almost all of active records functions of codeigniter

      Reply

  • pelukis

    Could you please tell me what it’s mean

    Thanks

    Reply

  • Dj

    Hi, thanks a lot for your great tutorial! I wonder if it is also possible to delete a row in the table without going to a delete page? (delete a row and the table gets refreshed instantly).

    Best regards,

    Dj

    Reply

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current day month ye@r *