PHP AJAX with MySQL Database: Real-Time Data Fetching and Updating
Last updated 2 months, 3 weeks ago | 124 views 75 5
Introduction: Why Combine PHP, AJAX, and Databases?
Today’s users expect instant updates, live content, and interactive experiences—all without page reloads. If your PHP web app connects to a MySQL database, using AJAX (Asynchronous JavaScript and XML) is a game-changer for fetching or updating data in real time.
PHP handles the database logic, MySQL stores your data, and AJAX lets you interact with both in the background—without disrupting the user experience.
You’ll learn how to:
-
Retrieve database records dynamically
-
Use AJAX to send/receive data to/from PHP
-
Display results in real-time on a webpage
What You'll Build
We’ll create a simple web app where:
-
A user clicks a button.
-
An AJAX request is sent to a PHP script.
-
The PHP script queries a MySQL database.
-
The data is returned and displayed without refreshing the page.
Prerequisites
Before we start, make sure you have:
-
A local server setup (XAMPP, WAMP, or similar)
-
A working MySQL database
-
Basic knowledge of HTML, PHP, and SQL
PHP AJAX with Database: Step-by-Step
✅ Step 1: Create the Database Table
CREATE DATABASE test_db;
USE test_db;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
INSERT INTO users (name, email) VALUES
('Alice', '[email protected]'),
('Bob', '[email protected]'),
('Charlie', '[email protected]');
✅ Step 2: Build the Frontend HTML + JavaScript (AJAX)
index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>PHP AJAX Database Example</title>
<script>
function fetchUsers() {
const xhr = new XMLHttpRequest();
xhr.open("GET", "fetch_users.php", true);
xhr.onload = function () {
if (xhr.status === 200) {
document.getElementById("result").innerHTML = xhr.responseText;
}
};
xhr.send();
}
</script>
</head>
<body>
<h2>Load Users from MySQL Using AJAX</h2>
<button onclick="fetchUsers()">Fetch Users</button>
<div id="result" style="margin-top: 20px;"></div>
</body>
</html>
✅ Step 3: Write the PHP Script to Query the Database
fetch_users.php
<?php
// Database credentials
$host = "localhost";
$user = "root";
$password = "";
$dbname = "test_db";
// Create connection
$conn = new mysqli($host, $user, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Fetch users
$sql = "SELECT * FROM users";
$result = $conn->query($sql);
// Display results
if ($result->num_rows > 0) {
echo "<table border='1' cellpadding='10'>";
echo "<tr><th>ID</th><th>Name</th><th>Email</th></tr>";
while ($row = $result->fetch_assoc()) {
echo "<tr>";
echo "<td>" . htmlspecialchars($row["id"]) . "</td>";
echo "<td>" . htmlspecialchars($row["name"]) . "</td>";
echo "<td>" . htmlspecialchars($row["email"]) . "</td>";
echo "</tr>";
}
echo "</table>";
} else {
echo "No users found.";
}
$conn->close();
?>
Full Example Recap: PHP AJAX with MySQL
Folder Structure
ajax-db-demo/
├── index.html
└── fetch_users.php
Open index.html in your browser and click "Fetch Users" to load user data from the MySQL database—without reloading the page.
Tips & Common Pitfalls
✅ Best Practices
-
Escape output with
htmlspecialchars()to prevent XSS attacks. -
Use prepared statements for dynamic SQL to avoid SQL injection.
-
Validate data on both frontend (JS) and backend (PHP).
-
Use JSON if returning complex data:
header('Content-Type: application/json'); echo json_encode($resultArray);
❌ Common Pitfalls
-
Forgetting to check database connection errors.
-
Not setting the correct Content-Type header for JSON.
-
Hardcoding credentials in production—use a secure config file.
-
Relying only on frontend validation—always validate on the server.
Comparison: Static PHP Page vs AJAX-Driven PHP
| Feature | Static PHP (Page Reload) | AJAX + PHP (No Reload) |
|---|---|---|
| Page Refresh Required | ✅ Yes | ❌ No |
| Real-Time Data Fetching | ❌ No | ✅ Yes |
| Better User Experience | ❌ Less Interactive | ✅ More Dynamic |
| Performance (UX) | ❌ Slower | ✅ Faster |
✅ Conclusion: Build Faster Web Apps with PHP and AJAX
Using PHP AJAX with a MySQL database unlocks the ability to build dynamic, fast, and user-friendly applications. Whether you're displaying live user data, saving form inputs, or powering an admin dashboard, AJAX ensures the interaction feels seamless.
Key Takeaways:
-
AJAX lets you fetch and display database results without refreshing the page.
-
Use PHP to process the request and interact with MySQL.
-
Always secure and validate your data before displaying it.
-
This setup is perfect for live content, dashboards, and real-time tools.
