User Row Count

[Smackie]

Hello

 

        I am trying to get a script going so it will output the user's name, number of games, number of kills, number of draws ect. well  my database table has id, user_name, kills, wins, draws, and bonus. i need to have it where it loops so it shows each user and their games, kills, wins, draws, and bonus. been trying to figure this out for days and ill i can get is it outputs all the users names (more then once) and the games go from 1 to how many id's there are :-\ but here is what i came up with from looking at different tutorials..

 

<table width="595" border="0">

  <tr>

    <td class="txt"><center>::Callsign::</center></td>

    <td class="txt"><center>::Games::</center></td>

    <td class="txt"><center>::Kills::</center></td>

    <td class="txt"><center>::Wins::</center></td>

    <td class="txt"><center>::Draws::</center></td>

    <td class="txt"><center>::Bonus::</center></td>

  </tr>

 

<?php

$games = 0;

$sql = "SELECT * FROM reports";

$result = mysql_query($sql);

while($line = mysql_fetch_array($result)) {

$user_name = $line['user_name'];

$kills = $line['kills'];

$wins = $line['wins'];

$games++;

$draws = $line['draws'];

$bonus = $line['bonus'];

?>

  <tr>

    <td class="txt"><center><?php echo $user_name; ?></center></td>

    <td class="txt"><center><?php echo $games; ?></center></td>

    <td class="txt"><center><?php echo $kills; ?></center></td>

    <td class="txt"><center><?php echo $wins; ?></center></td>

    <td class="txt"><center><?php echo $draws; ?></center></td>

    <td class="txt"><center><?php echo $bonus; ?></center></td>

  </tr>

  <?php } ?>

</table>

[blackcell]

It would be better to use sql statements for each thing and/or sql statements within your main loop.

<?php
$sql = "SELECT * FROM `reports`";
$result = mysql_query($sql) or die("Could not execute query: ".mysql_error);
while($line = mysql_fetch_array($result)) {
   $user_name = $line['user_name'];
   
   //Kills
   $sqlSub = "SELECT `kills` FROM `reports` WHERE `user_name` = '$user_name'";      
   $resultSub = mysql_query($sqlSub ) or die("Could not execute query kills: ".mysql_error);
   $kills = mysql_num_rows($resultSub);

   //Wins
   $sqlSub = "SELECT `wins` FROM `reports` WHERE `user_name` = '$user_name'";      
   $resultSub = mysql_query($sqlSub ) or die("Could not execute query wins: ".mysql_error);
   $wins = mysql_num_rows($resultSub);

   //Draws
   $sqlSub = "SELECT `draws` FROM `reports` WHERE `user_name` = '$user_name'";      
   $resultSub = mysql_query($sqlSub ) or die("Could not execute query draws: ".mysql_error);
   $draws= mysql_num_rows($resultSub);

   //Total Games
   $total = $wins + $draws + $kills;

   //Display the message roughly using echo. You can do this however but make sure it happens in the loop because the variables update each pass of the loop.
   echo "User: $user_name  |  Kills: $kills  |  Wins: $wins  |  Draws: $draws  |  TOTAL: $total";
}
?>

I think you want something like this let me know if you need anything more.

[realjumper]

What about:

 

// Get all the data from the "example" table


$result = mysql_query("SELECT * FROM reports")
or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>Name</th> <th>Games</th> <th>Kills</th> <th>Wins</th> <th>Draws</th> <th>Bonus</th></tr>";

// keeps getting the next row until there are no more to get

while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr>";
       echo "<td>$row['user_name']</td>;
       echo "<td>$row['games']</td>;
       echo "<td>$row['kills']</td>;
       echo "<td>$row['wins']</td>;
echo "<td>$row['draws']</td>;
echo "<td>$row['bonus']</td>;
       echo "</tr>";
}

echo "</table>";

[Smackie]

It would be better to use sql statements for each thing and/or sql statements within your main loop.

<?php
$sql = "SELECT * FROM `reports`";
$result = mysql_query($sql) or die("Could not execute query: ".mysql_error);
while($line = mysql_fetch_array($result)) {
   $user_name = $line['user_name'];
   
   //Kills
   $sqlSub = "SELECT `kills` FROM `reports` WHERE `user_name` = '$user_name'";      
   $resultSub = mysql_query($sqlSub ) or die("Could not execute query kills: ".mysql_error);
   $kills = mysql_num_rows($resultSub);

   //Wins
   $sqlSub = "SELECT `wins` FROM `reports` WHERE `user_name` = '$user_name'";      
   $resultSub = mysql_query($sqlSub ) or die("Could not execute query wins: ".mysql_error);
   $wins = mysql_num_rows($resultSub);

   //Draws
   $sqlSub = "SELECT `draws` FROM `reports` WHERE `user_name` = '$user_name'";      
   $resultSub = mysql_query($sqlSub ) or die("Could not execute query draws: ".mysql_error);
   $draws= mysql_num_rows($resultSub);

   //Total Games
   $total = $wins + $draws + $kills;

   //Display the message roughly using echo. You can do this however but make sure it happens in the loop because the variables update each pass of the loop.
   echo "User: $user_name  |  Kills: $kills  |  Wins: $wins  |  Draws: $draws  |  TOTAL: $total";
}
?>

I think you want something like this let me know if you need anything more.

 

First off wins draws and kills do not add up to how many games each user played. because user could get more then 1 kill in a game. the way i need it is to add up how many user_names that are the same like say Smackie and its in there 5 times ok so the out put would be 5 games

[blackcell]

Alright fine, I had no idea how your games database works. Smackie can write it out for you. Use the mysql_num_rows($result) to get how many rows are affected. So if your looking for how many rows have the user_name of 'dweeb' write your sql like $sql="SELECT `user_name` FROM `reports` WHERE `user_name` = 'dweeb'";