Displaying Database Results in HTML

Render fetched database text safely into HTML tables and cards.

The examples focus on the operation itself, including the important distinction: PDO fetch controls retrieval; HTML escaping controls browser interpretation.

What It Does

Use the examples below to observe the behavior on a small dataset before extending it to application code.

Before You Start

The examples use PHP 8.2+ features where shown. Database snippets require the corresponding PDO driver, demo tables, and credentials configured outside source code.

Example 1: Basic Use

Prepared statements protect SQL values; htmlspecialchars protects browser output from XSS.

<?php
$pdo = new PDO(
    "mysql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo") . ";charset=utf8mb4",
    getenv("DB_USER") ?: "app",
    getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false]
);
$rows = $pdo->query("SELECT email FROM users ORDER BY id LIMIT 10")->fetchAll(PDO::FETCH_ASSOC);
echo "<ul>";
foreach ($rows as $row) {
    echo "<li>" . htmlspecialchars($row["email"], ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8") . "</li>";
}
echo "</ul>";

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Example 2: Checking Behavior

Prepared statements protect SQL values; htmlspecialchars protects browser output from XSS. Database filtering does not remove the need for HTML escaping.

<?php
$pdo = new PDO(
    "mysql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo") . ";charset=utf8mb4",
    getenv("DB_USER") ?: "app",
    getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false]
);
$rows = $pdo->query("SELECT email FROM users WHERE active = 1 ORDER BY id LIMIT 5")->fetchAll(PDO::FETCH_ASSOC);
echo "<ul>";
foreach ($rows as $row) {
    echo "<li>" . htmlspecialchars($row["email"], ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8") . "</li>";
}
echo "</ul>";

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Example 3: Practical Example

Prepared statements protect SQL values; htmlspecialchars protects browser output from XSS. Database filtering does not remove the need for HTML escaping.

<?php
$pdo = new PDO(
    "mysql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo") . ";charset=utf8mb4",
    getenv("DB_USER") ?: "app",
    getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false]
);
$rows = $pdo->query("SELECT email FROM users ORDER BY email LIMIT 15")->fetchAll(PDO::FETCH_ASSOC);
echo "<ul>";
foreach ($rows as $row) {
    echo "<li>" . htmlspecialchars($row["email"], ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8") . "</li>";
}
echo "</ul>";

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Important Behavior and Edge Cases

Escape each cell with htmlspecialchars and a declared UTF-8 charset; prepared SQL alone does not stop XSS.

Comparison with Related Approaches

Common Beginner Mistakes

Key Takeaways

• Render fetched database text safely into HTML tables and cards.
• Escape each cell with htmlspecialchars and a declared UTF-8 charset; prepared SQL alone does not stop XSS.
• PDO fetch controls retrieval; HTML escaping controls browser interpretation.

Use Displaying Database Results in HTML when its documented behavior matches the problem. Test the relevant edge cases in your own runtime before deploying it.