Returning results before and after the actual result?

[Kryptix]

I'm writing some highscores for a game and I want to allow users to search their own name. which will show them as if they were in the middle of the table, showing 50 results before and 50 results after in the actual position.

 

Is there a way to do this in a query of it it multiple queries and using PHP?

[ngreenwood6]

i have one question in the table that you are querying does it have their place? For example if the high scores table holds my place i would be 20.

[Kryptix]

Nope, as it's constantly updating. I have to ORDER BY.

[ngreenwood6]

do you mind if I see the query so that I can see if I can give you a hand or not.

[Kryptix]

do you mind if I see the query so that I can see if I can give you a hand or not.

 

This is the query I'm currently using to display the top 500 in a certain skill (defined by $_GET['highscores']) but I want to change it so you will have $_GET['character'] which searches `rscd_players` to check it exists, and then does a JOIN with $_GET['highscores'] to show the selected stat:

 

SELECT `rscd_players`.`username`, `rscd_players`.`owner`, `rscd_players`.`user`, `rscd_experience`.`exp_" . $db->escape(trim(strtolower($_GET['highscores']))) . "`
FROM `rscd_experience`
JOIN `rscd_players`
ON `rscd_experience`.`user` = `rscd_players`.`user`
WHERE `rscd_players`.`highscores` = '0'
AND `rscd_players`.`banned` = '0' AND (`rscd_players`.`group_id` = '4' OR '5') AND `rscd_players`.`login_date` >= UNIX_TIMESTAMP() - 2678400
ORDER BY `rscd_experience`.`exp_" . $db->escape(trim(strtolower($_GET['highscores']))) . "` DESC, `rscd_players`.`creation_date` ASC
LIMIT 0, " . $db->escape($optionsArray['highscores_limit'])

[ngreenwood6]

I cant seem to think of a way to do this with the current query that you are performing but you can use php to help you display. There is a php function called array_search that you can use to search for the value in the array which will give you the key. You could then loop through that key - 50 to the key +50. here is an example:

 

$array = array('player1','player2','player3');
$array_key = array_search('player2',$array); //$array_key will be 1

//loop through the array - 50 to + 50
for($i = $array_key - 50; $i <= $array_key + 50; $i++){
  echo $array[$i]; //display the results
}

 

Obviously there is some error checking (making sure there is a key - 50 and + 50) to do here but that is a basic example. Also you will have to create a new array of the values that are output from your query in order for this to work so that they will get a numbered key.

[Kryptix]

Is there anyway that you could first find his position and then use LIMIT BY $usersPosition - 50, %usersPosition + 50 or have I got this all wrong?

[ngreenwood6]

Thats what I was originally thinking but I cant think of a way to do that in mysql when you are using just the orderby. If you actually had the users position you could do it but without knowing the position your kinda sol lol. Maybe someone else knows a better method but i just offered mine as a solution.

[fenway]

If you ranked them first, then figured out the rank position, then in principle you could do this.