Ignited DataTables

[eluser]DiLer[/eluser]
[quote author="laxdoctor" date="1338409544"]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:

Code:

$(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:

Code:

if($sSearch != '')

To:

Code:

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

[/quote]

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.

Code:

$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-s...n-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/

Code:

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!)

Code:

$(document).ready(function() {
    $('#example').dataTable( {
      "bProcessing": true,
      "bServerSide": true,
      "sAjaxSource": "<?php echo base_url(); ?>home/getdatabyajax",
      "fnServerData": function(sSource, aoData, fnCallback){
      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:

Code:

SELECT `id`, `username`, `create_date` FROM (`accounts`) WHERE (id LIKE '%'somestring'%' OR username LIKE '%'somestring'%' OR create_date LIKE '%'somestring'%' ) ORDER BY `id` asc 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

Code:

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:

Code:

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

I changed it too

Code:

$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!

[eluser]ηυмвєяσηє[/eluser]
[quote author="Sport_Billy" date="1338468565"][quote author="Sport_Billy" date="1338463706"]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

Code:

$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?[/quote]

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.[/quote]

Code:

/// 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 ..

Code:

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

[eluser]Sport_Billy[/eluser]
Thank you very much ηυмвєяσηє !!!
The helper did exactly what i wanted :-)

[eluser]Sport_Billy[/eluser]
I am using an ajax source to load my data by setting "sAjaxSource" parameter and "bServerSide": FALSE.
Data loads fine.

Now i am trying to set the parameter "bServerSide": TRUE but it does not load and just shows "Loading data from server".

What is the problem?
Should i set any other parameters?

In my controller i just ECHO the result of my Model:
Model:

Code:

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

Controller:

Code:

echo $this->MODEL_NAME->FUNCTION();

And here is my view's js code:

Code:

$('#TABLE').dataTable(
{
"bProcessing": false,
"bDestroy": true,
"bServerSide": true,
"bStateSave": true,
"sAjaxSource": CI.base_url + "index.php/CONTROLLER/FUNCTION",
"bDeferRender": true,
"sDom": '<"H"Rlfr>t<"F"ip>',
"aaSorting": [[ 8, "asc" ]],
"aoColumnDefs": [
  { "sClass": "defaultCountdown", "aTargets": [ 11 ] },
  { "sClass": "price", "aTargets": [ 8 ] },
  { "bSortable": false, "aTargets": [ 0 ] },
  { "bVisible": false, "aTargets": [ 12 ] },
  { "mDataProp": "0", "aTargets": [ 1 ] },
  { "mDataProp": "1", "aTargets": [ 2 ] },
  { "mDataProp": "2", "aTargets": [ 3 ] },
  { "mDataProp": "3", "aTargets": [ 4 ] },
  { "mDataProp": "4", "aTargets": [ 5 ] },
  { "mDataProp": "5", "aTargets": [ 6 ] },
  { "mDataProp": "6", "aTargets": [ 7 ] },
  { "mDataProp": "7", "aTargets": [ 8 ] },
  { "mDataProp": "8", "aTargets": [ 9 ] },
  { "mDataProp": "9", "aTargets": [ 10 ] },
  { "mDataProp": "10", "aTargets": [ 11 ] },
  { "mDataProp": "11", "aTargets": [ 0 ] }
]
});

[eluser]virtualgadjo[/eluser]
hi,

actually you should not echo anything in the controller but instead do something like this

Code:

$data['result'] = $this->your_model->your_function();
$this->load->view('raw_ajax', $data);

and then creat a view (raw_ajax.php in my example) with just

Code:

<?php echo $result; ?>

it should work a little better

have swing

[eluser]blagi[/eluser]
I did rewrite one of my datatables view with mDataProp function for almost all columns using "aoColumns". At the end I discovered that sorting a columns doesn't work because Ignited Code is receiving 'function' for mDataProp. Is is possible to redefine that 'function' type by returning something from one of a few type of calls to mDataProp function?

I don't like a solution with "aoColumnDefs" because I had to use "aTargets" with column numbers. mDataProp object approach is much better for my complicated and composed columns.

[eluser]Sport_Billy[/eluser]
[quote author="virtualgadjo" date="1339599065"]hi,

actually you should not echo anything in the controller but instead do something like this

Code:

$data['result'] = $this->your_model->your_function();
$this->load->view('raw_ajax', $data);

and then creat a view (raw_ajax.php in my example) with just

Code:

<?php echo $result; ?>

it should work a little better

have swing[/quote]

Hey thanks a lot for your reply.
I tryied what you suggested but still nothing displays if i set bServerSide: TRUE
If i set it to FALSE , everything works ok.

[eluser]blagi[/eluser]
I converted two "mDataProp" columns from functions to ones as field names. Now Ignited Code makes a query with their names (field1 & field2) as filters:
WHERE (field1 LIKE '%''%' OR field2 LIKE '%''%' )

As there is no filter values the query returns no records instead of returning all records!?

[eluser]m!H!r[/eluser]
echo prints result on the page as follows:
{"sEcho":0,"iTotalRecords":2,"iTotalDisplayRecords":2,"aaData":[["1","23096720","MIHIR","mihir.parekh@abc.com","1"],["2","12345","Mihir","[email protected]","1"]],"sColumns":"id,username,name,email,login_count"}

The table just displays processing...

<b>My controller</b>

Code:

function user_management_edit(){
  
  $this->load->library('Datatables');
  $this->datatables->select('id , username , name , email , login_count');
  $this->datatables->from('user');
  echo $this->datatables->generate();
  //$mytable = $this->datatables->generate();
  //echo ($mytable);
  //$this->load->view('user_management_edit');
  //$data['result'] = $this->datatables->generate();
   $data['main_content'] = 'user_management_edit';
   $this->load->view('Template', $data);
  
  
  }

<b>My View</b>

Code:

<section class="grid_12">
    <div class="box">
      <div class="title"><span class="icon16_sprite i_spreadsheet"></span>Data table</div>
      <p><a href="[removed]void(0);">Click to add a new row</a></p>
      <div class="inside">
<table cellpadding="0" cellspacing="0" border="0" class="display" id="test">
<thead>
  <tr>
   <th width="10%">Id</th>
   <th width="20%">Username</th>
   <th width="25%">Name</th>
   <th width="25%">Email</th>
   <th width="20%">Login Count</th>
  </tr>
</thead>
<tbody>
  <tr>        
   <td colspan="5" class="dataTables_empty">Loading data from server</td>
   </tr>
</tbody>
</table>
   </div>
  
  </div>
</section>

<b>My Template initializing script</b>

Code:

[removed]
  $(document).ready(function() {
  $('#test').dataTable( {
  
   "bProcessing": true,
     "bServerSide": true,
     "sServerMethod": "POST",
     "sPaginationType": "full_numbers",
     "bDeferRender": true,
     "sAjaxSource": "<?php echo base_url(); ?>index.php/home/user_management_edit",
     "aoColumns":
           [
      { 'sName' : 'id' },
      { 'sName' : 'username' },
      { 'sName' : 'name' },
      { 'sName' : 'email' },
      { 'sName' : 'login_count' }
           ],
     "fnServerData": function ( sSource, aoData, fnCallback )
    {
     $.ajax( {
      "dataType": "json",
      "type": "POST",
      "url": sSource,
      "data": aoData,
      "success": fnCallback,
      } );
    }
   } );
  } );
  [removed]

<b>Please help me solve this issue </b>

[eluser]kamal lamichhane[/eluser]
concat is not working on select

Code:

select("id, CONCAT('first_name', ' ', 'last_name') as name");