AJAX Database Example

AJAX (Asynchronous JavaScript and XML) is often used with server-side technologies to fetch or update data in a database without reloading the webpage. This example demonstrates how to use AJAX to interact with a MySQL database using PHP.

Steps to Implement AJAX with a Database

  1. Set Up the Database:
    • Create a database and populate it with sample data.
  2. Create the Server-Side Script:
    • Use PHP to query the database and return data.
  3. Implement Client-Side HTML and JavaScript:
    • Use JavaScript to send requests to the PHP script and display results dynamically.

Example: Fetching Data from a Database

Step 1: Create the Database

Use the following SQL to create and populate a table:

CREATE DATABASE ajax_example;

USE ajax_example;

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

INSERT INTO users (name, email)
VALUES
('Alice', '[email protected]'),
('Bob', '[email protected]'),
('Charlie', '[email protected]');

Step 2: Create the Server-Side Script (fetch_users.php)

This script queries the database and returns data as an HTML table.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "ajax_example";

// Connect to the database
$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 * FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table border='1'>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Email</th>
            </tr>";
    while ($row = $result->fetch_assoc()) {
        echo "<tr>
                <td>{$row['id']}</td>
                <td>{$row['name']}</td>
                <td>{$row['email']}</td>
              </tr>";
    }
    echo "</table>";
} else {
    echo "No users found.";
}

$conn->close();
?>

Step 3: Create Client-Side HTML and JavaScript

This code uses AJAX to fetch the database data and display it dynamically.

<!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>
  <script>
    function fetchUsers() {
      let xhr = new XMLHttpRequest();
      xhr.open("GET", "fetch_users.php", true);

      xhr.onload = function () {
        if (xhr.status === 200) {
          document.getElementById("output").innerHTML = xhr.responseText;
        } else {
          console.error("Error fetching data:", xhr.status, xhr.statusText);
        }
      };

      xhr.onerror = function () {
        console.error("Network error occurred.");
      };

      xhr.send();
    }
  </script>
</head>
<body>
  <h1>AJAX Database Example</h1>
  <button onclick="fetchUsers()">Fetch Users</button>
  <div id="output">Click the button to load users from the database.</div>
</body>
</html>

Explanation

  1. Database Setup:
    • A users table is created to store user information.
    • Sample data is inserted for demonstration purposes.
  2. Server-Side PHP:
    • Connects to the database and queries the users table.
    • Formats the result as an HTML table and sends it back to the client.
  3. Client-Side JavaScript:
    • Uses the XMLHttpRequest object to send a GET request to fetch_users.php.
    • Displays the server’s response in the #output div.

Example with User Input (Search Feature)

HTML and JavaScript for Search:

<input type="text" id="search" placeholder="Search by name">
<button onclick="searchUser()">Search</button>
<div id="result"></div>

<script>
  function searchUser() {
    let query = document.getElementById("search").value;
    let xhr = new XMLHttpRequest();
    xhr.open("GET", "search_user.php?name=" + encodeURIComponent(query), true);

    xhr.onload = function () {
      if (xhr.status === 200) {
        document.getElementById("result").innerHTML = xhr.responseText;
      }
    };

    xhr.send();
  }
</script>

PHP Script for Search (search_user.php):

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "ajax_example";

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

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

$name = $_GET['name'] ?? '';
$sql = "SELECT * FROM users WHERE name LIKE '%$name%'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "<p>{$row['name']} ({$row['email']})</p>";
    }
} else {
    echo "No users found.";
}

$conn->close();
?>

Conclusion

Using AJAX with a database allows for seamless dynamic content updates, providing a smoother user experience. This method is commonly used in real-time applications like search forms, live updates, and dashboards. For more tutorials, visit The Coding College.

Leave a Comment