在表中列出结果 - 同时在TD中循环和列表结果

Question

I have tried to find the answer for this but having no joy. I have a while loop that gives me numerous rows outputted in a table. It's here:

$result = mysqli_query($con,"SELECT a.winner AS horseWinner, a.twitter_pubstatus,
a.market, a.racetime, a.racecourse, a.date_dd, a.date_mm, b.username, b.course, 
b.horse,b.type, b.racetime, b.dd, b.mm FROM results a 
INNER JOIN bets b ON a.winner = b.horse WHERE a.twitter_pubstatus = 0 
AND a.market = '$win' AND b.type = '$userwin' 
AND a.date_dd = b.dd AND a.date_mm = b.mm GROUP BY a.winner");
while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['horseWinner'] . "</td>";
echo "<td>" . $row['username'] . "</td>";
echo "</tr>";
}
echo "</table>";

This is all well and good but the issue lies that there is more than one username that is per horseWinner. I have tried to do another while loop within a while loop. So I would like it to list the usernames in the second column seperated by comma.

Many thanks

Answer 1

Add a GROUP_CONCAT() on your username so that it returns a comma separated list -

SELECT a.winner AS horseWinner, a.twitter_pubstatus,
a.market, a.racetime, a.racecourse, a.date_dd, a.date_mm, 

GROUP_CONCAT(b.username) as username, 

b.course, b.horse,b.type, b.racetime, b.dd, b.mm FROM results a 
INNER JOIN bets b ON a.winner = b.horse WHERE a.twitter_pubstatus = 0 
AND a.market = '$win' AND b.type = '$userwin' 
AND a.date_dd = b.dd AND a.date_mm = b.mm GROUP BY a.winner