jQuery Datatables State Save : Client and Server-Side

datatables_state_saving
Written by Charaf JRA

One of my customers requested a feature which consists of saving the picture of a Datatable (current page, search inputs, filtered results, page size…) for future reloading of the page.
Three keywords describe well the current situation:  Datatables State Save

In this tutorial, you will be able to:

  • Save the state of a Datatable in the browser(client side) using HTML5 LocalStorage and SessionStorage
  • Save the state of a Datatable in a remote mySQL Database(server side) for a future use outside your browser

I will use the same table as in the previous tutorial Datatables:Client and server-side processing(PHP,MySQL,Ajax), but keep in mind that State Saving works the same in both cases Client and Server-Side processing.
I will include the latest version of jquery library, latest CSS and JS assets of the plugin from their official CDN (While I’m writing this article, jQuery 3.1 and jQuery Datatables 1.10.12), the markups to include them should be like this:

<link rel="stylesheet" href="//cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css"/>
<script src="https://code.jquery.com/jquery-3.1.0.min.js"></script>
<script src="//cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>

The common table for both ways:

<table id="datatable" class="display" cellpadding="1" cellspacing="1"  width="100%">
<thead>
<tr>
    <th>Name</th>
    <th>Phone</th>
    <th>City</th>
    <th>Email</th>
</tr>
</thead>
<tbody>
    <tr>
        <td>Rooney Richard</td>
        <td>1-996-276-4803</td>
        <td>Ingolstadt</td>
        <td>erat@augue.net</td>
    </tr>
    <tr>
        <td>Uriel Burris</td>
        <td>1-538-931-3803</td>
        <td>Ussassai</td>
        <td>a.malesuada.id@vestibulum.com</td>
    </tr>
</tbody>
<tfoot>
<tr>
    <th>Name</th>
    <th>Phone</th>
    <th>City</th>
    <th>Email</th>
</tr>
</tfoot>
</table>

Datatables State Save in the Browser using HTML5 LocalStorage or SessionStorage:

The simple and easiest way to perform a datatables state save is using LocalStorage(default method).We just have to enable the stateSave option:stateSave: true.
Once DOM is ready, we initialise  the Datatables like this :

$(document).ready(function() {
    $('#datatable').DataTable({
      stateSave: true
  });
} );

Et voilà! To verify that the parameters are stored, Simply open the Developer Tools  in your browser by pressing F12.
On Google Chrome, Click on the Resources tab and you will see localStorage’s content. From there you can add/edit/delete entries manually.

Datatables State Save using localStorage

Datatables State saved in the Browser like expected

If you want to limit the scope of the storage to the current Window and make its destruction automatic once the window is closed then use SessionStorage instead of LocalStorage:

$(document).ready(function() {
    $('#datatable').DataTable({
      stateSave: true,
      stateDuration:-1 //force the use of Session Storage
  });
} );

In this case, you will see the parameters in SessionStorage Folder.

View Demo

Datatables State Save in a Database(mySQL) using Ajax and PHP:

Datatables State Save in Mysql datatabase

In this example, I will store the state of our Datatable in a mySQL table. We can also store the state of other datatables of the same project if we want, the only constraint is that they should have different names to avoid states’ conflicts.

I create the new table using the following SQL Query:

CREATE TABLE IF NOT EXISTS `datatables_states` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `state` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

You can add a userId if you want so that each user in your project has his own state and configuration 🙂

To achieve our goal, I  will use two callback functions  defined in the options stateSaveCallback and stateLoadCallback.

Both of them will interact with a PHP page “dbManager.php”, its role is  getting or saving the state of a specific datatable using his name from the database, depending on an Action parameter.

In StateSaveCallback, I send using ajax two POST variables, name containing the identifier of my table and state which is an array containing the configuration of the datatable, this array will be JSON encoded and then stored in our table:

"stateSaveCallback": function (settings, data) {
 // Send an Ajax request to the server with the state object
 $.ajax( {
 "url": "dbManager.php?action=save",
 "data": {"name":"myKey", "state": data} ,//you can use the id of the datatable as key if it's unique
 "dataType": "json",
 "type": "POST",
 "success": function () {}
 });
}

StateLoadCallback will get the state by name from the table which is a JSON object :

"stateLoadCallback": function (settings) {
    var o;

    // Send an Ajax request to the server to get the data. Note that
    // this is a synchronous request since the data is expected back from the
    // function
    $.ajax( {
        "url": "dbManager.php?action=load",
        "data":{"name":"myKey"},
        "async": false,
        "dataType": "json",
        "type": "POST",
        "success": function (json) {
            o = json;
        }
    } );

    return o;
}

In server side, dbManager.php contains a DbManager class  which has two functions to interact with the database using  PDO interface.If PDO is not enabled on your server, you can use Mysqli as an alternative.

The first one is saveState which stores a row per datatable in the database:

public function saveState($name, $state)
{
 try {
 //if the name already exist then update else insert a new row in db
 $query = "INSERT INTO " . $this->table_name . "( `name`, `state`) VALUES ( :name , :state)";
 $query.= " ON DUPLICATE KEY UPDATE state=VALUES(state)";//to avoid multiple inserts, we update the column "state" if a row with the same name exists
 $stmt = $this->db->prepare($query);
 $stmt->bindParam(':name', $name);
 $stmt->bindParam(':state', json_encode($state));
 $stmt->execute();
 echo "saved";
 } catch (PDOException $e) {
 print "ERROR !: " . $e->getMessage() . "<br/>";
 die();
 }
}

Result will be similar to this screenshot:

datatables_stateSaving_mysql_db

The seconde one is loadState which restores the configuration from the database:

public function loadState($name)
{
 try {

 $stmt = $this->db->prepare("SELECT state FROM `datatables_states` WHERE `name` LIKE :name");

 $stmt->execute(array(":name" => $name));

 $result = $stmt->fetch(PDO::FETCH_ASSOC);
 echo $result['state'] ;
 } catch (PDOException $e) {
 print "ERROR !: " . $e->getMessage() . "<br/>";
 die();
 }
}

You can use your browser’s console to see what happens once you make a change on your datatable or once you reload the page, the image below describes what happens once I refresh the page.

datatables state save using mysql

Demo Download Source

If you have any question or feedback about this tutorial, feel free to add a detailed comment.

About the author

Charaf JRA

I'm a Code enthusiast , Blogger, Geek and Software Engineer. I created this blog to share my inspirations and my new experiences.If you like to contact me , use the contact form above or follow me on twitter

%d bloggers like this: