I'm currently creating a page in PHP that will display a user and their ranks in my site's highscores.
However, I have one field that is different from the rest and isn't functioning as intended.
Here is my table:
-- ----------------------------
-- Table structure for `playerstats`
-- ----------------------------
DROP TABLE IF EXISTS `playerstats`;
CREATE TABLE `playerstats` (
`uid` int(11) NOT NULL DEFAULT '0',
`gamelevel` int(11) NOT NULL DEFAULT '0',
`overall` int(11) NOT NULL DEFAULT '0',
`overallxp` bigint(20) NOT NULL DEFAULT '0',
KEY `uid` (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
...and some example data:
INSERT INTO `playerstats` VALUES ('14950', '123', '1495', '129825211')
INSERT INTO `playerstats` VALUES ('28967', '124', '1495', '127168799')
INSERT INTO `playerstats` VALUES ('95848', '121', '1495', '108481173')
Here is my query:
SELECT count(*) + 1 FROM (SELECT uid, overall, overallxp, gamelevel FROM playerstats GROUP BY playerstats.uid) AS x WHERE overall > (SELECT overall FROM playerstats WHERE uid = ". $userid .")"
...and $userid is:
$userid = (int) $_GET['searched'];
Now, when I navigate to the personal highscores of userid14950, it displays the correct overall ranking for that user because they are the person with the highest overallxp for their overall. However, when I visit the personal highscores of userid28967 or userid95848, their overall rank is the same as userid14950 for some reason (most likely because I don't account for users with the same overall result).
My question is: how would I go about making it so if two (or more) users share the same overall, they have their correct rank displayed, and not a duplicate one?
So that's about it.
Any help is very much appreciated :)
Thanks,
Mark