Rank in Highscores

[Kryptix]

Not really sure what to title this topic. However, I've made some Highscores for my RuneScape Classic private server but I'm unsure on how to do something.

 

Basically, I'm half way through making a character information page which will list their items and skill levels but next to the actual level (and experience) I want to put their global rank that they appear in the Highscores for that skill. I managed to generate the rank for the current Highscores page but simply using $rank++; as it loops through the results.

 

How would I go about doing it for the character information page?

 

All the experience is stored in a separate table (`rscd_experience`) whilst the players name and other details are stored in `rscd_players`.

 

I also would like to allow players to search for a users name which will put them at the position they're in and continue the Highscores from that position rather than the top if that makes any sense?

 

For example, you search for a character and it shows their rank in every skill separately:

 

 

After you click onto one of those stats it shows the Highscores from the position that he's currently at (showing some results above and some below):

 

 

Could someone explain to me how that's done? I'm pretty sure it will be a cron-job or something which generates all the data every few hours so they can actually store the rank and it only changes when they do another big looping query. Is it possible to do it on-the-fly?

[josborne]

This is actually a problem I ran into when starting out. There is a pretty easy solution but it will be a lot easier to make sense out of it if you provided the table structure(s) and maybe a sample query you'd like to run.

[Kryptix]

This is actually a problem I ran into when starting out. There is a pretty easy solution but it will be a lot easier to make sense out of it if you provided the table structure(s) and maybe a sample query you'd like to run.

Well, the table structure is simply:

 

user | exp_attack | exp_defense | exp_strength ... etc

 

I then JOIN the actual player table WHERE user = ID.

 

That returns results like below when viewing the Attack highscores (for example):

 

Bob | 13,385,385

Jim | 13,200,382

Tom | 10,285,386

Sam | 8,284,693

 

I then use a function to turn the XP into the level so I can display both the level and XP.

 

Bob | 99 | 13,385,385

Jim | 99 | 13,200,382

Tom | 96 | 10,285,386

Sam | 93 | 8,284,693

 

Any help would be really good. I have no-idea about this.

[josborne]

Well, it is a little hard to know if this is right because I am not totally clear on the table structures. Using SHOW CREATE TABLE is typically the best way to get help around here.

 

Anyway, from what I can tell, this would give you the attack_exp ranking for all users:

 

SET @num :=0;

SELECT a.user, SUM( a.exp_attack ) AS Attack, @num := @num +1 AS Rank
FROM `rscd_experience` a
GROUP BY a.user
ORDER BY Attack DESC

 

 

While this will give you the raking for an individual user (based on user ID):

 

SET @num :=0;

SELECT user, a.Rank 
FROM (
SELECT a.user, SUM( a.exp_attack ) AS Attack, @num := @num +1 AS Rank
FROM `rscd_experience` a
GROUP BY a.user
ORDER BY Attack DESC
) AS a
WHERE a.user = 1 

 

Hope that helps.

 

 

[Kryptix]

Thanks so much. I tried the first query but it doesn't put the rank in order. It just generates a rank randomly it seems (or when the entry was created).

 

I need to join the table `rscd_players` ON `rscd_experience`.`user` = `rscd_players`.`user` too, and select `username`, `exp_attack` and `rank`. Could you show me how to do that? I feel that you're a lot closer than I was.

[josborne]

Your tables are `rscd_experience` and `rscd_players`, right?

 

If so, run this command:

 

SHOW CREATE TABLE `rscd_experience`

 

then post the results.

 

Then post this command:

 

SHOW CREATE TABLE `rscd_players`

 

and post the results inside.

 

It will be a lot easier to figure out why that isn't working and to create the join witht hat info.

 

 

 

[Kryptix]

