How to Add, Edit and Delete a row in jQuery Flexigrid Using PHP and MySQL

jQuery Flexigrid is very popular grid plugin which is convert HTML table into beautiful and full functional grid listing. Flexigrid support pagination, sorting, searching, dynamic column binding and fixed column width etc, Earlier i have shared flexigrid article about 1.0 but in this post i will use jquery flexigrid 1.1.I will demonstrate add,edit and delete records using Ajax in flexigrid.

flexigrid has only dependent library is jquery. We will learn how to add, edit and delete records in flexigrid using php, MySQL and Ajax.

Add,Edit and Delete records using Flexigrid, PHP and MySQL

jQuery Flexigrid has in-built callback function to fire add, edit and delete action.We just create a function which will handle flexigrid action and passed to appropriate ajax method for add,edit and delete record.

I am using following files and folder

js folder:This folder will contains all js libs of jquery and flexigrid.
css folder:This folder will contains all css libs of jquery and flexigrid.
images folder:This folder will contains all images of jquery and flexigrid.
index.php:This file will use to create HTML layout and handle ajax response.
connection.php:This file will use to create database connection.
response.php:This file will use to handle all serverside fucntionality. I will create add,edit,delete action method and return back json response to ajax method.

Also Checkout other tutorials of flexi grid,

• Part 1 – Simple Example of Flexi Grid with PHP
• Part 4 – How To Add Dynamic Column in Flexi Grid

How to add listing records in Flexigrid

Step 1: Created connection.php file to handle database connection and return db connection object.

<!--?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;
 }
}

?>

Where is:

1. $servername: your mysql db hostname
2. $username: mysql db username
3. $password: mysql db pass
4. $dbname: database name

Step 2: Included all js and css files into head section of index.php file.

        <link rel="stylesheet" type="text/css" href="css/flexigrid.pack.css">
  <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
  <script type="text/javascript" src="js/flexigrid.pack.js"></script>
          <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css">
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>

Step 3: Create HTML table for listing data in index.php file.

<div class="container">




<table id="employees" style="display: none"></table>




</div>

Step 4: Applied flexigrid constructor method on HTML table.The flexigrid method converts HTML table into beautiful grid listing.

$("#employees").flexigrid({
                url : 'response.php',
                dataType : 'json',
    method: 'POST',
                colModel : [ {
                    display : 'ID',
                    name : 'id',
                    width : 90,
                    sortable : true,
                    align : 'center'
                    }, {
                        display : 'Name',
                        name : 'employee_name',
                        width : 120,
                        sortable : true,
                        align : 'left'
                    }, {
                        display : 'Salary',
                        name : 'employee_salary',
                        width : 120,
                        sortable : true,
                        align : 'left'
                    }, {
                        display : 'Age',
                        name : 'employee_age',
                        width : 80,
                        sortable : true,
                        align : 'left'
                    } ],
                buttons : [
                ],
                searchitems : [],
                sortname : "id",
                sortorder : "asc",
                usepager : true,
                title : 'Employees',
                useRp : true,
                rp : 15,
                showTableToggleBtn : true,
    height:'auto',
    striped:true,
                width : 550
            });

Added above jquery code into footer of index.php file.

Step 5: Added action handler in response.php file.

include("connection.php");
 $db = new dbObj();
 $connString =  $db->getConnstring();

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

 switch($action) {
  default:
  $empCls->getEmployees($params);
  return;
 }
 class Employee {
 protected $conn;
 protected $data = array();
 function __construct($connString) {
  $this->conn = $connString;
 }
 
 public function getEmployees($params) {
  $this->data = $this->getRecords($params);
  
  echo json_encode($this->data);
 }
 
 function getRecords() {
  $page = isset($_POST['page']) ? $_POST['page'] : 1;
  $rp = isset($_POST['rp']) ? $_POST['rp'] : 10;
  $sortname = isset($_POST['sortname']) ? $_POST['sortname'] : 'name';
  $sortorder = isset($_POST['sortorder']) ? $_POST['sortorder'] : 'desc';
  $query = isset($_POST['query']) ? $_POST['query'] : false;
  $qtype = isset($_POST['qtype']) ? $_POST['qtype'] : false;
  
  $sql = "SELECT * FROM `employee` order by ".$sortname." ".$sortorder." LIMIT ". $page*$rp .",".$rp;
  $sqlTot = "SELECT * FROM `employee`";
  $qtot = mysqli_query($this->conn, $sqlTot) or die("error to fetch tot employees data");
  $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(
   "page"            => $page,   
   "total"    => intval($qtot->num_rows),
   "rows"            => $data   // total data array
  );
  
  return $json_data;
 }
 
}

How to Insert/Add record in Flexigrid

There are add,edit and delete button images which will need to add in flexigrid plugin using css class.We need to add below css class in head of index.php file.
[code type=css]

Step 1: Added Bootstrap add modal box to add record in index.php file.

<div id="add_model" class="modal fade">




<div class="modal-dialog">




<div class="modal-content">




<div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>




<h4 class="modal-title">Add Employee</h4>




</div>








<div class="modal-body">
                <form method="post" id="frm_add">
    <input type="hidden" value="add" name="action" id="action">




<div class="form-group">
                    <label for="name" class="control-label">Name:</label>
                    <input type="text" class="form-control" id="name" name="name">
</div>








<div class="form-group">
                    <label for="salary" class="control-label">Salary:</label>
                    <input type="text" class="form-control" id="salary" name="salary">
</div>








<div class="form-group">
                    <label for="salary" class="control-label">Age:</label>
                    <input type="text" class="form-control" id="age" name="age">
</div>




                
            </form></div>








