如何遍历两个MySQL表并按Timestamp合并展示为HTML表格？

1. 编写合并排序的SQL查询 #

由于两个表结构不同，我们需要用UNION ALL统一字段，同时添加类型标记区分评论和文档，再按时间戳排序。注意原时间戳是日/月/年的字符串格式，需转成日期类型保证排序逻辑正确：

SELECT 
    'comment' AS type,
    CommentID AS id,
    Comment AS content,
    Timestamp,
    NULL AS file_path
FROM comments
UNION ALL
SELECT 
    'document' AS type,
    DocumentID AS id,
    NULL AS content,
    Timestamp,
    FilePath AS file_path
FROM documents
ORDER BY STR_TO_DATE(Timestamp, '%d/%m/%Y') DESC;

说明：

• type字段标记当前记录是评论还是文档
• 统一id字段对应各自主键，content存评论内容（文档记录为NULL），file_path存文档路径（评论记录为NULL）
• STR_TO_DATE将字符串时间转成日期类型，避免字符串排序的逻辑错误

2. 后端读取数据并生成HTML表格 #

以PHP为例，连接数据库并生成表格：

<?php
// 数据库连接信息
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_dbname";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 执行SQL查询
$sql = "SELECT 
            'comment' AS type,
            CommentID AS id,
            Comment AS content,
            Timestamp,
            NULL AS file_path
        FROM comments
        UNION ALL
        SELECT 
            'document' AS type,
            DocumentID AS id,
            NULL AS content,
            Timestamp,
            FilePath AS file_path
        FROM documents
        ORDER BY STR_TO_DATE(Timestamp, '%d/%m/%Y') DESC;";

$result = $conn->query($sql);

// 生成HTML表格
if ($result->num_rows > 0) {
    echo "<table border='1'>
            <tr>
                <th>类型</th>
                <th>ID</th>
                <th>内容/文件路径</th>
                <th>时间戳</th>
            </tr>";
    // 输出每行数据
    while($row = $result->fetch_assoc()) {
        echo "<tr>";
        echo "<td>" . ($row["type"] == "comment" ? "评论" : "文档") . "</td>";
        echo "<td>" . $row["id"] . "</td>";
        echo "<td>" . ($row["content"] ?? $row["file_path"]) . "</td>";
        echo "<td>" . $row["Timestamp"] . "</td>";
        echo "</tr>";
    }
    echo "</table>";
} else {
    echo "没有数据";
}
$conn->close();
?>

关键注意事项 #

• 如果时间戳字段是DATE或DATETIME类型，直接用ORDER BY Timestamp即可，无需STR_TO_DATE转换
• 若要按升序排序，把DESC改成ASC
• 可根据需求调整HTML表格样式，比如添加CSS美化

内容的提问来源于stack exchange，提问作者Ben