Post High Score and Retrieve Position

Question

I'm not so savvy with MYSQL, so my apologies in advance is this is a dumb question.

I've created a super basic PHP High Scores table. Upon inserting a new score into the DB Table, I'd like to retrieve the position of that score so that I can get 10 results with the persons score falling within that range.

My INSERT Query looks something like:

$stmt = $mysqli->prepare("INSERT INTO highscores (name, time, score) VALUES (?, ?, ?)");
$stmt->bind_param('sdi', $name, $time, $score);

UPDATE - I'm looking for a way to do this with as few queries as possible. I recall reading something about getting an INSERT ID when making an insert, but I would then still have to make a second query to get those results.

Answer 1

To get the number of people who scored better than the current score:

SELECT COUNT(*) FROM table_name WHERE score > $score

Thus, the score you are ranking would be in 1 + mysql_result($result, 0)th place.

Answer 2

"...but I would then still have to make a second query to get those results."

In order to get your result you don't need to do an INSERT 1st, you can get those result without inserting the row. As suggested by Joe Mejewski, you can do:

$query = "SELECT COUNT(*) FROM highscores WHERE score > '" . $score . "'";

If then you want to INSERT the row, you need a 2nd query.

"I'm looking for a way to do this with as few queries as possible!"

Anyway you need two queries and getting the last inserted id (wich I don't understand if you placed one autoincrement id in your highscore table) woudn't help you, because you don't need to get that id or one single row, but all the rows that scores higher than the one you inserted.

your comment: "...So 1st would have the highest score, last the lowest."

If you want to get back a table ordered by score so the 1st would have the highest score and the last the lowest, you simply need to call this query after you inserted the new row:

SELECT * FROM highscores ORDER BY score DESC

That's it!

Answer 3

I'm going to go out on a limb and say there is no way to do this with one query. So the fewest SQL statements, which is the way I think the question is intended, would be 2: One to insert and one to query.

Answer 4

What's wrong with using the LIMIT keyword?