<div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
                <button type="button" id="btn_add" class="btn btn-primary">Save</button>
</div>




   
</div>




</div>




</div>

Step 2: Added callback method in buttons array under flexigrid constructor,

{
     name : 'Add',
     bclass : 'add',
     onpress : gridAction
 }

here we have mentioned gridAction() JavaScript method which will call on add button click.

Step 3: Added java-script method gridAction() which will handle add record flexigrid event.

function gridAction(com, grid) {
    if (com == 'Add') {
     $('#add_model').modal('show');  
                }

Step 4: Added event on submit button to add modal input values ans send from data to response.php.

$( "#btn_add" ).click(function() {
     ajaxAction('add');
   });

Step 5: Added ajaxAction() method which will fire ajax request to server-side. We will passed json data to server side using ajax technology.

function ajaxAction(action) {
    data = $("#frm_"+action).serializeArray();
    $.ajax({
      type: "POST",  
      url: "response.php",  
      data: data,
      dataType: "json",       
      success: function(response)  
      {
     $('#'+action+'_model').modal('hide');
     $("#employees").flexReload();
      }   
    });
   }

Step 6: Added a switch option in response.php file.

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

Step 7: Added add action method in response.php file which will insert record in mysql database and send status.

function insertEmployee($params) {
  $data = array();;
  $sql = "INSERT INTO `employee` (employee_name, employee_salary, employee_age) VALUES('" . $params["name"] . "', '" . $params["salary"] . "','" . $params["age"] . "');  ";
  
  echo $result = mysqli_query($this->conn, $sql) or die("error to insert employee data");
  
 }

How to Edit/Update Record in Flexigrid

Step 1: Added Bootstrap edit modal box to update record in index.php file.

<div id="edit_model" class="modal fade">




<div class="modal-dialog">




<div class="modal-content">




<div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>




<h4 class="modal-title">Edit Employee</h4>




</div>








<div class="modal-body">
                <form method="post" id="frm_edit">
    <input type="hidden" value="edit" name="action" id="action">
    <input type="hidden" value="0" name="edit_id" id="edit_id">




<div class="form-group">
                    <label for="name" class="control-label">Name:</label>
                    <input type="text" class="form-control" id="edit_name" name="edit_name">
</div>








<div class="form-group">
                    <label for="salary" class="control-label">Salary:</label>
                    <input type="text" class="form-control" id="edit_salary" name="edit_salary">
</div>








<div class="form-group">
                    <label for="salary" class="control-label">Age:</label>
                    <input type="text" class="form-control" id="edit_age" name="edit_age">
</div>




                
            </form></div>








<div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
                <button type="button" id="btn_edit" class="btn btn-primary">Save</button>
</div>




   
</div>




</div>




</div>

Step 2: Added callback method in buttons array under flexigrid constructor,

,
                    {
                        name : 'Edit',
                        bclass : 'edit',
                        onpress : gridAction
                    }

here we have mentioned gridAction() function which will call on edit button click event.

Step 3: Added java-script method gridAction() which will handle add record flexigrid event.

function gridAction(com, grid) {
    else if (com == 'Edit') {
     $('#edit_model').modal('show');
     if($('.trSelected', grid).length >0) {
      
                        $.each($('.trSelected', grid),
                            function(key, value){
       
                                // collect the data
                                $('#edit_id').val(value.children[0].innerText); // in case we're changing the key
                                $('#edit_name').val(value.children[1].innerText);
                                $('#edit_salary').val(value.children[2].innerText);
                                $('#edit_age').val(value.children[3].innerText);
                        }); 
     } else {
      alert('Now row selected! First select row, then click edit button');
     }
     
                }

Step 4: Added click event on submit button to edit modal input values.

$( "#btn_edit" ).click(function() {
     ajaxAction('edit');
   });

Step 5: Added updateEmployee() method into switch option in response.php file.

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

Step 6: Added add action method in response.php file which will update record in mysql database and send status.

function updateEmployee($params) {
  $data = array();
  //print_R($_POST);die;
  $sql = "Update `employee` set employee_name = '" . $params["edit_name"] . "', employee_salary='" . $params["edit_salary"]."', employee_age='" . $params["edit_age"] . "' WHERE id='".$_POST["edit_id"]."'";
  
  echo $result = mysqli_query($this->conn, $sql) or die("error to update employee data");
 }

How to add Delete record in Flexigrid

You need to select row in flexigrid then click delete record button otherwise you will get 0 row selected message.

Step 1: Added callback method in buttons array under flexigrid constructor,

,
                    {
                        name : 'Delete',
                        bclass : 'delete',
                        onpress : gridAction
                    }

here we have mentioned gridAction() method which will call on delete button clicked event.

Step 2: Added JavaScript method gridAction() which will handle delete record flexigrid event.

function gridAction(com, grid) {
    else if(com == 'Delete') {
     var conf = confirm('Delete ' + $('.trSelected', grid).length + ' items?');
     alert(conf);
                    if(conf){
                        $.each($('.trSelected', grid),
                            function(key, value){
                                $.post('response.php', { id: value.firstChild.innerText, action:com.toLowerCase()}
                                    , function(){
                                        // when ajax returns (callback), 
          $("#employees").flexReload();
                                });
                        });    
                    }
    } 
     
                }

Step 3: Added deleteEmployee()method into a switch option in response.php file.

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

Step 4: Added add action method in response.php file which will insert record in MySQL database and send status.

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

You can download source code and Demo from below link.

Conclusion :

In previous tutorial, we have learn listing of flexigrid with PHP and MySQL.This tutorials help to add crud functionality with flexigrid using ajax.I ha demonstrate about how to add, edit and delete row using php,mysql and ajax.