AJAX Database

Loading Database Data Without Reloading the Page

In simple terms: Many interactive features show database information on demand: choosing a category loads its products into a second drop-down, selecting a product shows its price and stock, clicking "Load more" fetches the next orders. With AJAX, JavaScript requests exactly the data it needs from a PHP endpoint, and PHP reads it from the database and returns it as JSON.

Technically: The pattern combines the pieces from earlier lessons: a PHP endpoint that validates request parameters, queries the database with prepared statements (see PDO Prepared Statements), maps the rows to a deliberate JSON structure, and returns it with proper status codes; and JavaScript that calls the endpoint with fetch() and updates the page safely.

The examples use the pdo() helper from PDO Connect, the jsonResponse() helper from AJAX PHP, and the shop's products table (current stock: Gel Pen 118, Notebook A5 44, Desk Lamp 6, Backpack 0, Wireless Mouse 30).

Shaping the JSON Response

Never send database rows straight to the browser. Decide which fields the client may see and in what format:

<?php
// src/product-json.php
function productJson(array $row): array
{
    return [
        'sku' => $row['sku'],
        'name' => $row['name'],
        'price' => $row['price'],
        'price_formatted' => '₹' . number_format((float) $row['price'], 2),
        'stock' => (int) $row['stock'],
        'in_stock' => (int) $row['stock'] > 0,
    ];
}

echo json_encode(
    productJson(['sku' => 'LAMP-01', 'name' => 'Desk Lamp', 'price' => '1299.00', 'stock' => 6, 'cost_price' => '820.00']),
    JSON_UNESCAPED_UNICODE
) . "\n";

Output

{"sku":"LAMP-01","name":"Desk Lamp","price":"1299.00","price_formatted":"₹1,299.00","stock":6,"in_stock":true}

Why a Mapping Function?

• Internal columns (here, a hypothetical cost_price, or user password hashes, internal notes) never leak into public responses — even if the query or table changes later.
• Types are explicit: stock is a number and in_stock a boolean in JSON.
• The client receives display-ready values (price_formatted) while keeping the exact decimal string for calculations.
• The function can be tested without a database or browser.

The Product Endpoint: api/product.php

<?php
declare(strict_types=1);

require __DIR__ . '/../src/pdo.php';
require __DIR__ . '/../src/json-response.php';
require __DIR__ . '/../src/product-json.php';

$sku = $_GET['sku'] ?? '';

if (!is_string($sku) || !preg_match('/^[A-Z0-9-]{3,30}$/', $sku = strtoupper(trim($sku)))) {
    jsonResponse(400, ['error' => 'Invalid SKU.']);
}

$stmt = pdo()->prepare('SELECT sku, name, price, stock FROM products WHERE sku = ? AND is_active = 1');
$stmt->execute([$sku]);
$row = $stmt->fetch();

if ($row === false) {
    jsonResponse(404, ['error' => 'Product not found.']);
}

jsonResponse(200, productJson($row));

Responses

How It Works

1. The SKU is normalized to uppercase and validated against a strict pattern before any database access — invalid input gets a 400 response.
2. A prepared statement fetches the product; only active products are visible.
3. A missing product returns 404, so JavaScript can distinguish "not found" from success.
4. The row is shaped by productJson() and sent as JSON.

The Category Endpoint: api/products-by-category.php

<?php
declare(strict_types=1);

require __DIR__ . '/../src/pdo.php';
require __DIR__ . '/../src/json-response.php';

$allowedCategories = ['stationery', 'electronics', 'bags'];
$category = $_GET['category'] ?? '';

if (!in_array($category, $allowedCategories, true)) {
    jsonResponse(400, ['error' => 'Unknown category.']);
}

$stmt = pdo()->prepare('SELECT sku, name FROM products WHERE category = ? AND is_active = 1 ORDER BY name');
$stmt->execute([$category]);

jsonResponse(200, ['products' => $stmt->fetchAll()]);

Response for ?category=electronics

{"products":[{"sku":"LAMP-01","name":"Desk Lamp"},{"sku":"MOUSE-01","name":"Wireless Mouse"}]}

Returning an object with a products key (instead of a bare array) leaves room to add fields such as total or next_page later without breaking clients.

The Page: Dependent Drop-Downs and a Details Panel

<label>Category
    <select id="category">
        <option value="">Choose…</option>
        <option value="stationery">Stationery</option>
        <option value="electronics">Electronics</option>
        <option value="bags">Bags</option>
    </select>
</label>

<label>Product
    <select id="product" disabled></select>
</label>

<div id="details" aria-live="polite"></div>

<script>
    const categorySelect = document.getElementById('category');
    const productSelect = document.getElementById('product');
    const details = document.getElementById('details');

    async function getJson(url) {
        const response = await fetch(url, { headers: { Accept: 'application/json' } });
        const data = await response.json();
        if (!response.ok) {
            throw new Error(data.error ?? `HTTP ${response.status}`);
        }
        return data;
    }

    categorySelect.addEventListener('change', async () => {
        productSelect.replaceChildren(new Option('Loading…', ''));
        productSelect.disabled = true;
        details.textContent = '';

        if (categorySelect.value === '') {
            productSelect.replaceChildren();
            return;
        }

        try {
            const params = new URLSearchParams({ category: categorySelect.value });
            const { products } = await getJson(`api/products-by-category.php?${params}`);

            productSelect.replaceChildren(
                new Option('Choose a product…', ''),
                ...products.map(product => new Option(product.name, product.sku))
            );
            productSelect.disabled = false;
        } catch (error) {
            productSelect.replaceChildren();
            details.textContent = error.message;
        }
    });

    productSelect.addEventListener('change', async () => {
        if (productSelect.value === '') {
            details.textContent = '';
            return;
        }

        try {
            const params = new URLSearchParams({ sku: productSelect.value });
            const product = await getJson(`api/product.php?${params}`);

            details.textContent = product.in_stock
                ? `${product.name}: ${product.price_formatted} — ${product.stock} in stock`
                : `${product.name}: ${product.price_formatted} — currently out of stock`;
        } catch (error) {
            details.textContent = error.message;
        }
    });
</script>

Result in the Browser

Choosing Electronics fills the second drop-down with "Desk Lamp" and "Wireless Mouse". Choosing Desk Lamp then shows:

Desk Lamp: ₹1,299.00 — 6 in stock

How It Works

1. getJson() centralizes fetching: it parses the JSON and turns error responses into exceptions carrying the server's error message.
2. new Option(text, value) creates <option> elements safely — product names are inserted as text, never as HTML.
3. replaceChildren() replaces all options at once.
4. The product drop-down is disabled while loading, preventing selections from an outdated list.

Performance and Security Considerations

Common Mistakes

Returning Raw Database Rows

Effect: Internal or sensitive columns are exposed.

Fix: Map rows to explicit response structures.

Building Queries From Request Parameters

Effect: SQL injection.

Fix: Validate input and use prepared statements.

Inserting Data With innerHTML

Effect: XSS through product names or other stored text.

Fix: Use textContent, new Option(), or DOM methods.

Not Handling Errors in JavaScript

Effect: The page silently stops working.

Fix: Check response.ok and display error messages.

Best Practices

• Keep endpoints small: validate, query with prepared statements, map, respond.
• Return deliberate JSON structures with correct types and status codes.
• Validate parameters against allow-lists or strict patterns.
• Render data safely on the client and handle loading and error states.
• Paginate, cache, and rate-limit where appropriate.

Official reference: https://www.php.net/manual/en/book.mysqli.php