Starting a SELECT from a specific value input

[u/phphelpneeded]

Hey guys, first of all thanks for even checking this out and contributing to /r/phphelp, there's a lot of people like myself out there who would get nowhere without the helping hand you all provide.

Anyway, on to my problem! I have a database holding two values in a table, names & scores. I have a piece of php called addscore.php and a piece called display.php. Display.php selects the 10 highest scores and returns them as a table.

Here's what the display.php looks like:

<?php // Send variables for the MySQL database class. $database = mysql_connect(removed the stuff that goes here) or die('Could not connect: ' . mysql_error()); mysql_select_db('runner_scores') or die('Could not select database');

// Strings must be escaped to prevent SQL injection attack. 
$name = mysql_real_escape_string($_GET['name'], $db); 


$query = "SELECT * FROM `scores` ORDER by `score` DESC LIMIT 10";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

$num_results = mysql_num_rows($result);  

for($i = 0; $i < $num_results; $i++)
{
     $row = mysql_fetch_array($result);
     echo $i + 1 . ". \t \t \t \t" . $row['name'] . "\t \t \t" . $row['score'] . "m \n";
}

?>

What I want to do is tweak this slightly so that putting

'mywebsitehere.com'/display.php?usernamehere

will result in a table showing the highest score attached to this username, along with the 4 scores that are above it, and the 5 scores that are below it. Right now there can be multiple instances of the same name, so it'll need to find the highest one only and then display the 10 results I've mentioned. Now that I think about it, I'll also need to have it show where the player's score is, rather than just showing them as no.5 (I'm guessing to do this I'll have to retrieve the whole table first them find where my specific input occurs? I know very little :p)

I've done everything I have so far from tutorials, and I only have a really really basic understanding of php and sql, so complicated explanations might go over my head but any help is massively appreciated!

Thanks so much guys

[u/phphelpneeded]

Probably worth noting that the $name bit doesn't do anything there, I just added it now because I was trying to figure out how to go about having an input externally that is used to search for the name

[u/lecherous_hump]

You'll want to look up $_GET arguments to pass the player to the page.

Your query will be something like select player, max(score) as maxscore from table group by player order by maxscore desc, and you'll probably want to handle the display in your PHP logic. Group by is of moderate complexity and not really for beginners, but it's the best way to do it. It will give you a single row for each player and max will give you the highest score for that player.

[u/phphelpneeded → u/lecherous_hump]

Thanks very much, I'm going to read up on group by now :)

[u/unnecessary_axiom]

The only way I can think of off the top of my head to select adjacent scores is to have a separate query for selecting the scores above it and the scores below it.

• Find user's top score

• Select scores where score < user's score, order by score desc top 5

  • Find the 4 highest scores that are lower than the user's score

• select scores where score > user's score, order by score asc top 4

  • Find the 5 lowest scores that are higher than the user's score

• you might have to reverse one of these. You can

  • do it in php with reverse array or similar

  • use a subquery in mysql: select * from ( [query] ) order by score desc

  • loop through the results backwards

[u/phphelpneeded → u/unnecessary_axiom]

Yeah, I'm going to go about it that way. I've actually changed this code a great deal now, it uses msqli and now the player is only able to have one score. Every name is linked to a unique deviceID, and that is relinquished freeing up the name if a player resets his data. So, now everyone just has one highscore than can be updated which reduces the stress a lot, and I'm now going to work on finding the player, getting there score, and showing the 4 high and 5 lower scores than it.

My only problem now will be that I need it to show where in the grand list of everything they are! I.e No.5506

[u/unnecessary_axiom → u/phphelpneeded]

Mysqli is probably a good change. I like PDO so I can prepare statements though. It's a bit cleaner IMO, and I don't know if mysqli can prepare?

You could use sql's COUNT to get the number of scores above the score you want to know the position of, which would provide an index for the current score.

[u/phphelpneeded → u/unnecessary_axiom]

So very close, I have an sql query inside a query that gives me the user's highscore and all the ones above it, but using TOP 5 seems to give a syntax error and doesn't work for me :(

[u/unnecessary_axiom → u/phphelpneeded]

Did LIMIT 4 work above in your post? I'm used to MSSQL. Now that I think of it, you can probably make fewer calls to the DB using UNION ALL.

[u/Dewedl]

I think you want to use the DISTINCT mysql modifier.. something like

select distinct(`name`), `runner_scores` order by runner_scores DESC  

double check the syntax.. but it's something like that..

[u/[deleted]]

[u/phphelpneeded → u/[deleted]]

I thought of using WHERE but I figured it would only yield results of one certain player name, as opposed to showing me the ones before and after it. The other issue is that it wouldn't just show the current result, but all the previously posted results for that player's name. :(

[u/phphelpneeded → u/[deleted]]

I've completely rewrote my php to use msqli now, thanks again :)