SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Loop limit 7 with specific userid in middle?!?

    Confused!? What I want is to print a user list but only 7 users... 3 above the current user and 3 below.

    Lets say that the user is #49. I would then like to get a print like this:

    #46
    #47
    #48
    #49
    #50
    #51
    #52

    Is that possible, and if yes... How?!

    PHP Code:
    $query="SELECT * FROM users ORDER BY uid"
                
    $result mysql_query($query); 
                while(
    $row mysql_fetch_array($result)){

    echo 
    ''.$row['name'].'<br>';


  2. #2
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $pivotUid=49;
    $min=$pivotUid-3;
    $max=$pivotUid+3;
    $sql="select * from users where uid between $min and $max order by uid";
    $result mysql_query($query);
    while(
    $row mysql_fetch_array($result)){
      
    //do something


  3. #3
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks... Just what I was looking 4

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    um, in a word, no

    that scheme relies entirely upon there being no missing user id numbers

    that's pretty unreasonable, in my view
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I Must admit... R937 is right... It doesn't solve the issue if there is no users above or under the user... Does anybody have a solution for this?

    PHP Code:
    $pivotUid $new_userid;
                
    $min $pivotUid-3;
                
    $max $pivotUid+3;
                
                
    $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 ( 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 cu.new_userid BETWEEN 
    $min AND $max ORDER BY user_points.points DESC"

  6. #6
    SitePoint Zealot
    Join Date
    Dec 2006
    Posts
    182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try something like this:
    Code:
    (SELECT * FROM	(SELECT * FROM users WHERE userid < $pivotUid ORDER BY userid DESC LIMIT 3) as lowerthree
    WHERE userid < $pivotUid ORDER BY userid ASC LIMIT 3)
    UNION
    (SELECT * FROM users WHERE userid >= $pivotUid ORDER BY userid ASC LIMIT 4)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •