PHP 和 MySQL - 遍历结果并显示每个条目的名称和计数

Question

我正在尝试遍历 MySQL 的结果列表，并以 HTML 表格格式显示所有唯一的游戏名称和每个游戏的计数。它工作正常，直到最后一行不显示名称或计数。如果姓氏有多行，则它会显示姓名而不是计数。

我的代码:

$propertyGameQuery = "SELECT G.GAME_NAME FROM GAMES AS G, PROPERTY_GAMES AS PG WHERE PG.PROPERTY_ID=$cid AND G.ID=PG.GAME_ID ORDER BY G.GAME_NAME";
$propertyGameResult = mysql_query($propertyGameQuery);

$gameCount = 0;
$rowCounter = 0;
$number = mysql_num_rows($propertyGameResult);
$rws = mysql_fetch_assoc($propertyGameResult);
$starterName = $rws['GAME_NAME'];

while($propertyGames = mysql_fetch_assoc($propertyGameResult)) {
    $currentGameName = $propertyGames['GAME_NAME'];
    if(($gameCount == 0) or ($rowCounter == $number)){
    ?>
        <tr class="game-table"><td width="80%" title="<? echo $currentGameName; ?>" align="left" style="padding-left:5px;"><? echo $rowCounter ?> <? echo $currentGameName; ?></td>            
    <? 
    }
    if(($currentGameName == $starterName) or ($lastGameName == $currentGameName)){ 
        $gameCount = $gameCount + 1;    
    }else{
    ?>
        <td width="20%" align="center"><? echo $gameCount + 1; ?></td></tr>                             
    <? 
        $gameCount = 0;  
    } ?>
    <?
    $lastGameName = $currentGameName;
    $rowCounter++;
} 

Answer 1

你可以用查询来完成这一切

SELECT G.GAME_NAME, COUNT(*) as nb_games 
FROM GAMES G, PROPERTY_GAMES PG 
WHERE PG.PROPERTY_ID = $cid 
AND G.ID=PG.GAME_ID 
GROUP BY G.GAME_NAME
ORDER BY G.GAME_NAME

然后:

$sql = "SELECT G.GAME_NAME, COUNT(*) as nb_games"
    . " FROM GAMES G, PROPERTY_GAMES PG" 
    . " WHERE PG.PROPERTY_ID = $cid" 
    . " AND G.ID=PG.GAME_ID" 
    . " GROUP BY G.GAME_NAME"
    . " ORDER BY G.GAME_NAME";

$propertyGameResult = mysql_query($propertyGameQuery);
$counter = 0;
while(false !== ($rws = mysql_fetch_assoc($propertyGameResult)): $counter++; ?>
<tr class="game-table">
  <td width="80%" title="<?php echo $rws['GAME_NAME']; ?>" align="left" style="padding-left:5px;">  
    <?php echo $counter ?> <?php echo $rws['GAME_NAME']; ?>
  </td>
  <td width="20%" align="center">
    <?php echo $rws['nb_games']; ?>
  </td>
</tr>
<?php endwhile; ?>