Jump to content


Need help with db query!

  • Please log in to reply
3 replies to this topic

#1 hga77

  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 27 October 2006 - 01:44 PM

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:users{user_id, name}
table 2:codes{the_codes, 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:

user_id     name
-------    ------
1             Jim
2             Bob
3             Lee
4             Peter

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

Result I need after query:
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.

#2 obsidian

  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 27 October 2006 - 02:01 PM

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:
$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;

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
You can't win, you can't lose, you can't break even... you can't even get out of the game.

while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 hga77

  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 27 October 2006 - 04:04 PM

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

#4 hga77

  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 27 October 2006 - 04:15 PM

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

Cheers for that ;)

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users