Trouble with a nested While loop in an SQL statement...

[rogwilco]

Hope the title isn't too off putting!

Anyway I am having trouble getting the following to display what I want correctly. Here's the code then I'll explain;

$sql = "SELECT board FROM leaderboard"; 
$result = mysql_query($sql); 
  while($board = mysql_fetch_array($result, MYSQL_NUM)) 
       { 
        $query = "SELECT $board[0].rank, users.username, $board[0].W FROM users, $board[0] WHERE users.id = $board[0].id ORDER BY W DESC LIMIT 50"; 
         $result2 = @mysql_query ($query); 
         while ($row = mysql_fetch_array($result2, MYSQL_NUM)) 
{echo "<tr><td align=\"center\">$row[0]</td><td align=\"center\">$row[1]</td> 
                         <td align=\"center\">$row[2]</td><td align=\"center\">$board[0]</td></tr>"; 
           } 
           } 
           echo '</table>';

Now a little background re the DB. Table 'leaderboard' contains a field called 'board'. On each board peole record their game results i.e. wins.

I am trying to list who has the most wins out of ALL of the boards. At the moment the While loop is displaying in DESC order the wins in a board, THEN it moves onto the next board and does the same. I want it to list who has the most wins over ALL the boards. I'm in smelling distance but just can't figure the logic of the loop.

Hope someone can understand all that to give some help.

Thanks.

 

[azzazzello]

ok...how about this.

use UNION to get all the things into a single table like this (yea, this is vicious pseudo-code)

$sql = "SELECT board FROM leaderboard";

foreach $board ($boards)
{
 $statement .= "(SELECT $board.id, $board.rank, users.username, $board.W FROM users, $board WHERE users.id = $board.id)
    $statement .= " UNION " unless $last_pass;
}

so you wind up with something like this

$statement = "(SELECT board1.id, board1.rank, users.username, board1.W FROM users, board1 WHERE users.id = board1.id) UNION (SELECT board2.id, board2.rank, users.username, board2.W FROM users, board2 WHERE users.id = board2.id) UNION (SELECT board3.id, board3.rank, users.username, board3.W FROM users, board3 WHERE users.id = board3.id)";

it should give you everything in 1 table. You can then look through the result set, and within your application calculate the sum of W for distinct ids and figure out the winner across the boards.