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…
SELECT u.fname
, u.lname
, u.country
, u.seouname
, u.new_userid
, up.points
, COUNT(up2.points) + 1 AS rank
FROM ".$prefix."_users u
INNER
JOIN ( SELECT AVG(point) points
, userid
FROM ".$prefix."_LC_leaderboard WHERE lcSeason = '$currSeason' AND aproved=1 AND matchid > 0
GROUP
BY userid HAVING COUNT(userid) >= 1) up
ON u.new_userid = up.userid
LEFT OUTER
JOIN ( SELECT AVG(point) points
, userid
FROM ".$prefix."_LC_leaderboard WHERE lcSeason = '$currSeason' AND aproved=1 AND matchid > 0
GROUP
BY userid HAVING COUNT(userid) >= 1) up2
ON up2.points > up.points
WHERE u.new_userid = $userid
GROUP
BY u.new_userid
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;
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 = 1 AND lc.matchid != 0
GROUP BY lc.userid
But with no luck… Unknown column ‘u.userid’ in ‘on clause’?
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.
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
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.