Ignited DataTables

#61 / Feb 22, 2011 11:33pm

cryogenix
90 posts

because the datatables javascript fetches the data via ajax.
#62 / Feb 25, 2011 10:23pm

nomie7
17 posts

how can I use tabletools extra with your library?
#63 / Feb 26, 2011 7:04pm

petroz
13 posts

I wrote another library for the Datatables.net plugin. This one supports joins. You can check it here.

http://datatables.dyndns-web.com/
#64 / Mar 02, 2011 8:00am

Pegasus275
15 posts

How to use it with action column for example edit/delete etc.

<tr>
<td> id from db</td>
<td> name from db </td>
<td> Edit
</tr>
#65 / Mar 02, 2011 3:48pm

petroz
13 posts

I use a callback on the Datatables.net configuration to generate the table on the fly. Add `fnDrawCallback` to attach any javascript function/method to the generation process.

Here is one for adding columns.

Datatables.prototype.addDataColumn = function()
{
$("#datatable tr:gt(0)").append("<td>View Edit Delete</td>”);
}
#66 / Mar 02, 2011 11:25pm

HiTek
8 posts

How to use it with action column for example edit/delete etc.

I expanded cryogenix’s datatable class and add additional columns here.
#67 / Mar 02, 2011 11:28pm

petroz
13 posts

Do you have a link?
#68 / Mar 02, 2011 11:35pm

HiTek
8 posts

Do you have a link?

Here is link to my Blog. I put here some description and improved class as well (find the download link at the end of the post). But… this class is for PostgreSQL.

Hmmm… I think I have such class for MySQL too.
#69 / Mar 08, 2011 2:39am
cryogenix
90 posts
How to use it with action column for example edit/delete etc.

<tr>
<td> id from db</td>
<td> name from db </td>
<td> Edit
</tr>

you can use petroz’s client side solution to create additional columns:

I use a callback on the Datatables.net configuration to generate the table on the fly. Add `fnDrawCallback` to attach any javascript function/method to the generation process.

Here is one for adding columns.

Datatables.prototype.addDataColumn = function()
{
$("#datatable tr:gt(0)").append("<td>View Edit Delete</td>”);
}

or you can also modify the library where I commented:
```
/*
  add additional columns here
  like adding a Delete Row control for example:

  $aaData[$row_key][] = '<a href="#">Delete Button</a>';
*/
```
#70 / Mar 15, 2011 5:26pm
dobbler
28 posts
Hi, thanks for everyone’s work on this but I just can’t get it working and it’s driving me insane!

I just get “Loading…” on the table in the view but when I hit the listener I get this:
```
A PHP Error was encountered

Severity: Notice

Message: Undefined variable: sOrder

Filename: libraries/Datatables.php

Line Number: 143


A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$sOrder $sLimit' at line 4

SELECT SQL_CALC_FOUND_ROWS EmailId, Subject, Added FROM $table $sWhere $sOrder $sLimit
```
It’s weird, from the error it looks like the variables aren’t being replaced. I’ve tried everything but can’t seem to make any sense of it. I’m sure I’m missing something (like a decent brain..)

I can post any code you need but I really just copied the samples and altered the table & row variables.

CI version: 1.6.3 - (could be this??? - it’s a huge critical app so have been reluctant to upgrade but think I may have to. Going to try it on 1.7.3 and see if it works now that I think of it..)

Any help would be greatly appreciated as I’m under pressure!

Thanks,

Rob.
#71 / Mar 15, 2011 5:40pm

dobbler
28 posts

Just tried it on 1.7.3 and get the same error.. Argh!
#72 / Mar 15, 2011 9:57pm

cryogenix
90 posts

your javascript code for your datatables may be at fault here because the php script is reporting that it is not getting the necessary parameters like sOrder in order for it to function properly. try building your datatables using native php 1st until you make it work. it would be easy to port it to CI by then.

#73 / Mar 26, 2011 7:57am

ηυмвєяσηє

109 posts

i got the same problem with dobbler

and fixed it.. replace functions below

from:

protected function get_display_data($table, $columns, $sWhere, $sOrder, $sLimit)
    {
      return $this->ci->db->query
      ('
        SELECT SQL_CALC_FOUND_ROWS ' . str_replace(" , ", " ", implode(", ", $columns)) . '
        FROM $table
        $sWhere
        $sOrder
        $sLimit
      ');
    }

To:

protected function get_display_data($table, $columns, $sWhere, $sOrder, $sLimit)
    {
      return $this->ci->db->query
      ('
        SELECT SQL_CALC_FOUND_ROWS ' . str_replace(" , ", " ", implode(", ", $columns)) . '
        FROM '.$table.'
        '.$sWhere.'
        '.$sOrder.'
        '.$sLimit.'
      ');
    }

from:

protected function get_total_data_set_length($table, $index, $sWhere)
    {
      return $this->ci->db->query
      ('
        SELECT COUNT(' . $index . ')
        FROM $table
        $sWhere
      ');
    }

to:

protected function get_total_data_set_length($table, $index, $sWhere)
    {
      return $this->ci->db->query
      ('
        SELECT COUNT(' . $index . ')
        FROM '.$table.'
        '.$sWhere.'
      ');
    }

and line 84 :
from

$iTotal = $aResultTotal[0]['COUNT($index)'];

to:

$iTotal = $aResultTotal[0]['COUNT('.$index.')'];

those are things that ive changed and it worked for me..

#74 / Mar 26, 2011 10:00am

kunal doake

3 posts

Hi,
Thanks for posting this artical.
I m new to CI.

I copied and pasted the datatable.php file in application/library folder.

I am not able to see the data in datatable. i can see only column headers, a search box, loading… and navigation button ie (prev, next first, last) displayed. same as below post by “ruizpi”... There r only 4 records and not any Null value in my table.

I checked with firebug and it displays 404 error. file not found on server.
when i check with var_dump page shows the json data

<?php
    var_dump($result);
?>

output =

string(265) "{"sEcho":0,"iTotalRecords":"4","iTotalDisplayRecords":"4","aaData":[["1","kunal","b33a46f5ee81f6f0790f3ea9f02468e1"],["2","datta","3f7aaf2ae8456b2256cfe6825597c3ec"],["3","rajan","d66c264e1dbd73e6111d3ffc70908e8e"],["7","arati","13dd520baf6553c263452508a9d04ad8"]]}"

my controller is as follows - “mast_crud.php”

<?php
  class Mast_crud extends CI_Controller{
    var $base;
    var $css;
    var $js;
    var $images;
    
      function __consturct()      {
        parent::CI_Controller();
        
                  
      }
      function index(){
          echo('crud');
      }
      
      function party_master(){
        
        $this->base = $this->config->item('base_url');
        $this->css = $this->config->item('css');
        $this->js = $this->config->item('js');
        
        $data['base'] = $this->base;
        $data['css'] = $this->css;
        $data['js'] = $this->js;
        
        $table = "user_master";
        $columns = array("user_id", "user_name","password");
        $index = "user_id";
        
        $this->load->library('Datatable');
        $data["result"] = $this->datatable->generate($table ,$columns ,$index );
        $this->load->view('crud_party_mast',$data);
      }
      
      function user_master(){
          echo("user master");[removed]nullo();
      }
      
      function port_master(){
          echo("port_master");
      }
      
  }
  
  
?>

my view is as follow - “crud_party_mast.php”

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml2/DTD/xhtml1-strict.dtd">
<html >
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Menu</title>
<meta name="Author" content="Stu Nicholls" />
<base href="<?php echo("$base");?>">
<link rel="stylesheet" type="text/css" href="<?php echo("$base$css");?>/pro_dropdown_3.css" />
<link rel="stylesheet" type="text/css" href="<?php echo("$base$css");?>/demo_table.css" />
<link rel="stylesheet" type="text/css" href="<?php echo("$base$css")?>/Mycss.css ">
[removed][removed]
[removed][removed]
[removed]
$(document).ready(function()
  {
    $('#test').dataTable
    ({
      'bServerSide'    : true,
      'bAutoWidth'     : false,
      'sPaginationType': 'full_numbers',
      'aoColumns'      : 
      [
        null,
        null,
        null
      ],   
      'sAjaxSource'    : '<?php echo($base);?>mast_crud/party_master',
      'fnServerData': function(sSource, aoData, fnCallback)
      {
        $.ajax
        ({
          'dataType': 'json',
          'type'    : 'POST',
          'url'     : sSource,
          'data'    : aoData,
          'success' : fnCallback
        });
      }
    });
    
  });
[removed]
</head>

<body>

<div id="dt_example">
  <table id="test" class="display">
    <thead>
      <tr>
        <th>ID</th>
        <th>Name</th>
        <th>password</th>
      </tr>
    </thead>
    <tbody>
      <tr>
        <td>loading…</td>
      </tr>
    </tbody>
  </table>
</div>
<?php
    var_dump($result);
?>
</body>
</html>

Thanks in advance

#75 / Mar 26, 2011 10:25am

ηυмвєяσηє

109 posts

try this

<?php
  class Mast_crud extends CI_Controller{
    var $base;
    var $css;
    var $js;
    var $images;
    
      function __consturct()      {
        parent::CI_Controller();
        
                  
      }
      function index(){
          echo('crud');
      }
      
      function party_master(){
        
        $this->base = $this->config->item('base_url');
        $this->css = $this->config->item('css');
        $this->js = $this->config->item('js');
        
        $data['base'] = $this->base;
        $data['css'] = $this->css;
        $data['js'] = $this->js;
        
        $table = "user_master";
        $columns = array("user_id", "user_name","password");
        $index = "user_id";
        
        $this->load->library('Datatable');
        $data["result"] = $this->datatable->generate($table ,$columns ,$index );
        //$this->load->view('crud_party_mast',$data);
        echo $data["result"];    
      }
      
      function user_master(){
          echo("user master");[removed]nullo();
      }
      
      function port_master(){
          echo("port_master");
      }
      
  }
  
  
?>

Thread

cryogenix

nomie7

petroz

Pegasus275

petroz

HiTek

petroz

HiTek

cryogenix

dobbler

dobbler

cryogenix

ηυмвєяσηє

kunal doake

ηυмвєяσηє

Username

Password

Thread