By live CRUD with AJAX, we mean that you will be able to Create, Read, Update, Delete data without having to refresh the page. This tutorial does not have any dependency on jQuery, you can implement it without having to include jQuery in it. We will be using a sample database named classicmodels and it will be included in the source files below. It has multiple tables but we will be using a table named employees to perform all 4 operations.
Live CRUD with AJAX – PHP
Let’s get started:
Create
We will be creating a simple form to input all fields and a button to insert a new row in database. Be default, the browser redirects you to the action attribute of form but we will be using onsubmit event to prevent form from redirecting. And thus, we can call our javascript function to save data via AJAX.
<form method="POST" onsubmit="return doInsert(this);">
<input name="first_name" placeholder="First name">
<input name="last_name" placeholder="Last name">
<input type="submit" value="Insert">
</form>
<script>
function doInsert(form) {
var firstName = form.first_name.value;
var lastName = form.last_name.value;
var ajax = new XMLHttpRequest();
ajax.open("POST", "Http.php", true);
ajax.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
ajax.onreadystatechange = function () {
if (this.readyState == 4 && this.status == 200)
alert(this.responseText);
};
ajax.send("first_name=" + firstName + "&last_name=" + lastName + "&do_insert=1");
return false;
}
</script>
Http.php
This file will be responsible for serving all 4 operations. We will be creating a database connection and a separate block for each request. Simple !
<?php
$connection = mysqli_connect("localhost", "root", "", "classicmodels");
if (isset($_POST["do_insert"]))
{
$first_name = $_POST["first_name"];
$last_name = $_POST["last_name"];
$sql = "INSERT INTO `employees`(`lastName`, `firstName`) VALUES ('$last_name', '$first_name')";
mysqli_query($connection, $sql);
echo "Record has been inserted successfully.";
exit();
}
Read
Display data in tabular form requires a table with unique ID to tbody tag.
<table>
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
</tr>
</thead>
<tbody id="data"></tbody>
</table>
<script>
function getData() {
var ajax = new XMLHttpRequest();
ajax.open("GET", "Http.php?view_all=1", true);
ajax.send();
ajax.onreadystatechange = function () {
if (this.readyState == 4 && this.status == 200) {
var data = JSON.parse(this.responseText);
var html = "";
for (var a = 0; a < data.length; a++) {
html += "<tr>";
html += "<td>" + data[a].firstName + "</td>";
html += "<td>" + data[a].lastName + "</td>";
html += "</tr>";
}
document.getElementById("data").innerHTML = html;
}
};
}
getData();
</script>
Http.php
There are no parameters in the AJAX request so for the sake of simplicity, in this file we will just be fetching all records from database. Paste the following code right below INSERT block done in previous step in Http.php:
if (isset($_GET["view_all"]))
{
$sql = "SELECT * FROM employees";
$result = mysqli_query($connection, $sql);
$data = array();
while ($row = mysqli_fetch_object($result))
array_push($data, $row);
echo json_encode($data);
exit();
}
Delete
Removing a row from database is the simplest part of this tutorial, just enter an ID, sends an AJAX request and execute the DELETE command.
<form method="POST" onsubmit="return doDelete(this);">
<input name="employee_id" placeholder="Employee ID">
<input type="submit" value="Delete">
</form>
<script>
function doDelete(form) {
var employeeID = form.employee_id.value;
var ajax = new XMLHttpRequest();
ajax.open("POST", "Http.php", true);
ajax.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
ajax.onreadystatechange = function () {
if (this.readyState == 4 && this.status == 200)
alert(this.responseText);
};
ajax.send("employee_id=" + employeeID + "&do_delete=1");
return false;
}
</script>
Http.php
We will be using simple DELETE query to remove a row from database. Paste the following code right below VIEW ALL block done in previous step in Http.php:
if (isset($_POST["do_delete"]))
{
$employee_id = $_POST["employee_id"];
$sql = "DELETE FROM employees WHERE employeeNumber = '" . $employee_id . "'";
mysqli_query($connection, $sql);
echo "Record has been deleted";
exit();
}
Update (a)
Updating the data would require 2 forms, 1 to get data of record you want to update, and 2nd to actually update the data in database. First we are going to get the record which needs to be updated:
<form method="POST" onsubmit="return getData(this);">
<input name="employee_id" placeholder="Employee ID">
<input type="submit" value="Search">
</form>
<script>
function getData(form) {
var employeeID = form.employee_id.value;
var ajax = new XMLHttpRequest();
ajax.open("POST", "Http.php", true);
ajax.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
ajax.onreadystatechange = function () {
if (this.readyState == 4 && this.status == 200) {
var data = JSON.parse(this.responseText);
var form = document.getElementById("form-update");
/* Show data in 2nd form */
form.first_name.value = data.firstName;
form.last_name.value = data.lastName;
form.employee_id.value = employeeID;
form.style.display = "";
}
};
ajax.send("employee_id=" + employeeID + "&get_data=1");
return false;
}
</script>
Http.php
This query will be almost same as for VIEW ALL function. Paste the following code right below DELETE block done in previous step in Http.php:
if (isset($_POST["get_data"]))
{
$employee_id = $_POST["employee_id"];
$sql = "SELECT * FROM employees WHERE employeeNumber = '" . $employee_id . "'";
$result = mysqli_query($connection, $sql);
$row = mysqli_fetch_object($result);
echo json_encode($row);
exit();
}
Update (b)
In this step, we will actually be updating the record in database. This step is almost similar to the INSERT section.
<form method="POST" onsubmit="return doUpdate(this);" id="form-update" style="display: none;">
<input type="hidden" name="employee_id">
<input name="first_name" placeholder="First name">
<input name="last_name" placeholder="Last name">
<input type="submit" value="Update">
</form>
<script>
function doUpdate(form) {
var firstName = form.first_name.value;
var lastName = form.last_name.value;
var employeeID = form.employee_id.value;
var ajax = new XMLHttpRequest();
ajax.open("POST", "Http.php", true);
ajax.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
ajax.onreadystatechange = function () {
if (this.readyState == 4 && this.status == 200)
alert(this.responseText);
};
ajax.send("first_name=" + firstName + "&last_name=" + lastName + "&employee_id=" + employeeID + "&do_update=1");
return false;
}
</script>
Http.php
Paste the following code right below GET DATA block done in previous step in Http.php:
if (isset($_POST["do_update"]))
{
$first_name = $_POST["first_name"];
$last_name = $_POST["last_name"];
$employee_id = $_POST["employee_id"];
$sql = "UPDATE `employees` SET `lastName` = '$last_name', `firstName` = '$first_name' WHERE employeeNumber = '" . $employee_id . "'";
mysqli_query($connection, $sql);
echo "Record has been updated successfully.";
exit();
}
That’s how you can implement a complete live CRUD operation with AJAX, PHP and MySQL. If you want to get more advanced. You can check our tutorial on doing a CRUD operation in Vue JS.
[wpdm_package id=’146′]
