SQL Query and while

[TheOner]

Hi
I made this little TOP-score list and it works, BUT is this the good way when there are two querys and other one is inside while. Is there another way to do this? I have always made like this :quiet: . This is my bad habitat. I can have like 10 different querys in same page and many can be inside while loop. What is the RIGHT and less s**t way? :bemused:

  

<?
    $first_query = @mysql_query("SELECT expert_id, SUM(total) AS total FROM bets GROUP BY expert_id ORDER BY total DESC");
    while($fq = @mysql_fetch_array($first_query))
    {
      $second_query = @mysql_query("SELECT DISTINCT username FROM users WHERE id = '$fq[expert_id]'");
      $sq = @mysql_fetch_array($second_query);

  $number++;

  echo '
  <tr>
    <td width="40">'.$number.'.</td>
    <td width="120"><b>'.$fq["username"].'</b></td>
    <td align="right">'.$sq["total"].'p</td>
  </tr>
  ';
}
?>

[tomhath]

What you want is a JOIN. There are several different ways to join tables in one query so do a little reading to get the full benefit from them:

<?
    $first_query = @mysql_query("SELECT b.expert_id, SUM(total) AS total, u.username FROM bets b inner join users u on b.expert_id = u.id GROUP BY b.expert_id, u.username ORDER BY total DESC");
    while($fq = @mysql_fetch_array($first_query))
    {      

      $number++;

  echo '
  <tr>
    <td width="40">'.$number.'.</td>
    <td width="120"><b>'.$fq["username"].'</b></td>
    <td align="right">'.$fq["total"].'p</td>
  </tr>
  ';
}
?>