check box loose selected state on sorting and filtering

bsp

Hi,

I am new to datatables, I am using serverside processing and managed to add a checkbox to one of the columns returned from server using aoColumnDefs with fnRender( based on http://www.datatables.net/release-datatables/examples/advanced_init/column_render.html) . The issue I am currently facing is, when I check this checkbox in the rows and do a column sort or filter by entering values in the search box, the checked checkboxes goes back to the default unchecked state ( the way it was loaded by default).The column rendering working fine when I need to concatenate data from 2 columns which have no text boxes or checkboxes. Is there any way to maintain the state of the checkboxes when the user does sorting , so that what the user unchecks and checks are maintained ? I tried 'bStateSave': true, option which was not doing the trick. Please help, I think this is because, when I do a sort or search it redraws and selection in the check box goes back to default state which is unchecked , is there any workaround.. The snippet of my code as follows
[code]
$(document).ready(function() {
$('#dtMytable').dataTable({
'bServerSide' : true,
'bAutoWidth' : false,
'bStateSave': true,
'bJQueryUI': true,
'sPaginationType': 'full_numbers',
'sAjaxSource': '<?php echo site_url('my_jsonurl');?>',
'aoColumns' : [
{ 'sName': 'name'},
{ 'sName': 'id',
'bVisible': false},
{ 'sName': 'age' },

],
'aoColumnDefs': [
{
"fnRender": function ( oObj ) {
return ' '+ ''+oObj.aData[0]+'';
},
"aTargets": [ 0 ]
},
{ "bVisible": false, "aTargets": [ 1 ] }

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

[/code]

fbas

you'll have to save checkbox state. you should probably associate a given checkbox with it's row's unique primary key id so you can re-apply the checkbox values after sort/filter.

you can save it in an array (catch the events that change the checkbox, and when table is sorted or filtered, reapply) but depending on the size of your database that could turn into a lot of in-memory storage

You could also store it in the database (a bit easier if you ask me, because you'll get the value of the checkbox when you run your SELECT query on every update) and convert the cell's value from text/boolean or whatever into a checkbox with fnRender or some other callback. This second option obviously requires you to have access to the db schema.

bsp

saving to db
----------
I do have access to the db schema, how do i save the checked value to the db if we are going to save it, like is there is any function that I can call which would save as soon as the user checks the check box, the scenario is, there is a list of rows in the datatable, how do i capture the check event as soon as the user checks/unchecks the box and save the data in this sceneario, because the user checks few check boxes in the table and then if user do a sort or filter the state of the check box goes away.

in memory
--------
if i am planning to save the state in memory, is there any functions that happens after sort/filter event like afterSort() or Afterfilter() where I can set the values back to what it was before ?

or does it make sense to get the data from server and then set this array as the datatable source , which would then become client side iguess, is there any way to maintain state easily incase of client side sorting and filtering. I am new to jquery and just learning as part of the project. Thanks for your help

fbas

if you are using the edible plug-in, I would use the update method from that plugin (and an appropriate server side script).

if you aren't, you can write your own like this, in your click/change handler for checkboxes. I'm going to use pseudocode because I don't know if I can write the whole thing without errors if I use real javascript - and that might confuse others reading this code
[code]
// use live so that new checkboxes get this handler applied to them, even if they don't exist at the time this function is first called
$('checkboxes with a certain class or whatever').live ("change', function () {
// get id for this row. maybe it's in column 0 or maybe it's in the TR's id, depending on how you set things up.
var colname = "TheColumnWithTheCheckboxStatus"; // ok, you can use a better column field name in your db
var val = $(this).val(); // I think this works with checkboxes, but check jquery docs
var row_id = get_row_id(this); // process the check box to get it's parent or TR parent or a column in the same row that has an identifying key value that we can use for db update code.

// craft an AJAX call to update the database with the checkbox value; I'm assuming the db script expects a table name, key (primary key in db or something), column name being updated, and a value for this column
// you can add more finesse to this, like a success callback function, and error callback function, etc., use POST instead of GET, etc.
$.ajax({
url: "your_update_script.php?table=tablename&key="+unique_db_row_id+"&value="+val;
data: {
table: "tablename", // replace this, obviously - or your script might not need table name if you just have one table and this script only updates that table
key: row_id,
col: colname,
value: val
}
});
});
[/code]

your php server script might look like this:
[code]
<?php
// initialize variables (not necessary if you have WARNINGS turned to a low level
$table = $col = $row_id = $val = "";
$valid_tables = array('table1', 'table_allowing_updates', ... ); // array of tables that this script is allowed to update
$valid_cols = array ('checkbox_col', 'other_column_allowed_to_update'); // array of columns this script is allowed to update

$id_column = "name_of_your_ID_column ";

// connect to database
// http://php.net/manual/en/function.mysql-connect.php
mysql_connect ( ... );

// get submitted parameters. change to $_POST if you aren't using GET submission
if (isset($_GET['table'])) $table = mysql_real_escape_string($_GET['table']);
if (isset($_GET['row_id'])) $row_id= mysql_real_escape_string($_GET['row_id']);
if (isset($_GET['col'])) $col= mysql_real_escape_string($_GET['col']);
if (isset($_GET['val'])) $val= mysql_real_escape_string($_GET['val']);

// make sure valid data is passed
if ($table == "" | $col == "" || $row_id == "" || $val == "" )
die ("invalid data passed");
if (!in_array($table, $valid_tables)) die ("no access to table $table");
if (!in_array($col, $valid_columns)) die ("no access to column $col");

// create query, http://www.php.net/manual/en/function.mysql-query.php
$query = "UPDATE $table SET $col='$val' WHERE $id_column='$row_id' ";
$result = mysql_query($query) or die ("query error: " + mysql_error() );

// return results
...
[/code]

and also in your client side query code to the server side, get the value of the checkboxes on every draw/refresh. easiest way would be to add a column to your DataTable for that field. you can use your fnRender function to convert from whatever value is in the db (maybe it's "true" or "false" if you used a string, or 0 or 1 if you use an int/bool)) to a checkbox with the value displayed as a check
[code]
"fnRender": function ( oObj ) {
var checked = "";
if (oObj.aData[oObj.iDataColumn] == "1") checked = 'checked="checked"'; // HTML attribute to set checkbox to "checked"
return ';
}
[/code]

fbas

[quote]bsp said: if i am planning to save the state in memory, is there any functions that happens after sort/filter event like afterSort() or Afterfilter() where I can set the values back to what it was before ?[/quote]

Yes, there are callbacks for after a draw (when you sort, draw is called, which calls your server side script for the data). you can use fnDrawCallback, or you could use the row callback as well. whichever you prefer. http://datatables.net/ref

bsp

Thank you so much for your help, I am able to maintain the state of the checkboxes in client side.