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> Add Employee</button>
<button class="btn btn-info" type="button" id="btn-view"> <span class="glyphicon glyphicon-eye-open"></span> 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 🙂