AJAX Database

PHP - AJAX Interaction with a Database

AJAX (Asynchronous JavaScript and XML) allows web pages to communicate with a server in the background without requiring a full page reload. This enables dynamic and interactive user experiences, such as fetching database information on the fly.

Modern AJAX typically uses the JavaScript fetch API and often exchanges data using JSON instead of XML, although the core concept remains the same.

AJAX Database Example: Fetch User Details

This example demonstrates how selecting a user from a dropdown list can trigger an AJAX request to a PHP script, which then queries a MySQL database and returns the user’s details to be displayed on the page.

Conceptual User Interface:

<form>
  <label for="user-select">Select a person:</label>
  <select name="users" id="user-select">
    <option value="">Select a person:</option>
    <option value="1">Peter Griffin</option>
    <option value="2">Lois Griffin</option>
    <option value="3">Joseph Swanson</option>
    <option value="4">Glenn Quagmire</option>
  </select>
</form>
<br>
<div id="txtHint">**Person info will be listed here...**</div>

The MySQL Database Table

Assume we have a users table in our database with the following structure and data:

CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  FirstName VARCHAR(50) NOT NULL,
  LastName VARCHAR(50) NOT NULL,
  Age INT,
  Hometown VARCHAR(50),
  Job VARCHAR(50),
  INDEX(LastName) -- Example index
);

INSERT INTO users (FirstName, LastName, Age, Hometown, Job) VALUES
('Peter', 'Griffin', 41, 'Quahog', 'Brewery'),
('Lois', 'Griffin', 40, 'Newport', 'Piano Teacher'),
('Joseph', 'Swanson', 39, 'Quahog', 'Police Officer'),
('Glenn', 'Quagmire', 41, 'Quahog', 'Pilot');

Table Data:

id	FirstName	LastName	Age	Hometown	Job
1	Peter	Griffin	41	Quahog	Brewery
2	Lois	Griffin	40	Newport	Piano Teacher
3	Joseph	Swanson	39	Quahog	Police Officer
4	Glenn	Quagmire	41	Quahog	Pilot

Example Explained: HTML and JavaScript (Client-Side)

The HTML sets up the dropdown. JavaScript using the fetch API listens for changes on the dropdown, sends the selected user’s ID to the server, and updates the txtHint div with the response.

File: index.html (or .php)

<!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>
    <style>
        #txtHint table { width: 100%; border-collapse: collapse; }
        #txtHint table, #txtHint th, #txtHint td { border: 1px solid black; padding: 5px; text-align: left; }
    </style>
</head>
<body>

<h1>Select User</h1>
<form>
    <label for="user-select">Select a person:</label>
    <select name="users" id="user-select">
        <option value="">Select a person:</option>
        <option value="1">Peter Griffin</option>
        <option value="2">Lois Griffin</option>
        <option value="3">Joseph Swanson</option>
        <option value="4">Glenn Quagmire</option>
    </select>
</form>
<br>
<div id="txtHint">**Person info will be listed here...**</div>

<script>
    // Wait for the DOM to be fully loaded
    document.addEventListener('DOMContentLoaded', function() {
        const userSelect = document.getElementById('user-select');
        const hintContainer = document.getElementById('txtHint');

        userSelect.addEventListener('change', function() {
            const userId = this.value;
            hintContainer.innerHTML = '**Loading...**'; // Provide feedback

            if (userId === "") {
                hintContainer.innerHTML = "**Person info will be listed here...**";
                return;
            }

            // Use Fetch API to send request to the PHP backend
            fetch(`get_user.php?q=${encodeURIComponent(userId)}`) // Use encodeURIComponent for safety
                .then(response => {
                    if (!response.ok) {
                        // Handle HTTP errors (e.g., 404, 500)
                        throw new Error(`HTTP error! Status: ${response.status}`);
                    }
                    return response.text(); // Get the response body as text (HTML in this case)
                })
                .then(html => {
                    // Update the hint container with the HTML received from the server
                    hintContainer.innerHTML = html;
                })
                .catch(error => {
                    // Handle fetch errors (network issues, etc.) or errors from the server
                    console.error('Fetch Error:', error);
                    hintContainer.innerHTML = `**Error loading data: ${error.message}**`;
                });
        });
    });
</script>

</body>
</html>

JavaScript Explanation:

• An event listener is attached to the change event of the <select> element.
• When a user selects an option, the selected userId is retrieved.
• If userId is empty, the hint area is reset.
• The fetch() function sends an asynchronous GET request to get_user.php.
• The userId is passed as a URL parameter q. encodeURIComponent() ensures the value is safely added to the URL.
• .then(response => ...) handles the response from the server. It checks if the response status is OK (e.g., 200).
• .then(html => ...) processes the response body (expected to be HTML text) and updates the innerHTML of the txtHint div.
• .catch(error => ...) handles any errors during the fetch process (e.g., network error, server error).
• Providing ‘Loading…’ feedback improves user experience.

The PHP Script (Server-Side): get_user.php

