Log in

View Full Version : Improve the Raid Progress Ranking List!


Turiel
13-11-2007, 12:20 AM
So, I don't really have time to develop code for the site atm, so I thought I'd post the code here that we use to sort the list.

Feel free to improve it so that it takes date killed into account in the sorting order so that it gives a more accurate list.

SQL:

mysql> describe boss_kills;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| boss_id | int(11) | NO | | 0 | |
| gid | int(11) | NO | | 0 | |
| instance_id | int(11) | NO | | 0 | |
| kill_date | varchar(35) | NO | | NULL | |
| image | varchar(60) | NO | | NULL | |
+-------------+-------------+------+-----+---------+----------------+



PHP:

<?

$rg_query = "SET @rownum = 0, @rank = 0, @prev_val = NULL";
mysql_query($rg_query);

$rg_query = "SELECT @rownum := @rownum + 1 AS row, @rank := IF(@prev_val!=raid
_score,@rownum,@rank) as rank, name, @prev_val := raid_score as raid_score from
ss_guild where raid_score > 0 order by raid_score desc";

$rg_result = mysql_query($rg_query);

echo "<ul>";

while ($rg_row = mysql_fetch_array($rg_result))
{
echo "<li>".$rg_row[1].". ".$rg_row[2]." (".$rg_row[3]." points)";
if ($rg_row[1] >= 5)
break;
}

echo "</ul>";

?>

Cheiftan
13-11-2007, 12:31 PM
The following code should order first by ranking, then by the time of the guild’s latest kill. If several guilds are on the same ranking then the one who killed their latest boss earliest will go to the top. This is the original code:


$rg_query = "SELECT @rownum := @rownum + 1 AS row, @rank := IF(@prev_val!=raid
_score,@rownum,@rank) as rank, name, @prev_val := raid_score as raid_score from
ss_guild where raid_score > 0 order by raid_score desc";


Change it to this:

$rg_query = "SELECT @rownum := @rownum + 1 AS row, @rank := IF(@prev_val!=raid
_score,@rownum,@rank) as rank, name, @prev_val := raid_score as raid_score,
(SELECT MAX(cast(kill_date as datetime)) FROM boss_kills WHERE boss_kills.gid = ss_guild.gid) as last_kill_date
from
ss_guild where raid_score > 0
order by
raid_score desc,
(SELECT MAX(cast(kill_date as datetime)) FROM boss_kills WHERE boss_kills.gid = ss_guild.gid) asc";




This assumes that the primary key on the ss_guild table is 'gid’. Only you can actually test it though, and it may not be 100% accurate but should be close enough for you to get it working. This will not change their rankings per se, just the order in which they appear on screen if rankings are equal.

If you want to show the date of the latest kill along with the points for clarity, then change this line:


echo "<li>".$rg_row[1].". ".$rg_row[2]." (".$rg_row[3]." points)";


To something like this:


echo "<li>".$rg_row[1].". ".$rg_row[2]." (".$rg_row[3]." Points – Last kill on: ".$rg_row[4].")";

Paratwa
27-11-2007, 10:36 AM
I have a couple of suggestions:
- moving the columns around a bit would improve the progress 'feel': the 'difficulty' order should be BT-MH-TK-SSC-ZA-single-Kara
- leaving the cells with zero kills blank (instead of 0/6 for example), would give an instant glimpse where a given guild is currently progressing (a different background would also be nice)
- dunno the general policy, but there are some guilds that disbanded since their kills; while maybe they should still be listed, those could be grayed-out or something
- a ranking number would be nice; disbanded guilds should mantain their position but not increment that number

just my 2c

Cheiftan
27-11-2007, 12:51 PM
i agree with either the greying out or removal of guilds who have either disbanded or are no longer active in the raiding department.

Elexin
27-11-2007, 02:37 PM
I have a couple of suggestions:
- moving the columns around a bit would improve the progress 'feel': the 'difficulty' order should be BT-MH-TK-SSC-ZA-single-Kara

At present they're organised by their 'difficulty' rating, and then sub-organised alphabetically from what I can tell.

Kara was 10, SSC and TK (being the next step up) were 15, then BT and Hyjal (being the next step up from that) were 20. Single bosses were sort of the leftovers of varying difficulty so got put together as an extra category.
ZA, being higher than Kara, got put on 15 as well.

The current order is almost your order anyway.

Hyjal - BT - SSC - TK - Kara - Single

Hyjal/BT and SSC/TK have switched around (due to being different alphabetically, and BT being listed as The Black Temple, not Black Temple), but each pair of instances is meant to be roughly equal anyway. And ZA already comes after SSC and TK by conveniently starting with a Z, and before Kara via difficulty :)
(Frankly, with the number of nerfs TK has seen, I don't believe its really harder than SSC either)

a ranking number would be nice; disbanded guilds should mantain their position but not increment that number
The guilds already have a ranking number of sorts, based on the 'point score' of the bosses that they've killed. Its just not an overly neat number, and isn't displayed on the raid progress page itself (in favour of the clearer and more obvious boxes)
Its visible on the Raid Guild Rankings on the mainpage though.