Simple jQuery Add, Update, Delete with PHP and MySQL

codernew
In this tutorial we will cover a simple Insert, Update and Delete using jQuery, PHP and MySQL with PDO, well we have lot’s of CRUD tutorials on this blog but we haven’t jQuery CRUD tutorial yet, and few email requests i received from readers regarding jQuery Insert, Update, Delete with PHP MySQL, so here in this tutorial we will do it, using jQuery to perform such operations become easy and for the better user interface bootstrap is here i have used, let’s start.

Simple jQuery Insert, Update, Delete with PHP & MySQL

Database & Table

i have created employees table here in “jquery_crud” database, just create “jquery_crud” database in your PHPMyAdmin and paste following SQL code it will create the table. 


CREATE TABLE IF NOT EXISTS `tbl_employees` (
  `emp_id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_name` varchar(25) NOT NULL,
  `emp_dept` varchar(50) NOT NULL,
  `emp_salary` varchar(7) NOT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

dbconfig.php

as usual simple database configuration file with PDO extension. 

<?php

 $db_host = "localhost";
 $db_name = "jquery_crud";
 $db_user = "root";
 $db_pass = "";
 
 try{
  
  $db_con = new PDO("mysql:host={$db_host};dbname={$db_name}",$db_user,$db_pass);
  $db_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 }
 catch(PDOException $e){
  echo $e->getMessage();
 }

?>

index.php

i know it looks little lengthy but to use bootstrap design and jquery functions we have to add, contains code which displays MySQL employee records from the table, little jQuery i have used here it will used to load insert, update form directly without page refresh, MySQL records will be displayed within jQuery Datatable. 


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Insert, Update, Delete using jQuery, PHP and MySQL</title>
<link href="bootstrap/css/bootstrap.min.css" rel="stylesheet" media="screen">
<link href="bootstrap/css/bootstrap-theme.min.css" rel="stylesheet" media="screen"> 
<link href="assets/datatables.min.css" rel="stylesheet" type="text/css">

<script type="text/javascript" src="assets/jquery-1.11.3-jquery.min.js"></script>

<script type="text/javascript">
$(document).ready(function(){
 
 $("#btn-view").hide();
 
 $("#btn-add").click(function(){
  $(".content-loader").fadeOut('slow', function()
  {
   $(".content-loader").fadeIn('slow');
   $(".content-loader").load('add_form.php');
   $("#btn-add").hide();
   $("#btn-view").show();
  });
 });
 
 $("#btn-view").click(function(){
  
  $("body").fadeOut('slow', function()
  {
   $("body").load('index.php');
   $("body").fadeIn('slow');
   window.location.href="index.php";
  });
 });
 
});
</script>

</head>

<body>
    

 <div class="container">
      
        <h2 class="form-signin-heading">Employee Records.</h2><hr />
        <button class="btn btn-info" type="button" id="btn-add"> <span class="glyphicon glyphicon-pencil"></span> &nbsp; Add Employee</button>
        <button class="btn btn-info" type="button" id="btn-view"> <span class="glyphicon glyphicon-eye-open"></span> &nbsp; View Employee</button>
        <hr />
        
        <div class="content-loader">
        
        <table cellspacing="0" width="100%" id="example" class="table table-striped table-hover table-responsive">
        <thead>
        <tr>
        <th>Emp ID</th>
        <th>Emp Name</th>
        <th>department</th>
        <th>salary</th>
        <th>edit</th>
        <th>delete</th>
        </tr>
        </thead>
        <tbody>
        <?php
        require_once 'dbconfig.php';
        
        $stmt = $db_con->prepare("SELECT * FROM tbl_employees ORDER BY emp_id DESC");
        $stmt->execute();
  while($row=$stmt->fetch(PDO::FETCH_ASSOC))
  {
   ?>
   <tr>
   <td><?php echo $row['emp_id']; ?></td>
   <td><?php echo $row['emp_name']; ?></td>
   <td><?php echo $row['emp_dept']; ?></td>
   <td><?php echo $row['emp_salary']; ?></td>
   <td align="center">
   <a id="<?php echo $row['emp_id']; ?>" class="edit-link" href="#" title="Edit">
   <img src="edit.png" width="20px" />
            </a></td>
   <td align="center"><a id="<?php echo $row['emp_id']; ?>" class="delete-link" href="#" title="Delete">
   <img src="delete.png" width="20px" />
            </a></td>
   </tr>
   <?php
  }
  ?>
        </tbody>
        </table>
        
        </div>

    </div>
    
<script src="bootstrap/js/bootstrap.min.js"></script>
<script type="text/javascript" src="assets/datatables.min.js"></script>
<script type="text/javascript" src="crud.js"></script>

<script type="text/javascript" charset="utf-8">
$(document).ready(function() {
 $('#example').DataTable();

 $('#example')
 .removeClass( 'display' )
 .addClass('table table-bordered');
});
</script>
</body>
</html>

add_form.php

simple html form to insert employee records contains three text box to enter employee name, department and salary, it will be loaded within “index.php” by clicking “add employee” button. 


<style type="text/css">
#display{
 display:none;
}
</style>
    
    <div id="display">
    <!-- here message will be displayed -->
 </div>
        
  
  <form method='post' id='emp-SaveForm' action="#">
 
    <table class='table table-bordered'>
 
        <tr>
            <td>Employee Name</td>
            <td><input type='text' name='emp_name' class='form-control' placeholder='EX : john doe' required /></td>
        </tr>
 
        <tr>
            <td>Employee Department</td>
            <td><input type='text' name='emp_dept' class='form-control' placeholder='EX : Web Design, App Design' required></td>
        </tr>
 
        <tr>
            <td>Employee Salary</td>
            <td><input type='text' name='emp_salary' class='form-control' placeholder='EX : 180000' required></td>
        </tr>
 
        <tr>
            <td colspan="2">
            <button type="submit" class="btn btn-primary" name="btn-save" id="btn-save">
      <span class="glyphicon glyphicon-plus"></span> Save this Record
   </button>  
            </td>
        </tr>
 
    </table>