CREATE TABLE `rscd_experience` (

`user` varchar(255) NOT NULL,

`exp_attack` int(10) unsigned NOT NULL DEFAULT '0',

`exp_defense` int(10) unsigned NOT NULL DEFAULT '0',

`exp_strength` int(10) unsigned NOT NULL DEFAULT '0',

`exp_hits` int(10) unsigned NOT NULL DEFAULT '1200',

`exp_ranged` int(10) unsigned NOT NULL DEFAULT '0',

`exp_prayer` int(10) unsigned NOT NULL DEFAULT '0',

`exp_magic` int(10) unsigned NOT NULL DEFAULT '0',

`exp_cooking` int(10) unsigned NOT NULL DEFAULT '0',

`exp_woodcut` int(10) unsigned NOT NULL DEFAULT '0',

`exp_fletching` int(10) unsigned NOT NULL DEFAULT '0',

`exp_fishing` int(10) unsigned NOT NULL DEFAULT '0',

`exp_firemaking` int(10) unsigned NOT NULL DEFAULT '0',

`exp_crafting` int(10) unsigned NOT NULL DEFAULT '0',

`exp_smithing` int(10) unsigned NOT NULL DEFAULT '0',

`exp_mining` int(10) unsigned NOT NULL DEFAULT '0',

`exp_herblaw` int(10) unsigned NOT NULL DEFAULT '0',

`exp_agility` int(10) unsigned NOT NULL DEFAULT '0',

`exp_thieving` int(10) unsigned NOT NULL DEFAULT '0',

`exp_quest` int(10) unsigned NOT NULL DEFAULT '0',

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`) USING BTREE,

KEY `user` (`user`),

KEY `user_3` (`user`),

FULLTEXT KEY `user_2` (`user`)

) ENGINE=MyISAM AUTO_INCREMENT=9322 DEFAULT CHARSET=latin1

 

CREATE TABLE `rscd_players` (

`user` varchar(255) NOT NULL,

`username` varchar(255) NOT NULL DEFAULT '',

`group_id` int(10) DEFAULT '4',

`owner` int(5) unsigned NOT NULL,

`combat` int(10) DEFAULT '3',

`skill_total` int(10) DEFAULT '3',

`x` int(5) unsigned DEFAULT '213',

`y` int(5) unsigned DEFAULT '452',

`fatigue` int(10) DEFAULT '0',

`combatstyle` tinyint(1) DEFAULT '0',

`block_chat` tinyint(1) unsigned DEFAULT '0',

`block_private` tinyint(1) unsigned DEFAULT '0',

`block_trade` tinyint(1) unsigned DEFAULT '0',

`block_duel` tinyint(1) unsigned DEFAULT '0',

`block_global` tinyint(1) unsigned NOT NULL DEFAULT '1',

`cameraauto` tinyint(1) unsigned DEFAULT '0',

`onemouse` tinyint(1) unsigned DEFAULT '0',

`soundoff` tinyint(1) unsigned DEFAULT '0',

`showroof` tinyint(1) DEFAULT '0',

`autoscreenshot` tinyint(1) DEFAULT '0',

`combatwindow` tinyint(1) DEFAULT '0',

`haircolour` int(5) unsigned DEFAULT '2',

`topcolour` int(5) unsigned DEFAULT '8',

`trousercolour` int(5) unsigned DEFAULT '14',

`skincolour` int(5) unsigned DEFAULT '0',

`headsprite` int(5) unsigned DEFAULT '1',

`bodysprite` int(5) unsigned DEFAULT '2',

`male` tinyint(1) unsigned DEFAULT '1',

`skulled` int(10) unsigned DEFAULT '0',

`pass` varchar(255) NOT NULL,

`creation_date` int(10) unsigned NOT NULL DEFAULT '0',

`creation_ip` varchar(15) NOT NULL DEFAULT '0.0.0.0',

`login_date` int(10) unsigned DEFAULT '0',

`login_ip` varchar(15) DEFAULT '0.0.0.0',

`loggedin` tinyint(1) DEFAULT '0',

`banned` tinyint(1) DEFAULT '0',

`muted` tinyint(1) DEFAULT '0',

`deaths` int(10) DEFAULT '0',

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`online` tinyint(1) unsigned zerofill DEFAULT '0',

`kills` int(10) NOT NULL,

`highscores` tinyint(1) NOT NULL DEFAULT '0',

`clan` int(3) NOT NULL DEFAULT '0',

`clan_confirmed` tinyint(1) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

KEY `user` (`user`),

KEY `user_3` (`user`),

FULLTEXT KEY `user_2` (`user`)

) ENGINE=MyISAM AUTO_INCREMENT=8059 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='InnoDB free: 9216 kB'

[josborne]

Okay, let's get the ranking working before worrying about the join.

 

I made a mistake and made things a little more complicated than needed.

 

SET @num :=0;

SELECT a.user, a.exp_attack AS Attack, @num := @num +1 AS Rank
FROM `rscd_experience` a
GROUP BY a.user
ORDER BY a.exp_attack DESC

 

Please note that you must include the SET @num :=0; bit at the top. If you don't it ranking will be out of order.

[fenway]

Or you can set the variable as part of the query directly:

 

 

SELECT a.user, a.exp_attack AS Attack, @num := @num +1 AS Rank

FROM `rscd_experience` a, ( @num := 0 ) b

GROUP BY a.user

ORDER BY a.exp_attack DESC

[Kryptix]

Or you can set the variable as part of the query directly:

 

 

SELECT a.user, a.exp_attack AS Attack, @num := @num +1 AS Rank

FROM `rscd_experience` a, ( @num := 0 ) b

GROUP BY a.user

ORDER BY a.exp_attack DESC

 

#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 '@num := 0 ) b GROUP BY a.user ORDER BY a.exp_attack DESC LIMIT 0, 30' at line 2

 

Again though, will that assign them a rank depending on the order DESC of the exp_attack?

[josborne]

 

Again though, will that assign them a rank depending on the order DESC of the exp_attack?

 

Not sure about the error message (though setting the variable in the statement itself will be very useful) but that query will pull all the records, sort them by exp_attack from highest to lowest and number each result. That number is the ranking.

[Kryptix]

Okay, let's get the ranking working before worrying about the join.

 

I made a mistake and made things a little more complicated than needed.

 

SET @num :=0;

SELECT a.user, a.exp_attack AS Attack, @num := @num +1 AS Rank
FROM `rscd_experience` a
GROUP BY a.user
ORDER BY a.exp_attack DESC

 

Please note that you must include the SET @num :=0; bit at the top. If you don't it ranking will be out of order.

 

OK I'm using that query and I think that's where the problem is, PhpMyAdmin seems to only execute the bottom query (thus not setting the var), and I can't get fenway's query working.

 

 

Do you know how I can run two at once or let PhpMyAdmin run them both? How would I do it on a actual query on a PHP page?

[fenway]

Sorry, my bad, cut and paste error:

 

Try this:

 

SELECT a.user, a.exp_attack AS Attack, @num := @num +1 AS Rank
FROM `rscd_experience` a, ( SELECT @num := 0 ) b
GROUP BY a.user
ORDER BY a.exp_attack DESC

[josborne]

OKay, Try fenway's query.

 

If that doesn't work export the table a SQL (through the "Export" tab) copy the results to notepad and save it. Then upload it here as an attachment. This way I can run the query myself.

[Kryptix]

Thanks guys but it's still not right, the order of rank is wrong.

 

Here's the table:http://86.7.138.3/RSCE/rscd_experience.sql.gz

[fenway]

Yikes -- why the redundant index?

 

Anyway, we generally prefer pastebin to random IPs.

 

But define "wrong".

[josborne]

Hmmm...that query works perfectly for me. Are you sure you are copy/pasting the whole thing correctly? You should be able to paste the whole thing and run both queries in PHP admin.

 

Weird. I just tried fenway's query and got exactly the same result you posted that I believe is when you ran the set variable and query separately.

 

fenway, you are the master and I am out of my league. There appears to be something going on with the variable and I can't figure it out.

[Kryptix]

Yikes -- why the redundant index?

 

Anyway, we generally prefer pastebin to random IPs.

 

But define "wrong".

I have no experience with indexes which is why I posted the thread about them the other day. I'm quite new to this.

 

Look at the screenshot I posted, it's ordered by exp_attack but rank seems random. It's not assigning the rank properly. The person with the highest exp_attack should be rank 1 and the person with the lowest exp_attack should be the lowest rank.

 

Hmmm...that query works perfectly for me. Are you sure you are copy/pasting the whole thing correctly? You should be able to paste the whole thing and run both queries in PHP admin.

 

Weird. I just tried fenway's query and got exactly the same result you posted that I believe is when you ran the set variable and query separately.

 

fenway, you are the master and I am out of my league. There appears to be something going on with the variable and I can't figure it out.

For some reason PhpMyAdmin only seems to execute the bottom query when I run yours, but that doesn't explain fenway's method as that's a single query. I honestly don't know but I'd love to get this resolved, thank-you both for your continuing help.

 

How do I run your queries as one on PhpMyAdmin? When I do it, it only shows the bottom one in the query executed.

[josborne]

I just ran this query:

 

SET @num :=0;

SELECT a.user, a.exp_attack AS Attack, @num := @num +1 AS Rank
FROM `rscd_experience` a
GROUP BY a.user
ORDER BY a.exp_attack DESC

 

exactly like that through PHPMyadmin and it work perfectly. There is something I am not getting here.

 

user 	Attack 	Rank
71684325834627 	76239612 	1
148139693 	59175864 	2
983153 	57564069 	3
38261171 	55051182 	4
3203405653017260 	53017189 	5
7897310397846 	45193294 	6
71711257452993 	41300376 	7
77130664793626944 	40323414 	8
8482433060366 	39848166 	9
49577460057 	36624060 	10
4672448 	35501760 	11
21167447082 	35184450 	12
1779170004809624 	34269840 	13
8804383563837 	34100295 	14
1604541127 	33556662 	15
19446421692267282 	32674434 	16
288424075748 	32317470 	17
2664494867034954 	31643868 	18
34822228156 	31156614 	19
21602605516 	30225240 	20
1660564469941630521 	30120840 	21
4369001 	29810814 	22
595099269 	28966866 	23
683134 	28410894 	24
269892 	28185714 	25
1722929401841 	28168098 	26
1260834943459 	27503256 	27
16883898 	27490044 	28
721552 	27422532 	29
695883957 	26070606 	30

 

[Kryptix]

 

It only seems to run the first query, but if I can get it working in PHP I'm not really bothered. Do you just run it within the same query? Could you give me an example?

 

How would I do a join from there? I need to JOIN `rscd_players` ON `rscd_players`.`user` = `rscd_experience`.`user` and then just SELECT `username`, `exp_attack` and `rank`

 

I wish I could fix this PhpMyAdmin problem out.

 

Thanks again for your on-going help.

[josborne]

The JOIN should work like any other JOIN. Play with it, you'll figure it out. The JOIN will come after the SELECT.

 

You might also want to pick up a good MySQL or database book. In a day, you'd have at least an understanding of how to build, normalize and index the database. Believe me, knowing that stuff will save you a ton of effort later on.

[Kryptix]

The JOIN should work like any other JOIN. Play with it, you'll figure it out. The JOIN will come after the SELECT.

 

You might also want to pick up a good MySQL or database book. In a day, you'd have at least an understanding of how to build, normalize and index the database. Believe me, knowing that stuff will save you a ton of effort later on.

 

Cheers for your help josborne but I still haven't got this working. when you paste the two queries into PhpMyAdmin does it show both of them being executed at the top?

[josborne]

When it displays the results, it only shows the last query at the top.

 

Did you try the query fenway posted?:

 

SELECT a.user, a.exp_attack AS Attack, @num := @num +1 AS Rank
FROM `rscd_experience` a, ( SELECT @num := 0 ) b
GROUP BY a.user
ORDER BY a.exp_attack DESC

 

As I mentioned, this query gives me the weird results that my query is giving you but maybe it will work for you.

[Kryptix]

When it displays the results, it only shows the last query at the top.

 

Did you try the query fenway posted?:

 

SELECT a.user, a.exp_attack AS Attack, @num := @num +1 AS Rank
FROM `rscd_experience` a, ( SELECT @num := 0 ) b
GROUP BY a.user
ORDER BY a.exp_attack DESC

 

As I mentioned, this query gives me the weird results that my query is giving you but maybe it will work for you.

 

Same as you mate.

 

Could you show me how to use the query you wrote in PHP? Is it all within the same query?

[fenway]

OK, here's the issue -- the GROUP BY clause.  The counter makes no sense this way.

 

You have duplicate rows for the same user value -- why?