SitePoint Sponsor

User Tag List

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

    Show on list but not counted?!

    I'm making a leaderboard list and works just fine, but now its getting tricky... A player is shown on the list as soon a score is submitted.

    But what if I want to show all users signed up for the leaderboard even though a score is not yet submitted...

    When a user is signing up a record in the leaderboard DB is submitted with "0" as score, matchid as "0" and aproved set as "0".

    When a score should get into the eqation there should be a matchid > 0 and aproved set as "1"... Can this be done...

    The problem here is that the leaderboard is based on the avreage score... The "0" from the signup should then not count... A score submitted though could be "0" and thats ok...

    Here is what I have gor working so far...
    PHP Code:
    SELECT u.fname
         
    u.lname
         
    u.country 
         
    u.seouname
         
    u.new_userid
         
    up.points 
         
    COUNT(up2.points) + AS rank
      FROM 
    ".$prefix."_users u  
    INNER 
      JOIN 
    SELECT AVG(pointpoints
                  
    userid
               FROM 
    ".$prefix."_LC_leaderboard WHERE lcSeason '$currSeason' AND aproved=AND matchid 0           
             GROUP 
                 BY userid HAVING COUNT
    (userid) >= 1up 
        ON u
    .new_userid up.userid         
    LEFT OUTER 
      JOIN 
    SELECT AVG(pointpoints
                  
    userid 
               FROM 
    ".$prefix."_LC_leaderboard WHERE lcSeason '$currSeason' AND aproved=AND matchid 0         
             GROUP 
                 BY userid HAVING COUNT
    (userid) >= 1up2 
        ON up2
    .points up.points
        WHERE u
    .new_userid $userid
    GROUP
        BY u
    .new_userid 
    Hoping for help and thanks in advance...

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    Turn your INNER JOIN into a LEFT JOIN.

  3. #3
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    Turn your INNER JOIN into a LEFT JOIN.
    Not quite... I only want the users who has signed up in the LC_leaderboard... Your solution gets all the useres from users table...

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    Oh okay so not all users are on the leaderboard. Gotcha.

    So... (let me make sure i'm getting this right)
    Users holds user data. Relevant key field being userid.
    LC_Leaderboard holds... multiple records from an individual user containing points, a season, a tinyint approved flag, and a matchid (which presumably is a FK that doesnt really matter to this function other than to say it's not 0.)

    You want to retrieve all users, that have some entries in the leaderboards, but you dont want to count the 0 entry. And then you want to retrieve... something... else? I dont get the whole up2 table bit.

    SELECT <userfields>, AVG(points)
    FROM users
    LEFT JOIN leaderboard ON leaderboard.userid = user.userid
    GROUP BY leaderboard.userid
    WHERE leaderbaord.seasonid = $myseasonid AND leaderboard.approved = 1 AND leaderboard.matchid != 0;

    Is where I'm at atm.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    starlion, you can't put WHERE after GROUP BY

    and if you have WHERE conditions on the right table, then the LEFT OUTER JOIN is crippled and becomes in effect an inner join
    rudy.ca | @rudydotca
    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)
    Tryid it like this:
    PHP Code:
    SELECT u.fname
         
    u.lname
         
    u.country 
         
    u.seouname
         
    u.h_index
         
    u.new_userid
         
    AVG(point)
    FROM ".$prefix."_users u 
    LEFT JOIN 
    ".$prefix."_LC_leaderboard lc ON lc.userid u.userid
    WHERE lc
    .lcSeason $currSeason AND lc.aproved AND lc.matchid != 0  
    GROUP BY lc
    .userid 
    But with no luck... Unknown column 'u.userid' in 'on clause'?

    Any ideas?

  7. #7
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    because... you dont have a userid field in your user name, you have new_userid ? (which..makes it all the more confusing?)

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jmansa View Post
    When a user is signing up a record in the leaderboard DB is submitted with "0" as score, matchid as "0" and aproved set as "0".
    i wouldn't've done this, but since you did, you should be using an INNER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    because... you dont have a userid field in your user name, you have new_userid ? (which..makes it all the more confusing?)
    My bad... But after change it still only takes the records with no matchid...

    It should take records with matchid=0 and aproved=1 but not count them in the AVG(point)?

    Hope this makes sense?

  10. #10
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i wouldn't've done this, but since you did, you should be using an INNER JOIN
    Yeah i'm not sure why you did either. Personally I'd just do a flag on the user table.... but anyway!

    Now I'm at....
    SELECT <userfields>, (SUM(points) / MAX(COUNT(points)-1,1) AS avg
    FROM users
    INNER JOIN leaderboard ON leaderboard.userid = users.new_userid
    WHERE leaderbaord.seasonid = $myseasonid AND leaderboard.approved = 1
    GROUP BY leaderboard.userid;

    still not sure what the 'rank' was supposed to be in your original query though.

  11. #11
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can see the error from the trees...

    PHP Code:
    SELECT u.fname
         
    u.lname
         
    u.country 
         
    u.seouname
         
    u.h_index
         
    u.new_userid
         
    SUM(point) / MAX(COUNT(point)-1,1) AS avg
    FROM 
    ".$prefix."_users u 
    LEFT JOIN 
    ".$prefix."_LC_leaderboard lc ON lc.userid u.new_userid
    WHERE lc
    .lcSeason $currSeason AND lc.aproved 1  
    GROUP BY lc
    .userid 
    Gives me this error:
    Error: (1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1) AS avg FROM fb_users u LEFT JOIN fb_LC_leaderboard lc ON lc.us' at line 7

    Any ideas?

  12. #12
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    er because i'm not coding in PHP i'm coding in mySQL. Silly me. Mind isnt in it today it seems lol.

    Strip the max out and just do COUNT(point) - 1..... if my mind hasnt COMPLETELY left me, MySQL returns a NULL on division by 0... which would give you the difference between someone who's scored 0 in all their matches, and someone who's never had a match.


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
  •