PHP+PDO合并两张MySQL表按日期分组查询报错求助

现有两张MySQL表：

• hta表：字段包含ta1、ta2、ta3、dateb（datetime类型）
• biometrie表：字段包含taille、poids、date_biometrie（datetime类型）

需要将两表数据合并，按日期分组生成时间线展示效果。单表查询功能正常，但使用UNION ALL联合查询时无法正常工作，相关PHP+PDO代码见下文。

1. UNION ALL字段不匹配：子查询中，第一个SELECT返回5个字段，第二个SELECT仅返回3个字段，字段数量不一致导致联合查询失败
2. 子查询缺少别名：MySQL要求FROM后的子查询必须指定别名，否则语法报错
3. GROUP BY无效/多余：第一个子查询中GROUP BY date的date不是查询字段，属于语法错误；若无需聚合数据，GROUP BY会导致数据丢失
4. SQL注入风险：直接将$username拼接进SQL语句，存在安全漏洞
5. PHP字段引用错误：biometrie表无nnheure字段，但代码中仍尝试输出该字段，会触发未定义索引错误

修正后的SQL查询逻辑 #

SELECT nndate, n1, n2, n3, nnheure, source_table
FROM (
    -- 从hta表提取数据，标记来源
    SELECT 
        ta1 AS n1, 
        ta2 AS n2, 
        ta3 AS n3, 
        DATE_FORMAT(dateb, '%d %b') AS nndate, 
        DATE_FORMAT(dateb, '%H h %i') AS nnheure,
        'hta' AS source_table
    FROM hta 
    WHERE username = :username 
    UNION ALL
    -- 从biometrie表提取数据，填充缺失字段，标记来源
    SELECT 
        poids AS n1, 
        taille AS n2, 
        NULL AS n3, 
        DATE_FORMAT(date_biometrie, '%d %b') AS nndate, 
        DATE_FORMAT(date_biometrie, '%H h %i') AS nnheure,
        'biometrie' AS source_table
    FROM biometrie 
    WHERE username = :username 
) AS combined
-- 按原始日期排序，避免字符串排序逻辑错误
ORDER BY STR_TO_DATE(nndate, '%d %b') DESC, nnheure DESC
LIMIT 0,30

修正后的PHP+HTML代码 #

<!-- Timelime example  -->
<div class="row">
  <div class="col-md-12">
  <!-- The time line -->
  </div>
<div class="timeline">
<?php
session_start();
$username = isset($_SESSION['username']) ? $_SESSION['username'] : NULL;

try
{
    $bdd = new PDO('mysql:host=localhost;dbname=***;charset=utf8', '***', '***');
    $bdd->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // 开启错误调试模式
}
catch(Exception $e)
{
        die('Erreur : '.$e->getMessage());
}

// 使用预处理语句执行联合查询
$query = "
SELECT nndate, n1, n2, n3, nnheure, source_table
FROM (
    SELECT 
        ta1 AS n1, 
        ta2 AS n2, 
        ta3 AS n3, 
        DATE_FORMAT(dateb, '%d %b') AS nndate, 
        DATE_FORMAT(dateb, '%H h %i') AS nnheure,
        'hta' AS source_table
    FROM hta 
    WHERE username = :username 
    UNION ALL
    SELECT 
        poids AS n1, 
        taille AS n2, 
        NULL AS n3, 
        DATE_FORMAT(date_biometrie, '%d %b') AS nndate, 
        DATE_FORMAT(date_biometrie, '%H h %i') AS nnheure,
        'biometrie' AS source_table
    FROM biometrie 
    WHERE username = :username 
) AS combined
ORDER BY STR_TO_DATE(nndate, '%d %b') DESC, nnheure DESC
LIMIT 0,30";

$stmt = $bdd->prepare($query);
$stmt->bindParam(':username', $username);
$stmt->execute();

$nndate = '';

while ($donneesm3 = $stmt->fetch(PDO::FETCH_ASSOC))
{ 
  if ($nndate != $donneesm3['nndate'])
  {
?>
    <!-- timeline time label MOIS PAIRE-->
    <?php echo "<div class='time-label'>
      <span class='bg-secondary'>".$donneesm3['nndate']."</span>
    </div>"; } ?>
    <!-- /.timeline-label -->
              
    <!-- timeline item -->
    <div>
      <!-- 根据数据来源显示不同图标 -->
      <i class="<?php echo $donneesm3['source_table'] == 'hta' ? 'fas fa-tachometer-alt bg-blue' : 'fas fa-weight bg-green'; ?>"></i>
      <div class="timeline-item"> 
        <span class="time"><i class="fas fa-clock"></i> <?php 
          echo $donneesm3['nnheure'] ?? ''; // 处理空值避免报错
      ?> </span>
        <h3 class="timeline-header">
          <div class="row">
            <div class="col-8" align="center">
              <?php if ($donneesm3['source_table'] == 'hta'): ?>
                <!-- 展示hta表数据格式 -->
                <?php echo $donneesm3['n1']; ?> / <?php echo $donneesm3['n2']; ?> / <?php echo $donneesm3['n3']; ?>
              <?php else: ?>
                <!-- 展示biometrie表数据格式 -->
                Poids: <?php echo $donneesm3['n1']; ?> | Taille: <?php echo $donneesm3['n2']; ?>
              <?php endif; ?>
            </div>
            <div class="col-4" align="right">
              <a class="btn btn-tool"><i class="fas fa-pencil-alt"></i> </a>
              <a class="btn btn-tool"><i class="fas fa-trash-alt"></i> </a>
            </div>
          </div>
        </h3>
      </div>
    </div>
              
  <?php
  $nndate = $donneesm3['nndate'];
}
$stmt->closeCursor();

?>
</div>

• 保证UNION ALL两边字段数量、类型一致，缺失字段用NULL填充
• 给子查询添加别名，符合MySQL语法要求
• 使用PDO预处理语句，避免SQL注入风险
• 按原始日期排序，避免格式化后字符串排序的逻辑错误
• 根据数据来源区分展示内容，处理biometrie表无n3字段的情况，避免PHP报错
• 移除不必要的GROUP BY，若需聚合数据需配合MAX()/AVG()等聚合函数

内容的提问来源于stack exchange，提问作者Pierre-François Doré