This PHP script receives the user ID (q), connects to the database, securely queries the database using a prepared statement, formats the result as an HTML table, and sends it back to the browser.

Security Warning: Always use prepared statements to prevent SQL injection vulnerabilities when dealing with user input in database queries.

<?php
declare(strict_types=1);

// --- Database Configuration ---
// !! IMPORTANT: Store credentials securely, not directly in the code.
// Consider environment variables or a configuration file outside the web root.
$dbHost = 'localhost';
$dbUser = 'your_db_username'; // Replace with your database username
$dbPass = 'your_db_password'; // Replace with your database password
$dbName = 'your_database_name'; // Replace with your database name

// --- Get User ID from Request ---
// Use filter_input for safer access to GET/POST variables
$userId = filter_input(INPUT_GET, 'q', FILTER_VALIDATE_INT);

if ($userId === false || $userId === null) {
    // Handle invalid or missing ID - exit gracefully or return an error message
    // Setting HTTP response code can be helpful for the client-side script
    // http_response_code(400); // Bad Request
    echo "Invalid user ID provided.";
    exit;
}

// --- Database Connection (using MySQLi Procedural style as example) ---
$conn = mysqli_connect($dbHost, $dbUser, $dbPass, $dbName);

// Check Connection
if (!$conn) {
    // Log the detailed error internally, show a generic message to the user
    error_log("Database Connection Error: " . mysqli_connect_error());
    // http_response_code(500); // Internal Server Error
    echo "Error: Could not connect to the database.";
    exit;
}

// Set character set (recommended)
mysqli_set_charset($conn, 'utf8mb4');

// --- Prepare SQL Statement (Security!) ---
$sql = "SELECT FirstName, LastName, Age, Hometown, Job FROM users WHERE id = ?";
$stmt = mysqli_prepare($conn, $sql);

if (!$stmt) {
    error_log("SQL Prepare Error: " . mysqli_error($conn));
    echo "Error preparing database query.";
    mysqli_close($conn);
    exit;
}

// --- Bind Parameter and Execute ---
mysqli_stmt_bind_param($stmt, "i", $userId); // 'i' specifies the variable type is integer

mysqli_stmt_execute($stmt);

// --- Get Result ---
$result = mysqli_stmt_get_result($stmt);

// --- Build HTML Response ---
if ($result && mysqli_num_rows($result) > 0) {
    echo "<table>
            <tr>
                <th>Firstname</th>
                <th>Lastname</th>
                <th>Age</th>
                <th>Hometown</th>
                <th>Job</th>
            </tr>";

    while ($row = mysqli_fetch_assoc($result)) {
        echo "<tr>";
        // Use htmlspecialchars to prevent XSS if displaying data back in HTML
        echo "<td>" . htmlspecialchars($row['FirstName'] ?? '', ENT_QUOTES, 'UTF-8') . "</td>";
        echo "<td>" . htmlspecialchars($row['LastName'] ?? '', ENT_QUOTES, 'UTF-8') . "</td>";
        echo "<td>" . htmlspecialchars((string)($row['Age'] ?? ''), ENT_QUOTES, 'UTF-8') . "</td>";
        echo "<td>" . htmlspecialchars($row['Hometown'] ?? '', ENT_QUOTES, 'UTF-8') . "</td>";
        echo "<td>" . htmlspecialchars($row['Job'] ?? '', ENT_QUOTES, 'UTF-8') . "</td>";
        echo "</tr>";
    }
    echo "</table>";
} else if ($result) {
    echo "No user found with ID: " . htmlspecialchars((string)$userId, ENT_QUOTES, 'UTF-8');
} else {
    error_log("SQL Execute Error: " . mysqli_stmt_error($stmt));
    echo "Error retrieving user data.";
}

// --- Clean up ---
mysqli_stmt_close($stmt);
mysqli_close($conn);

?>

PHP Script Explanation:

1. Configuration: Database credentials are set (should be stored securely).
2. Input Validation: filter_input() retrieves and validates the q parameter as an integer.
3. Database Connection: Connects to MySQL using mysqli_connect(). Error handling is crucial.
4. Character Set: Sets the connection character set to utf8mb4 (recommended).
5. Prepared Statement: An SQL query with a placeholder (?) is prepared using mysqli_prepare().
6. Bind Parameter: The validated $userId is bound to the placeholder using mysqli_stmt_bind_param(). This is the key step for SQL injection prevention.
7. Execute: The prepared statement is executed with mysqli_stmt_execute().
8. Get Result: mysqli_stmt_get_result() fetches the results.
9. Build Response: If a user is found, an HTML table is constructed with the data. htmlspecialchars() is used on output for safety.
10. Error Handling: Checks for errors at connection, prepare, and execute stages.
11. Clean Up: The statement and connection are closed using mysqli_stmt_close() and mysqli_close().

This example uses the MySQLi procedural style. You could achieve the same using MySQLi Object-Oriented style or PDO (PHP Data Objects), both of which also strongly support prepared statements.