Selecting an area of data before/after a match from a Database

[Mutley]

Hi guys,

 

I'm wishing to select a range of data around a certain result for a Leaderboard. Out of hundreds of users, I would like it to show where yourself is on the Leaderboard of points, then the next 5 players and the previous 5 players in the table.

 

How would I filter this? I don't know where I should start!

 

Thanks in advance,

Nick.

[ignace]

well i would suggest using the $id of the user and do something like

 

$min = $id - 5;

$max = $id + 5;

 

select * from users where id between $min and $max

 

however this would suggest you aren't able to delete rows from the users table, i was also thinking about a mysql function range() but i'm looking into that right now

 

i'm going through the reference manual right now:

http://dev.mysql.com/doc/refman/5.0/en/func-op-summary-ref.html

[Mutley]

Thanks for your reply & interest.

 

The flaw there however is it's limiting the results by the users and not their points. Doing it that way, means that there are a lot of potential players that have scores closer to the user but not displayed as there ID is out of range.

 

But I could select the players score data, then do the same thing.

 

[akitchin]

i would guess that you'd SELECT the leaders list (ie. select users ordered by their points descending), and keep going until you're at the user of interest.  then use mysql_data_seek() to go back 5 rows (or to 0, whichever is greater) and take 11 starting from there:

 

<?php
$resource = mysql_query('SELECT stuff FROM users ORDER BY points DESC') or die('blah');
$i = 0; // row counter
$my_pointer = 'unassigned'; // keep track of user's spot
while ($row = mysql_fetch_assoc($resource))
{
  if ($my_pointer != 'unassigned')
  {
    if ($i <= $my_pointer + 5)
    {
      // echo the currently fetched user's stats
    }
    else
    {
      // we've reached the user that's 6 above our current user, so let's get out
      break;
    }
  }
  elseif ($my_pointer == 'unassigned' && $row['username'] == $_SESSION['my_username'])
  {
    // track your pointer
    $my_pointer = $i;

    // turn back the resource's pointer
    $new_pointer = ($i - 5 > 0) ? $i - 5 : 0;
    mysql_data_seek($resource, $new_pointer);

    // reset the counter to the new pointer, start back at the new pointer
    $i = $new_pointer;
    continue;
  }

  $i++;
}
?>

 

EDIT:  just to explain what's happening, the loop will go through and increment $i until the row matches our current user (i've assumed it's in the $_SESSION data - i would encourage you to edit that).  in that case, it will set the user's pointer, set the new pointer to 5 rows BEFORE him, reset the row counter to that pointer, and send us back through the loop.  then it will enter the first if() (because the user's pointer is now set) and if it's below our user + 5, it will do whatever.  if it's above that, it'll crap out of the loop for you.

[Mutley]

Thanks a lot akitchin.

 

Is the 'unassigned' $my_pointer variable supposed to have some other data in it? I'm not too sure what that variable is doing.

 

Regards,

Nick.

[akitchin]

the 'unassigned' gets changed to the user's ID when our original run through the result set finally hits that user's row.  then, once it has been changed from 'unassigned' to a number, it triggers the turn-back by 5 IDs.