Need help with db query!

[hga77]

I have two tables. codes and users. The codes table holds the unique codes along with a reference key which is the user_id, to link to the users table.

They look like this:

table 1:[b]users[/b]{[u]user_id[/u], name}
table 2:[b]codes[/b]{[u]the_codes[/u], user_id, final_score, seconds}

Users must answer 10 questions in the fastest time. This query is needed for the leaderboard.

Lets fill those tables with data:

[b]users[/b]
user_id     name
-------    ------
1             Jim
2             Bob
3             Lee
4             Peter

[b]codes[/b]
the_codes     user_id      final_score      seconds
----------    --------     -----------     -----
1111               1                10               5
2222               1                10               4
3333               1                6                 2
4444               3                10               8
5555               4                8                 8

[b]Result I need after query:[/b]
the_codes     user_id      final_score      seconds    name
----------    --------     -----------     -----       ------
2222               1                10               4           Jim
4444               3                10               8           Lee
5555               4                8                 8          Peter

**notice the order from top to bottom, heighest score with lowest time, and without duplicating all the scores for Jim, just his best score!

Now I need to query those two tables and get a list of scores (dont want scores by the same user, just one score per user being the best score ofcourse) starting with the highest score with the lowest time, onwards. I can do this fine, but I cant remove the duplicates. I have tried Distict 'user_id', Group By 'user_id', Union etc. But I'm not an SQL master so I dont really know advanced sql.

This is the current sql Im using:

$sql = "SELECT * FROM codes, users WHERE codes.user_id=users.user_id ORDER BY final_score DESC, seconds ASC";

Please tell me how I can modify this so I dont get those annoying duplicates and still get the best score per user.

Thank you.

[obsidian]

I'm not sure right off how you'd limit your results to your restrictions with a single query because of the aggregates that would have to be used, but you could use something like this to filter your results, and you'd be good to go:
[code]
<?php
$sql = mysql_query("SELECT u.name, c.* FROM codes c, users u WHERE c.user_id = u.user_id ORDER BY final_score DESC, seconds ASC");
if (mysql_num_rows($sql) > 0) {
  $res = array();
  // since we know that the first record will be their best, we simply list their first record alone:
  while ($x = mysql_fetch_array($sql)) {
    if (!isset($res[{$x['user_id']}])) {
      $res[{$x['user_id']}] = $x;
    }
  }
}
?>
[/code]

Now, your $res variable contains an array of ONLY one score per user. You can loop through it with the foreach(), and you can display them as you see fit.

Hope this helps

[hga77]

Thanks for this. I wanted to have the sql do all the hard work but this should work great too. Now the other issue is Im struggling to use foreach to retrieve the content. Maybe an example :)

Thanks mate

[hga77]

Thanks mate it works now...hours of fustrations and now its good to go...:)

Cheers for that ;)