How Can I Dynamically Generate HTML Tables from a MySQL Database Using PHP?

Dynamically Creating HTML Tables Using MySQL and PHP

Despite the availability of posts describing how to construct tables in HTML using PHP and MySQL, frequent changes to MySQL column headers after table creation can be a hassle. This article explores a method to automatically update the PHP code, allowing you to specify the table name and have the table printed without the need for manual insertion of tags.

$table = "user";
$database = "database";
$conn = mysqli_connect("localhost", "username", "password", "database", "3306");

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

$sql = "SELECT * FROM $table";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "<tr><td>" . $row["id"] . "</td><td>" . $row["first_name"] . "</td><td>" . $row["last_name"] . "</td><td>" . $row["birthday"] . "</td></tr>";
    }
    echo "</table>";
} else {
    echo "0 result";
}

$conn->close();

Dynamic Solution

To simplify the process, a function can be created to perform these operations dynamically. It checks for the existence of the table, fetches the data, and generates the HTML table with headers.

MySQLi Solution

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = mysqli_connect("localhost", "username", "password", "database", "3306");
$mysqli->set_charset('utf8mb4'); // always set the charset

function outputMySQLToHTMLTable(mysqli $mysqli, string $table)
{
    // Verify table existence
    $tableNames = array_column($mysqli->query('SHOW TABLES')->fetch_all(), 0);
    if (!in_array($table, $tableNames, true)) {
        throw new UnexpectedValueException('Unknown table name provided!');
    }

    $res = $mysqli->query('SELECT * FROM ' . $table);
    $data = $res->fetch_all(MYSQLI_ASSOC);

    echo '<table">';
    // Table header
    echo '<thead>';
    echo '<tr>';
    foreach ($res->fetch_fields() as $column) {
        echo '<th' . htmlspecialchars($column->name) . '</th>';
    }
    echo '</tr>';
    echo '</thead>';

    // Table body
    if ($data) {
        foreach ($data as $row) {
            echo '<tr>';
            foreach ($row as $cell) {
                echo '<td' . htmlspecialchars($cell) . '</td>';
            }
            echo '</tr>';
        }
    } else {
        echo '<tr><td colspan="' . $res->field_count . '">No records in the table!</td></tr>';
    }
    echo '</table>';
}

outputMySQLToHTMLTable($mysqli, 'user');

PDO Solution

$pdo = new PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", 'username', 'password', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false
]);

function outputMySQLToHTMLTable(pdo $pdo, string $table)
{
    // Verify table existence
    $tableNames = $pdo->query('SHOW TABLES')->fetchAll(PDO::FETCH_COLUMN);
    if (!in_array($table, $tableNames, true)) {
        throw new UnexpectedValueException('Unknown table name provided!');
    }

    $stmt = $pdo->query('SELECT * FROM ' . $table);
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $columnCount = $stmt->columnCount();

    echo '<table">';
    // Table header
    echo '<thead>';
    echo '<tr>';
    for ($i = 0; $i < $columnCount; $i++) {
        echo '<th' . htmlspecialchars($stmt->getColumnMeta($i)['name']) . '</th>';
    }
    echo '</tr>';
    echo '</thead>';

    // Table body
    if ($data) {
        foreach ($data as $row) {
            echo '<tr>';
            foreach ($row as $cell) {
                echo '<td' . htmlspecialchars($cell) . '</td>';
            }
            echo '</tr>';
        }
    } else {
        echo '<tr><td colspan="' . $columnCount . '">No records in the table!</td></tr>';
    }
    echo '</table>';
}

outputMySQLToHTMLTable($pdo, 'user');

This optimized solution verifies table existence using a more efficient query:

$tableNames = $pdo->prepare('SELECT COUNT(1) FROM information_schema.TABLES WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME=?');
$tableNames->execute([$table]);
if (!$tableNames->fetchColumn()) {
    throw new UnexpectedValueException('Unknown table name provided!');
}

The above is the detailed content of How Can I Dynamically Generate HTML Tables from a MySQL Database Using PHP?. For more information, please follow other related articles on the PHP Chinese website!