MySQL多表查询后PHP处理丢失留言的问题排查

数据老陈

2026-7-3

问题：获取用户信息时丢失第一条留言

场景说明

数据库包含两张表：

Users表：userID, username, password, email
Guestbook表：gbID, userID, toID, text, date

需求是通过$_GET['userID']获取目标用户的信息，同时拉取该用户收到的所有留言（toID = $_GET['userID']）。

问题现象

SQL语句经验证无逻辑错误，但PHP处理代码出现异常：

先执行$userData = $db->fetchAssoc($stmt)提取用户信息，再用while循环遍历留言时，会丢失第一条留言
若将while循环放在$userData赋值之前，则能正常获取所有留言

问题代码示例

<?php 
$thisId = intval($_GET['userID']);
$sql = "
    SELECT
        u1.*,
        g.text AS entry_text,
        g.date AS guestbook_date,
        u2.userID AS from_userID,
        u2.username AS from_username
    FROM
        users AS u1
    LEFT JOIN
        guestbook AS g ON u1.userID = g.toID
    INNER JOIN
        users AS u2 ON u2.userID = g.userID 
    WHERE
        u1.userID = :userId
    ORDER BY
        g.date DESC;";

$stmt = $db->prepare($sql);
$stmt->bindParam(':userId', $thisId, PDO::PARAM_INT);
$stmt->execute();

$userData = $db->fetchAssoc($stmt); // 取出第一条结果
if (empty($userData)) { 
    echo '<div class="column"><h2><span class="blue">PLAY.</span> Profil ej hittad</h2><div class="alert-error">Användaren du försökte hitta finns ej i vårat system...</div></div>';
}
else { 
    // 渲染用户信息部分
    echo '<div class="column column-60">';
    echo '<h2><span class="radius"><span class="blue">PLAY.</span> Profil</span></h2>';
    echo '<div class="UserTitle">'.$userData['username'].' </div>';
    echo '<div class="Content">'; 
    echo '<div class="ImageBox"><img src="/img/users/1.png"></div>';
    echo '</div>';
    echo '</div>';
    echo '<div class="column column-40">'; 
    echo '<h2><span class="radius"><span class="blue">PLAY.</span> Gästbok</span></h2>';
    if (empty($userData['entry_text'])) {
            echo '<div class="alert-error">Tyvärr så har inte '.$userData['username'].' några gästboksinlägg :(</div>'; 
    }
    else { 
    echo '<table cellspacing="0" cellpadding="0" class="GuestBook">';
     while ($row = $db->fetchAssoc($stmt)) {    // 只能从第二条结果开始遍历
        echo '<tr><td width="60" valign="top"><div class="ImageBox"><img src="/img/users/1.png"></div></td><td valign="top"><div class="Title"><a href="/user/'.$row['from_userID'].'">'.$row['from_username'].'</a> - '.date('Y-m-d H:i', strtotime($row['guestbook_date'])).'</div><div class="Content">'.$row['entry_text'].'</div></td></tr>';
    }
    echo '</table>';
    echo '<div class="pagination"></div>';
    }
    if ($userID != $thisId) { echo '<form method="post"><input type="hidden" name="toID" value="'.$thisId.'"><textarea name="text" placeholder="Skriv ett meddelande..."></textarea><input type="submit" name="addGB" value="Skicka" class="btn-blue"></form>'; }
    echo '</div>';
}
$db->close();
?>

原因分析

PDO的结果集使用单向游标，每次调用fetchAssoc()都会将游标向前移动一位并取出当前行数据。第一次执行$db->fetchAssoc($stmt)已经取出了包含用户信息和第一条留言的结果，后续while循环只能从第二条结果开始遍历，导致第一条留言丢失。

解决方案

提供两种可行的修复方式：

方式1：一次性取出所有结果，再分离用户信息与留言

<?php 
$thisId = intval($_GET['userID']);
$sql = "
    SELECT
        u1.*,
        g.text AS entry_text,
        g.date AS guestbook_date,
        u2.userID AS from_userID,
        u2.username AS from_username
    FROM
        users AS u1
    LEFT JOIN
        guestbook AS g ON u1.userID = g.toID
    INNER JOIN
        users AS u2 ON u2.userID = g.userID 
    WHERE
        u1.userID = :userId
    ORDER BY
        g.date DESC;";

$stmt = $db->prepare($sql);
$stmt->bindParam(':userId', $thisId, PDO::PARAM_INT);
$stmt->execute();

