high score database

Question

This is a high level/design type question rather than anything specific.

In this game http://koreanwordgame.com/ users have to get as many correct answers as possible in a minute (though its set to 5 seconds for development purposes). As soon as the minute is up I fire an Ajax call with JQuery passing the users score.

On the server side I use this SQL query to get the 5 scores above and 5 below

$getquery = "(SELECT * 
FROM highscore 
WHERE score >= '$score'
ORDER BY score ASC
LIMIT 5)

UNION ALL

(SELECT * 
FROM highscore 
WHERE score < '$score'
ORDER BY score DESC
LIMIT 5)";

This returns an HTML table to the client which you will see upon completing the (5 sec) challenge (you don't have to get any answers right).

Now, ultimately I want the table to be about 100 rows long (although it will still only display the surrounding 10 cells, 5 above, 5 below), with the rows position (ID) determined not chronological as it is now but by the score, so that if someone gets 1000 answers right they would go straight into position 1 on the database. Obviously if a user gets lower than any of the 100 rows already existing in the table then it should simply be discarded. Then when the table comes back I want a text input to appear inside the table cell where the user is placed so they can enter their name and fire off another Ajax call so their entry gets inserted into the right place in the database.

I know it would be easier just to ask the user to enter their details first and then scoop off the top 10 results whether they got there or not, but the idea is that I want anyone to be able to get some kind of entry as this will encourage people to play more.

A tough one perhaps..I certainly haven't been able to find anything similar by Googling, all help much appreciated!

EDIT:

In case anyone is interested this is what I ended up with, it's far from finished I need to make it way more robust for example entering an anonymous username in case the user closes the browser and also prevent double posts (though this will be client side).

but thank you everyone for the help, I would not have done it without you. If you see any obvious improvements that could be made feel free to point them out!

<?php

$dbcon = mysql_connect("localhost", "XXXX", "XXXX") or      die(mysql_error());
mysql_select_db("tulesblo_koreangame", $dbcon) or die(mysql_error());
mysql_query("SET NAMES utf8");

$name = $_POST["name"];
$email = $_POST["email"];
$score = $_POST["score"];
$table = "";
$submit = "";
$input = "";
$newposition = $_POST['position'];

$position = mysql_query("(SELECT position 
    FROM highscore 
   WHERE score < '$score'
ORDER BY score DESC
   LIMIT 1)");

if(!$name){

$gethigherrows = "(SELECT * 
    FROM highscore 
   WHERE score >= '$score'
ORDER BY score ASC
   LIMIT 5)";

$getlowerrows = "(SELECT * 
    FROM highscore 
   WHERE score < '$score'
ORDER BY score DESC
   LIMIT 5)";

$higherrows= mysql_query($gethigherrows);
$lowerrows= mysql_query($getlowerrows);

if(mysql_error())echo mysql_error();

while($row=mysql_fetch_array($higherrows)) 
{
    $uppertable .= "<tr><td>$row[position]</td><td>$row[name]</td>    <td>$row[score]</td></tr>";
}

$x = 0; 
if (mysql_num_rows($lowerrows) > 0)
{   mysql_query("UPDATE highscore SET position = position + 1 WHERE score <  '$score'")or die("update failed");
    while($row=mysql_fetch_array($lowerrows)) 
    {   
        if ($x == 0)
            {$position = $row['position'];};
        $x++;
        $newpos = $row[position]+1;
        $lowertable.= "<tr><td>$newpos</td><td>$row[name]</td>    <td>$row[score]</td></tr>";
    }
    $input = "<tr><td id='position'>$position</td><td><input  id='nameinput'type='text' /></td><td>$score</td></tr>";
    $submit = "<br />Enter email if you want to receive a prize!<br /><input  id='emailinput'type='text' /><br /><input id='submithighscore'type='submit'  value='Submit'>";
}

$table .= "<table id='scoretable'><tr><th>Position</th><th>Name</th><th>Score</th></tr>";
$table .= $uppertable;
$table .= $input;
$table .= $lowertable;
$table .= "</table>";
$table .= $submit;
$table .= "<br /><span class='msg'></span>";
echo $table;

}else{  echo($newposition);
mysql_query("INSERT INTO highscore VALUES (NULL, '$score', '$name', '$email',     '$newposition')");
}

?>

Answer 1

You did not say where lies your problem, but I suppose it's mostly a misconception about SQL. Here's how I would do the whole app.

For the SQL schema:

• You shouldn't care about the order of the rows in the DB. You can set the order in the queries (SELECT, etc). There is no "right place" to insert.
• You don't need a "rank" column, for the same reason.
• Your table can be as simple as: id (INT AUTO_INCREMENT), name, score, when (timestamp).
• Initialize it with 100 dummy scores.

