Ignited DataTables

#256 / Oct 09, 2011 4:12pm

DiLer
10 posts

Well I don’t want to echo in the controller, too.
What I meant was, why do you save the output of your database call (which gives you your json code) in the variable “result” in your controller (list_all)?
Do you use the controller “list_all” to display the view “ajax”, or is there another controller function for that?

My idea was to use your code the same way I use all the other CodeIgniter stuff.
A model that gets the data from the table and a controller that passes the result through to the view. They both work. I can see the json code in my view, I just can’t get the table to load it.

Do I really need aoColumns and fnServerData?
Both should be optional as far as I understand.
But even if I do use them, it still doesn’t work.

If I understand the dataTables wiki correctly, then sAjaxSource can only link to a url/textfile/etc?! Is this the reason for your idea to echo it out in the controller?

Do you have an example with a complete view and corresponding controller?
If I would have a working example I could understand how it works and work my way to the solution I need.
#257 / Oct 16, 2011 12:53am

cebalzer
40 posts

Hello people, I am trying to implement the DataTables, but without success due to a problem with PostgreSQL, I am using the function, but this bank has no chance, how can I do to change this function?

(RESOLVED)
#258 / Oct 16, 2011 2:22pm

cebalzer
40 posts

Hello people, I need help with the script of the DataTable.
#259 / Oct 17, 2011 9:02pm

cryogenix
90 posts

ignited datatables uses ci’s active record which in actuality uses mysql underneath. so i guess you’ll have to forgo with postgresql if you want to use our library.

also, in case you need help, do provide some more details on your problem.
#260 / Oct 18, 2011 7:26am
cebalzer
40 posts
Actually works with postgres, for testing I’m using the example of post http://ellislab.com/forums/viewthread/160896/P10,
so that adapted for use with postgres, I am not expert in php, but I changed a few lines of
code for use, the problem is when I have more than 10 columns, the sorting does not work.
I also downloaded the library Datatables.php the github works almost perfect if not for the
message I’m receiving in an Alert ();
```
warning DataTables (table id = 'example'):
 Requested unknown parameter '1 'from the date source for row 0
```
still can not solve
it. I hope that I look at a script I am using the post where the file is http://ellislab.com/forums/viewthread/202245/. Ajax and php.
f you need more information tell me what you need, please need urgent help with this.
Thank you for answering the topic.
#261 / Oct 18, 2011 3:39pm
cebalzer
40 posts
This worked almost perfect, I have a problem in the search, could not understand what to do to fix this.
This error:
```
HINT:  No operator matches the given name and argument type(s).
 You might need to add explicit type casts.

 SELECT p.id_pessoa,p.nm_pessoa, p.nm_sobrenome, p.nm_email, nr_cpf
 FROM  pessoa p  
inner join pessoa_fisica on (p.id_pessoa = pessoa_fisica.id_pessoa)
WHERE p.id_pessoa ILIKE ce 
OR p.nm_pessoa 
ILIKE ce 
OR p.nm_sobrenome 
ILIKE ce 
OR p.nm_email 
ILIKE ce 
OR nr_cpf 
ILIKE ce   
ORDER BY p.nm_pessoa asc  
LIMIT 10 OFFSET 0
```
I had to remove the quotes and percentages in order to post ok.

Another issue that is bothering me, when I pass the array of columns without the id, the sorting works fine if I pass the id, the sorting does not work anymore. How do to solve this, anyone have any ideas?

I ask to help me modify the code please.

#262 / Oct 20, 2011 1:47pm

cebalzer

40 posts

[RESOLVED]
Finally managed to adapt the code, but when I search does not show the result of filtered, but the rest works perfect.
I am posting to share the code.
CI 2.0.3 + PostgreSQL 9.0.

<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of datatableModel
 *
 * @author cebalzer
 * @property CI_Active_Record db
 */
class Datatables_model extends CI_Model {
    var $ci;
    var $imported;
    public function __construct() {
        $this->ci = & get_instance();
    }

