Dynamic table in HTML using MySQL and php

phphtmlmysql


PHP Snippet 1:

<?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');

PHP Snippet 2:

<?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');

PHP Snippet 3:

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

PHP Snippet 4:

<?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>

Related Snippets