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.in previous post 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.

jQuery flexigrid 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

add-edit-flexigrid

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 retun back json response to ajax method.

Also Checkout other tutorials of flexi grid,

How to add listing in Flexigrid

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

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

?>

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 index.php file.

Select Code
1
2
3
4
5
<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.

Select Code
1
2
3
<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.

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
$("#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.

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
50
51
52
53
54
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 add insert 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 heade of index.php file.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<style type="text/css">
  .flexigrid div.fbutton .add {
        background: url(images/add.png) no-repeat center left;
    }

    .flexigrid div.fbutton .delete {
        background: url(images/close.png) no-repeat center left;
    }

    .flexigrid div.fbutton .edit {
        background: url(images/edit.png) no-repeat center left;
    }
  </style>
  <title>Simple Flexigrid example with crud features using JSON</title>
</head>

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

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
<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">&times;</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>
                
            </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>
            </form>
        </div>
    </div>
</div>

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

Select Code
1
2
3
4
5
{
     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.

Select Code
1
2
3
4
function gridAction(com, grid) {
                if (com == 'Add') {
                    $('#add_model').modal('show');      
                }

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

Select Code
1
2
3
$( "#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.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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.

Select Code
1
2
3
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.

Select Code
1
2
3
4
5
6
7
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 add Edit record in Flexigrid

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

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
<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">&times;</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>
                
            </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>
            </form>
        </div>
    </div>
</div>

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

Select Code
1
2
3
4
5
6
,
                    {
                        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.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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.

Select Code
1
2
3
$( "#btn_edit" ).click(function() {
              ajaxAction('edit');
            });

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

Select Code
1
2
3
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.

Select Code
1
2
3
4
5
6
7
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 msg.

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

Select Code
1
2
3
4
5
6
,
                    {
                        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 add record flexigrid event.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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 a switch option in response.php file.

Select Code
1
2
3
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.

Select Code
1
2
3
4
5
6
7
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 have demonstrate about how to add,edit and delete row using php,mysql and ajax.

  • Jayric Conde

    Thanks a lot for this.all works well but the search function did not work. help please. 😀 TIA

    • i hvnt added search in this tuts,but you can add search params in querystring and rebind grid

      • Jayric Conde

        thanks for an update man. i used your codes right now. but the delete function has a little problem wont work.

      • Jayric Conde

        problem solved. i just removed the comment Thanks Again! 😀