    public function generate($table, $columns, $index, $joins, $where, $search, $groupby) {
        $sLimit = $this->get_paging();
        $sOrder = $this->get_ordering($columns);
        $sWhere = $this->get_filtering($columns, $where, $search);
        $rResult = $this->get_display_data($table, $columns, $joins, $sWhere, $sOrder, $sLimit, $where);
        $tamanho = $this->total_rows($table);
        $rResultFilterTotal = $this->get_data_set_length($table, $sLimit, $joins);
        $aResultFilterTotal = $rResultFilterTotal->result_array();
        $iFilteredTotal = $tamanho;
        $rResultTotal = $this->get_total_data_set_length($table, $index, $sWhere, $joins, $where, $groupby, $columns);
        $aResultTotal = $rResultTotal->result_array();
        $iTotal = $aResultTotal;
        if ($groupby == "") {
            $aResultTotal = $rResultTotal->result_array();
            $iTotal = $tamanho;
        } else {
            $iTotal = strval($rResultTotal);
        }
        return $this->produce_output($columns, $iTotal, $iFilteredTotal, $rResult);
    }
    //Monta a paginação
    protected function get_paging() {

        if ($this->ci->input->post("iDisplayStart") && $this->ci->input->post("iDisplayLength") !== "-1") {
            $sLimit = "LIMIT " . $this->ci->input->post("iDisplayLength") . " OFFSET " . $this->ci->input->post("iDisplayStart");
        } else {
            $iDisplayLength = $this->ci->input->post("iDisplayLength");
            if (empty($iDisplayLength)) {
                $sLimit = "LIMIT " . " 10 OFFSET  0 ";
            } else {
                $sLimit = "LIMIT " . $iDisplayLength . " OFFSET " . $this->ci->input->post("iDisplayStart");
            }
        }
        return $sLimit;
    }
    //Monta a ordenação
    protected function get_ordering($columns) {
        $sOrder = "";
        if ($this->ci->input->post("iSortCol_0")) {
            $sOrder = "ORDER BY ";
            for ($i = 0; $i < intval($this->ci->input->post("iSortingCols")); $i++)
                $sOrder .= $columns[intval($this->ci->input->post("iSortCol_" . $i))] . " " . $this->ci->input->post("sSortDir_" . $i) . ", ";
            $sOrder = substr_replace($sOrder, "", -2);
        }
        return $sOrder;
    }
    //Ajusta o filtro
    protected function get_filtering($columns) {
        $sWhere = "WHERE 1=1 ";
        if ($this->input->post("sSearch") != "") {
            $sWhere .= " AND (";

            for ($i = 0; $i < count($columns); $i++) {
                $col_name = $columns[$i];
                $col_name = preg_replace("/(\s+AS\s+.+)$/i", "", $col_name);
                $sWhere .= $col_name . "::text ILIKE '%" . $this->input->post("sSearch") . "%' OR ";
            }
            $sWhere = substr_replace($sWhere, "", -3) . ")";
        }
        return $sWhere;
    }
    //Popula a tabela
    protected function get_display_data($table, $columns, $joins, $sWhere, $sOrder, $sLimit) {
        $res = " SELECT " . str_replace(" , ", " ", implode(", ", $columns)) . " FROM  $table  $joins  $sWhere  $sOrder  $sLimit ";
        return $this->ci->db->query($res);
    }
    //Calcula o número de registros da tabela
    protected function total_rows($table) {
        $sql = "select count(*) as qtd from $table ";
        $res = $this->db->query($sql);
        $res = $res->row();
        return $res->qtd;
    }

    protected function get_data_set_length($table) {
        $sql = "select count(*) from $table";
        $res = $this->db->query($sql);
        return $res;
    }

    protected function get_total_data_set_length($table, $index, $sWhere, $joins, $where, $groupby, $columns) {
        if ($groupby == "") {
            $Consulta = "SELECT COUNT(*) FROM $table $joins $sWhere ";
            $DatoSalida = $this->db->query($Consulta);
        } else {
            $ConsultaSql = "SELECT " . implode(", ", $columns) . " FROM $table $joins $sWhere $groupby ";
            $Consulta = $this->db->query($ConsultaSql);
            $DatoSalida = $Consulta->num_rows();
        }
        return $DatoSalida;
    }
    //Prepara os dados para saída
    protected function produce_output($columns, $iTotal, $iFilteredTotal, $rResult) {
        $aaData = array();
        foreach ($rResult->result_array() as $row_key => $row_val) {
            foreach ($row_val as $col_key => $col_val) {
                if ($row_val[$col_key] == "version")
                    $aaData[$row_key][$col_key] = ($aaData[$row_key][$col_key] == 0) ? "-" : $col_val;
                else {
                    switch ($row_val[$col_key]) {
                        default: $aaData[$row_key][] = $col_val;
                            break;
                    }
                }
            }
        }
        $sOutput = array
            (
            "sEcho" => intval($this->ci->input->post("sEcho")),
            "iTotalRecords" => $iTotal,
            "iTotalDisplayRecords" => $iFilteredTotal,
            "aaData" => $aaData
        );
        return json_encode($sOutput);
    }
}

