Page 1 of 1

Getting current user + 5 above and below?!?

Posted: Wed Jun 24, 2009 5:40 am
by jmansa
I'm trying to make a golfleaderboard, and have that under control, but now I want to show the leaderboard for the current user and the 5 above the user and the 5 below...

Here is how its shown at this time...

Code: Select all

 
$currentUser = $row['userid'];
echo '<table cellpadding="0" cellspacing="1">
        <tr>
            <td width="10%" valign="middle" bgcolor="#669900"><div style="padding:3px;color:#FFFFFF;text-align:center;">P</div></td>
            <td width="80%" align="left" valign="middle" bgcolor="#669900"><div style="padding:3px;color:#FFFFFF;">'._PLAYER.'</div></td>
            <td width="10%" align="center" valign="middle" bgcolor="#669900"><div style="padding:3px;color:#FFFFFF;text-align:center;">'._POINTS.'</div></td>
        </tr>';
        
        $sql = "SELECT u.fname, u.lname, u.country, user_points.points FROM ".$prefix."_users cu 
                    INNER JOIN ".$prefix."_users u ON u.new_userid = cu.new_userid 
                    INNER JOIN ".$prefix."_GA_users ON u.new_userid = ".$prefix."_GA_users.userid
                    INNER JOIN ( SELECT AVG(point) points, userid FROM ".$prefix."_GA_leaderboard 
                    WHERE seasonid = $seasonid 
                    GROUP BY userid ) user_points ON cu.new_userid = user_points.userid 
                    WHERE ".$prefix."_GA_users.aproved = 1 ORDER BY user_points.points DESC LIMIT 25";
                    
        $result = mysql_query($sql);
        
        $x = 0; 
        $i = 0; 
        $prev = 0;
        $r_count = 0;
 
        while($row = mysql_fetch_array($result)){
            
            $fname = $row['fname'];
            $lname = $row['lname'];
            $ucountry = $row['country'];
            
            $sql2 = "SELECT * FROM ".$prefix."_geo_countries WHERE con_id='$ucountry'";
            $result2 = mysql_query($sql2);
            $row2 = mysql_fetch_array($result2);
        
            
                $curr = $row['points']; 
                if ($curr != $prev) 
                { 
                    $i = $x + 1; 
                } 
                
                 echo '<tr style="background-color:#' .((++$r_count %2 == 0) ? 'FFFFFF' : 'EEEEEE'). '">';
                 echo '<td align="center" valign="middle"><input name="placement[]" type="text" readonly="true" value="'.$i.'" style="font-size:11px;width:16px;text-align:center;border:none;background-color:transparent;font-family:lucida grande,tahoma,verdana,arial,sans-serif;"/></td>';
                 echo '<td align="left" valign="middle">&nbsp;';
                 if(!$row['country']) {
                         echo '<img src="http://www.golfacross.com/facebook/images/lang/nocountry.png">&nbsp;';
                     } else {
                         echo '<img src="http://www.golfacross.com/facebook/images/lang/'.$row2['iso'].'.png">&nbsp;';
                     }
                 echo ''.$fname.' '.$lname.'</td>';
                 echo '<td align="center" valign="middle"><div style="text-align:center;">' . number_format($row['points'], 2) . '</div></td>';
                 echo '</tr>';
            
                $prev = $curr; 
                $x++; 
            
        }
        echo '</table>';
        echo '</div>';
As it is now it shows the top 25 on the leaderboard, but if the current user is placed lets say as number 247 he is either not shown or I would have to make an long scrolling print...

Any help please...

Re: Getting current user + 5 above and below?!?

Posted: Wed Jun 24, 2009 10:17 am
by Christopher
LIMIT allows you to specify and OFFSET. Calculate the position minus 5 and then show 10 records. You may have to do some bounds checking.

Re: Getting current user + 5 above and below?!?

Posted: Wed Jun 24, 2009 1:16 pm
by jmansa
Hmmm... Never heard of that... Can you give an example please ;-)

Re: Getting current user + 5 above and below?!?

Posted: Wed Jun 24, 2009 1:44 pm
by Eric!
Find out where your guy ranks in the score board, say he's at $rank=201;

SELECT * FROM table LIMIT '$rank-5',11;

This will pull out the data (assuming it is sorted based on scores), with rows like:

196 users ranked above
197
198
199
200
201 (your user)
202
203
204
205
206 users ranked below

EDIT: I just remembered database rows start at 0, so your ranking (which starts at 1) will be off by one according to the database rows...so you'll need to adjust the offset to fit your data.

Re: Getting current user + 5 above and below?!?

Posted: Thu Jun 25, 2009 3:30 am
by jmansa
Eric! wrote:Find out where your guy ranks in the score board, say he's at $rank=201;
Perfect, but one problem... My script is build up so it figures out what place in the loop a user is this way:

Code: Select all

SELECT AVG(point) points, userid FROM ".$prefix."_GA_leaderboard
So how do I get the rank???

I have an idea with this:

Code: Select all

SELECT COUNT(*) + 1 AS rank FROM table WHERE points > (SELECT points FROM table WHERE userid = '$current_user')
But not sure how to put this into this?!?!

Code: Select all

SELECT u.fname, u.lname, u.new_userid, u.country, user_points.points FROM ".$prefix."_users cu 
INNER JOIN ".$prefix."_users u ON u.new_userid = cu.new_userid 
INNER JOIN ".$prefix."_GA_users ON u.new_userid = ".$prefix."_GA_users.userid
INNER JOIN ( SELECT AVG(point) points, userid FROM ".$prefix."_GA_leaderboard 
WHERE seasonid = $seasonid 
GROUP BY userid ) user_points ON cu.new_userid = user_points.userid 
WHERE ".$prefix."_GA_users.aproved = 1 ORDER BY user_points.points
Any help is apreciated :-)

Re: Getting current user + 5 above and below?!?

Posted: Thu Jun 25, 2009 11:25 am
by jmansa
Still havent found the solution... Please help :-|