For the workflow:

• Do not care for performance now, aim for readability.
• As you wrote, once a player has finished, use AJAX to find the 10 surrounding scores. You already have the SQL queries for this part. You can either prepare the HTML on the server side in PHP, or send raw data in order build the HTML with Javascript. This can be done in one request (use json_encode() in PHP to return an array/object).
• If there are no scores below, then it's not one of the 100 best scores, do nothing.

• Else, ask the user its name, and send a new AJAX request. The SQL can be kept simple:

  INSERT INTO Highscore SET name=?, score=?, when=NOW()

• After each insertion, delete the lowest score

  DELETE FROM Highscore ORDER BY score ASC, when DESC LIMIT 1

• If you want to prevent cheating, you'll need to add hashes in your ajax calls. For instance, send (username, score, hash=md5(username.score."secretphrase") and check the hash still matches the username and score.

• If you want to display the ranks along with the scores, then put a "rank" colum in your table. When you insert a score, set its rank to the max rank of the lower scores (you already computed this for the first AJAX request, so send it in the second AJAX request). Then UPDATE Highscore SET rank = rank + 1 WHERE score < ?.

Answer 2

I'm havinga pickle to understand what you're exactly after. Here's a query to insert the result in the highscore.

$str_user_id = $_GET['user_id'];
$str_score = $_GET['score'];

mysql_query("
INSERT INTO highscore (position, user_id, score)
SELECT (@rownum := $rownum + 1) AS position, user_id, score
FROM (SELECT user_id, score
      FROM (SELECT '{$str_user_id}' AS user_id, '{$str_score}' AS score
            UNION ALL
            SELECT user_id, score
            FROM highscore) AS h, (@rownum := 0) AS vars
      WHERE '{$str_score}' <= 100
      ORDER BY score ASC) AS h2)
LIMIT 100
ON DUPLICATE KEY UPDATE user_id = VALUES(user_id), score = VALUES(score)
");

Answer 3

I couldn't entirely make sense of your question, but based on this:

"I know it would be easier just to ask the user to enter their details first and then scoop off the top 10 results whether they got there or not, but the idea is that I want anyone to be able to get some kind of entry as this will encourage people to play more"

I suspect you want the score table shown to the user to look something like this:

    1. John Doe (score: 1,000,000)
    2. Larry Roe (score: 999,999)
    3. Jane Doe (score: 985,742)
 ...
 5746. Susan Player (score: 894)
 5747. *** YOU *** (score: 893)
 5748. Joe R. Player (score: 889)

I second Mytskine's initial suggestion that you shouldn't try to store the ranks in the DB at all — keeping a rank column up to date is possible, like Mytskine shows at the end of their answer, but it's a lot of work for little if any gain. Just have your score table consist of the obvious columns like name and score and time, plus maybe an AUTO_INCREMENT column id.

Now, let's say you want to show the N highest-scoring players, plus K players before and after the new score. First, we need to figure out the player's rank:

SELECT COUNT(*) + 1 FROM scores WHERE score > ?

Here, the ? is a placeholder for the player's score. This counts how many players have a higher score than the one we're going to insert below. If we favor the latest scores in the case of ties, this plus one will be the player's rank. Let's denote this rank by R.

Now, if R < N + K, we might was well just show the top N + 2*K (or max(N, R+K) or whatever) entries:

SELECT * FROM scores ORDER BY score DESC, time DESC LIMIT N+2*K

Otherwise, we need to do it in three steps: first, fetch the top N entries:

SELECT * FROM scores ORDER BY score DESC, time DESC LIMIT N

then, the K entries above the player:

SELECT * FROM scores WHERE score > ? ORDER BY score ASC, time ASC LIMIT K

and finally the K entries below the player:

SELECT * FROM scores WHERE score <= ? ORDER BY score DESC, time DESC LIMIT K

Note that the second result set will be in reverse order, but that's OK — just reverse it in PHP. Now we need to assign ranks to the entries we've fetched, but that's simple: the first set has ranks 1 to N, the second set (after reversal) has ranks R-K to R-1, and the last set has ranks R+1 to R+K. (Rank R belongs to the current player, of course.)

Finally, after the player has entered their name, we can insert the player's score into to the database simply with:

INSERT INTO scores (name, score, time) VALUES (?, ?, ?)

If you want, you can also remove the lowest scores from the database at this point to limit its size, but in practice it probably won't matter unless you have literally billions of players. (A single score entry is going to take a few dozen bytes, mostly depending on how long a name the player enters, so a million entries will still be just a few dozen megabytes.) You should, however, remember to create an index on (score, time) to keep the queries efficient.