EllisLab text mark
Advanced Search
22 of 30
22
   
Ignited DataTables
Posted: 05 March 2012 01:50 AM   [ # 321 ]   [ Rating: 0 ]
Avatar
Joined: 2010-07-15
91 posts

try the wiki if that will shed some light for you: https://github.com/IgnitedDatatables/Ignited-Datatables/wiki/Method-Chaining

i will try to give a full blown example as soon as i get some time off… really really busy right now so i’m kind of sorry for the inactivity…

 Signature 

Ignited Datatables

 
Posted: 05 March 2012 08:37 AM   [ # 322 ]   [ Rating: 0 ]
Joined: 2012-03-01
2 posts

hi! thnx for the reply! i’m working on it!
i thing it works nearly well because it shows the number of result properly but not show any result only a messagebox saying:  DataTables warning(table id=‘test’): Requested unknown parameter ‘0’ from the data source for row 0.

the controller:

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class 
Home extends CI_Controller {
 
public function __construct()
 
{
  parent
::__construct();
  
$this->load->model'mUsers' );
 
}
 
public function index()
 
{
  $this
->load->view'home' );
 
}
 
public function read()
 
{
  $this
->load->library('Datatables'); 
  
$this->datatables
  
->select('id, name,email')
   ->
from('users');
  echo 
$this->datatables->generate();
  
 
}


and the view:

<html>
 <
head>
  <
title>
   
Test
  
</title>
  <
base href="<?php echo base_url(); ?>" />
  <
link type="text/css" rel="stylesheet" href="css/smoothness/jquery-ui-1.8.2.custom.css" />
  <
link type="text/css" rel="stylesheet" href="css/styles.css" />
  
[removed][removed]
  [removed][removed]
</head>
 <
body>
  <
div id="dt_example">
  <
table id="test" class="display">
    <
thead>
      <
tr>
        <
th>ID</th>
        <
th>Name</th>
        <
th>mail</th>
      </
tr>
    </
thead>
    <
tbody>
      <
tr>
        <
td colspan="3" class="dataTables_empty">Loading...</td>
      </
tr>
    </
tbody>
  </
table>
</
div>
[removed]
  
var sity;

  $(
document).ready(function()
  
{
    sity 
= $('#test').dataTable
    
({
      
'iCookieDuration'60,
      
'bStateSave'     true,
      
'bServerSide'    true,
      
'bAutoWidth'     false,
      
'sPaginationType''full_numbers',
      
'sAjaxSource'    '<?php echo base_url();?>index.php/home/read',
      
'aoColumns'      
      
[
        
        { 
'sName' 'id' },
         
'sName' 'name' },
     
'sName' 'email' }
      ]
,
      
'fnServerData': function(sSourceaoDatafnCallback)
      
{
        
$.ajax
        
({
          
'dataType''json',
          
'type'    'POST',
          
'url'     sSource,
          
'data'    aoData,
          
'success' fnCallback
        }
);
      
}
    }
);
  
});
[removed]
   
 
</body>
</
html

 

thanks for the help!

EDIT: that works!
nothing to solve!

 
Posted: 05 March 2012 10:36 PM   [ # 323 ]   [ Rating: 0 ]
Avatar
Joined: 2010-07-15
91 posts

i’m glad to be of help then…. =)

 Signature 

