PHP合并两张数据表实现多查询结果拆分展示问题求助

当前代码中，$joinresult结果集在第一次内层while循环时就被完全遍历完毕，结果集指针移到末尾，后续遍历WeekData条目时，该结果集已无数据可读取，导致所有JoinedBy内容只显示在第一个WeekData条目中。

以下两种方法均可解决该问题：

方法一：预加载JoinedBy数据到分组数组 #

先将JoinedBy数据按StreamID分组存入数组，遍历WeekData时直接取出对应ID的关联数据：

<?php
$con = new mysqli("mysql.hostinger.co.uk", "-", "-", "-");            
$con->set_charset('utf8mb4');

// 预加载JoinedBy数据，按StreamID分组存储
$joinData = [];
$joinresult = $con->query("SELECT JoinedBy, StreamID FROM `JoinedBy`") or die($con->error);
while($join = mysqli_fetch_array($joinresult)){
    $streamId = $join['StreamID'];
    if(!isset($joinData[$streamId])){
        $joinData[$streamId] = [];
    }
    $joinData[$streamId][] = $join['JoinedBy'];
}

// 查询WeekData目标数据
$result = $con->query("SELECT StreamDayID, WeekColor, DayName, StreamTitle, StreamGame, TIME_FORMAT(StartTime, '%r') AS StartTime, DATE_FORMAT(StreamDate, '%D %b') AS StreamDate FROM `WeekData` WHERE WeekColor='Blue'") or die($con->error);

echo "<div id='blue' class='day'>";
echo "<h1 class='row'>Blue:</h1>";

while($row = mysqli_fetch_array($result)){
    $currentStreamId = $row['StreamDayID'];
    echo "<div id='blue{$currentStreamId}' class='les'>";
        echo "<div class='title'>";
            echo "<h2>{$row['StreamTitle']}</h2>";
        echo "</div>";
        echo "<div class='detail'>";
            echo "<h4>Day: {$row['DayName']}</h4>";
            echo "<h4>Game: {$row['StreamGame']}</h4>";
            echo "<h4 class='joiners'>Joined By:";
            // 取出当前StreamID对应的JoinedBy列表
            if(isset($joinData[$currentStreamId])){
                foreach($joinData[$currentStreamId] as $joiner){
                    echo "<a class='delink' href='https://twitch.tv/{$joiner}'>{$joiner}</a>";
                }
            }
            echo "</h4>";
            echo "<h4>Start Time: {$row['StartTime']}</h4>";
            echo "<h4>Stream Date: {$row['StreamDate']}</h4>";
        echo "</div>";
    echo "</div>";
};
?>

方法二：用SQL的GROUP_CONCAT直接合并结果 #

通过SQL查询直接将每个WeekData条目对应的JoinedBy合并为字符串，减少PHP数组处理：

<?php
$con = new mysqli("mysql.hostinger.co.uk", "-", "-", "-");            
$con->set_charset('utf8mb4');

// LEFT JOIN关联表，用GROUP_CONCAT合并每个StreamDayID的JoinedBy数据
$result = $con->query("
    SELECT 
        w.StreamDayID, 
        w.WeekColor, 
        w.DayName, 
        w.StreamTitle, 
        w.StreamGame, 
        TIME_FORMAT(w.StartTime, '%r') AS StartTime, 
        DATE_FORMAT(w.StreamDate, '%D %b') AS StreamDate,
        GROUP_CONCAT(j.JoinedBy SEPARATOR ', ') AS JoinedByList
    FROM `WeekData` w
    LEFT JOIN `JoinedBy` j ON w.StreamDayID = j.StreamID
    WHERE w.WeekColor='Blue'
    GROUP BY w.StreamDayID
") or die($con->error);

echo "<div id='blue' class='day'>";
echo "<h1 class='row'>Blue:</h1>";

while($row = mysqli_fetch_array($result)){
    $currentStreamId = $row['StreamDayID'];
    echo "<div id='blue{$currentStreamId}' class='les'>";
        echo "<div class='title'>";
            echo "<h2>{$row['StreamTitle']}</h2>";
        echo "</div>";
        echo "<div class='detail'>";
            echo "<h4>Day: {$row['DayName']}</h4>";
            echo "<h4>Game: {$row['StreamGame']}</h4>";
            echo "<h4 class='joiners'>Joined By:";
            // 拆分合并后的字符串并生成链接
            if(!empty($row['JoinedByList'])){
                $joiners = explode(', ', $row['JoinedByList']);
                foreach($joiners as $joiner){
                    echo "<a class='delink' href='https://twitch.tv/{$joiner}'>{$joiner}</a>";
                }
            }
            echo "</h4>";
            echo "<h4>Start Time: {$row['StartTime']}</h4>";
            echo "<h4>Stream Date: {$row['StreamDate']}</h4>";
        echo "</div>";
    echo "</div>";
};
?>

• 替换代码中数据库连接参数（"-"部分）为实际的用户名、密码、数据库名
• 用$con->error替代旧的mysql_error()，适配mysqli扩展
• 若需在JoinedBy链接间添加分隔符，可在循环中加入（注意避免最后一个元素多余分隔符）

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