Simple Example of JTable With PHP and MySQL

jTable is a very popular jquery HTML table plugin. I got a request about jtable with PHP and MySQL integration. This tutorial help to understand what is jTable and how to use it with PHP and MySQL.

jTable is a table plugin that creates an HTML table and loads records from the server using AJAX. The table supports main CRUD features like adding a record, editing a record, and deleting a record. I will extend this functionality and integrate it with PHP and MySQL.

The 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 a user creates a record, it sends data to the server using AJAX and adds the same record to the table in the page.
• Creates ‘edit record’ jQueryUI dialog form. When a user edits a record, it updates the 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 a user deletes a record, it deletes the record from the server using AJAX and deletes the record from the table on 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 users to select rows.
• Allows users to resize columns.
• Allows the 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 the style of everything to use plugins in your pages. The CSS file is well defined and commented.
• It comes with pre-defined color themes.
• It is not dependent on any server-side technology.
• It is platform-independent and works on all common browsers.

We are using the below Files, The details are:

Index.php - This file will responsible to create HTML and instance jtable using jquery code.
response.php – This file is responsible to create database connection strings and convert records into JSON strings and returning data as a response.
connection.php – This file is responsible to create a MySQL database connection.

Simple Example of jTable with PHP and MySQL

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

<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. -->
  
<!-- Include jTable script file. -->
<script src="dist/jquery.jtable.min.js" type="text/javascript"></script>

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

<?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.

<?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-&gt;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 jTable instance, So we will instantiate jTable on div id 'employee_grid' and define jtable parameters for the grid.

$( 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 a response file with listAction property.
We have used jtable('load') method for auto load data from response.php file.

You can download the source code and Demo from the below link.

Conclusion :

We have learned about the basics 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 the next tutorial about jTable with server-side using PHP and MySQL.