POPULAR
Instant Online HTML Formatter
NEW TOOL
Try our Interactive Python Online Playground!
FEATURED
HTML, CSS & JS Live Code Playground
POPULAR
Try Our Online Code Editor and Compiler
POPULAR
Instant Online HTML Formatter
NEW TOOL
Try our Interactive Python Online Playground!
FEATURED
HTML, CSS & JS Live Code Playground
POPULAR
Try Our Online Code Editor and Compiler
AJAX Database
AJAX becomes more powerful when combined with a database, allowing web pages to fetch, insert, update, or delete data dynamically without refreshing the page. This is essential for building responsive web applications such as live search, real-time notifications, or interactive dashboards.
In this tutorial, we will learn how to use AJAX with PHP and MySQL to interact with a database efficiently and update content dynamically.
Setting Up the Database
First, create a database and a table for demonstration.
SQL:
CREATE DATABASE ajax_demo;
USE ajax_demo;
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
course VARCHAR(50) NOT NULL
);
INSERT INTO students (name, age, course) VALUES
('Riya Sharma', 20, 'Web Development'),
('Ananya Singh', 22, 'Data Science'),
('Meera Patel', 21, 'UI/UX Design');
Connecting PHP to the Database
Create a PHP file db.php to handle the database connection:
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "ajax_demo";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
Fetching Data Dynamically with AJAX
HTML File: index.html
<!DOCTYPE html>
<html>
<head>
<title>AJAX Database Example</title>
<script>
function fetchStudents() {
var xhr = new XMLHttpRequest();
xhr.open("GET", "fetch_students.php", true);
xhr.onreadystatechange = function() {
if (xhr.readyState == 4 && xhr.status == 200) {
document.getElementById("result").innerHTML = xhr.responseText;
}
};
xhr.send();
}
</script>
</head>
<body>
<h2>Students List</h2>
<button onclick="fetchStudents()">Load Students</button>
<div id="result" style="margin-top:20px;"></div>
</body>
</html>
PHP File: fetch_students.php
<?php
include "db.php";
$sql = "SELECT * FROM students";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo "<table border='1' cellpadding='5'><tr><th>ID</th><th>Name</th><th>Age</th><th>Course</th></tr>";
while($row = $result->fetch_assoc()) {
echo "<tr>
<td>".$row['id']."</td>
<td>".$row['name']."</td>
<td>".$row['age']."</td>
<td>".$row['course']."</td>
</tr>";
}
echo "</table>";
} else {
echo "No records found";
}
$conn->close();
?>
Clicking the “Load Students” button fetches student records from the database without reloading the page.
Inserting Data Using AJAX
You can also insert data dynamically:
HTML Form:
<input type="text" id="name" placeholder="Name">
<input type="number" id="age" placeholder="Age">
<input type="text" id="course" placeholder="Course">
<button onclick="addStudent()">Add Student</button>
<div id="addStatus"></div>
<script>
function addStudent() {
var name = document.getElementById("name").value;
var age = document.getElementById("age").value;
var course = document.getElementById("course").value;
var xhr = new XMLHttpRequest();
xhr.open("POST", "add_student.php", true);
xhr.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
xhr.onreadystatechange = function() {
if (xhr.readyState == 4 && xhr.status == 200) {
document.getElementById("addStatus").innerHTML = xhr.responseText;
fetchStudents(); // Refresh the list
}
};
xhr.send("name=" + name + "&age=" + age + "&course=" + course);
}
</script>
PHP File: add_student.php
<?php
include "db.php";
$name = $_POST['name'];
$age = $_POST['age'];
$course = $_POST['course'];
$sql = "INSERT INTO students (name, age, course) VALUES ('$name', $age, '$course')";
if ($conn->query($sql) === TRUE) {
echo "Student added successfully!";
} else {
echo "Error: " . $conn->error;
}
$conn->close();
?>
This allows you to add new students to the database dynamically.
Updating Records Dynamically
You can also update a student’s record using AJAX:
JavaScript Example:
function updateStudent(id) {
var newName = prompt("Enter new name:");
if(newName) {
var xhr = new XMLHttpRequest();
xhr.open("POST", "update_student.php", true);
xhr.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
xhr.onreadystatechange = function() {
if (xhr.readyState == 4 && xhr.status == 200) {
alert(xhr.responseText);
fetchStudents(); // Refresh the list
}
};
xhr.send("id=" + id + "&name=" + newName);
}
}
PHP File: update_student.php
<?php
include "db.php";
$id = $_POST['id'];
$name = $_POST['name'];
$sql = "UPDATE students SET name='$name' WHERE id=$id";
if ($conn->query($sql) === TRUE) {
echo "Student updated successfully!";
} else {
echo "Error: " . $conn->error;
}
$conn->close();
?>
Deleting Records Dynamically
AJAX can also delete records without page reload:
JavaScript:
function deleteStudent(id) {
if(confirm("Are you sure to delete this student?")) {
var xhr = new XMLHttpRequest();
xhr.open("POST", "delete_student.php", true);
xhr.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
xhr.onreadystatechange = function() {
if(xhr.readyState == 4 && xhr.status == 200) {
alert(xhr.responseText);
fetchStudents();
}
};
xhr.send("id=" + id);
}
}
PHP File: delete_student.php
<?php
include "db.php";
$id = $_POST['id'];
$sql = "DELETE FROM students WHERE id=$id";
if ($conn->query($sql) === TRUE) {
echo "Student deleted successfully!";
} else {
echo "Error: " . $conn->error;
}
$conn->close();
?>
Advantages of AJAX with Database
-
Dynamic content updates without page reload.
-
Improved user experience for web applications.
-
Efficient server communication – only relevant data is sent.
-
Supports CRUD operations (Create, Read, Update, Delete) dynamically.
-
Works seamlessly with PHP and MySQL.
Summary of the Tutorial
Using AJAX with PHP and MySQL allows developers to create interactive and responsive web applications. Key concepts:
-
Use
XMLHttpRequestto send requests to PHP scripts. -
PHP scripts interact with MySQL for CRUD operations.
-
Responses can be in HTML, JSON, or plain text.
-
Dynamic updates improve speed, usability, and interactivity.
This approach is fundamental for building modern web apps like dashboards, live search, and real-time content updates.
⚡ Want to test or run code snippets from this lesson?
Use CodePractice Universal Try-It Editor to run HTML, CSS, JS, Python, C, C++, Java & MySQL live in your browser!
Launch Free Live Code EditorPractice Questions
-
Create an HTML page that fetches all student records from a MySQL database using AJAX and displays them in a table.
-
Add a form to insert a new student into the database dynamically using AJAX POST.
-
Modify the student fetch example to sort students by age or name without reloading the page.
-
Create an AJAX request to update a student’s name when clicking an “Edit” button.
-
Implement an AJAX-based delete button for each student row to remove records dynamically.
-
Use AJAX to search for students by name in real time and display matching results.
-
Create a PHP script that returns student data in JSON format. Fetch and display it using AJAX.
-
Add error handling for all AJAX database operations and display alerts for success or failure.
-
Implement a dynamic dropdown list of courses that updates student lists based on selection using AJAX.
-
Build a small dashboard that dynamically updates the total number of students in the database whenever a record is added or deleted.
