current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I'm trying to loop through a list of results from MySQL and display all the unique game names and the count for each in an HTML table format. It works fine up until the last row which doesn't display the name or count. If the last name has multiple rows then it displays the name but not the count.

My code:

$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++;
}
share|improve this question
    
what do u want to show in the table ? just the game names ? –  kritya Jun 22 '12 at 20:30
    
Welcome to Stack Overflow! Please, don't use mysql_* functions for new code. They are no longer maintained and the community has begun the deprecation process. See the red box? Instead you should learn about prepared statements and use either PDO or MySQLi. If you can't decide, this article will help to choose. If you care to learn, here is good PDO tutorial. –  Second Rikudo Jun 22 '12 at 20:34
    
Just to be clear, you want to see a count for each game name, is that correct? –  Drewness Jun 22 '12 at 20:34

3 Answers 3

up vote 1 down vote accepted

You can do this all with the query

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

So then:

$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; ?>
share|improve this answer
    
Problem Solved! This worked perfectly. Thanks! –  user1475843 Jun 23 '12 at 18:55
up vote 0 down vote

You call this function once outside the while loop it should, it should only be called inside the while loop to ensure all rows are outputted.

mysql_fetch_assoc( $propertyGameResult);
share|improve this answer
up vote 1 down vote

For the last row to show, you need to change:

if(($gameCount == 0) or ($rowCounter == $number)){

TO

if(($gameCount == 0) or ($rowCounter-1 == $number)){

This is because you are starting the count at 0 and don't put the row count until after this call.

You also should change:

if(($currentGameName == $starterName) or ($lastGameName == $currentGameName)){

TO

if((($currentGameName == $starterName) or ($lastGameName == $currentGameName)) && ($rowCounter-1 != $number)){

That is how to fix your code, but you could do this by optimizing your query.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.