Ignited Datatables

 
Posted: 13 April 2012 05:12 AM   [ # 324 ]   [ Rating: 0 ]
Joined: 2012-03-05
4 posts

Hello everybody,
I have a little bug in filtering my datatable using your library.
This is my query to feed the datatable :

$this->datatables->select('NOM_FICHIER AS ID_NOM_FICHIER,NUM_DOSSIER,LABO_INTERNE,NOM,PRENOM,DATE_RECEPTION,DATE_EXPEDITION,DATE_SUPPRESSION,USER_SUPPRESSION,CODE_DESTINATAIRE,NOM_DESTINATAIRE,NOM_FICHIER,TYPE_DOC,STATUT,AUDIT,DATE_RECEPTION_AUDIT')->from($groupement.'_impression')->where('STATUT=\'Supprimé\' OR STATUT=\'Envoyé\'',NULL,FALSE); 

Everything is ok for feeding the datatable, but when I want to search withing the results, for exemple “Envoyé” (which is supposed to return only the rows with STATUT at “Envoyé”), it returns the rows “Supprimé” too.
And whatever I type in the search field, it returns always the rows with “Supprimé”.
If I remove ‘STATUT=\‘Supprimé\’ from my query everything is fine.
I don’t understand where the problem is, I’ve made several datatables, but it’s the only one which is buggy.

Thanks in advance !

 
Posted: 13 April 2012 02:58 PM   [ # 325 ]   [ Rating: 0 ]
Avatar
Joined: 2011-03-26
111 posts

try:

->where('( STATUT=\'Supprimé\' OR STATUT=\'Envoyé\' )',NULL,FALSE);  // added parenthesis there.. 

insted of

->where('STATUT=\'Supprimé\' OR STATUT=\'Envoyé\'',NULL,FALSE); 

Regards,
Yusuf

 Signature 

Ignited Datatables
Nested Sets

 
Posted: 13 April 2012 03:30 PM   [ # 326 ]   [ Rating: 0 ]
Joined: 2012-03-05
4 posts
ηυмвєяσηє - 13 April 2012 02:58 PM

try:

->where('( STATUT=\'Supprimé\' OR STATUT=\'Envoyé\' )',NULL,FALSE);  // added parenthesis there.. 

insted of

->where('STATUT=\'Supprimé\' OR STATUT=\'Envoyé\'',NULL,FALSE); 

Regards,
Yusuf

It works ! Many many thanks Yusuf, you made my day ! :DDD

 
Posted: 07 May 2012 09:02 PM   [ # 327 ]   [ Rating: 0 ]
Avatar
Joined: 2009-03-24
111 posts

I was trying to use the column filtering with Datatables and date ranges. (See http://jquery-datatables-column-filter.googlecode.com/svn/trunk/index.html)

I made some changes to the get_filtering() method to allow it to split the date into two parts and convert that to a valid ‘BETWEEN’ clause.

It works for me so far, but it may require some cleaning up.

I also changed it to use $this->ci->db->like instead of where.

/**
    * Generates the LIKE portion of the query
    *
    * @return mixed
    */
    
protected function get_filtering()
    
{
      
if ($this->check_mDataprop())
        
$mColArray $this->get_mDataprop();
      elseif (
$this->ci->input->post('sColumns'))
        
$mColArray explode(','$this->ci->input->post('sColumns'));
      else
        
$mColArray $this->columns;

      
$sWhere '';
      
$sSearch $this->ci->db->escape_like_str($this->ci->input->post('sSearch'));//$this->ci->input->post('sSearch');
      //$sSearch = mysql_real_escape_string($this->ci->input->post('sSearch'));
      
      
$mColArray array_values(array_diff($mColArray$this->unset_columns));
      
$columns array_values(array_diff($this->columns$this->unset_columns));

      if(
$sSearch != '')
        for(
$i 0$i count($mColArray); $i++)
          if(
$this->ci->input->post('bSearchable_' $i) == 'true' && in_array($mColArray[$i]$columns))
            
$sWhere .= $this->select[$mColArray[$i]] " LIKE '%" $sSearch "%' OR ";

      
$sWhere substr_replace($sWhere'', -3);
      

      if(
$sWhere != '')
        
$this->ci->db->where('(' $sWhere ')');

      for(
$i 0$i intval($this->ci->input->post('iColumns')); $i++)
      
{
        
if(isset($_POST['sSearch_' $i]) && $this->ci->input->post('sSearch_' $i) != '' && in_array($mColArray[$i]$columns))
        
{
          $miSearch 
explode(','$this->ci->input->post('sSearch_' $i));
          foreach(
$miSearch as $val)
          
{
            
if(preg_match("/(<=|>=|=|<|>)(\s*)(.+)/i"trim($val), $matches)) {
             $this
->ci->db->where($this->select[$mColArray[$i]].' '.$matches[1]$matches[3]);
            
else {
             
if($this->ci->input->post('sRangeSeparator') != ''{
              $pos 
strpos($val,$this->ci->input->post('sRangeSeparator'));
              if(
$pos === false{
               $this
->ci->db->like($this->select[$mColArray[$i]]$val);
              
else {
               $exp 
explode($this->ci->input->post('sRangeSeparator'),$val);
               if(
strlen($exp[0]) > && strlen($exp[1]) > 0{
                $this
->ci->db->where($this->select[$mColArray[$i]].' BETWEEN '.$this->ci->db->escape($exp[0].' 00:00:00').' AND '.$this->ci->db->escape($exp[1].' 23:59:59'));
               
}
              }
             } 
else {
              $this
->ci->db->like($this->select[$mColArray[$i]]$val);
             
}
            }
          }
        }
      }

      
foreach($this->filter as $val{
        $this
->ci->db->where($val[0]$val[1]$val[2]);
      
}
    } 

UPDATED: Added beginning time at 0:00:00, and ending time 23:59:59.

 Signature 

WIP - WuGen Form Library

 
Posted: 08 May 2012 01:11 PM   [ # 328 ]   [ Rating: 0 ]
Avatar
Joined: 2011-03-26
111 posts

hi Kyle,

I made some examples about multifiltering and column filtering for native php version of the library but it is very similar to this library.
you may wanna check these..

some online examples : (has no dependency on Codeigniter)
http://numberone.kodingen.com/datatables/examples/basic_init/
http://numberone.kodingen.com/datatables/examples/range_filtering/ //range filtering with where
http://numberone.kodingen.com/datatables/examples/range_filtering2/ //range filtering with filter
http://numberone.kodingen.com/datatables/examples/range_filtering_date/
http://numberone.kodingen.com/datatables/examples/multi_filtering/
http://numberone.kodingen.com/datatables/examples/range_filtering3


original post : http://datatables.net/forums/discussion/comment/22964#Comment_22964

Regards,
Yusuf

 Signature 

Ignited Datatables
Nested Sets

 
Posted: 30 May 2012 02:22 PM   [ # 329 ]   [ Rating: 0 ]
Joined: 2011-05-03
11 posts

Hey,
I just want a really simple basic working example. But for some reason it doesn’t work.

The jQuery script:

$(document).ready(function() {
  
$('#example').dataTable{
  
"bProcessing"true,
  
"bServerSide"true,
  
"sAjaxSource""<?php echo base_url(); ?>home/getdatabyajax"
  
);
); 

The controller that outputs the json code

public function getdatabyajax()
 
{
  $this
->load->library('Datatables');
  
$this->datatables
   
->select('id,username,create_date')
   ->
from('accounts');
  echo 
$this->datatables->generate();
 

If you open the controller (localhost/mysite/home/getdatabyajax) you get this:

{"sEcho":0,"iTotalRecords":14,"iTotalDisplayRecords":14,"aaData":[["1","test","1337976133"],["2","test2","1337976451"],["3","test3","1337976751"],["4","test4","1337976776"],["6","test5","1338072108"],["7","test6","1338152271"],["8","test7","1338400806"],["9","test8","1338400822"],["10","test9","1338400830"],["11","test10","1338400837"],["12","test11","1338400843"],["13","test12","1338400850"],["14","test13","1338400857"],["15","test14","1338400867"]],"sColumns":"id,username,create_date"

(Just some test database)

If you open the actual page you get the table, but it only shows “Processing…”.
Firebug shows me that the json code was loaded. Here is what I’m wondering: why is sEcho 0. Shouldn’t that be 1? If I manually change it to 1 the data is shown inside the table. If I understand it correctly, the value should get updatet everytime I update the table. Basically a draw count. So what is going wrong?
Another thing is: shouldn’t I only get the first 10 accounts, not all 14?

I’m working on this for far too long. Quite frustated right now :/
If anyone can help, I would appreciate it.
I pretty much followed this tutorial Ignited Datatables Tutorial on Vimeo

Thanks.

 
Posted: 30 May 2012 04:25 PM   [ # 330 ]   [ Rating: 0 ]
Joined: 2012-05-30
1 posts

DiLer - I’m having the same exact problem. When I directly access my controller that produces the json I see the correct data output. I was also able to get it to work when I set bServerSide to false.

EDIT 2: I made two changes to get this to work.

First, set add “sServerMethod”: “POST” so your script looks something like:

$(document).ready(function() {
 
$('#test_table').dataTable{
  
"bProcessing"true,
  
"bServerSide"true,
  
"sServerMethod""POST",
  
"sAjaxSource""/index.php/tables/listener"
 
);
); 

Second, change Line 295 of the datatables library

From:

if($sSearch != ''

To:

if($sSearch != && $sSearch != ''
 
Posted: 31 May 2012 07:28 AM   [ # 331 ]   [ Rating: 0 ]
Joined: 2012-05-31
8 posts

I want to add a column by using add_column() function only under certain circumstances.

For example in a table with data, only if a user is the creator of the table’s row data to be able to edit it by showing a column like “Edit”, otherwise the column will be hidden or display nothing.


For now i am using this

$this->datatables->select(SELECT VALUES);
            
$this->datatables->from(TABLE);
            
$this->datatables->where(CONDITION);
            
$this->datatables->add_column('edit''<a class="edit" href="$1">Edit</a>''id');
            return 
$this->datatables->generate(); 

And i would like the add_column to be added and shown only on certain rows based on a check that i will be doing if the user is the creator.

Is it possible somehow or do i have to edit the core of the datatables library?

 
Posted: 31 May 2012 08:49 AM   [ # 332 ]   [ Rating: 0 ]
Joined: 2012-05-31
8 posts
Sport_Billy - 31 May 2012 07:28 AM

I want to add a column by using add_column() function only under certain circumstances.

For example in a table with data, only if a user is the creator of the table’s row data to be able to edit it by showing a column like “Edit”, otherwise the column will be hidden or display nothing.


For now i am using this

$this->datatables->select(SELECT VALUES);
            
$this->datatables->from(TABLE);
            
$this->datatables->where(CONDITION);
            
$this->datatables->add_column('edit''<a class="edit" href="$1">Edit</a>''id');
            return 
$this->datatables->generate(); 

And i would like the add_column to be added and shown only on certain rows based on a check that i will be doing if the user is the creator.

Is it possible somehow or do i have to edit the core of the datatables library?

OK i found a way, even if in my opinion is not the best one.

I am taking the output of the above function,
decoding using json_decode,
checking through other function for each desired json object ‘id’ if current user is its creator and if true then changing its ‘edit’ column value to the desired output,
then re-json_encode() and display the output.

There should be an easier way though.

 
Posted: 31 May 2012 02:39 PM   [ # 333 ]   [ Rating: 0 ]
Joined: 2011-05-03
11 posts
laxdoctor - 30 May 2012 04:25 PM

DiLer - I’m having the same exact problem. When I directly access my controller that produces the json I see the correct data output. I was also able to get it to work when I set bServerSide to false.

EDIT 2: I made two changes to get this to work.

First, set add “sServerMethod”: “POST” so your script looks something like:

$(document).ready(function() {
 
$('#test_table').dataTable{
  
"bProcessing"true,
  
"bServerSide"true,
  
"sServerMethod""POST",
  
"sAjaxSource""/index.php/tables/listener"
 
);
); 

Second, change Line 295 of the datatables library

From:

if($sSearch != ''

To:

if($sSearch != && $sSearch != ''

Oh man, thanks a lot!
At first it didn’t work, but I could figure out why. I enabled csrf protection in the config file. There is an issue with ajax and this option when set to TRUE.

$config['csrf_protection'TRUE

The moment I disabled it, it worked. BUT I don’t want to disable it. So I googled for an hour or so and here is what I found:

http://unruhdesigns.com/blog/2012/03/a-simple-solution-to-codeigniter-csrf-protection-and-ajax
This pretty much explains the problem. I did not test the solution posted there, but it should work, too.

My solution was something I found here:
http://ellislab.com/forums/viewthread/214772/

data<?php echo $this->security->get_csrf_token_name(); ?>=<?php echo $this->security->get_csrf_hash(); ?> 

This combined with some other stuff I found gave me this: (and it works!)

$(document).ready(function() {
    
$('#example').dataTable{
      
"bProcessing"true,
      
"bServerSide"true,
      
"sAjaxSource""<?php echo base_url(); ?>home/getdatabyajax",
      
"fnServerData": function(sSourceaoDatafnCallback){
      aoData
.push"name""<?php echo $this->security->get_csrf_token_name(); ?>""value""<?php echo $this->security->get_csrf_hash() ?>" );
      $.
ajax({
 
'dataType''json',
 
'type''POST',
 
'url'sSource,
 
'data'aoData,
 
'success'fnCallback
      }
);
    
}  
  } 
);
); 

This works even with crsf_protection enabled.

So in the end I just changed the code as showed above and modified the datatables library as you mentionend.
I guess “sServerMethod”: “POST” and ajax(‘type’: ‘POST’) is pretty much the same?! That’s why I didn’t need it?!

Thanks again laxdoctor, you made my day (or year)

/edit
Ok the search does not work :/
Probably because of the modification of the datatables library…does anyone have a solution?

/edit2
Oh man I hope this is the right solution…
I found out that the sql queries are escaped wrong. Take a look at this:

SELECT `id`, `username`, `create_dateFROM (`accounts`) WHERE (id LIKE '%'somestring'%' OR username LIKE '%'somestring'%' OR create_date LIKE '%'somestring'%' ORDER BY `idasc LIMIT 10 

This is what datatables does. But as you can see ‘somestring’ (the word I searched for) shouldn’t have the ‘’ around it. The query should be ...LIKE ‘%somestring%’ .... and not ...LIKE ‘%‘somestring’%’...

With that in mind I changed the datatables library back to normal. So line 295 is

if($sSearch != ''

again.
But I changed line 290 (I tested a lot of stuff, so maybe it’s a line above or under that, I’m not 100% sure).

The line is:

$sSearch $this->ci->db->escape($this->ci->input->post('sSearch')); 

I changed it too

$sSearch $this->ci->input->post('sSearch'); 

Now the string isn’t in quotes anymore and everything works fine. I hope this helps other people out there!

 
Posted: 31 May 2012 03:59 PM   [ # 334 ]   [ Rating: 0 ]
Avatar
Joined: 2011-03-26
111 posts
Sport_Billy - 31 May 2012 08:49 AM
Sport_Billy - 31 May 2012 07:28 AM

I want to add a column by using add_column() function only under certain circumstances.

For example in a table with data, only if a user is the creator of the table’s row data to be able to edit it by showing a column like “Edit”, otherwise the column will be hidden or display nothing.


For now i am using this

$this->datatables->select(SELECT VALUES);
            
$this->datatables->from(TABLE);
            
$this->datatables->where(CONDITION);
            
$this->datatables->add_column('edit''<a class="edit" href="$1">Edit</a>''id');
            return 
$this->datatables->generate(); 

And i would like the add_column to be added and shown only on certain rows based on a check that i will be doing if the user is the creator.

Is it possible somehow or do i have to edit the core of the datatables library?

OK i found a way, even if in my opinion is not the best one.

I am taking the output of the above function,
decoding using json_decode,
checking through other function for each desired json object ‘id’ if current user is its creator and if true then changing its ‘edit’ column value to the desired output,
then re-json_encode() and display the output.

There should be an easier way though.

 

/// load helper here ..
            
$this->datatables->select(SELECT VALUES);
            
$this->datatables->from(TABLE);
            
$this->datatables->where(CONDITION);
            
$this->datatables->add_column('edit''$1''check_this(id)');
            return 
$this->datatables->generate(); 

make a helper function like this ..

function check_this($id)
{
  
if $id bla bla..
  return  
'<a class="edit" href="$id">Edit</a>';
  else
  return 
'';
 Signature 

Ignited Datatables
Nested Sets

 
Posted: 01 June 2012 03:21 AM   [ # 335 ]   [ Rating: 0 ]
Joined: 2012-05-31
8 posts

Thank you very much ηυмвєяσηє !!!
The helper did exactly what i wanted grin

 
22 of 30
22