</form>

create.php

this will insert a new record using jQuery into tbl_employees, on submit button click #emp-SaveForm form will be submitted using jQuery. 


<?php
require_once 'dbconfig.php';
 
 if($_POST)
 {
  $emp_name = $_POST['emp_name'];
  $emp_dept = $_POST['emp_dept'];
  $emp_salary = $_POST['emp_salary'];
  
  try{
   
   $stmt = $db_con->prepare("INSERT INTO tbl_employees(emp_name,emp_dept,emp_salary) VALUES(:ename, :edept, :esalary)");
   $stmt->bindParam(":ename", $emp_name);
   $stmt->bindParam(":edept", $emp_dept);
   $stmt->bindParam(":esalary", $emp_salary);
   
   if($stmt->execute())
   {
    echo "Successfully Added";
   }
   else{
    echo "Query Problem";
   } 
  }
  catch(PDOException $e){
   echo $e->getMessage();
  }
 }
?>

edit_form.php

to fetch editable data from index.php and the data will be set within the following text box to update, this will loaded too within index.php file in #container div, this jquery code will set QueryString to edit_form.php : $(“.content-loader”).load(‘edit_form.php?edit_id=’+edit_id); 


<?php
include_once 'dbconfig.php';

if($_GET['edit_id'])
{
 $id = $_GET['edit_id']; 
 $stmt=$db_con->prepare("SELECT * FROM tbl_employees WHERE emp_id=:id");
 $stmt->execute(array(':id'=>$id)); 
 $row=$stmt->fetch(PDO::FETCH_ASSOC);
}

?>
<style type="text/css">
#dis{
 display:none;
}
</style>
    
    <div id="dis">
    
 </div>
         
  <form method='post' id='emp-UpdateForm' action='#'>
 
    <table class='table table-bordered'>
   <input type='hidden' name='id' value='<?php echo $row['emp_id']; ?>' />
        <tr>
            <td>Employee Name</td>
            <td><input type='text' name='emp_name' class='form-control' value='<?php echo $row['emp_name']; ?>' required></td>
        </tr>
 
        <tr>
            <td>Employee Department</td>
            <td><input type='text' name='emp_dept' class='form-control' value='<?php echo $row['emp_dept']; ?>' required></td>
        </tr>
 
        <tr>
            <td>Employee Salary</td>
            <td><input type='text' name='emp_salary' class='form-control' value='<?php echo $row['emp_salary']; ?>' required></td>
        </tr>
 
        <tr>
            <td colspan="2">
            <button type="submit" class="btn btn-primary" name="btn-update" id="btn-update">
      <span class="glyphicon glyphicon-plus"></span> Save Updates
   </button>
            </td>
        </tr>
 
    </table>
