xa0s
Banned
Registered: 4th Mar 08
Location: Dartford, Kent Car: Turbo'd Fabia vRS
User status: Offline
|
So basically we have a highscores for a RPG, there's around 40,000 character records in the rscd_experience table with 18 columns for each skill. I need to obtain their overall rank for each skill (ranking the person with the most experience at #1 and the person with the least experience at the end).
code:
<?php
$skills = array("attack", "defense", "strength", "hits", "ranged", "prayer", "magic", "cooking", "woodcut", "fletching", "fishing", "firemaking", "crafting", "smithing", "mining", "herblaw", "agility", "thieving");
for($i = 0; $i < count($skills); $i++){
$rank = $db->query("SELECT `user` FROM `rscd_experience` WHERE `exp_" . $skills[$i] . "` > 0");
$rank = $db->num_rows($rank) + 1;
echo ucwords($skills[$i]) . " Rank: " . number_format($getRank) . "<br />";
}
?>
I've heard it can be done by setting a @var within the query and then looping through, I've managed to do it with one skill but it involves setting the @var before the query which fucks up when using PHP.
PS. The above isn't my code. I left this feature out as it's tacky and I'd sooner not have a feature than have the above taking 4 seconds to load a page...
Major props if anyone is able to do this...
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
So each person has one row in the table?
And each row has a column for each skill, 18 in total?
And what is the final output you are trying to produce?
|
noshua
Member
Registered: 19th Nov 08
User status: Offline
|
I can help with the coding but in regards with having the page taking 4 seconds to load, you could set up a job to do it once every hour and publish it to a file. It'd save a he'll of a lot of stress on your server, especially with people constantly refreshing.
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Couldn't you self join the `rscd_experience` table (obviously use aliases)? Atleast then you'd get it down to one query.
|
daymoon
Premium Member
Registered: 1st Aug 08
Location: Selby, North Yorkshire
User status: Offline
|
Runescape 
|
xa0s
Banned
Registered: 4th Mar 08
Location: Dartford, Kent Car: Turbo'd Fabia vRS
User status: Offline
|
quote:
Originally posted by Dom
Couldn't you self join the `rscd_experience` table (obviously use aliases)? Atleast then you'd get it down to one query.
Give me an example? Never heard of a self join.
quote:
Originally posted by noshua
I can help with the coding but in regards with having the page taking 4 seconds to load, you could set up a job to do it once every hour and publish it to a file. It'd save a he'll of a lot of stress on your server, especially with people constantly refreshing.
I know that and probably will, but I really want to fix this. I have turned into a SQL head lately and it annoys me that I'm having difficulties with it.
quote:
Originally posted by James
So each person has one row in the table?
And each row has a column for each skill, 18 in total?
And what is the final output you are trying to produce?
Correct.
Output should be like:
user | exp_attack | rank_attack | exp_strength | rank_strength | exp_defense | rank_defense | exp_hits | rank_hits ...
xa0s | 130349385 | 3 | 10000 | 1394 | 0 | 35093 | 3000000 | 392t
I need to search by user.
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Google MySQL Self Join, this might also help (re-reading your post though i don't think it will) - http://www.devshed.com/c/a/MySQL/MySQL-Table-Joins/4/
Are you trying to get a single user details (skills)? Why aren't you just doing a "SELECT * FROM `rscd_experience` WHERE `user_id == '10'" etc then grabbing each column? You then just search by `user_id` (or name or what ever) and ORDER BY what ever skill you want.
If you want all users then get rid of the WHERE and just ORDER BY.
It's just that query seems to grab all users per skill and echos them out, which doesn't make sense if you're trying pull users from the table and rank them.
I'm most likely way off the mark with this all though!
|
xa0s
Banned
Registered: 4th Mar 08
Location: Dartford, Kent Car: Turbo'd Fabia vRS
User status: Offline
|
You are way off...
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Can you post a few rows from the table and a mock-up of what you want to see.
I'll probably end up recommending that you get rid of that bloody awful string concatenation to derive your field names, but I'll gladly give it a try.
|
Reedy
Member
Registered: 11th Apr 04
Location: Hammersmith
User status: Offline
|
you could have a temp table (or a normal one) for every column which contains just the username and rank.
INSERT INTO skill_name_rank_table VALUES
SELECT username from TABLE ORDER BY skill_name ASC
Would this not work?
Obviously you would have to do it 18 times, but it would be fairly easy to maintain/update and selects should be fairly fast.
|
Paul_J
Member
Registered: 6th Jun 02
Location: London
User status: Offline
|
Right, I think I've got what you need... almost... 2 secs.
|
Paul_J
Member
Registered: 6th Jun 02
Location: London
User status: Offline
|
Ok... I just mocked up a simple example on MS SQL - So forgive me if this doesn't work in MySQL.
Basically, I would do a select like the following (but obviously continuing it for all your columns)... and load the data into a datatable.
I would then use that datatable to pull out any statistics I need... e.g. query the datatable for a user's overall statistics, or query it for the top 1 ranked player in exp_strength... or give me a list the top 100 players with exp_hits or whatever...
e.g. a query on the above datatable where user = 'tommy' would give
user | exp_attack | rank attack | exp_hits | rank_hits | exp_defense | rank_defense
tommy | 26 | ranked 1st | 26 | ranked 3rd | 25 | ranked 2nd
Up to you though, but that's how i'd do it.
[Edited on 12-05-2010 by Paul_J]
|
Paul_J
Member
Registered: 6th Jun 02
Location: London
User status: Offline
|
p.s. I think a lot of people here have mis understood his situation and what he wants.
He doesn't have a 'rank' he needs to figure out what each person's rank is, based on their experience (e.g. the person with the most experience is highest rank (either 1 for first, or top number (just change the order desc to asc)).
Therefore it needs to figure out, per skill, where that user is out of all the records and return the result as a 'rank' number.
I took the assumption in the above that 'rank' would be 1 for the person with the highest exp in each skill. e.g. '1st in that skill'
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
quote:
Originally posted by Ian
Can you post a few rows from the table and a mock-up of what you want to see.
|
Paul_J
Member
Registered: 6th Jun 02
Location: London
User status: Offline
|
good to see xaos followed this thread up with an update.
|
