使用MySQL和php的HTML中的动态表 [英] Dynamic table in HTML using MySQL and php

问题描述

我看过很多关于如何使用PHP和Mysql在HTML中构建表的文章，但是我的问题是，我经常更改MySQL列的标题.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 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();

推荐答案

如果要将数据库表的全部内容显示为HTML表，建议您创建一个函数来为您动态地完成所有这些工作.此函数应检查表是否存在，获取所有数据并获取带有标题的输出HTML表.

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的建议.首先，您必须确保该表确实存在.然后，您可以从表中获取所有数据. mysqli :: query() 将具有有关列名称的所有元数据信息，可用于显示标题行.您可以使用 fetch_fields() 遍历每个列元数据.可以使用 fetch_all() 方法.

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解决方案

使用PDO非常相似，但是您必须注意API之间的差异.

PDO Solution

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

要获取表名，可以使用 fetchAll(PDO:: FETCH_COLUMN) ，而不是 array_column().要获取列元数据，您需要使用 getColumnMeta() 函数.

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..可以使用以下代码来优化表存在检查:

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

这篇关于使用MySQL和php的HTML中的动态表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！