</form>

update.php

simple file which will update the selected row from the “edit_form.php” and this will be loaded too via a jQuery on submit function. 


<?php
require_once 'dbconfig.php';
 
 if($_POST)
 {
  $id = $_POST['id'];
  $emp_name = $_POST['emp_name'];
  $emp_dept = $_POST['emp_dept'];
  $emp_salary = $_POST['emp_salary'];
  
  $stmt = $db_con->prepare("UPDATE tbl_employees SET emp_name=:en, emp_dept=:ed, emp_salary=:es WHERE emp_id=:id");
  $stmt->bindParam(":en", $emp_name);
  $stmt->bindParam(":ed", $emp_dept);
  $stmt->bindParam(":es", $emp_salary);
  $stmt->bindParam(":id", $id);
  
  if($stmt->execute())
  {
   echo "Successfully updated";
  }
  else{
   echo "Query Problem";
  }
 }
?>

delete.php

this file will delete rows from mysql – a simple code loaded via jQuery and delete rows from mysql without page refresh. id will be get through this function : $.post(‘delete.php’, {‘del_id’:del_id} 


<?php
include_once 'dbconfig.php';

if($_POST['del_id'])
{
 $id = $_POST['del_id']; 
 $stmt=$db_con->prepare("DELETE FROM tbl_employees WHERE emp_id=:id");
 $stmt->execute(array(':id'=>$id)); 
}
?>

crud.js

finally here is the complete jQuery file which will responsible to perform Insert, Update and Delete contains only jQuery/JavaScript code. 


// JavaScript Document

$(document).ready(function(){
 
 /* Data Insert Starts Here */
 $(document).on('submit', '#emp-SaveForm', function() {
   
    $.post("create.php", $(this).serialize())
        .done(function(data){
   $("#dis").fadeOut();
   $("#dis").fadeIn('slow', function(){
     $("#dis").html('<div class="alert alert-info">'+data+'</div>');
        $("#emp-SaveForm")[0].reset();
       }); 
   });   
      return false;
    });
 /* Data Insert Ends Here */
 
 
 /* Data Delete Starts Here */
 $(".delete-link").click(function()
 {
  var id = $(this).attr("id");
  var del_id = id;
  var parent = $(this).parent("td").parent("tr");
  if(confirm('Sure to Delete ID no = ' +del_id))
  {
   $.post('delete.php', {'del_id':del_id}, function(data)
   {
    parent.fadeOut('slow');
   }); 
  }
  return false;
 });
 /* Data Delete Ends Here */
 
 /* Get Edit ID  */
 $(".edit-link").click(function()
 {
  var id = $(this).attr("id");
  var edit_id = id;
  if(confirm('Sure to Edit ID no = ' +edit_id))
  {
   $(".content-loader").fadeOut('slow', function()
    {
    $(".content-loader").fadeIn('slow');
    $(".content-loader").load('edit_form.php?edit_id='+edit_id);
    $("#btn-add").hide();
    $("#btn-view").show();
   });
  }
  return false;
 });
 /* Get Edit ID  */
 
 /* Update Record  */
 $(document).on('submit', '#emp-UpdateForm', function() {
  
    $.post("update.php", $(this).serialize())
        .done(function(data){
   $("#dis").fadeOut();
   $("#dis").fadeIn('slow', function(){
        $("#dis").html('<div class="alert alert-info">'+data+'</div>');
        $("#emp-UpdateForm")[0].reset();
     $("body").fadeOut('slow', function()
     {
     $("body").fadeOut('slow');
     window.location.href="index.php";
     });     
       }); 
  });   
     return false;
    });
 /* Update Record  */
});

if you have any query regarding this tutorial fill free to contact me, download this jQuery Add, Update, Delete tutorial and try it, that’s it isn’t it simple 🙂