Simple Example of JTable With PHP and MySQL

jTable is very popular jquery HTML table plugin.I got a request about jtable with php and MySQL integration.This tutorials help to understand what is jTable and how to use with php and mysql.
jTable is a table plugin which creates HTML table and loads records from server using AJAX.The table supports main CRUD features like add/edit and delete records.I will extend these functionality and integrate with php and mysql.

Main Features of jTable are as follows

  • Automatically creates HTML table and loads records from server using AJAX.
  • Creates ‘create new record’ jQueryUI dialog form. When user creates a record, it sends data to server using AJAX and adds the same record to the table in the page.
  • Creates ‘edit record’ jQueryUI dialog form. When user edits a record, it updates server using AJAX and updates all cells on the table in the page.
  • Allow user to ‘delete a record’ by jQueryUI dialog based confirmation. When user deletes a record, it deletes the record from server using AJAX and deletes the record from the table in the page.
  • Shows animations for create/delete/edit operations on the table.
  • Supports server side paging using AJAX.
  • Supports server side sorting using AJAX.
  • Supports master/child tables.
  • Allows user to select rows.
  • Allows user to resize columns.
  • Allows user to show/hide columns.
  • Exposes some events to enable validation with forms.
  • It can be localized easily.
  • All styling of table and forms are defined in a CSS file, so you can easily change style of everything to use plugin in your pages. CSS file is well defined and commented.
  • It comes with per-defined color themes.
  • It is not depended on any server side technology.
  • It is platform independent and works on all common browsers.

We are using below Files,The details are:

Index.php - This file will responsible to create html and instance jtable using jquery code.
response.php - This file responsible to create database connection string and convert records into json string and returns data as a response.
connection.php - This file responsible to create mysql database connection.

jtable-with-php-and-mysql

Simple Example of jTable with PHP and MySQL

Step 1: Include js and css file into section of index.php file.

Select Code
1
2
3
4
5
6
<script src="dist/jquery-1.11.1.min.js"></script>
<script   src="https://code.jquery.com/ui/1.11.3/jquery-ui.min.js"   integrity="sha256-xI/qyl9vpwWFOXz7+x/9WkG5j/SVnSw21viy8fWwbeE="   crossorigin="anonymous"></script>
<!-- Include one of jTable styles. -->
<link href="dist/themes/metro/blue/jtable.min.css" rel="stylesheet" type="text/css" />
<!-- Include jTable script file. -->
<script src="dist/jquery.jtable.min.js" type="text/javascript"></script>

Step 2: Create connection file connection.php and define database variable and connect with MySQL database.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?php
Class dbObj{
  /* Database connection start */
  var $servername = "localhost";
  var $username = "root";
  var $password = "";
  var $dbname = "test";
  var $conn;
  function getConnstring() {
    $con = mysqli_connect($this->servername, $this->username, $this->password, $this->dbname) or die("Connection failed: " . mysqli_connect_error());

    /* check connection */
    if (mysqli_connect_errno()) {
      printf("Connect failed: %s\n", mysqli_connect_error());
      exit();
    } else {
      $this->conn = $con;
    }
    return $this->conn;
  }
}

?>

Step 3: Create response.php file and fetch records from mysql database and passed records json object to jTable instance.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
<?php
//include connection file 
include("connection.php");
$db = new dbObj();
$connString =  $db->getConnstring();

$params = $_REQUEST;
$action = $params['action'] !='' ? $params['action'] : '';
$empCls = new Employee($connString);

switch($action) {
 case 'list':
  $empCls->getEmployees();
 break;
 default:
 return;
}


class Employee {
  protected $conn;
  protected $data = array();
  function __construct($connString) {
    $this->conn = $connString;
  }
  
  function getEmployees() {
    $data = array();
    $sql = "SELECT * FROM `employee` ";
    
    $queryRecords = mysqli_query($this->conn, $sql) or die("error to fetch employees data");
    
    while( $row = mysqli_fetch_assoc($queryRecords) ) { 
      $data[] = $row;
      //echo "<pre>";print_R($data);die;
    }
    
    $json_data = array(
      "Result" => 'OK', 
      "Records"  => $data   // total data array
      );

  echo json_encode($json_data);  // send data as json format*/
    
    
  }
  

}

Step 4: Now we have JSON format of records and will use into jatable instance, So we will instantiate jTable on div id 'employee_grid' and define jtable parameters for grid.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
$( document ).ready(function() {
  $('#employee_grid').jtable({
            title: 'List of Employees',
            actions: {
                listAction: 'response.php?action=list'
            },
            fields: {
                id: {
                    title: 'EMPId',
                    width: '10%',
                    edit: false
                },
                employee_name: {
                    title: 'Employee Name',
                    width: '40%'
                },
                employee_salary: {
                    title: 'Employee Salary',
                    width: '20%'
                },
                employee_age: {
                    title: 'Age',
                    width: '30%'
                }
            }
        });
    $('#employee_grid').jtable('load');
});

Here i have applied jTable method on employee_grid div and defined all options here, I have added response file with listAction property.
We have used jtable('load') method for auto load data from response.php file.

You can download source code and Demo from below link.

Conclusion :

We have learn about basic of jTable jquery table plugin with options.We have integrated jTable jquery grid plugin with PHP and MySQL and display records into jtable grid.I have left add/edit and delete functionality for next tutorial about jTable with server-side using php and MySQL.