StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <p>For the SQL schema:</p> <ul> <li>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.</li> <li>You don't need a "rank" column, for the same reason.</li> <li>Your table can be as simple as: id (INT AUTO_INCREMENT), name, score, when (timestamp).</li> <li>Initialize it with 100 dummy scores.</li> </ul> <p>For the workflow:</p> <ul> <li>Do not care for performance now, aim for readability.</li> <li>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 <code>json_encode()</code> in PHP to return an array/object).</li> <li>If there are no scores below, then it's not one of the 100 best scores, do nothing.</li> <li><p>Else, ask the user its name, and send a new AJAX request. The SQL can be kept simple:</p> <p>INSERT INTO Highscore SET name=?, score=?, when=NOW()</p></li> <li><p>After each insertion, delete the lowest score</p> <p>DELETE FROM Highscore ORDER BY score ASC, when DESC LIMIT 1</p></li> <li><p>If you want to prevent cheating, you'll need to add hashes in your ajax calls. For instance, send <em>(username, score, hash=md5(username.score."secretphrase")</em> and check the hash still matches the username and score.</p></li> <li>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 <code>UPDATE Highscore SET rank = rank + 1 WHERE score < ?</code>.</li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload