Dynamic Table In Html Using Mysql And Php

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 automatic

Solution 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 charsetfunctionoutputMySQLToHTMLTable(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)) {
        thrownewUnexpectedValueException('Unknown table name provided!');
    }
    $res = $mysqli->query('SELECT * FROM '.$table);
    $data = $res->fetch_all(MYSQLI_ASSOC);
    
    echo'<table>';
    // Display table headerecho'<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 rowif ($data) {
        foreach ($dataas$row) {
            echo'<tr>';
            foreach ($rowas$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
]);

functionoutputMySQLToHTMLTable(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)) {
        thrownewUnexpectedValueException('Unknown table name provided!');
    }
    $stmt = $pdo->query('SELECT * FROM '.$table);
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $columnCount = $stmt->columnCount();
    
    echo'<table>';
    // Display table headerecho'<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 rowif ($data) {
        foreach ($dataas$row) {
            echo'<tr>';
            foreach ($rowas$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()) {
    thrownewUnexpectedValueException('Unknown table name provided!');
}

Solution 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><htmllang="en"><head><metacharset="UTF-8"><metaname="viewport"content="width=device-width, initial-scale=1.0"><title>Document</title><style>table, th, td {
            border: 1px solid black;
        }
    </style></head><body><tablestyle="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($rowsas$row):
        ?><tr><td><?phpecho$row['id'];  ?></td><td><?phpecho$row['first_name']; ?></td><td><?phpecho$row['last_name']; ?></td><td><?phpecho$row['birthday']; ?></td></tr></tbody><?phpendforeach;
    endif;
        ?></table></body></html>