MySQL Loops only displaying One User

[Lamez]

My loop is only displaying one user, how do I get it to display all records?

 

The information is coming from three different tables. The loop is coming out of userpoints.

 

here is the code:

<?php
include ("../../style/include/session.php"); 
include ("../../style/include/cons/head_2.php");
print '<div class="box">';
if($session->logged_in){
include ("../../style/include/checkuser.php");
?>
<table width="100%" border="0">
  <tr>
    <td> </td>
    <td><strong>Username</strong></td>
    <td><strong>Store # </strong></td>
    <td><strong>2nd Round </strong></td>
    <td><strong>Sweet 16 </strong></td>
    <td><strong>Elite 8</strong></td>
    <td><strong>Final 4 </strong></td>
    <td><strong>Finals</strong></td>
    <td><strong>Championship</strong></td>
    <td><strong>Tie Breaker </strong></td>
    <td><strong>Total</strong></td>
  </tr>
<?php
$q = "SELECT * FROM `userpoints` ORDER BY `total`";
$r = mysql_query($q);

while ($rs=mysql_fetch_array($r)){
$q = "SELECT * FROM `users` WHERE `username`='".$rs['username']."'";
$r = mysql_query($q);
$re = mysql_fetch_array($r);

$q = "SELECT * FROM `champ` WHERE `username`='".$rs['username']."'";
$r = mysql_query($q);
$tb = mysql_fetch_array($r);
?>
  <tr>
    <td> </td>
    <td><?php echo $rs['username']; ?></td>
    <td><?php echo $re['store']; ?></td>
    <td><?php echo $rs['rnd1']; ?></td>
    <td><?php echo $rs['rnd2']; ?></td>
    <td><?php echo $rs['rnd3']; ?></td>
    <td><?php echo $rs['rnd4']; ?></td>
    <td><?php echo $rs['rnd5']; ?></td>
    <td><?php echo $rs['champ']; ?></td>
    <td><?php echo $tb['tb']; ?></td>
    <td><?php echo $rs['total']; ?></td>
<?
}
?>
  </tr>
</table>
<?php
}else{
include ("../../style/include/cons/member.php");
}
print '</div>';
include ("../../style/include/cons/foot.php");
?>

 

[Lamez]

I did not think this would be hard for the experts out there. Any Idea?

[roxki]

You're sure you've the same users in all 3 MySQL tabels?

[Lamez]

yep.

 

I looked and made sure the information match to the user. But I am only get one user to output. I do not know why.

[kenrbnsn]

You're using the same variable "$r" to store the results of the different mysql_query() calls, so the while loop is being screwed up. Use different variables:

<?php
$q = "SELECT * FROM `userpoints` ORDER BY `total`";
$r = mysql_query($q);

while ($rs=mysql_fetch_array($r)){
$q = "SELECT * FROM `users` WHERE `username`='".$rs['username']."'";
$r1 = mysql_query($q);
$re = mysql_fetch_array($r1);

$q = "SELECT * FROM `champ` WHERE `username`='".$rs['username']."'";
$r2 = mysql_query($q);
$tb = mysql_fetch_array($r2);
?>
  <tr>
    <td> </td>
    <td><?php echo $rs['username']; ?></td>
    <td><?php echo $re['store']; ?></td>
    <td><?php echo $rs['rnd1']; ?></td>
    <td><?php echo $rs['rnd2']; ?></td>
    <td><?php echo $rs['rnd3']; ?></td>
    <td><?php echo $rs['rnd4']; ?></td>
    <td><?php echo $rs['rnd5']; ?></td>
    <td><?php echo $rs['champ']; ?></td>
    <td><?php echo $tb['tb']; ?></td>
    <td><?php echo $rs['total']; ?></td>
<?php
}
?>

 

Ken

[Lamez]

Wow that fixed it, thanks.