Getting Started

jTable - A jQuery Plugin to Create AJAX-Based CRUD Tables

Getting Started

Introduction

Here, I will examine a simple table implementation with jTable. Assume that you have a database table as shown below.

Surely, you don't have to work with SQL Server, or even with a DBMS. jTable does not care about any server-side technology.

The demo sql for import into MySQL can be found here

Dependencies

jTable depends on jQuery. So, if you haven't yet, you must first download the latest version of jQuery from jquery.com.

Downloading jTable

You can download jTable from the Releases section in Github.

After downloading, you will have a folder structure like that:

Demo DB and php files

The demo sql for import into MySQL can be found here
The demo PHP files (the head section needs to be updated to your path of jquery/theme/jTable):
jTableSimple.php
jTableSimplePagedSorted.php
PersonActions.php
PersonActionsPagedSorted.php

Creating Header

Add these lines to the HEAD section of your HTML document:

<!-- Include one of jTable styles. -->
<link href="/jtable/themes/metro/blue/jtable.min.css" rel="stylesheet" type="text/css" />

<!-- Include jTable script file. -->
<script src="/jtable/jquery.jtable.min.js" type="text/javascript"></script>

You can select any theme and color schema in the themes folder.

NOTE: You must also add the needed jQuery JavaScript file before importing jTable files.

Creating a Container

jTable needs only a container element for your table.

<div id="PersonTableContainer"></div>

Container element can be a simple div element as shown above.

Creating a jTable Instance

Add these JavaScript codes to your page:

<script type="text/javascript">
    $(document).ready(function () {
        $('#PersonTableContainer').jtable({
            title: 'Table of people',
            actions: {
                listAction: '/GettingStarted/PersonList',
                createAction: '/GettingStarted/CreatePerson',
                updateAction: '/GettingStarted/UpdatePerson',
                cloneAction: '/GettingStarted/ClonePerson',
                deleteAction: '/GettingStarted/DeletePerson'
            },
            fields: {
                PersonId: {
                    key: true,
                    list: false
                },
                Name: {
                    title: 'Author Name',
                    width: '40%'
                },
                Age: {
                    title: 'Age',
                    width: '20%'
                },
                RecordDate: {
                    title: 'Record date',
                    width: '30%',
                    type: 'date',
                    create: false,
                    edit: false
                }
            }
        });
    });
</script>

All HTML and JavaScript codes are ready! We set the title of the table, action URLs to perform AJAX operations on the server, and the structure of our Person record fields. If you run the page now, you will see the table without data:

If you click '+ Add new record' link, a dialog is automatically created:

We must create server-side codes to be able to run the page.

Creating the List Action

The listAction option of jTable is used to get data to create a table of records. It's a regular URL like '/GettingStarted/PersonList'. If you are working with PHP, it might be '/GettingStarted/PersonList.php'. jTable performs an AJAX POST request to this URL to get records when you call the load method.

$('#PersonTableContainer').jtable('load');

The load method can be called after the table is initialized.

All server actions used by jTable must return a JSON object. Here is a sample return value for this example:

{
 "Result":"OK",
 "Records":[
  {"PersonId":1,"Name":"Benjamin Button","Age":17,"RecordDate":"\/Date(1320259705710)\/"},
  {"PersonId":2,"Name":"Douglas Adams","Age":42,"RecordDate":"\/Date(1320259705710)\/"},
  {"PersonId":3,"Name":"Isaac Asimov","Age":26,"RecordDate":"\/Date(1320259705710)\/"},
  {"PersonId":4,"Name":"Thomas More","Age":65,"RecordDate":"\/Date(1320259705710)\/"}
 ]
}

Don't worry about creating a JSON object. All common server-side technologies have the ability to create these objects easily (see samples below).

The Result property can be "OK" or "ERROR". If it is "OK", the Records property must be an array of records. If it is "ERROR", a Message property can explain the reason for the error to show to the user.

Here are sample server-side codes for listAction in PHP:

//Get records from database
$result = mysqli_query("SELECT * FROM people;");

//Add all records to an array
$rows = [];
while($row = mysqli_fetch_array($result))
{
    $rows[] = $row;
}

//Return result to jTable
$jTableResult = [
    'Result' => "OK",
    'Records' => $rows
];
print json_encode($jTableResult);

So, now we can run the page and see the result:

Creating a New Record

When we click the '+ Add new record' link below the table, a create record form is automatically generated by jTable:

When we try to add a person, we get an error since we haven't implemented createAction yet! The createAction option of jTable is used to submit (POST) a 'create record form' to the server. When you press the Save button, a POST data is sent to the server as shown below:

Name=Dan+Brown&Age=55

