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

jQuery Bootgrid is very popular grid plugin and especially 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. Bootgrid is very rich jQuery grid plugin which is used to convert a simple HTML table into grid with powerful features like sorting,pagination,searching, add record,edit record and delete record functionality. We will cover below listed features using PHP, MySQL and ajax.I done many googling but do not found any example which has below features,

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

You can bind data in bootgrid, Either you use the server-side way (e.g. a REST service) for querying a dynamic data source like a database or you use the client-side way for simple in-memory data.

jQuery Bootgrid dependent library is jquery and bootstrap. We will learn how to add,edit and delete records in bootgrid using php, mysql and ajax.

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

I am using following files and folder

dist folder: This folder will contains all css and js libs of jquery,bootstrap and bootgrid.
font folder: This folder will contains all fonts related files.
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 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,

How to add listing in Bootgrid

Step 1: Created connection 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;
	}
}

?>

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

$servername: your mysql db hostname
$username: mysql db username
$password: mysql db pass
$dbname: database name

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

<link rel="stylesheet" href="dist/bootstrap.min.css" type="text/css" media="all">
<link href="dist/jquery.bootgrid.css" rel="stylesheet" />
<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: Create HTML table for listing data in index.php file.

<div class="col-sm-8">
		<div class="well clearfix">
			<div class="pull-right"><button type="button" class="btn btn-xs btn-primary" id="command-add" data-row-id="0">
			<span class="glyphicon glyphicon-plus"></span> 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>

<span class="glyphicon glyphicon-plus"></span> Record</button></div></div>

<thead>

<tr>

<th data-column-id="id" data-type="numeric" data-identifier="true">Empid</th>

<th data-column-id="employee_salary">Salary</th>

<th data-column-id="commands" data-formatter="commands" data-sortable="false">Commands</th>

</tr>

</thead>

</table>

</div>

Here we have added a 'Add record' button which will use to create new record and table for listing records.

Step 4: Applied Bootgrid constructor method on HTML table.The Bootgrid method converts HTML table into 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 type=\"button\" class=\"btn btn-xs btn-default command-edit\" data-row-id=\"" + row.id + "\"><span class=\"glyphicon glyphicon-edit\"></span></button> " + 
                    "<button type=\"button\" class=\"btn btn-xs btn-default command-delete\" data-row-id=\"" + row.id + "\"><span class=\"glyphicon glyphicon-trash\"></span></button>";
            }
        }
   })

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 type=\"button\" class=\"btn btn-xs btn-default command-edit\" data-row-id=\"" + row.id + "\"><span class=\"glyphicon glyphicon-edit\"></span></button> " +

"<button type=\"button\" class=\"btn btn-xs btn-default command-delete\" data-row-id=\"" + row.id + "\"><span class=\"glyphicon glyphicon-trash\"></span></button>";

}

})

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

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: 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">&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>

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

</div>

</div>

<label for="salary" class="control-label">Salary:</label>

</div>

</div>

<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>

</div>

</form>

</div>

Step 2: Added callback method in 'add record' button to open add modal box.

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

$( "#command-add" ).click(function() {

$('#add_model').modal('show');

});

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

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

$( "#btn_add" ).click(function() {

ajaxAction('add');

});

Step 4: 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');
          $("#employee_grid").bootgrid('reload');
          }   
        });
			}

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 6: Added a switch option in response.php file.

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

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

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 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">&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>

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

</div>

</div>

<label for="salary" class="control-label">Salary:</label>

</div>

</div>

<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>

</div>

</form>

</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');
          }
    })
})

.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 of clicked row which you want to edit and shows value into modal box.

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

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

$( "#btn_edit" ).click(function() {

ajaxAction('edit');

});

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

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

case 'edit':

$empCls->updateEmployee($params);

break;

Step 5: 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");
	}

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 need to select row in Bootgrid then click delete record button icon, Each row of bootgrid has edit and delete row icon and bonded action on them.

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"))
                    }
    })

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

case 'delete':

$empCls->deleteEmployee($params);

break;

Step 3: 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");
	}

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.

Live Demo

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.

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

I am using following files and folder

How to add listing in Bootgrid

How to add insert record in Bootgrid

How to add Edit record in Bootgrid

How to add Delete record in Bootgrid

How To Use Downloaded Source Code

Conclusion :

Sponsors