Loading and Saving Sates to / from a Mariadb SQL database

PaulVickery · October 10

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

Please feel free to improve on it and many thanks to all who have indirectly contributed to this whose code I have copied.

Please note that this example is limited in what it can do with regards editing as it does not use the PHP controller file that the Editor Generator produces to retrieve data. It cannot integrate with things like the Select field type where the selected data is coming from another table. Hopefully, someone can integrate this with the PHP controller file, ( e.g. table.user_data.php) but I don't know how to do it.

To start, you should 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.

I have included colReorder and colvis in the example. However, I have not included individual column filtering as shown here at https://datatables.net/extensions/colreorder/examples/initialisation/col_filter.html , but this functionality appears to work as well.

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.

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
* stateRestoreSave.php
* stateRestoreLoad.php

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

<?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/dt-2.3.4/b-3.2.5/b-colvis-3.2.5/cr-2.1.2/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/dt-2.3.4/b-3.2.5/b-colvis-3.2.5/cr-2.1.2/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>

<?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/dt-2.3.4/b-3.2.5/b-colvis-3.2.5/cr-2.1.2/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/dt-2.3.4/b-3.2.5/b-colvis-3.2.5/cr-2.1.2/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>

    // 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',
            // idSrc required because DT_RowId not present
            idSrc: 'id',
            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', {
            ajax: {
                url: '../php/stateRestoreLoad.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/stateRestoreSave.php'
                            }
                        }
                    ]
                }
            },
            select: true,
            stateSave: true,
            colReorder: true,
        });
    });

PaulVickery · October 10

    //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: stateRestoreSave.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;
    exit();
    ?>

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

    include("./lib/DataTables.php");

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

    $SQL_DATA_QUERY = "SELECT * FROM data";
    $user_data = $db->sql($SQL_DATA_QUERY)->fetchAll();
    $user_data_array = array("data" => $user_data);

    $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) {
        echo (json_encode(($user_data_array)));
    } else {

        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));
        $user_data_array["stateRestore"] = $statedata_array;
        echo (json_encode(($user_data_array)));
    }
    $db = null;
    exit();
    ?>

allan · October 10

Superb! Thank you for sharing this with us

Allan

Loading and Saving Sates to / from a Mariadb SQL database

Loading and Saving Sates to / from a Mariadb SQL database

Replies

Howdy, Stranger!

Categories

DataTables

Loading and Saving Sates to / from a Mariadb SQL database

Loading and Saving Sates to / from a Mariadb SQL database

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables