Dynamic table in HTML using MySQL and php

Question

I've seen many posts about how to build a table in HTML with PHP and Mysql, but my problem is, that I often change the headers of MySQL columns. Is there any way that PHP automatically updates the code so that I can just put in the table name and it prints the table without that I have to put in all the tags?

<?php

$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();

Answer 1

If you want to display the full contents of the database table as an HTML table, I suggest you make a function that will do all of this dynamically for you. This function should check that the table exists, fetch all the data, and fetch output HTML table with headers.

MySQLi solution

Here is my suggestion using MySQLi. First of all, you must make sure that the table actually exists. Then you can fetch all the data from the table. The object returned by mysqli::query() will have all metadata information about column names which you can use to display the header row. You can use fetch_fields() to iterate over each column metadata. The data can be fetched using fetch_all() method.

<?php

// create global connection using mysqli
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)
{
    // Make sure that the table exists in the current database!
    $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>';
    // Display table header
    echo '<thead>';
    echo '<tr>';
    foreach ($res->fetch_fields() as $column) {
        echo '<th>'.htmlspecialchars($column->name).'</th>';
    }
    echo '</tr>';
    echo '</thead>';
    // If there is data then display each row
    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

Using PDO is very similar but you have to pay attention to the differences in the APIs.

To get the table names, you can use fetchAll(PDO::FETCH_COLUMN) instead of array_column(). To get the column metadata, you need to use getColumnMeta() function.

<?php

$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)
{
    // Make sure that the table exists in the current database!
    $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>';
    // Display table header
    echo '<thead>';
    echo '<tr>';
    for ($i = 0; $i < $columnCount; $i++) {
        echo '<th>'.htmlspecialchars($stmt->getColumnMeta($i)['name']).'</th>';
    }
    echo '</tr>';
    echo '</thead>';
    // If there is data then display each row
    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');

P.S. The table existence check can be optimized with the following code instead:

$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!');
}

Answer 2

I just read your question, and coded to meet your needs, but I used PDO and not MYSQLI, maybe it is better for you to understand the code, because with PDO everything is simpler. And the way I did it, you don't need to put all the tags, PHP will update automatically, and know that the file must end with the extension .php!

<?php

$conn = new PDO("mysql:host=localhost;dbname=YOUR_DATABASE_HERE", "USERNAME", "PASSWORD");


if(!$conn){
    echo "Could not connect!";
}          

?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
    <style>
        table, th, td {
            border: 1px solid black;
        }
    </style>
</head>
<body>
    <table style="width:100%;">
        <thead>
        <tr>
            <th>ID</th>
            <th>FirstName</th>
            <th>LastName</th>
            <th>Birthday</th>
            </tr>
        </thead>
        <tbody>
        <?php
        
        $sql = "SELECT * FROM user";
        $result = $conn->prepare($sql);
        $result->execute();

        if($result->rowCount() > 0):
            $rows = $result->fetchAll();
            foreach($rows as $row):
        ?>
        <tr>
            <td><?php echo $row['id'];  ?></td>
            <td><?php echo $row['first_name']; ?></td>
            <td><?php echo $row['last_name']; ?></td>
            <td><?php echo $row['birthday']; ?></td>
        </tr>
        </tbody>

        <?php
        endforeach;
    endif;
        ?>
    </table>
</body>
</html>