Thanks to friends who helped me strongly to modify the code.

#263 / Oct 24, 2011 7:30am

cebalzer
40 posts

Hello people, I had taken to solve the problems, but was not quite how it happened, actually is not working when I use the ORDER BY, GROUP BY, and also, if anyone has any idea to be able to help will be grateful.

#264 / Oct 31, 2011 12:04pm

ahenriksen

2 posts

I’ve got an issue with the edit_column callback functions not… well… calling back. For example: This code…

function format_date($tdate)
 {
  return mdate("%m/%d/%Y - %h:%i %A", gmt_to_local($tdate, $this->settings['time_zone'], FALSE));
 }
 
 public function listener($id = FALSE) 
 {
  $this->load->library("Datatables");
  
  $this->datatables->select('id, file_name, created_on, file_size, file_type')
      ->from('customers_files')
      ->where('customer_id', $id)
      ->where('deleted', 0)
      ->edit_column('created_on', '$1', 'format_date(created_on)')
      ->add_column('Delete', '<a href="http://%27.base_url.%27files/delete/$1" class="delete-file">'.image('icons/red/16x16/trash.png','',array('alt' => 'Delete', 'title' => 'Delete')).'</a>','id');
  
  echo $this->datatables->generate();
 }

The created_on column simply shows “format_date(created_on)” in the DataTable. I’ve tried all variations of code - ‘format_date(“created_on”)’, “format_date(‘created_on’)” and I get the same result every time. Thanks in advance for any help you can offer.

Regards,
Adam

#265 / Oct 31, 2011 4:36pm

ahenriksen
2 posts

Nevermind. I didn’t RTFM. Have to put the callback functions in a helper file. Doh!
#266 / Nov 03, 2011 11:05pm
SemutCilik
2 posts
hello, I’m confused about why the callback could not be called to :sick:
```
public function transaksiTables() {
        $this->load->library('Datatables');
        $this->datatables
                ->select('kode_member,nama_member,tanggal_pinjam,kode_buku,judul_buku,qty,harga,iskembali,kembali,denda,id')
                ->from('vpeminjaman')
                ->add_column('DT_RowClass', '$1', '[b]transaksiTablesRowClass(iskembali, denda)[/b]');
        $data['result'] = $this->datatables->generate();
        echo $data['result'];

    }

    public function transaksiTablesRowClass($iskembali, $denda) {
        $result = 'gradeC';
        if ($denda > 0) {
            $result = 'gradeA';
        }
        return $result;
    }
```
:coolmad:

{"sEcho":0,"iTotalRecords":3,"iTotalDisplayRecords":3,"aaData":[["00001","Agus Basuki","2011-10-29","0000007","Matematika Diskrit","1.00","2000","N",null,"1500","1","transaksiTablesRowClass(iskembali, denda)"],["00001","Agus Basuki","2011-11-02","000003","Guru Ku","1.00","2000","N",null,"0","2","transaksiTablesRowClass(iskembali, denda)"],["00001","Agus Basuki","2011-10-28","000003","Guru Ku","1.00","2000","Y","2011-11-02","1000","3","transaksiTablesRowClass(iskembali, denda)"]],"sColumns":"kode_member,nama_member,tanggal_pinjam,kode_buku,judul_buku,qty,harga,iskembali,kembali,denda,id,DT_RowClass"}

:ahhh:

or :roll:
```
public function transaksiTables() {
        $this->load->library('Datatables');
        $this->datatables
                ->select('kode_member,nama_member,tanggal_pinjam,kode_buku,judul_buku,qty,harga,iskembali,kembali,denda,id')
                ->from('vpeminjaman')
                ->add_column('DT_RowClass', '[b]transaksiTablesRowClass($1, $2)[/b]', 'iskembali, denda');
        $data['result'] = $this->datatables->generate();
        echo $data['result'];

    }

    public function transaksiTablesRowClass($iskembali, $denda) {
        $result = 'gradeC';
        if ($denda > 0) {
            $result = 'gradeA';
        }
        return $result;
    }
```
:shut:

{"sEcho":0,"iTotalRecords":3,"iTotalDisplayRecords":3,"aaData":[["00001","Agus Basuki","2011-10-29","0000007","Matematika Diskrit","1.00","2000","N",null,"1500","1","transaksiTablesRowClass(N, 1500)"],["00001","Agus Basuki","2011-11-02","000003","Guru Ku","1.00","2000","N",null,"0","2","transaksiTablesRowClass(N, 0)"],["00001","Agus Basuki","2011-10-28","000003","Guru Ku","1.00","2000","Y","2011-11-02","1000","3","transaksiTablesRowClass(Y, 1000)"]],"sColumns":"kode_member,nama_member,tanggal_pinjam,kode_buku,judul_buku,qty,harga,iskembali,kembali,denda,id,DT_RowClass"}