$allResults = $stmt->fetchAll(PDO::FETCH_ASSOC); // 一次性获取所有结果
if (empty($allResults)) { 
    echo '<div class="column"><h2><span class="blue">PLAY.</span> Profil ej hittad</h2><div class="alert-error">Användaren du försökte hitta finns ej i vårat system...</div></div>';
}
else { 
    $userData = $allResults[0]; // 提取用户信息（所有行用户信息一致）
    // 渲染用户信息部分
    echo '<div class="column column-60">';
    echo '<h2><span class="radius"><span class="blue">PLAY.</span> Profil</span></h2>';
    echo '<div class="UserTitle">'.$userData['username'].' </div>';
    echo '<div class="Content">'; 
    echo '<div class="ImageBox"><img src="/img/users/1.png"></div>';
    echo '</div>';
    echo '</div>';
    echo '<div class="column column-40">'; 
    echo '<h2><span class="radius"><span class="blue">PLAY.</span> Gästbok</span></h2>';
    if (empty($userData['entry_text'])) {
            echo '<div class="alert-error">Tyvärr så har inte '.$userData['username'].' några gästboksinlägg :(</div>'; 
    }
    else { 
    echo '<table cellspacing="0" cellpadding="0" class="GuestBook">';
     foreach ($allResults as $row) {    // 遍历所有结果，包含第一条留言
        echo '<tr><td width="60" valign="top"><div class="ImageBox"><img src="/img/users/1.png"></div></td><td valign="top"><div class="Title"><a href="/user/'.$row['from_userID'].'">'.$row['from_username'].'</a> - '.date('Y-m-d H:i', strtotime($row['guestbook_date'])).'</div><div class="Content">'.$row['entry_text'].'</div></td></tr>';
    }
    echo '</table>';
    echo '<div class="pagination"></div>';
    }
    if ($userID != $thisId) { echo '<form method="post"><input type="hidden" name="toID" value="'.$thisId.'"><textarea name="text" placeholder="Skriv ett meddelande..."></textarea><input type="submit" name="addGB" value="Skicka" class="btn-blue"></form>'; }
    echo '</div>';
}
$db->close();
?>

方式2：拆分SQL查询，分别获取用户信息和留言

将原SQL拆分为两个独立查询，避免结果集游标冲突：

<?php 
$thisId = intval($_GET['userID']);

// 先查询用户信息
$userSql = "SELECT * FROM users WHERE userID = :userId";
$userStmt = $db->prepare($userSql);
$userStmt->bindParam(':userId', $thisId, PDO::PARAM_INT);
$userStmt->execute();
$userData = $userStmt->fetchAssoc();

if (empty($userData)) { 
    echo '<div class="column"><h2><span class="blue">PLAY.</span> Profil ej hittad</h2><div class="alert-error">Användaren du försökte hitta finns ej i vårat system...</div></div>';
}
else { 
    // 渲染用户信息部分
    echo '<div class="column column-60">';
    echo '<h2><span class="radius"><span class="blue">PLAY.</span> Profil</span></h2>';
    echo '<div class="UserTitle">'.$userData['username'].' </div>';
    echo '<div class="Content">'; 
    echo '<div class="ImageBox"><img src="/img/users/1.png"></div>';
    echo '</div>';
    echo '</div>';
    echo '<div class="column column-40">'; 
    echo '<h2><span class="radius"><span class="blue">PLAY.</span> Gästbok</span></h2>';
    
    // 再查询用户收到的留言
    $gbSql = "
        SELECT
            g.text AS entry_text,
            g.date AS guestbook_date,
            u.userID AS from_userID,
            u.username AS from_username
        FROM
            guestbook AS g
        INNER JOIN
            users AS u ON u.userID = g.userID 
        WHERE
            g.toID = :toId
        ORDER BY
            g.date DESC;";
    $gbStmt = $db->prepare($gbSql);
    $gbStmt->bindParam(':toId', $thisId, PDO::PARAM_INT);
    $gbStmt->execute();
    $guestbookEntries = $gbStmt->fetchAll(PDO::FETCH_ASSOC);
    
    if (empty($guestbookEntries)) {
            echo '<div class="alert-error">Tyvärr så har inte '.$userData['username'].' några gästboksinlägg :(</div>'; 
    }
    else { 
    echo '<table cellspacing="0" cellpadding="0" class="GuestBook">';
     foreach ($guestbookEntries as $row) {    
        echo '<tr><td width="60" valign="top"><div class="ImageBox"><img src="/img/users/1.png"></div></td><td valign="top"><div class="Title"><a href="/user/'.$row['from_userID'].'">'.$row['from_username'].'</a> - '.date('Y-m-d H:i', strtotime($row['guestbook_date'])).'</div><div class="Content">'.$row['entry_text'].'</div></td></tr>';
    }
    echo '</table>';
    echo '<div class="pagination"></div>';
    }
    if ($userID != $thisId) { echo '<form method="post"><input type="hidden" name="toID" value="'.$thisId.'"><textarea name="text" placeholder="Skriv ett meddelande..."></textarea><input type="submit" name="addGB" value="Skicka" class="btn-blue"></form>'; }
    echo '</div>';
}
$db->close();
?>

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

MySQL多表查询后PHP处理丢失留言的问题排查

场景说明 #

问题现象 #

问题代码示例 #

原因分析 #

解决方案 #

方式1：一次性取出所有结果，再分离用户信息与留言 #

方式2：拆分SQL查询，分别获取用户信息和留言 #