AJAX and MySQL with PHP – Dynamic Database Operations Tutorial

php Guru

· February 17, 2023 · 4 min read · Updated February 17, 2023

📌 Key Takeaways

AJAX and MySQL with PHP – Dynamic Database Operations Tutorial
What Does AJAX and MySQL Mean in PHP?
Why Use AJAX with MySQL in PHP?
Prerequisites for AJAX MySQL Tutorial
Sample MySQL Table Used in Examples
PHP MySQL Database Connection File

Table of Contents:

AJAX and MySQL – PHP Complete Beginner Tutorial

Combining AJAX, PHP, and MySQL allows you to build fast, interactive, and dynamic web applications. Data can be fetched or updated from the database without reloading the page, providing a smooth user experience.

This tutorial explains how AJAX works with PHP and MySQL, using clear examples and real outputs.

What Does AJAX and MySQL Mean in PHP?

AJAX is used on the client side to send requests, while PHP handles server-side logic and MySQL manages database storage.

Flow:

User triggers an action
AJAX sends request to PHP
PHP queries MySQL
MySQL returns data
PHP sends response
AJAX updates the page

Why Use AJAX with MySQL in PHP?

Using AJAX with MySQL helps to:

Load data instantly
Update content without refresh
Create live search systems
Build dynamic dashboards
Reduce server load

Prerequisites for AJAX MySQL Tutorial

Before proceeding, ensure you know:

Basic HTML and JavaScript
PHP fundamentals
MySQL basics
Database connection concepts

Sample MySQL Table Used in Examples

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

PHP MySQL Database Connection File

<?php
$conn = mysqli_connect("localhost", "root", "", "testdb");

if (!$conn) {
    die("Database connection failed");
}
?>

Example 1: Fetch Data from MySQL Using AJAX and PHP

This example shows how to load database data without page reload.

Step 1: HTML and JavaScript (index.html)

<!DOCTYPE html>
<html>
<head>
    <title>AJAX MySQL Example</title>
    <script>
        function loadUsers() {
            var xhr = new XMLHttpRequest();
            xhr.open("GET", "fetch.php", true);
            xhr.onreadystatechange = function () {
                if (xhr.readyState == 4 && xhr.status == 200) {
                    document.getElementById("result").innerHTML = xhr.responseText;
                }
            };
            xhr.send();
        }
    </script>
</head>
<body>

<button onclick="loadUsers()">Load Users</button>
<div id="result"></div>

</body>
</html>

Step 2: PHP File to Fetch MySQL Data (fetch.php)

<?php
include "db.php";

$result = mysqli_query($conn, "SELECT * FROM users");

while ($row = mysqli_fetch_assoc($result)) {
    echo $row['name'] . " - " . $row['email'] . "<br>";
}
?>

Output on Button Click

Rahul - rahul@gmail.com
Amit - amit@gmail.com
Neha - neha@gmail.com

Example 2: Send Data to MySQL Using AJAX and PHP

This example inserts data into MySQL without refreshing the page.

Step 1: HTML Form

<form onsubmit="saveData(); return false;">
    <input type="text" id="name" placeholder="Enter Name">
    <input type="email" id="email" placeholder="Enter Email">
    <button type="submit">Save</button>
</form>

<div id="msg"></div>

Step 2: JavaScript AJAX POST Request

<script>
function saveData() {
    var name = document.getElementById("name").value;
    var email = document.getElementById("email").value;

    var xhr = new XMLHttpRequest();
    xhr.open("POST", "insert.php", true);
    xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");

    xhr.onreadystatechange = function () {
        if (xhr.readyState == 4 && xhr.status == 200) {
            document.getElementById("msg").innerHTML = xhr.responseText;
        }
    };

    xhr.send("name=" + name + "&email=" + email);
}
</script>

Step 3: PHP Insert Script (insert.php)

<?php
include "db.php";

$name = $_POST['name'];
$email = $_POST['email'];

mysqli_query($conn,
    "INSERT INTO users (name, email)
     VALUES ('$name', '$email')");

echo "Data inserted successfully";
?>

Output

Data inserted successfully

Example 3: Live Search Using AJAX and MySQL

AJAX is commonly used for real-time search.

SELECT * FROM users WHERE name LIKE '%keyword%';

This integrates with the PHP MySQL Search Using LIKE tutorial.

Common Errors When Using AJAX with MySQL

Database connection failure
Incorrect AJAX URL
SQL syntax errors
Not handling response properly

AJAX and MySQL PHP, PHP AJAX MySQL tutorial, PHP AJAX database example, AJAX fetch data MySQL PHP, PHP asynchronous database operations

Best Practices for AJAX MySQL with PHP

Always validate data in PHP
Use prepared statements
Return JSON for structured data
Handle AJAX errors gracefully
Avoid exposing sensitive data

Frequently Asked Questions (FAQ)

1. Can AJAX work with MySQL directly?

No, AJAX communicates with PHP, not MySQL.

2. Does AJAX require PHP?

No, but PHP is commonly used.

3. Can AJAX update database records?

Yes, via PHP scripts.

4. Is AJAX secure?

Yes, if PHP validation is applied.

5. Is AJAX still relevant?

Yes, widely used in modern web apps.

php Guru