:coolcheese: :coolgrin:

❓
#267 / Nov 04, 2011 5:28am

ηυмвєяσηє
109 posts

keep callback functions in a helper file.

#268 / Nov 07, 2011 12:53am

SemutCilik

2 posts

okay I’ll try it.

but this my problem again??

public function transaksiTables() {
        $this->load->library('Datatables');
        $status = $this->ci->input->post('status');
        $kode_member = $this->ci->input->post('peminjam');

        $this->datatables
                ->select("kode_member,nama_member,tanggal_pinjam,kode_buku,judul_buku,qty,harga,if(iskembali='N','Dipinjam','Kembali')iskembali,kembali,denda,id,
                    if(denda>0 and iskembali='N', 'gradeA' ,if(denda=0 and iskembali='N','gradeB', 'gradeC') ) as RowClass")
                ->from('vpeminjaman')
                ->att_column('DT_RowClass', '$1', 'RowClass');
     //   if ($status == 'peminjaman')
            $this->datatables
               //     ->where('kode_member like', "$kode_member")
                    ->edit_column('id', "<a href="#">./images/template/hr.gif</a>", "id")
                    ->unset_column('nama_member');

        $data['result'] = $this->datatables->generate();
        echo $data['result'];
    }

problem on ->unset_column(‘nama_member’);

A PHP Error was encountered

Severity: Notice

Message: Undefined offset: 11

Filename: libraries/Datatables.php

Line Number: 351

attach for automatic row ID addition
http://datatables.net/release-datatables/examples/server_side/ids.html

#269 / Nov 10, 2011 6:25am
dblu
2 posts
Update again: Several hours later I tried many things and decided to switch DataTables Get to a POST and that fixed it. Hopefully this will help someone.
```
$(document).ready(function() {
    $('#example').dataTable( {
        "bProcessing": true,
 "bServerSide": true,
 "fnServerData": function ( sSource, aoData, fnCallback ) {
 $.ajax( {
  "dataType": 'json',
  "type": "POST",
  "url": sSource,
  "data": aoData,
  "success": fnCallback
  } );
 }, 
 "sAjaxSource": "<?php //echo base_url();?>clients/ClientsSource"
    } );
} );
```
Update: After 6 hours I finally decided to use the script that is on DataTables website here, http://www.datatables.net/release-datatables/examples/data_sources/server_side.html, and then it worked right away. It appears that sEcho is working right on one script and not the other:

{"sEcho":1,"iTotalRecords":"50","iTotalDisplayRecords":"50","aaData":[["1","MyFirst","MyLast","MyOrg","MyAddress","MyCity","MyState","MyZip"],["2","MyFirst","MyLast","MyOrg","MyAddress","MyCity","MyState","MyZip"],["3","MyFirst","MyLast","MyOrg","MyAddress","MyCity","MyState","MyZip"],["4","MyFirst","MyLast","MyOrg","MyAddress","MyCity","MyState","MyZip"],["5","MyFirst","MyLast","MyOrg","MyAddress","MyCity","MyState","MyZip"],["6","MyFirst","MyLast","MyOrg","MyAddress","MyCity","MyState","MyZip"],["7","MyFirst","MyLast","MyOrg","MyAddress","MyCity","MyState","MyZip"],["8","MyFirst","MyLast","MyOrg","MyAddress","MyCity","MyState","MyZip"],["9","MyFirst","MyLast","MyOrg","MyAddress","MyCity","MyState","MyZip"],["10","MyFirst","MyLast","MyOrg","MyAddress","MyCity","MyState","MyZip"]]}

PS. the events did work but nothing happened.. now it does though.

bServerSide does need to be true.

Hi, I have your wrapper in and working now, but none of the events work. The drop down length and the pagination buttons do not work. I saw a guy with a similar is around post 248 or so. i ended up having it call the controller which calls the model and echos the ajax data back. Works I think.

It has 50 entries in the DB and I get this “Showing 1 to 10 of 10 entries”

It should be Showing 1 to 10 of 50 entries I would think.

any thoughts? Is this error on my end, the wrapper, or datatables?

Here is the JSON returned:
```
{"sEcho":0,"iTotalRecords":"50","iTotalDisplayRecords":"50","aaData":[["1","MyFirst","MyLast","MyOrg","MyAddress","MyCity","MyState","MyZip"],["2","MyFirst","MyLast","MyOrg","MyAddress","MyCity","MyState","MyZip"],["3","MyFirst","MyLast","MyOrg","MyAddress","MyCity","MyState","MyZip"],["4","MyFirst","MyLast","MyOrg","MyAddress","MyCity","MyState","MyZip"],["5","MyFirst","MyLast","MyOrg","MyAddress","MyCity","MyState","MyZip"],["6","MyFirst","MyLast","MyOrg","MyAddress","MyCity","MyState","MyZip"],["7","MyFirst","MyLast","MyOrg","MyAddress","MyCity","MyState","MyZip"],["8","MyFirst","MyLast","MyOrg","MyAddress","MyCity","MyState","MyZip"],["9","MyFirst","MyLast","MyOrg","MyAddress","MyCity","MyState","MyZip"],["10","MyFirst","MyLast","MyOrg","MyAddress","MyCity","MyState","MyZip"]],"sColumns":"id,first_name,last_name,organization,address,city,state,zip"}
```
Here is my JS.
```
[removed]

$(document).ready(function() {
    $('#example').dataTable( {
        "bProcessing": true,
  "sAjaxSource": "<?php echo base_url();?>clients/ClientsSource"
    } );
} );

[removed]
```

#270 / Nov 16, 2011 1:15am

zadro

5 posts

Great work everyone!! It took me a couple hours to get through this entire thread 😊

I’m trying to call the ajax source by passing it to the view instead of calling a controller (don’t like using echo in controller), but can’t get it to work.

For example, this works:

$(document).ready(function() {
  lTable = $('#leadsTable').dataTable({
  'bJQueryUI': true,
  'sPaginationType': 'full_numbers',
  "bServerSide": true,
  'bProcessing': true,
  'bAutoWidth': false,
  "sAjaxSource": '<?=base_url();?>dt/leads',
        "fnServerData": function(sSource,aoData,fnCallback){
            $.ajax({
                "dataType": 'json',
                "type": "POST",
                "url": sSource,
                "data": aoData,
                "success": fnCallback
            });
        },
  'aoColumns': [{'bVisible':false},null,null,null,null,null,null,null,null,null,null],
  'aaSorting': [[1,'desc']]
 });
});

This doesn’t:

$(document).ready(function() {
  lTable = $('#leadsTable').dataTable({
  'bJQueryUI': true,
  'sPaginationType': 'full_numbers',
  "bServerSide": true,
  'bProcessing': true,
  'bAutoWidth': false,
  "sAjaxSource": '<?=$result;?>',
        "fnServerData": function(sSource,aoData,fnCallback){
            $.ajax({
                "dataType": 'json',
                "type": "POST",
                "url": sSource,
                "data": aoData,
                "success": fnCallback
            });
        },
  'aoColumns': [{'bVisible':false},null,null,null,null,null,null,null,null,null,null],
  'aaSorting': [[1,'desc']]
 });
});

working controller:

public function leads()
 {
  if (!$this->ion_auth->logged_in())
  {
   redirect('auth/login');
  }
  else
  {
   $this->load->model('leads_model');
   echo $this->leads_model->getLeads();
  }
 }

Doen’t work:

public function leads()
 {
  if (!$this->ion_auth->logged_in())
  {
   redirect('auth/login');
  }
  else
  {
   $this->load->model('leads_model');
   $data['result'] = $this->leads_model->getLeads();
   $this->load->view('admin/leads',$data);
  }
 }

Model for reference:

function getLeads()
 {
  $this->load->library("Datatables");
  $this->datatables
    ->select('leads.id,leaddate,leadstatus,leadtype,fullname,coname,cophone,coemail,runvol,firstvol,startdate')
    ->join('lead_status','leads.lead_status_id=lead_status.id')
    ->join('lead_type','leads.lead_type_id=lead_type.id')
    ->from('leads');
  return $this->datatables->generate();
 }

The $result variable and the controller produce the same JSON except for the first value “sEcho” as seen in the screen shot attached. Below the table is the ‘echo $results’ while the table is using the working example above. Any ideas???

I think if anyone has a full MVC example, that would be really helpful. Thanks!

Thread

DiLer

cebalzer

cebalzer

cryogenix

cebalzer

cebalzer

cebalzer

cebalzer

ahenriksen

ahenriksen

SemutCilik

ηυмвєяσηє

SemutCilik

dblu

zadro

Username

Password

Thread