AJAX - Database Interaction

The most powerful use of AJAX is retrieving data directly from a **database** (like MySQL). This allows you to build single-page applications where data updates automatically based on user choices—for example, selecting a Customer Name to instantly see their order history.

The Architecture

JavaScript cannot talk directly to a database for security reasons. Instead, it follows this 3-step path:

1. JavaScript sends an AJAX request with a parameter (like an ID).
2. The Server Script (PHP/Node) queries the database using that ID.
3. The Server Script returns the data (as HTML or JSON), which JS displays.

Client-Side Request

In this example, we send a user's selection from a dropdown menu to a server-side file.

function showCustomer(str) {
    if (str == "") {
        document.getElementById("info").innerHTML = "";
        return;
    }
    
    var xhttp = new XMLHttpRequest();
    xhttp.onreadystatechange = function() {
        if (this.readyState == 4 && this.status == 200) {
            document.getElementById("info").innerHTML = this.responseText;
        }
    };
    xhttp.open("GET", "getcustomer.php?id=" + str, true);
    xhttp.send();
}

Server-Side Query (PHP Example)

The PHP file connects to the database, executes a SQL query, and builds an HTML table from the results.

<?php
$mysqli = new mysqli("localhost", "user", "pass", "database");

// Search for the ID passed from AJAX
$stmt = $mysqli->prepare("SELECT name, city, balance FROM customers WHERE id = ?");
$stmt->bind_param("s", $_GET['id']);
$stmt->execute();
$result = $stmt->get_result();

// Build an HTML table to send back
echo "<table>";
while($row = $result->fetch_assoc()) {
    echo "<tr><td>" . $row['name'] . "</td></tr>";
}
echo "</table>";
?>

Practical Use Cases

• E-commerce: Filter products by category or price range live.
• Forms: Check if a username is already taken while the user types.
• Dashboards: Load specific charts or reports based on a date range picker.

Key Points to Remember

• AJAX enables real-time database querying without page reloads.
• The server-side script acts as the middleman between JS and SQL.
• Always use Prepared Statements for security.
• You can return HTML blocks or JSON data from the database.
• AJAX database calls are the heart of Single Page Applications (SPAs).