AJAX Database Example - The Coding College

Welcome to The Coding College, your go-to platform for mastering coding concepts! In this tutorial, we’ll demonstrate how to use AJAX to interact with a database. By combining AJAX with server-side scripting (e.g., PHP or ASP) and a database (e.g., MySQL), you can build dynamic web applications that fetch and update data without page reloads.

Why Use AJAX for Database Interaction?

Dynamic Updates: Update or retrieve data without reloading the webpage.
Improved User Experience: Seamlessly fetch large datasets and display them dynamically.
Efficiency: Reduce server load and improve response times by only requesting necessary data.

Example: Fetch Data from a Database Using AJAX

We’ll create an example where AJAX retrieves data from a database and displays it dynamically on the webpage.

Tools Required:

Database: MySQL or any SQL database.
Server-Side Script: PHP (for this example).
AJAX: JavaScript’s XMLHttpRequest or Fetch API.

Step 1: Create a Database Table

Run the following SQL commands to create a database and a table with some sample data.

SQL:

CREATE DATABASE ajax_example;

USE ajax_example;

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

INSERT INTO users (name, email) VALUES
('John Doe', '[email protected]'),
('Jane Smith', '[email protected]'),
('Mike Johnson', '[email protected]');

Step 2: Create the PHP Script

Create a PHP script (fetch_users.php) to query the database and return data as JSON.

PHP (fetch_users.php):

<?php
// Database connection
$servername = "localhost";
$username = "root"; // Replace with your DB username
$password = ""; // Replace with your DB password
$dbname = "ajax_example";

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Fetch data from the database
$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);

$users = [];

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $users[] = $row;
    }
}

// Return data as JSON
header('Content-Type: application/json');
echo json_encode($users);

$conn->close();
?>

Step 3: Create the HTML File

HTML:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>AJAX Database Example</title>
</head>
<body>
    <h1>AJAX Example: Fetch Data from Database</h1>
    <button id="fetchData">Load Users</button>
    <div id="output"></div>
    <script src="script.js"></script>
</body>
</html>

Step 4: Write JavaScript to Fetch Data from PHP

JavaScript (script.js):

document.getElementById("fetchData").addEventListener("click", function () {
    // Step 1: Create an XMLHttpRequest object
    const xhr = new XMLHttpRequest();

    // Step 2: Configure the request
    xhr.open("GET", "fetch_users.php", true);

    // Step 3: Handle the server response
    xhr.onreadystatechange = function () {
        if (xhr.readyState === 4 && xhr.status === 200) {
            // Parse the JSON response
            const users = JSON.parse(xhr.responseText);

            // Build the output dynamically
            let output = "<h2>User List</h2><ul>";
            users.forEach(user => {
                output += `
                    <li>
                        <strong>${user.name}</strong> (Email: ${user.email})
                    </li>
                `;
            });
            output += "</ul>";

            // Display the data on the webpage
            document.getElementById("output").innerHTML = output;
        }
    };

    // Step 4: Send the request
    xhr.send();
});

Example: Insert Data into a Database Using AJAX

Now, let’s create an example where AJAX sends user input to a PHP script, which then inserts the data into the database.

Step 1: Modify the PHP Script

Create another PHP script (insert_user.php) to insert data into the database.

PHP (insert_user.php):

<?php
// Database connection
$servername = "localhost";
$username = "root"; // Replace with your DB username
$password = ""; // Replace with your DB password
$dbname = "ajax_example";

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Insert data into the database
if ($_SERVER["REQUEST_METHOD"] === "POST") {
    $name = $_POST["name"];
    $email = $_POST["email"];

    $stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
    $stmt->bind_param("ss", $name, $email);

    if ($stmt->execute()) {
        echo json_encode(["status" => "success", "message" => "User added successfully!"]);
    } else {
        echo json_encode(["status" => "error", "message" => "Failed to add user."]);
    }

    $stmt->close();
}

$conn->close();
?>

Step 2: Add a Form to the HTML