SitePoint Sponsor

User Tag List

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

    Get position in a row inside a query!?

    I have a script which prints out the position of a user in a while loop like this:
    PHP Code:
    $sql "SELECT u.fname, u.lname, u.userid, up.points FROM users u 
            INNER JOIN ( SELECT SUM(point) points, userid FROM bonuspoints  
            GROUP BY user ) up ON u.userid = up.userid 
            ORDER BY up.points DESC"
    ;
            
    $result mysql_query($sql);
                        
    $x 0
    $i 0
    $prev 0;
    $r_count 0;

    while(
    $row mysql_fetch_array($result)){
        
        
    $curr $row['points']; 
        
        if (
    $curr != $prev
        { 
            
    $i $x 1
        }
        
        echo 
    ''.$i.' '.$row['fname'].' '.$row['lname'].'<br>';
        
        
    $prev $curr
        
    $x++;
        

    Now this works like a charm, but... I want to be able to, without a while loop, to tell the user what position he or she has... How do I figure that out...?

    Thanks in advance...

  2. #2
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    I think the easiest way would be to count the number of records returned, when you search for all records with higher points than the selected user.

    E.g.

    Points User
    10 Bob
    7 Dole
    3 YOU

    You would search for all rows with more points than you (3) ordering by points, desc (which would return 2 + you = 3).

  3. #3
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mal Curtis View Post
    I think the easiest way would be to count the number of records returned, when you search for all records with higher points than the selected user.
    Not quite sure what you mean... Can you give an example?

    Thanks :-)

  4. #4
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, just realized that the option suggested doesnt do the job course... Points are calculated in the query:
    PHP Code:
    SELECT SUM(pointpoints 
    That means that the total amount of point is not listed in the db but alot of records for each user in the table...

    That makes it a bit trickier I think...

    Any ideas?!?!

    At ease

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    try this (untested) --
    Code:
    SELECT u.fname
         , u.lname
         , u.userid
         , up.points 
         , r.rank
      FROM users u         
    INNER 
      JOIN ( SELECT SUM(point) points
                  , userid 
               FROM bonuspoints           
             GROUP 
                 BY userid ) up 
        ON u.userid = up.userid         
    CROSS 
      JOIN ( SELECT COUNT(*)+1 AS rank
               FROM ( SELECT SUM(point) points
                           , userid 
                        FROM bonuspoints           
                      GROUP 
                          BY userid ) up2
              WHERE up2.points > up.points ) r
    ORDER 
        BY up.points DESC
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    try this (untested) --
    Hmm... When trying to do the script I get this error?!?!

    Unknown column 'up.points' in 'where clause'

    I have tryied to figure out why, but with no luck... any ideas?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i'm not sure...

    say, were you thinking of running a query for all users, or for just a specific user?

    if for all users, what i gave you is excessively complicated and you can get the rank in php

    if for one user, there is a much simpler query
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist
    Join Date
    Jun 2006
    Location
    Wigan, Lancashire. UK
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Much easier in Oracle
    When's MySQL going to implement RANK and DENSE RANK?
    ---
    Development Projects:
    PHPExcel
    PHPPowerPoint

  9. #9
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i'm not sure...

    say, were you thinking of running a query for all users, or for just a specific user?

    if for all users, what i gave you is excessively complicated and you can get the rank in php

    if for one user, there is a much simpler query
    I'm trying to figure out what rank one specific user have... Not all users...

    The origianl script is OK for getting a rank leaderboard, but in that the ranking is found outside the query...

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    boy, my first query was messed up...

    try this --
    Code:
    SELECT u.fname
         , u.lname
         , u.userid
         , up.points 
         , COUNT(up2.points) + 1 AS rank
      FROM users u         
    INNER 
      JOIN ( SELECT SUM(point) points
                  , userid 
               FROM bonuspoints           
             GROUP 
                 BY userid ) up 
        ON u.userid = up.userid         
    LEFT OUTER 
      JOIN ( SELECT SUM(point) points
                  , userid 
               FROM bonuspoints           
             GROUP 
                 BY userid ) up2 
        ON up2.points > up.points    
     WHERE u.userid = 42     -- specify the user you want
    GROUP
        BY u.userid
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    boy, my first query was messed up...
    YES... Works like a charm, but... Where do I put the "WHERE" conditions like "WHERE up.count >= 3". I need to be able to make sure that a user has atleast 3 records in the table before he or she can be included in the ranking.

    PHP Code:
    SELECT u.fname
         
    u.lname
         
    cu.new_userid
         
    up.strokes
         
    up.count 
         
    COUNT(up2.strokes) + AS rank
      FROM 
    ".$prefix."_club_users cu  
    INNER 
      JOIN 
    ".$prefix."_users u ON u.new_userid cu.new_userid
    INNER 
      JOIN 
    SELECT AVG(strokenet_totalstrokes
                  
    user 
                  
    COUNT(usercount 
               FROM 
    ".$prefix."_score_info           
             GROUP 
                 BY user ORDER BY strokes DESC 
    up 
        ON u
    .new_userid up.user         
    LEFT OUTER 
      JOIN 
    SELECT AVG(strokenet_totalstrokes
                  
    user 
               FROM 
    ".$prefix."_score_info          
             GROUP 
                 BY user ORDER BY strokes DESC 
    up2 
        ON up2
    .strokes up.strokes 
        WHERE up
    .count >= <-- Here... 
    GROUP
        BY cu
    .new_userid
         ORDER BY rank
    "; 
    If I put it at the buttom the user is excluded from the list but is still being included in the calculation, so the users ranking just dont exist... like this:

    1. user a
    3. user c
    4. user d

    User b is excluded but still has a ranking?!?

    Where do I put in the clause?!"?!

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT u.fname
         , u.lname
         , cu.new_userid
         , up.strokes
         , up.count 
         , COUNT(up2.strokes) + 1 AS rank
      FROM ".$prefix."_club_users cu  
    INNER 
      JOIN ".$prefix."_users u ON u.new_userid = cu.new_userid
    INNER 
      JOIN ( SELECT AVG(strokenet_total) strokes
                  , user 
                  , COUNT(user) count 
               FROM ".$prefix."_score_info           
             GROUP 
                 BY user ) up 
        ON up.user = u.new_userid
       AND up.count >= 3 -- here     
    LEFT OUTER 
      JOIN ( SELECT AVG(strokenet_total) strokes
                  , user 
               FROM ".$prefix."_score_info          
             GROUP 
                 BY user ) up2 
        ON up2.strokes < up.strokes 
    GROUP
        BY cu.new_userid
    ORDER 
        BY rank
    notice no ORDER BY clause in the subqueries
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    notice no ORDER BY clause in the subqueries
    Well, not quite. This takes the users with lower records than 3 out of the list, but it still count them in the ranking order. So if a user has a better score than one of those with more than 3 records he will still get a ranking number which means that this number is not for use... Hope this makes sense !?

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i don't get it

    you haven't really explained what you're doing, and you've played "bait and switch" with your table design on me

    here's another guess on my part (probably my last) --
    Code:
    SELECT u.fname
         , u.lname
         , cu.new_userid
         , up.strokes
         , up.count 
         , COUNT(up2.strokes) + 1 AS rank
      FROM ".$prefix."_club_users cu  
    INNER 
      JOIN ".$prefix."_users u ON u.new_userid = cu.new_userid
    INNER 
      JOIN ( SELECT AVG(strokenet_total) strokes
                  , user 
                  , COUNT(user) count 
               FROM ".$prefix."_score_info           
             GROUP 
                 BY user 
             HAVING COUNT(user) > 3 ) up 
        ON up.user = u.new_userid  
    LEFT OUTER 
      JOIN ( SELECT AVG(strokenet_total) strokes
                  , user 
               FROM ".$prefix."_score_info          
             GROUP 
                 BY user 
             HAVING COUNT(user) > 3 ) up2 
        ON up2.strokes < up.strokes 
    GROUP
        BY cu.new_userid
    ORDER 
        BY rank
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry r937... What I want is taht a user only would be counted into the equation if the user has 3 records or more... Otherwise the user should be excluded for all the calculations...

    I really epreciates all you have done...

    Your the man :-)

  16. #16
    SitePoint Enthusiast
    Join Date
    Jun 2009
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    show some of the output result... i think i can help u out...


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
  •