On the server side, you can save the new person to the database. The createAction must return the newly created record (as a JSON object)! A sample return value for createAction can be:

{
 "Result":"OK",
 "Record":{"PersonId":5,"Name":"Dan Brown","Age":55,"RecordDate":"\/Date(1320262185197)\/"}
}

Same as all jTable actions, the returning object must contain a Result property that's value can be "OK" or "ERROR". If it's "OK", the Record property is the created record.

Here are sample server-side codes for createAction in PHP:

//Insert record into database
$result = mysqli_query("INSERT INTO people(Name, Age, RecordDate) VALUES('" . $_POST["Name"] . "', " . $_POST["Age"] . ",now());");

//Get last inserted record (to return to jTable)
$result = mysqli_query("SELECT * FROM people WHERE PersonId = LAST_INSERT_ID();");
$row = mysqli_fetch_array($result);

//Return result to jTable
$jTableResult = [
    'Result' => "OK",
    'Record' => $row
];
print json_encode($jTableResult);

When the server successfully saves the new record, the same record is automatically added to the jTable with an animation.

Editing/Updating a Record

When we click the edit icon for a record, an edit record form is automatically generated by jTable:

When we change the age of Douglas Adams and save the form, a POST operation is made to the updateAction URL with the following values:

PersonId=2&Name=Douglas+Adams&Age=43

On the server side, you can update fields in the database table for PersonId=2. The updateAction must return a JSON object like this:

{"Result":"OK"}

If Result is "ERROR", a Message property can explain the error reason. If Result is "OK", jTable updates cells on the table in the page with an animation.

Here are sample server-side codes for updateAction in PHP:

//Update record in database
$result = mysqli_query("UPDATE people SET Name = '" . $_POST["Name"] . "', Age = " . $_POST["Age"] . " WHERE PersonId = " . $_POST["PersonId"] . ";");

//Return result to jTable
$jTableResult = [
    'Result' => "OK"
];
print json_encode($jTableResult);

Clone a Record

When we click the clone icon for a record, an clone record form is automatically generated by jTable:

When we change the age of Douglas Adams and save the form, a POST operation is made to the cloneAction URL with the following values:

Name=Douglas+Adams&Age=43

On the server side, you can create fields in the database table with a new PersonId. The cloneAction must return a JSON object like this:

{"Result":"OK"}

If Result is "ERROR", a Message property can explain the error reason. If Result is "OK", jTable adds a row on the table in the page with an animation.

Here are sample server-side codes for cloneAction in PHP:

//Insert record into database
$result = mysqli_query("INSERT INTO people(Name, Age, RecordDate) VALUES('" . $_POST["Name"] . "', " . $_POST["Age"] . ",now());");

//Get last inserted record (to return to jTable)
$result = mysqli_query("SELECT * FROM people WHERE PersonId = LAST_INSERT_ID();");
$row = mysqli_fetch_array($result);

//Return result to jTable
$jTableResult = [
    'Result' => "OK",
    'Record' => $row
];
print json_encode($jTableResult);

Deleting a Record

When we click the delete icon for a record, a confirmation dialog is shown to the user by jTable (confirmation is optional but open by default):

When we click the delete button, a POST operation is made to the deleteAction URL with the following values:

PersonId=3

You can delete the record 3 on the server. The deleteAction also returns a JSON object like this:

{"Result":"OK"}

If Result is "ERROR", a Message property can explain the error reason. If Result is "OK", jTable deletes the related row from the table in the page with an animation.

Here are sample server-side codes for deleteAction in PHP:

//Delete from database
$result = mysqli_query("DELETE FROM people WHERE PersonId = " . $_POST["PersonId"] . ";");

//Return result to jTable
$jTableResult = [
    'Result' => "OK"
];
print json_encode($jTableResult);

The Result

Here is the result jTable instance. Try it yourself:

<script type="text/javascript">
    $(document).ready(function () {
        $('#PersonTableContainer').jtable({
            title: 'Table of people',
            actions: {
                listAction: '/GettingStarted/PersonList',
                createAction: '/GettingStarted/CreatePerson',
                updateAction: '/GettingStarted/UpdatePerson',
                deleteAction: '/GettingStarted/DeletePerson'
            },
            fields: {
                PersonId: {
                    key: true,
                    list: false
                },
                Name: {
                    title: 'Author Name',
                    width: '40%'
                },
                Age: {
                    title: 'Age',
                    width: '20%'
                },
                RecordDate: {
                    title: 'Record date',
                    width: '30%',
                    type: 'date',
                    create: false,
                    edit: false
                }
            }
        });

        $('#PersonTableContainer').jtable('load');
    });
</script>