Add,Edit and Delete Record using Bootgrid , PHP and MySQL

jQuery Bootgrid is a very popular grid plugin and specially designed for bootstrap. This tutorial help to add .edit and delete records using PHP and MySQL. I am using AJAX technology to add, edit and delete records with MySQL using Bootgrid.

Bootgrid is a powerful jQuery grid plugin that converts a simple HTML table into a grid with powerful features such as sorting, pagination, searching, add a record, edit a record, and delete the record functionality. Using PHP, MySQL, and ajax.

We will go over the features listed below. I did a lot of googling but couldn’t find any examples with the following characteristics.

• Bootgrid listing Using Ajax
• Bootgrid listing with search server-side using Ajax
• Enable ajax pagination
• Enable Sorting
• Enable Command button with for-matter function
• Enable server side dynamic pagination
• Add records using bootstrap ModalBox and ajax
• Edit records using bootstrap ModalBox and ajax
• Delete records using bootstrap ModalBox and ajax

In bootgrid, you can bind data in two ways: server-side (via a REST service) for querying a dynamic data source such as a database, or client-side (via simple in-memory data).

jQuery and Bootstrap are Bootgrid’s dependent libraries. We will learn how to use PHP, MySQL, and ajax to add, edit, and delete records in bootgrid.

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

I am using the following files and folder:

• dist folder: This folder will contain all css and js libs of jquery, bootstrap and bootgrid.
• font folder: This folder will contain all fonts related files.
• index.php: This file will use to create an HTML layout and handle ajax response.
• connection.php: This file will use to create a database connection.
• response.php: This file will use to handle all server side functionality. I will create add, edit, delete action methods and return back json response to ajax method.

Also Checkout other tutorials of grid,

• Simple Example Bootgrid (Server Side) with PHP, MySQL and Ajax
• How to Add, Edit and Delete a row in jQuery Flexigrid Using PHP and MySQL
• Datatable Pagination, Sorting and Search – Server Side (PHP/MySQl) Using Ajax

How to Add Listing in Bootgrid

Step 1: Created a connection file to handle the database connection and return the database 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-&gt;username, $this-&gt;password, $this-&gt;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-&gt;conn = $con;
  }
  return $this-&gt;conn;
 }
}

?>

Where is:

1. $servername: This variable contains your MySQL db hostname
2. $username: This is the MySQL Database username.
3. $password: This is the MySQL Database password.
4. $dbname: This is the MySQL Database name.

Step 2: Included all js and css files into header of the index.php file.

<script src="dist/jquery-1.11.1.min.js"></script>
<script src="dist/bootstrap.min.js"></script>
<script src="dist/jquery.bootgrid.min.js"></script>

Step 3: Created an HTML table to display listing data into the index.php file.

<div class="col-sm-8">
<div class="well clearfix">
<div class="pull-right"><button id="command-add" class="btn btn-xs btn-primary" type="button" data-row-id="0"> Record</button></div>
</div>
<table id="employee_grid" class="table table-condensed table-hover table-striped" width="60%" cellspacing="0" data-toggle="bootgrid">
<thead>
<tr>
<th data-column-id="id" data-type="numeric" data-identifier="true">Empid</th>
<th data-column-id="employee_name">Name</th>
<th data-column-id="employee_salary">Salary</th>
<th data-column-id="employee_age">Age</th>
<th data-column-id="commands" data-formatter="commands" data-sortable="false">Commands</th>
</tr>
</thead>
</table>
</div>

Here, we’ve added a 'Add record' button that will be used to create a new record and a table for listing records.

Step 4: Applied Bootgrid constructor method on HTML table.The Bootgrid method transforms an HTML table into a beautiful grid listing.

var grid = $("#employee_grid").bootgrid({
    ajax: true,
    rowSelect: true,
    post: function ()
    {
      /* To accumulate custom parameter with the request object */      return {
        id: "b0df282a-0d67-40e5-8558-c9e93b7befed"
      };
    },
    
    url: "response.php",
    formatters: {
            "commands": function(column, row)
            {
                return "<button id="" class="\&quot;btn" type="\&quot;button\&quot;" data-row-id="\&quot;&quot;"></button> " + 
                    "<button id="" class="\&quot;btn" type="\&quot;button\&quot;" data-row-id="\&quot;&quot;"></button>";
            }
        }
   })

I added the above jquery code to the footer of the index.php file.

Step 5: Let’s add action handler method into the 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($params) {
    $rp = isset($params['rowCount']) ? $params['rowCount'] : 10;
    
    if (isset($params['current'])) { $page  = $params['current']; } else { $page=1; };  
        $start_from = ($page-1) * $rp;
    
    $sql = $sqlRec = $sqlTot = $where = '';
    
    if( !empty($params['searchPhrase']) ) {   
      $where .=" WHERE ";
      $where .=" ( employee_name LIKE '".$params['searchPhrase']."%' ";    
      $where .=" OR employee_salary LIKE '".$params['searchPhrase']."%' ";

      $where .=" OR employee_age LIKE '".$params['searchPhrase']."%' )";
     }
     
     // getting total number records without any search
    $sql = "SELECT * FROM `employee` ";
    $sqlTot .= $sql;
    $sqlRec .= $sql;
    
    //concatenate search sql if value exist
    if(isset($where) && $where != '') {

      $sqlTot .= $where;
      $sqlRec .= $where;
    }
    if ($rp!=-1)
    $sqlRec .= " LIMIT ". $start_from .",".$rp;
    
    
    $qtot = mysqli_query($this->conn, $sqlTot) or die("error to fetch tot employees data");
    $queryRecords = mysqli_query($this->conn, $sqlRec) or die("error to fetch employees data");
    
    while( $row = mysqli_fetch_assoc($queryRecords) ) { 
      $data[] = $row;
    }

    $json_data = array(
      "current"            => intval($params['current']), 
      "rowCount"            => 10,      
      "total"    => intval($qtot->num_rows),
      "rows"            => $data   // total data array
      );
    
    return $json_data;
  }

