This Php Ajax Project will teach you how to do basic database functions that are CREATE RETIEVE UPDATE and DELETE. using Mysql Database using Ajax. The INSERT, SELECT, UPDATE and DELETE statements can be used in any database system, because this is support by all relational database systems.The project is very helpful for learn Ajax technology.
All the libraries i put in to one folder which name is components.
https://drive.google.com/drive/folders/1Wn6kACTV24qFw6ztKUcfQ9CsQUF9PFF1
how to download it i attached the screen shot image below.
First Step Establish the Database Connection
Create the Page db.php.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "studcrud";
$conn = new mysqli($servername,$username,$password,$dbname);
if($conn->connect_error)
{
die("connection failed" .$conn->connect_error);
}
?>After that design the index page here we are going to implement the crud opertation.
Index.php
<html>
<head>
<link rel="stylesheet" href="components/bootstrap/dist/css/bootstrap.css">
<link rel="stylesheet" href="components/bootstrap/dist/css/bootstrap.min.css">
<link rel="stylesheet" href="components/jquery-confirm-master/css/jquery-confirm.css">
<link rel="stylesheet" href="http://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css">
</head>
<body>
<nav class="nav navbar-inverse">
<div class="container-fluid">
<div class="navbar-header" align="center">
<a class="navbar-brand" href="#"><b>Simple Student Management Crud</b></a>
</div>
</div>
</nav>
</br></br>
<div class="container-fluid bg-2 text-center">
<div class="row">
<div class="col-sm-4">
<form id="frmProject" name="frmProject">
<div class="form-group" align="left">
<label class="form-label">Student Name</label>
<input type="text" class="form-control" placeholder="Student Name" id="studname" name="studname" size="30px" required>
</div>
<div class="form-group" align="left">
<label class="form-label">Course</label>
<input type="text" class="form-control" placeholder="Course" id="course" name="course" size="30px" required>
</div>
<div class="form-group" align="left">
<label class="form-label">Fee</label>
<input type="text" class="form-control" placeholder="Fee" id="fee" name="fee" size="30px" required>
</div>
<div class="form-group">
<label for="" class="col-sm-2 control-label">Version Name</label>
<div class="col-sm-10">
<select class="form-control" id="project_version_id" name="project_version_id" required>
<option value="">Please Selecssst</option>
</select>
</div>
</div>
</form>
</div>
<div class="col-sm-8">
<div class="panel-body">
<table id="tbl-projects" class="table table-responsive table-bordered" cellspacing="0" width="100%">
<thead>
<tr>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
</tr>
</table>
</div>
</div>
</div>
</div>
<script src="components/jquery/dist/jquery.js"></script>
<script src="components/jquery/dist/jquery.min.js"></script>
<script src="components/jquery-confirm-master/js/jquery-confirm.js"></script>
<script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<script src="components/jquery.validate.min.js"></script>
<script>
var isNew =true;
var project_id = null;
get_all();
function addStudent()
{
if($("#frmProject").valid())
{
var url= '';
var dat = '';
var method = '';
if(isNew == true)
{
url = 'add_project.php';
data = $('#frmProject').serialize();
method = 'POST';
}
else
{
url = 'update_project.php';
data = $('#frmProject').serialize() + "&project_id=" + project_id;
method = 'POST';
}
$.ajax({
type : method,
url : url,
dataType : 'JSON',
data : data,
success : function (data)
{
$('#frmProject')[0].reset();
$('#save').html('');
$('#save').append('Add');
var msg;
get_all();
if(isNew)
{
msg = "Course Created";
}
else
{
msg = "Course Updated";
}
$.alert({
title: 'Success!',
content: msg,
type: 'green',
boxWidth: '400px',
theme: 'light',
useBootstrap: false,
autoClose: 'ok|2000'
});
isNew = true;
},
error: function (xhr, status, error) {
alert(xhr);
console.log(xhr.responseText);
$.alert({
title: 'Fail!',
// content: xhr.responseJSON.errors.product_code + '<br>' + xhr.responseJSON.msg,
type: 'red',
autoClose: 'ok|2000'
});
$('#save').prop('disabled', false);
$('#save').html('');
$('#save').append('Save');
}
});
}
}
function get_all()
{
$('#tbl-projects').dataTable().fnDestroy();
$.ajax({
url : "all_project.php",
type : "GET",
dataType : "JSON",
success:function(data)
{
$('#tbl-projects').html(data);
$('#tbl-projects').dataTable({
"aaData": data,
"scrollX": true,
"aoColumns": [
{"sTitle": "StudentName", "mData": "name"},
{"sTitle": "Course", "mData": "course"},
{"sTitle": "Fee", "mData": "fee"},
{
"sTitle": "Status","mData": "status", "render": function (mData, type, row, meta) {
if (mData == 1) {
return '<span class="label label-info">Active</span>';
}
else if (mData == 2) {
return '<span class="label label-warning">Deactive</span>';
}
}
},
{
"sTitle": "Edit",
"mData": "id",
"render": function (mData, type, row, meta) {
return '<button class="btn btn-xs btn-success" onclick="get_project_details(' + mData + ')">Edit</button>';
}
},
{
"sTitle": "Delete",
"mData": "id",
"render": function (mData, type, row, meta) {
return '<button class="btn btn-xs btn-primary" onclick="Remove_details(' + mData + ')">Delete</button>';
}
}
]
});
},
error: function (xhr, status, error) {
alert(xhr);
console.log(xhr.responseText);
$.alert({
title: 'Fail!',
// content: xhr.responseJSON.errors.product_code + '<br>' + xhr.responseJSON.msg,
type: 'red',
autoClose: 'ok|2000'
});
$('#save').prop('disabled', false);
$('#save').html('');
$('#save').append('Save');
}
});
}
function get_project_details(id) {
$.ajax({
type: 'POST',
url: 'project_return.php',
dataType: 'JSON',
data: {project_id: id},
success: function (data) {
$("html, body").animate({scrollTop: 0}, "slow");
isNew = false
console.log(data);
project_id = data.id
$('#studname').val(data.name);
$('#course').val(data.course);
$('#fee').val(data.fee);
$('#status').val(data.status);
$('#frmProject').modal('show');
},
error: function (xhr, status, error) {
alert(xhr);
console.log(xhr.responseText);
$.alert({
title: 'Fail!',
// content: xhr.responseJSON.errors.product_code + '<br>' + xhr.responseJSON.msg,
type: 'red',
autoClose: 'ok|2000'
});
}
});
}
function Remove_details(id)
{
$.confirm({
theme: 'supervan',
buttons: {
yes : function()
{
$.ajax({
type: 'POST',
url: 'delete_project.php',
dataType: 'JSON',
data: {project_id: id},
success: function (data) {
get_all();
},
error: function (xhr, status, error) {
alert(xhr);
console.log(xhr.responseText);
$.alert({
title: 'Fail!',
// content: xhr.responseJSON.errors.product_code + '<br>' + xhr.responseJSON.msg,
type: 'red',
autoClose: 'ok|2000'
});
}
});
},
no : function()
{
}
}
});
}
</script>
</body>
</html>Add records :- when you get add request from the Ajax call
add_project.php
<?php
include("db.php");
if($_SERVER['REQUEST_METHOD'] == 'POST')
{
$stmt = $conn->prepare("insert into records(name,course,fee,status)values(?,?,?,?)");
$stmt->bind_param("ssss",$name,$course,$fee,$status);
$name = $_POST['studname'];
$course = $_POST['course'];
$fee = $_POST['fee'];
$status = $_POST['status'];
if($stmt->execute())
{
echo 1;
}
else
{
echo 0;
}
$stmt->close();
}
?> View the Record from database to datatable:- when you get view request from the Ajax call
<?php
include ("db.php");
$stmt = $conn->prepare("select id,name,course,fee,status from records order by id Desc");
$stmt->bind_result($id,$name,$course,$fee,$status);
if($stmt->execute())
{
while($stmt->fetch())
{
$output[] = array('id' => $id,'name' => $name,'course' => $course,'fee' => $fee,'status' => $status);
}
echo json_encode($output);
}
$stmt->close();
?>After load the Datatable we have two option Edit and Delete. before going edit the data we have to select particular row Edit button in the datatable .click edit button particular row data will pass in to the form. after will make the chages and click save data will updated. in the application we do save and edit using same form.
This following code use to pass the value in to the form.
<?php
include ("db.php");
if($_SERVER['REQUEST_METHOD'] == 'POST') {
$stmt = $conn->prepare("select id,name,course,fee,status from records where id = ?");
$stmt->bind_param("s",$project_id);
$stmt->bind_result($id, $name, $course, $fee, $status);
$project_id = $_POST['project_id'];
if ($stmt->execute()) {
while ($stmt->fetch()) {
$output = array('id' => $id, 'name' => $name, 'course' => $course, 'fee' => $fee, 'status' => $status);
}
echo json_encode($output);
}
$stmt->close();
}
?>Edit
After pass the value in to form click edit button record will be updated.
<?php
include("db.php");
if($_SERVER['REQUEST_METHOD'] == 'POST')
{
$stmt = $conn->prepare("update records set name= ? , course = ?, fee = ?, status = ? where id = ?");
$stmt->bind_param("sssss",$name,$course,$fee,$status,$project_id);
$project_id = $_POST['project_id'];
$name = $_POST['studname'];
$course = $_POST['course'];
$fee = $_POST['fee'];
$status = $_POST['status'];
if($stmt->execute())
{
echo 1;
}
else
{
echo 0;
}
$stmt->close();
}
?>Delete
which record need delete select particular row delete button and click.paricular row has been deleted.
<?php
include("db.php");
if($_SERVER['REQUEST_METHOD'] == 'POST')
{
$stmt = $conn->prepare("delete from records where id = ?");
$stmt->bind_param("s",$project_id);
$project_id = $_POST['project_id'];
if($stmt->execute())
{
echo 1;
}
else
{
echo 0;
}
$stmt->close();
}
?>