jTable Add,Edit and Delete Using PHP and MySQL

In the previous tutorial, we have to learn jTable features and integration with PHP and MySQL.This jTable tutorial is the second part of Simple Example of JTable With PHP and MySQL, We will implement AJAX based add, edit and delete record from MySQL database using PHP and jTable.

jTable is a very well-documented jQuery grid table plugin and helps to create AJAX-based CRUD tables. You can easily implement insert record, update and delete record functionality using PHP and MySQL. There is an inbuilt call-back function that will handle your add, edit and delete record request.

I am assuming you have read my previous tutorial of JTable With PHP and MySQL , so I am extending the previous tutorial code and adding CRUD features to that source code.

Insert Record into jQuery jTable Using PHP and MySQL

We will create one record of data and insert it into a MySQL table using PHP. We will show newly created data in jTable listing.

Step 1: We will add insert callback url into jTable instance using createAction option.

createAction: 'response.php?action=create',

I have added action file name with action name, The php file is response.php and passed action name is action=create using query parameters. I will get these parameters into response.php file to identify which operation will perform.

Step 2: Added one more condition into php switch method in response.php file for create action.

case 'create':
  $empCls->insertEmployee();
break;

I have called insertEmployee() method on the action of insert record, This method will respond to create a new record into MySQL table.

Step 3: We will create insertEmployee() action method in response.php file.

function insertEmployee() {
  $data = array();
  //print_R($_POST);die;
  $sql = "INSERT INTO `employee` (employee_name, employee_salary, employee_age) VALUES('" . $_POST["employee_name"] . "', '" . $_POST["employee_salary"] . "','" . $_POST["employee_age"] . "');  ";
  
   $result = mysqli_query($this->conn, $sql) or die("error to insert employee data");
  
   //Get last inserted record (to return to jTable)
   $result = mysqli_query($this->conn, "SELECT * FROM employee order by id DESC");
   $row = mysqli_fetch_array($result);

   //Return result to jTable
   $jTableResult = array();
   $jTableResult['Result'] = "OK";
   $jTableResult['Record'] = $row;
   echo json_encode($jTableResult);  // send data as json format*/  
}

This method will get all POST parameters for the insert record and passed to MySQL insert query, once the record is successfully created, we will send created row data as a response.

Update Record into jTable Using PHP and MySQL

Step 1: We will add update callback url into jTable instance using updateAction param.

updateAction: 'response.php?action=update',

We have passed action name is update like action=update.

Step 2: Added one more condition into switch method in response.php file.

case 'update':
  $empCls->updateEmployee();
break;

We have called updateEmployee() method on the updated record event.

Step 3: We will create new action method updateEmployee() in response.php file to update record in MySQL.

function updateEmployee() {
 $data = array();
 $sql = "Update `employee` set employee_name = '" . $_POST["employee_name"] . "', employee_salary='" . $_POST["employee_salary"]."', employee_age='" . $_POST["employee_age"] . "' WHERE id='".$_POST["id"]."'";
 
 $result = mysqli_query($this->conn, $sql) or die("error to update employee data");
 
 //Get last inserted record (to return to jTable)
 //$result = mysqli_query($this->conn, "SELECT * FROM employee order by id DESC");
   // $row = mysqli_fetch_array($result);

 //Return result to jTable
 $jTableResult = array();
 $jTableResult['Result'] = "OK";
 
 
 echo json_encode($jTableResult);  // send data as json format*/ 
}

I created update SQL query using posted data and run using MySQL query method.We have send updated row json data in response.

How to Implement Delete Record in jTable Using PHP and MySQL

Step 1: We will add delete callback url and added action method within into jTable instance.

deleteAction: 'response.php?action=delete'

Step 2: Added a switch condition into response.php file.

case 'delete':
 $empCls->deleteEmployee();
 break;

Step 3: Added action method in response.php file

function deleteEmployee() {
 $data = array();
 //print_R($_POST);die;
 $sql = "delete from `employee` WHERE id='".$_POST["id"]."'";
 
 $result = mysqli_query($this->conn, $sql) or die("error to delete employee data");

 //Return result to jTable
 $jTableResult = array();
 $jTableResult['Result'] = "OK";
 //$jTableResult['Record'] = $row;
 
 echo json_encode($jTableResult);  // send data as json format*/ 
}

This method create delete row query and run query using php.

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

Conclusion :

In previous tutorial, we have learn about basic of jTable jquery grid plugin with PHP and MySQL and display records into jtable grid.This tutorials help to add, edit and delete functionality of jTable with php and MySQL using AJAX.