How to add insert record in Bootgrid

Step 1: In the index.php file, I added a Bootstrap add modal box to add a record.

<div id="add_model" class="modal fade">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header"><button class="close" type="button" data-dismiss="modal" aria-hidden="true">×</button>
<h4 class="modal-title">Add Employee</h4>
</div>
<div class="modal-body"><form id="frm_add" method="post"><input id="action" name="action" type="hidden" value="add" />
<div class="form-group"><label class="control-label" for="name">Name:</label> <input id="name" class="form-control" name="name" type="text" /></div>
<div class="form-group"><label class="control-label" for="salary">Salary:</label> <input id="salary" class="form-control" name="salary" type="text" /></div>
<div class="form-group"><label class="control-label" for="salary">Age:</label> <input id="age" class="form-control" name="age" type="text" /></div>
</form></div>
<div class="modal-footer"><button class="btn btn-default" type="button" data-dismiss="modal">Close</button> <button id="btn_add" class="btn btn-primary" type="button">Save</button></div>
</div>
</div>
</div>

Step 2: To open the add modal box, I added a callback method to the 'add record' button.

$( "#command-add" ).click(function() {
    $('#add_model').modal('show');
});

Step 3: Added a submit button event to add modal input values.

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

Step 4: Added the ajaxAction() method, which will send an ajax request to the server. Using ajax technology, we will send json data to the server.

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

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

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

Step 6: In the response.php file, an add action method has been added that will insert a record into the MySQL database and send the 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 add Edit record in Bootgrid

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

<div id="edit_model" class="modal fade">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header"><button class="close" type="button" data-dismiss="modal" aria-hidden="true">×</button>
<p>&nbsp;</p>
<h4 class="modal-title">Edit Employee</h4>
</div>
<div class="modal-body"><form id="frm_edit" method="post"><input id="action" name="action" type="hidden" value="edit"> <input id="edit_id" name="edit_id" type="hidden" value="0">
<p>&nbsp;</p>
<div class="form-group"><label class="control-label" for="name">Name:</label> <input id="edit_name" class="form-control" name="edit_name" type="text"></div>
<div class="form-group"><label class="control-label" for="salary">Salary:</label> <input id="edit_salary" class="form-control" name="edit_salary" type="text"></div>
<div class="form-group"><label class="control-label" for="salary">Age:</label> <input id="edit_age" class="form-control" name="edit_age" type="text"></div>
</form></div>
<div class="modal-footer"><button class="btn btn-default" type="button" data-dismiss="modal">Close</button> <button id="btn_edit" class="btn btn-primary" type="button">Save</button></div>
</div>
</div>
</div>

Step 2: Added callback method on event of Bootgrid contsructor,

.on("loaded.rs.jquery.bootgrid", function()
{
    /* Executes after data is loaded and rendered */    grid.find(".command-edit").on("click", function(e)
    {
        //alert("You pressed edit on row: " + $(this).data("row-id"));
      var ele =$(this).parent();
      var g_id = $(this).parent().siblings(':first').html();
            var g_name = $(this).parent().siblings(':nth-of-type(2)').html();
    
    $('#edit_model').modal('show');
          if($(this).data("row-id") >0) {
              
                                // collect the data
                                $('#edit_id').val(ele.siblings(':first').html()); // in case we're changing the key
                                $('#edit_name').val(ele.siblings(':nth-of-type(2)').html());
                                $('#edit_salary').val(ele.siblings(':nth-of-type(3)').html());
                                $('#edit_age').val(ele.siblings(':nth-of-type(4)').html());
          } else {
           alert('Now row selected! First select row, then click edit button');
          }
    })
})

We are collecting all data from the clicked row that you want to edit and display in the modal box.

Step 3: To edit the modal input values, a click event on the submit button has been added.

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

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

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

Step 5: In the response.php file, an add action method has been added that will update the record in the MySQL database and send the 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 Bootgrid

You must first select a row in Bootgrid and then click the delete record button icon. Each row in Bootgrid has an edit and delete row icon as well as a bound action.

Step 1: Added delete callback method under bootgrid constructor,

.end().find(".command-delete").on("click", function(e)
    {
  
    var conf = confirm('Delete ' + $(this).data("row-id") + ' items?');
          alert(conf);
                    if(conf){
                                /*$.post('response.php', { id: $(this).data("row-id"), action:'delete'}
                                    , function(){
                                        // when ajax returns (callback), 
                    $("#employee_grid").bootgrid('reload');
                                });  */                //$(this).parent('tr').remove();
                //$("#employee_grid").bootgrid('remove', $(this).data("row-id"))
                    }
    })

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

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

Step 3: In the response.php file, an add action method has been added that will delete a record from the MySQL database and send the 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.

How To Use Downloaded Source Code

Conclusion :

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