This tutorial is created to implement CRUD operations via AJAX (Asynchronous JavaScript and XML). Ajax is used for rich-internet applications that emulate the responsiveness and complex user interfaces. It is a set of inter-related technologies like JavaScript, DOM, XML, HTML/XHTML, CSS, XMLHttpRequest. It helps to send and receive data asynchronously without reloading the web page and it’s faster.
- Create the DB(Database)
Create the DB and run the following SQL query to create a table student inside the MySQL database.
SQL query to create a table named student
CREATE TABLE student (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
username varchar(255) NOT NULL,
password varchar(255) NOT NULL
);
Code language: PHP (php)Now, It’s time to create the DB config.php file as shown below
config.php
<?php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', '');
define('DB_PASSWORD', '');
define('DB_NAME', 'ajax_application');
$link = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($link->connect_errno) {
printf("Connect failed: %s\n", $link->connect_error);
exit();
}
Code language: HTML, XML (xml)- Creating the Pages
You need to create the following files to run the Ajax CRUD application
- index.php
- add.php
- delete.php
- edit.php
- update.php
index.php
<!DOCTYPE html>
<html>
<head>
<title>AJAX for Database Operations | bishrulhaq.com</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css"
integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
</head>
<body>
<div class="container" style="margin-top: 20px;">
<div class="card">
<div class="card-body">
<h1 class="text-center">AJAX for Database Operations</h1>
</div>
</div>
</div>
<div class="container" style="margin-top: 25px;">
<div class="row">
<div class="col-12">
<div class="text-center">
<h5>Add New Record</h5>
</div>
<div class="form-inline" style="margin-bottom: 25px;">
<div class="form-group col-3">
<input type="text" name="name" id="name" placeholder="Name" class="form-control"/>
</div>
<div class="form-group col-3">
<input type="text" name="username" id="username" placeholder="Username" class="form-control"/>
</div>
<div class="form-group col-3">
<input type="text" id="password" name="password" placeholder="Password" class="form-control"
required/>
</div>
<div class="form-group col-3">
<button type="button" class="btn btn-primary" id="add" name="add" onclick="addRecord()">Add Record
</button>
</div>
</div>
</div>
</div>
<div class="row">
<div class="col-12">
<div id="link-edit">
</div>
</div>
</div>
<div class="row">
<div class="col-12">
<div id="records_content"></div>
<div class="col-12" id="table_content">
</div>
</div>
</div>
</div>
<script src="https://code.jquery.com/jquery-3.4.1.min.js" integrity="sha256-CSXorXvZcTkaix6Yvo6HppcZGetbYMGWSFlBw8HfCJo=" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js" integrity="sha384-ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q" crossorigin="anonymous"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
<script>
$(document).ready(function () {
$.get("view.php", function (data) {
$("#table_content").html(data);
});
});
function addRecord() {
var name = $('#name').val();
var username = $('#username').val();
var password = $('#password').val();
$.ajax({
url: "add.php",
type: "POST",
data: {name: name, username: username, password: password},
success: function (data, status, xhr) {
$('#name').val('');
$('#username').val('');
$('#password').val('');
$.get("view.php", function (html) {
$("#table_content").html(html);
});
$('#records_content').fadeOut(1100).html(data);
},
error: function () {
$('#records_content').fadeIn(3000).html('<div class="text-center">Error!</div>');
},
beforeSend: function () {
$('#records_content').fadeOut(700).html('<div class="text-center">Loading...</div>');
}
});
}
</script>
</body>
</html>
Code language: HTML, XML (xml)In this step, Let’s create the “add.php” and add the following code inside it.
add.php
<?php
require_once('config.php');
$name = $_POST['name'];
$username = $_POST['username'];
$password = $_POST['password'];
if (!empty($name) && !empty($username) && !empty($password)) {
$query = $con->prepare("INSERT into student (name, username, password) VALUES (?,?,?)");
$query->bind_param('sss', $name, $username, $password);
$result = $query->execute();
if ($result) {
echo '1 Record Added!';
} else {
exit(mysqli_error($con));
}
}
Code language: HTML, XML (xml)Create “delete.php” and add the following code inside it. Which will help to delete particular user by passing the ID.
delete.php
<?php
require_once('config.php');
$id = $_POST['id'];
if (empty($id)) {
echo 'Error!';
}
$query = $con->prepare("DELETE FROM student where id= ?");
$query->bind_param('i', $id);
$result = $query->execute();
if ($result) {
echo '1 Record Deleted!';
} else {
exit(mysqli_error($con));
}
Code language: HTML, XML (xml)Now, Let’s Create the “edit.php” and add the following code inside it.
<?php
require_once('config.php');
$id = $_POST['id'];
if (empty($id)) { ?>
<div class="text-center">No records found under this selection <a href="#" onclick="$('#link-add').hide();$('#show-add').show(700);">Hidethis</a></div>
<?php die();
}
$query = "SELECT * FROM student where id = ?";
if ($stmt = $con->prepare($query)) {
$stmt->bind_param("i", $id);
if ($stmt->execute()) {
$result = $stmt->get_result();
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
?>
<div class="form-inline" id="edit-data" style="margin-bottom: 20px;">
<div class="form-group col-3">
<input type="text" name="student_name" id="student_name" value="<?php echo $row['name']; ?>"
placeholder="Name" class="form-control" required/>
</div>
<div class="form-group col-3">
<input type="text" name="student_username" id="student_username" placeholder="Username" class="form-control"
value="<?php echo $row['username']; ?>" required/>
</div>
<div class="form-group col-3">
<input type="text" id="student_password" name="student_password" placeholder="Password" class="form-control"
value="<?php echo $row['password']; ?>" required/>
</div>
<div class="form-group col-3">
<button type="button" class="btn btn-primary update" id="<?php echo $row['id']; ?>" name="update"
onclick="updateRecord(<?php echo $row['id']; ?>)">Update Record
</button>
<button type="button" href="javascript:void(0);" class="btn btn-default" id="cancel"
onclick="$('#link-edit').slideUp(400);$('#show-add').show(700);">Cancel
</button>
</div>
</div>
<?php }}}} ?>
<script type="text/javascript">
function updateRecord(student_id) {
var id = student_id;
var name = $('#student_name').val();
var username = $('#student_username').val();
var password = $('#student_password').val();
$.ajax({
url: "update.php",
type: "POST",
data: {id: id, name: name, username: username, password: password},
success: function (data, status, xhr) {
$('#name').val('');
$('#username').val('');
$('#password').val('');
$('#records_content').fadeOut(1100).html(data);
$.get("view.php", function (html) {
$("#table_content").html(html);
});
$('#records_content').fadeOut(1100).html(data);
},
complete: function () {
$('#link-edit').hide();
$('#show-add').show(700);
}
});
}
</script>
Code language: HTML, XML (xml)Create “update.php” and add the following code inside it. Which will help to update the particular user data
update.php
<?php
require_once('config.php');
$id = $_POST['id'];
$name = $_POST['name'];
$username = $_POST['username'];
$password = $_POST['password'];
if (!empty($name) && !empty($username) && !empty($password) && !empty($id)) {
$query = "UPDATE student SET name = ?, username = ?, password = ? WHERE id = ?";
if ($stmt = $con->prepare($query)) {
$stmt->bind_param("sssi", $name, $username, $password, $id);
$stmt->execute();
if ($stmt->error) {
echo '' . $stmt->error . '';
} else {
echo '1 Record updated!';
}
}
} else {
echo 'error while updating record';
}
Code language: HTML, XML (xml)Hope this tutorial helped you! Feel free to drop your opinion at the comment section.