In this tutorial we will create a
Simple Live CRUD Operation In HTML Table Using PHP/jQuery. jQuery is a cross-platform JavaScript library designed to simplify the client-side scripting of HTML. It is designed to make it easier to navigate a document, via classes and id's attribute. So let's now do the coding...
Before we started:
First you have to download & install WAMPserver or any local server that run PHP scripts. Here's the link for WAMP server
http://www.wampserver.com/en/.
Creating the database Connection
This is the where the database connection, just simple copy/paste the provided code below.
<?php
$conn = new mysqli('localhost', 'root', '', 'phptut');
if(!$conn){
die("Fatal Error: Connection Error!"); }
?>
The Main Interface
This is the where the database connection, just simple copy/paste the provided code below.
<!DOCTYPE html>
<html lang = "en">
<head>
<title>PHP - Live CRUD Operation In HTML Table</title>
<link rel
= "stylesheet" type
= "text/css" href
= "css/bootstrap.css" /> <meta charset = "UTF-8" name = "viewport" content = "width=device-width, initial-scale=1"/>
</head>
<body>
<nav class = "navbar navbar-default">
<div class = "container-fluid">
<a class = "navbar-brand" href = "https://sourcecodester.com">Sourcecodester</a>
</div>
</nav>
<div class = "col-md-3"></div>
<div class = "col-md-6 well">
<h3 class = "text-primary">PHP - Live CRUD Operation In HTML Table</h3>
<hr style = "border-top:1px solid #000;" />
<div id = "data"></data>
</div>
</body>
<script src = "js/jquery-3.2.1.js"></script>
<script src = "js/script.js"></script>
</html>
The Read Script
This is where the code display all the data. To do that just copy/paste the code below.
<?php
require 'connect.php';
$result = '';
$query = $conn->query("SELECT * FROM `member`");
$result .= '
<table class = "table table-bordered">
<thead>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Address</th>
<th>Action</th>
</tr>
</thead>
<tbody>
';
if($query->num_rows > 0){
while($fetch = $query->fetch_array()){
$result .='
<tr>
<td class = "firstname" data-firstname = '.$fetch['mem_id'].' contenteditable >'.$fetch['firstname'].'</td>
<td class = "lastname" data-lastname = '.$fetch['mem_id'].' contenteditable>'.$fetch['lastname'].'</td>
<td class = "address" data-address = '.$fetch['mem_id'].' contenteditable>'.$fetch['address'].'</td>
<td><center><button class = "btn btn-danger btn_delete" name = "'.$fetch['mem_id'].'"><span class = "glyphicon glyphicon-remove"></span></button></center></td>
</tr>
';
}
$result .='
<tr>
<td id = "firstname" contenteditable></td>
<td id = "lastname" contenteditable></td>
<td id = "address" contenteditable></td>
<td><center><button class = "btn btn-success" id = "btn_add"><span class = "glyphicon glyphicon-plus"></span></button></center></td>
</tr>
';
}else{
$result .='
<tr>
<td id = "firstname" contenteditable></td>
<td id = "lastname" contenteditable></td>
<td id = "address" contenteditable></td>
<td><center><button class = "btn btn-success" id = "btn_add"><span class = "glyphicon glyphicon-plus"></span></button></center></td>
</tr>
';
}
$result .= '
</tbody>
</table>
';
echo $result;
?>
The Create Script
This is where the code insert the data to database server. To do that copy/paste the code below.
<?php
require 'connect.php';
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$address = $_POST['address'];
$query = $conn->query("INSERT INTO `member` (firstname, lastname, address) VALUES('$firstname', '$lastname', '$address')");
echo 'Data Inserted';
?>
The Update Script
This is where the code update all the data when the highlighted field is being edited. To do that just simply copy/paste the code below.
<?php
require 'connect.php';
$id = $_POST['id'];
$text = $_POST['text'];
$column = $_POST['column'];
if($column == "firstname"){
$query = $conn->query("UPDATE `member` SET `firstname` = '$text' WHERE `mem_id` = '$id'");
}
if($column == "lastname"){
$query = $conn->query("UPDATE `member` SET `lastname` = '$text' WHERE `mem_id` = '$id'");
}
if($column == "address"){
$query = $conn->query("UPDATE `member` SET `address` = '$text' WHERE `mem_id` = '$id'");
}
echo "Data Updated";
?>
The Delete Script
This is where the code delete the row of the data from database server. Just copy/paste the code below.
<?php
require "connect.php";
$mem_id = $_POST['mem_id'];
$conn->query("DELETE FROM `member` WHERE `mem_id` = '$mem_id'");
echo "Data Deleted!";
?>
The jQuery Script
This script handles all the necessary call function for all the PHP script. To make this happen just simple copy/paste the code below.
$(document).ready(function(){
ReadData();
function ReadData(){
$.ajax({
url: "select.php",
method: "POST",
success: function(data){
$('#data').html(data);
}
})
}
$(document).on('click', '#btn_add', function(){
var firstname = $('#firstname').text();
var lastname = $('#lastname').text();
var address = $('#address').text();
$.ajax({
url: "insert.php",
method: "POST",
dataType: "text",
data:{
firstname: firstname, lastname: lastname, address: address
},
success: function(data){
alert(data);
ReadData();
}
});
});
$(document).on('blur', '.firstname', function(){
var id = $(this).data("firstname");
var firstname = $(this).text();
UpdateData(id, firstname, 'firstname');
});
$(document).on('blur', '.lastname', function(){
var id = $(this).data("lastname");
var lastname = $(this).text();
UpdateData(id, lastname, 'lastname');
});
$(document).on('blur', '.address', function(){
var id = $(this).data("address");
var address = $(this).text();
UpdateData(id, address, 'address');
});
$(document).on('click', '.btn_delete', function(){
var id = $(this).attr('name');
$.ajax({
url: 'delete.php',
method: "POST",
data:{
mem_id: id
},
success: function(data){
alert(data);
ReadData();
}
});
});
function UpdateData(id, text, column){
$.ajax({
url: "update.php",
method: "POST",
data: {
id: id, text: text, column: column
},
success: function(data){
}
});
}
});
There you have it we just create a
Simple Live CRUD Operation In HTML Table. I hope that this simple tutorial help to your projects. For more updates and tutorials just kindly visit this site. Enjoy Coding!!!