Loading and Saving Sates to / from a Mariadb SQL database Take 2

PaulVickery · November 5

Here is another example of saving / loading state data to / from a SQL MariaDb database based on https://datatables.net/extensions/staterestore/examples/initialisation/ajax.html

This example does integrate with Editor as it uses the PHP controller file that the Editor Generator produces to retrieve data. It also works with a Select field getting its data from another table. I have also tried it with "Column Control", colReorder and colvis and all seem to work fine. You can change the user_id variable in user_data.php so that different users can just see their own states.

To get this to work, I have added another method to the php/lib/Editor.php file. (in my case I added it after the json() method).

To start, log into the DataTables website and generate a basic site from the Editor page (https://editor.datatables.net/ ) as the files below rely on the libraries / structure of what the generator produces. You will then need to save the files detailed below in the relevant directories. Make sure you use stateRestore version 1.4.3 or later.

Initially download the package from the generator and get it working and then use / copy the files below.

SQL Scripts
data_table_script.sql. This script creates the main "data" table
state_table_script.sql. Creates the table to store the state information.
10data.sql: Optional file to load 10 example rows of data into the "data" table.

Editor.add.php
Method to be included in the php/lib/Editor.php file.

Save the following in the root directory
* user_data.php
* state_info.php

Save the following in the js directory
* table.user_data.js
* table.state_info.js

Save the following in the PHP directory
* table.state_info.php
* table.user_data.php
* table.user_and_state_data
* stateSave.php
* stateLoad.php

Edit php/lib/Editor.php and add Editor.add.php

Edit table.user_and_state_data and check that the path for the $stateLoad variable is correct.

Note that I opted for separate controller files for datatables and editor in table.user_data.js so that the statesave info is not sent back to the server after editing.

-- Data Table (filename: data_table_script.sql )
DROP TABLE IF EXISTS data;
CREATE TABLE `data` (
  `id` int(10) NOT NULL,
  `first_name` varchar(25) DEFAULT NULL,
  `last_name` varchar(25) DEFAULT NULL,
  `zip_post_code` varchar(25) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE `data`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `data`
  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT;

  -- State Table (filename: state_table_script.sql )

DROP TABLE IF EXISTS states;
CREATE TABLE `states` (
  `id` int(10) NOT NULL,
  `state_name` varchar(10) NOT NULL,
  `state_info` text DEFAULT NULL,
  `user_id` varchar(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE `states` ADD PRIMARY KEY (`id`);
ALTER TABLE `states` MODIFY `id` int(10) NOT NULL AUTO_INCREMENT;
ALTER TABLE `states` ADD UNIQUE `unique_index`(`state_name`, `user_id`);

-- Data for load (filename: 10data.sql)
INSERT INTO data (first_name, last_name,zip_post_code,Country)
VALUES
("Armand","Warren","56045","Taiwan"),
("Xenos","Salas","71090","Liberia"),
("Virginia","Whitaker","62723","Nicaragua"),
("Kato","Patrick","97662","Palau"),
("Penelope","Hensley","76634","Greenland"),
("Georgia","Erickson","81358","Bolivia"),
("Tanisha","Humphrey","93371","Kenya"),
("Claire","Espinoza","I8S 2S8","Panama"),
("Raya","Tucker","O8D 8W7","Botswana"),
("Otto","Briggs","57590","Anguilla")

    /** File: Editor.add to be added to php/lib/Editor.php
     * jsonAddState, echo out json and state information.
     */
        public function jsonAddState($statescript = null)
    {
        include($statescript);
        $json = $this->_out;
        if ($statedata_array === []) {
            $json = json_encode($json);
            echo $json;
        } else {

            $json["stateRestore"] =  $statedata_array;
            $json = json_encode($json);
            echo $json;
        }
        return $this;
    }

<?php
    // filename user_data.php
    session_start();
    $_SESSION['user_id'] = 2;
    ?>

<!doctype html>
<html>

<head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8" />
    <meta name="viewport" content="width=device-width,initial-scale=1">

    <title>User Data</title>

        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/jq-3.7.0/moment-2.29.4/dt-2.3.4/b-3.2.5/b-colvis-3.2.5/cr-2.1.2/cc-1.1.1/date-1.6.1/sl-3.1.3/sr-1.4.3/datatables.min.css">
        <link rel="stylesheet" type="text/css" href="css/generator-base.css">
        <link rel="stylesheet" type="text/css" href="css/editor.dataTables.min.css">

        <script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/v/dt/jq-3.7.0/moment-2.29.4/dt-2.3.4/b-3.2.5/b-colvis-3.2.5/cr-2.1.2/cc-1.1.1/date-1.6.1/sl-3.1.3/sr-1.4.3/datatables.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="js/dataTables.editor.min.js"></script>

    <script type="text/javascript" charset="utf-8" src="js/table.user_data.js"></script>

    <style>
        div.desktopContainer {
            width: auto;
            margin-left: 200px;
            margin-right: 200px;
        }
    </style>
</head>

<body class="dataTables">
    <div class="desktopContainer">
        <h2>User Data</h2>
        <table cellpadding="0" cellspacing="0" border="0" class="display" id="user_data" width="100%">
            <thead>
                <tr>
                    <th>Id</th>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>ZIP / Post code</th>
                    <th>Country</th>
                </tr>
            </thead>
        </table>
    </div>
</body>

</html>

<?php
    // Filename state_info.php
    ?>
<!doctype html>
<html>

<head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8" />
    <title>State Information</title>

        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/jq-3.7.0/moment-2.29.4/dt-2.3.4/b-3.2.5/b-colvis-3.2.5/cr-2.1.2/cc-1.1.1/date-1.6.1/sl-3.1.3/sr-1.4.3/datatables.min.css">
        <link rel="stylesheet" type="text/css" href="css/generator-base.css">
        <link rel="stylesheet" type="text/css" href="css/editor.dataTables.min.css">

        <script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/v/dt/jq-3.7.0/moment-2.29.4/dt-2.3.4/b-3.2.5/b-colvis-3.2.5/cr-2.1.2/cc-1.1.1/date-1.6.1/sl-3.1.3/sr-1.4.3/datatables.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="js/dataTables.editor.min.js"></script>

    <script type="text/javascript" charset="utf-8" src="js/table.state_info.js"></script>

    <style>
        div.desktopContainer {
            width: auto;
            margin-left: 90px;
            margin-right: 90px;
        }
    </style>

</head>

<body class="dataTables">
    <div class="desktopContainer">
        <h2>State Information</h2>
        <table cellpadding="0" cellspacing="0" border="0" class="display" id="state_info" width="100%">
            <thead>
                <tr>
                    <th>Id</th>
                    <th>User Id</th>
                    <th>State Name</th>
                    <th>State Information</th>
                </tr>
            </thead>
        </table>
    </div>
</body>

</html>

PaulVickery · November 5

// filename: table.user_data.js

addEventListener("DOMContentLoaded", function () {
    DataTable.type('num', 'className', 'numeric');

    var editor = new DataTable.Editor({
        ajax: 'php/table.user_data.php',
        table: '#user_data',
        fields: [
            {
                "label": "First Name:",
                "name": "first_name"
            },
            {
                "label": "Last Name:",
                "name": "last_name"
            },
            {
                "label": "ZIP \/ Post code:",
                "name": "zip_post_code"
            },
            {
                "label": "Country:",
                "name": "country"
            }
        ]
    });

    var table = new DataTable('#user_data', {
        columnControl: {
            target: 1,
            content: ['search']
        },
        ajax: {
            url: '../php/table.user_state_data.php',
            dataSrc: 'data'
        },
        columns: [
            {
                "data": "id"
            },
            {
                "data": "first_name"
            },
            {
                "data": "last_name"
            },
            {
                "data": "zip_post_code"
            },
            {
                "data": "country"
            }
        ],
        layout: {
            topStart: {
                buttons: [
                    { extend: 'create', editor: editor },
                    { extend: 'edit', editor: editor },
                    { extend: 'remove', editor: editor },
                    'colvis',
                    'createState',
                    {
                        extend: 'savedStates',
                        config: {
                            ajax: '../php/stateSave.php'
                        }
                    }
                ]
            }
        },
        select: true,
        //   stateSave: true,
        colReorder: true,
    });
});

//filename: table.state_info.js

   addEventListener("DOMContentLoaded", function () {
       var editor = new DataTable.Editor({
           ajax: '../php/table.state_info.php',
           table: '#state_info',
           fields: [
               {
                   "label": "User Id:",
                   "name": "user_id"
               },
               {
                   "label": "State Name:",
                   "name": "state_name"
               },
               {
                   "label": "State Information:",
                   "name": "state_info"
               }
           ]
       });

       var table = new DataTable('#state_info', {
           ajax: '../php/table.state_info.php',
           columns: [
               {
                   "data": "id"
               },
               {
                   "data": "user_id"
               },
               {
                   "data": "state_name"
               },
               {
                   "data": "state_info"
               }
           ],
           layout: {
               topStart: {
                   buttons: [
                       { extend: 'create', editor: editor },
                       { extend: 'edit', editor: editor },
                       { extend: 'remove', editor: editor },
                       'colvis'
                   ]
               }
           },
           select: true,
           stateSave: true,
       });
   });

<?php
   // Filename: table.state_info.php
   /*
    * Editor server script for DB table statesave
    * Created by http://editor.datatables.net/generator
    */

   // DataTables PHP library and database connection
   include("lib/DataTables.php");

   // Alias Editor classes so they are easy to use
   use
   DataTables\Editor,
   DataTables\Editor\Field,
   DataTables\Editor\Format,
   DataTables\Editor\Mjoin,
   DataTables\Editor\Options,
   DataTables\Editor\Upload,
   DataTables\Editor\Validate,
   DataTables\Editor\ValidateOptions;

   // Build our Editor instance and process the data coming from _POST
   Editor::inst($db, 'states')
       ->fields(
           Field::inst('id'),
           Field::inst('state_name'),
           Field::inst('state_info'),
           Field::inst('user_id')

       )
       ->process($_POST)
       ->json();

<?php
// Filename: table.user_data.php
/*
 * Editor server script for DB table sampledata
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'data', 'id' )
    ->fields(
        Field::inst( 'id' ),
        Field::inst( 'first_name' ),
        Field::inst( 'last_name' ),
        Field::inst( 'zip_post_code' ),
        Field::inst( 'country' )
    )
    ->process( $_POST )
    ->json();

<?php
// Filename: table.user_state_data.php
/*
 * Editor server script for DB table sampledata
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

// Ensure that $stateLoad points to the correct file location for stateLoad.php
$stateLoad = 'stateLoad.php'; 

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'data', 'id' )
    ->fields(
        Field::inst( 'id' ),
        Field::inst( 'first_name' ),
        Field::inst( 'last_name' ),
        Field::inst( 'zip_post_code' ),
        Field::inst( 'country' )
    )
    ->process( $_POST )
    ->jsonAddState($stateLoad);
    //->json();

<?php
// Filename: stateSave.php

session_start();
$user_id = $_SESSION['user_id'];
include("./lib/DataTables.php");

use
    DataTables\Database,
    DataTables\Database\Query,
    DataTables\Database\Result;

if (isset($_POST) && isset($_POST['stateRestore']) && isset($_POST['action'])) {
    $keys = array_keys($_POST['stateRestore']);

    if ($_POST['action'] === 'rename') {
        foreach ($keys as $key) {

            $new_name = ($_POST['stateRestore'][$key]);
            $old_name = $keys[0];

            $SQL_STATE_UPDATE = "UPDATE states SET state_name = '$new_name' WHERE state_name = '$old_name' and user_id = '$user_id'";
            $stateupdate = $db->sql($SQL_STATE_UPDATE);
        }
    } else if ($_POST['action'] === 'save') {

        foreach ($keys as $key) {

            $stateRestore[$key] = $_POST['stateRestore'][$key];
            $stateinfo = $stateRestore[$key];

            $stateinfo = json_encode($stateinfo);
            $SQL_STATE_INSERT = "INSERT INTO states (user_id,state_name,state_info) VALUES ('$user_id','$key','$stateinfo') ON DUPLICATE KEY UPDATE state_info='$stateinfo' ";
            $stateinsert = $db->sql($SQL_STATE_INSERT);
        }
    } else if ($_POST['action'] === 'remove') {
        foreach ($keys as $key) {
            $SQL_STATE_DELETE = "DELETE from  states where state_name = '$keys[0]' and user_id = '$user_id'";
            $statedelete = $db->sql($SQL_STATE_DELETE);
        }
    }
}
$db = null;

PaulVickery · November 5

<?php
// Filename: stateLoad.php
session_start();
$user_id = $_SESSION['user_id'];

require __DIR__ . '/lib/Bootstrap.php';
try {

    $SQL_STATE_QUERY = "SELECT state_name, state_info FROM states where user_id = '$user_id'";
    $statedata = $db->sql($SQL_STATE_QUERY)->fetchAll();

    $record_count = count($statedata);
    if (count($statedata) !== 0) {
        foreach ($statedata as $row) {
            $state_name = $row["state_name"];
            $state_info = $row["state_info"];
            $state_info = json_decode($state_info);
            $statedata[$state_name] = $state_info;
        }
        $statedata_array = (array_slice($statedata, -$record_count, $record_count, true));
    } else {
        $statedata_array = [];
    }
} catch (PDOException $e) {

    echo $SQL_STATE_QUERY . "<br>" . $e->getMessage();
}
$db = null;

PaulVickery · November 5

Sorry, just noticed, filename should be table.user_state_data.php and not table.user_and_state_data as I mentioned above.

rf1234 · November 5

Interesting!

I implemented a rather simple solution for state saving on the server. I have one MariaDB table that holds the states. In addition to what data tables provides I also save the table width. It's basically regular width or full screen width.

This is my client side code working for all data tables. My users can choose whether they want state saving on the server or in the browser (local storage). The default by now is state saving on the server. I only post the code for state saving on the server here.

//global variables for the tables arrays
var stateSaveTablesArray = [];
var stateLoadTablesArray = [];

$.extend( true, $.fn.dataTable.defaults, {
    stateSaveCallback: function(settings, data) {                
        setTimeout(function() {
            var width = $('.container').width();
            var parentWidth = $('.container').offsetParent().width();
            var percent = 100 * width / parentWidth;
            var tableId = settings.nTable.id; //id of the data table
            //Avoid simultaneous calls for the same table, saving
            //should never be done while retrieving is still running
            if ( $.inArray(tableId, stateSaveTablesArray) < 0 && 
                 $.inArray(tableId, stateLoadTablesArray) < 0    ) {  
                stateSaveTablesArray.push(tableId);
                $.ajax({
                    type: "POST",
                    url: 'actions.php?action=saveState',
                    data: {
                        userId:     currentUserId,
                        dataTable:  tableId,
                        webPage:    webPage,
                        client:     navigator.userAgent,         
                        width:      percent,
                        values:     JSON.stringify(data)
                    },
                    success: function (tbl) {
                        var ix = stateSaveTablesArray.indexOf(tbl);
                        stateSaveTablesArray.splice(ix, 1);
                    }
                });
            }
        }, 500); //make sure we don't save before loading!!
    },
    stateLoadCallback: function(settings, callback) {
        var that = this;
        var tableId = settings.nTable.id; //id of the data table
        stateLoadTablesArray.push(tableId);
        $.ajax({
            type: "POST",
            url: 'actions.php?action=retrieveState',
            data: {
                userId:     currentUserId,
                dataTable:  tableId,
                webPage:    webPage,
                client:     navigator.userAgent   
            },
            dataType: "json",
            success: function (d) {
                $(document).one('ajaxStop', function () {                        
                    if ( that.api().button('toggleWidth:name').node().length ) {
                        var css = d.width > 90 ? {'width': '100%'} : {'width': ''};
                        $('.container').css(css);
                    }
                } );
                var ix = stateLoadTablesArray.indexOf(d.data_table);
                stateLoadTablesArray.splice(ix, 1);
                callback(JSON.parse(d.values));
            }
        });
    }
} );

The server code:

function saveState($userId, $dataTable, $webPage, $values, $client, $width, $dbh) {
    
    //We don't save the state if an lgf user logged in with a client user!
    if ( $_SESSION['orig_id'] !== $_SESSION['id'] ) {
        return;
    }
    
    $clientString = getClientString($client);
      
    $dbh->query('SELECT id 
                   FROM saved_state 
                  WHERE user_id         = :userId
                    AND data_table      = :dataTable
                    AND web_page        = :webPage
                    AND `client`        = :client');  
    $dbh->bind(':userId',       $userId);
    $dbh->bind(':dataTable',    $dataTable);
    $dbh->bind(':webPage',      $webPage);
    $dbh->bind(':client',       $clientString);
    $row = $dbh->singleAssoc(); // a one-dimensional array is returned SINGLE
    if ( (bool)$dbh->rowCount() ) {
        $dbh->query('UPDATE saved_state   
                        SET `values` = :values,
                            `width`  = :width
                      WHERE id       = :id');
        $dbh->bind(':id',           $row['id']);
        $dbh->bind(':values',       $values);
        $dbh->bind(':width',        $width);
        $result = $dbh->execute();    
    } else {
        $dbh->query('INSERT INTO saved_state   
                     (user_id, data_table, web_page, `values`, `client`, `width`)  
                                    VALUES  
                     (:userId, :dataTable, :webPage, :values, :client, :width)'); 
        $dbh->bind(':userId',       $userId);
        $dbh->bind(':dataTable',    $dataTable);
        $dbh->bind(':webPage',      $webPage);
        $dbh->bind(':values',       $values);
        $dbh->bind(':client',       $clientString);
        $dbh->bind(':width',        $width);
        $result = $dbh->execute();        
    }
}

function retrieveState($userId, $dataTable, $webPage, $client, $dbh) {   
    
    $clientString = getClientString($client);
    
    $dbh->query('SELECT `values`, `width`, data_table 
                   FROM saved_state 
                  WHERE user_id         = :userId
                    AND data_table      = :dataTable
                    AND web_page        = :webPage
                    AND `client`        = :client');
    $dbh->bind(':userId',       $userId);
    $dbh->bind(':dataTable',    $dataTable);
    $dbh->bind(':webPage',      $webPage);
    $dbh->bind(':client',       $clientString);
    $row = $dbh->singleAssoc(); // a one-dimensional array is returned SINGLE
    if ( (bool)$dbh->rowCount() ) {
        return $row;
    }
    return [ "values"       => '{}',
             "width"        => 80,
             "data_table"   => ''    ];
}

function deleteState($userId, $dataTable, $webPage, $client, $dbh) {  
    
    $clientString = getClientString($client);
    
    if ( $dataTable === "allUserStates" ) {
        $dbh->query('DELETE
                       FROM saved_state 
                      WHERE user_id         = :userId
                        AND `client`        = :client');  
        $dbh->bind(':userId',       $userId);
        $dbh->bind(':client',       $clientString);
        $result = $dbh->execute();
    } else {    
        $dbh->query('DELETE
                       FROM saved_state 
                      WHERE user_id         = :userId
                        AND data_table      = :dataTable
                        AND web_page        = :webPage
                        AND `client`        = :client');  
        $dbh->bind(':userId',       $userId);
        $dbh->bind(':dataTable',    $dataTable);
        $dbh->bind(':webPage',      $webPage);
        $dbh->bind(':client',       $clientString);
        $result = $dbh->execute();
    }
}

function getClientString($client) {
    $client = strtolower($client);
    
    // Check if the "mobile" word exists in User-Agent 
    $isMob = is_numeric(strpos($client, "mobile")) ? "true" : "false";

    // Check if the "tablet" word exists in User-Agent 
    $isTab = is_numeric(strpos($client, "tablet")) ? "true" : "false";

    // Platform check  
    $isWin = is_numeric(strpos($client, "windows"));
    $isAndroid = is_numeric(strpos($client, "android"));
    $isIPhone = is_numeric(strpos($client, "iphone"));
    $isIPad = is_numeric(strpos($client, "ipad"));
    $isIOS = $isIPhone || $isIPad;
    
    if ( $isWin ) {
        $os = "win";
    } elseif ( $isAndroid ) {
        $os = "android";
    } elseif ( $isIOS ) {
        $os = "ios";
    } else {
        $os = "other";
    }
    if ( is_numeric(strpos($client, "firefox") ) ) {
        $browser = "firefox";
    } elseif ( is_numeric(strpos($client, "edg") ) ) {
        $browser = "edge";
    } elseif ( is_numeric(strpos($client, "chrome") ) ) {
        $browser = "chrome";
    } elseif ( is_numeric(strpos($client, "safari") ) ) {
        $browser = "safari";
    } else {
        $browser = "other";
    }
    return "mobile: " . $isMob . 
           ", tablet: " . $isTab .
           ", os: " . $os . 
           ", browser: " . $browser;
}

I also have buttons that allow the user to delete all of his states or just one. Hence the function "deleteStates".

This is part of the code of how those functions are called:

case "saveState":
    ....
    saveState($userId, $dataTable, $webPage, $values, $client, $width, $dbh);
    echo ( $dataTable );
    break;
case "retrieveState":
    ....
    echo json_encode(retrieveState($userId, $dataTable, $webPage, $client, $dbh));
    break;

Loading and Saving Sates to / from a Mariadb SQL database Take 2

Loading and Saving Sates to / from a Mariadb SQL database Take 2

Replies

Howdy, Stranger!

Categories

DataTables

Loading and Saving Sates to / from a Mariadb SQL database Take 2

Loading and Saving Sates to / from a Mariadb SQL database Take 2

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables