Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I couldn't entirely make sense of your question, but based on this:</p> <blockquote> <p>"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"</p> </blockquote> <p>I suspect you want the score table shown to the user to look something like this:</p> <pre><code> 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) </code></pre> <p>I second Mytskine's initial suggestion that you shouldn't try to store the ranks in the DB at all &mdash; 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 <code>name</code> and <code>score</code> and <code>time</code>, plus maybe an AUTO_INCREMENT column <code>id</code>.</p> <p>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:</p> <pre><code>SELECT COUNT(*) + 1 FROM scores WHERE score &gt; ? </code></pre> <p>Here, the <code>?</code> 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.</p> <p>Now, if R &lt; N + K, we might was well just show the top N + 2*K (or max(N, R+K) or whatever) entries:</p> <pre><code>SELECT * FROM scores ORDER BY score DESC, time DESC LIMIT N+2*K </code></pre> <p>Otherwise, we need to do it in three steps: first, fetch the top N entries:</p> <pre><code>SELECT * FROM scores ORDER BY score DESC, time DESC LIMIT N </code></pre> <p>then, the K entries above the player:</p> <pre><code>SELECT * FROM scores WHERE score &gt; ? ORDER BY score ASC, time ASC LIMIT K </code></pre> <p>and finally the K entries below the player:</p> <pre><code>SELECT * FROM scores WHERE score &lt;= ? ORDER BY score DESC, time DESC LIMIT K </code></pre> <p>Note that the second result set will be in reverse order, but that's OK &mdash; 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.)</p> <p>Finally, after the player has entered their name, we can insert the player's score into to the database simply with:</p> <pre><code>INSERT INTO scores (name, score, time) VALUES (?, ?, ?) </code></pre> <p>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 <code>(score, time)</code> to keep the queries efficient.</p>
    singulars
    1. This table or related slice is empty.
    1. POhigh score database
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USIlmari Karonen
    1. USIlmari Karonen
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COyes this is basically what I want it to look like except I want to show a text input inside the html table of results so the player sees where they are before entering their name. So I really need to do 2 Ajax calls, one to pull the results first and then 1 to insert the new one.
      singulars
      1. PO
      1. USTules
    2. CO@user555116: Yes, that also works. In fact, I just edited my answer to put the insert last.
      singulars
      1. PO
      1. USIlmari Karonen
    3. COlook above if you are interested in seeing what I ended up with :)
      singulars
      1. PO
      1. USTules
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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