The World of Warcraft Armory data provides all you need to build a basic Burning Crusade Progress Page. The page can list who is capable to have Heroic Keys by faction along with who should be Karazhan keyed based off character reputation.
First up is a little database design. I created several tables in MySQL to house the data I would be using including a couple of lookup tables for data not found in the Armory XML. The first is the guild table which will house the majority of the information. Here is an abbreviated schema:
CREATE TABLE `wow_roster_guild` (
`characterID` smallint(5) unsigned NOT NULL auto_increment,
`classID` tinyint(3) unsigned NOT NULL default '0',
`genderID` tinyint(1) NOT NULL default '0',
`level` tinyint(3) unsigned NOT NULL default '0',
`name` varchar(35) NOT NULL default '',
`raceID` tinyint(3) unsigned NOT NULL default '0',
`rank` tinyint(3) unsigned NOT NULL default '0',
`url` varchar(50) NOT NULL default '',
`active` tinyint(1) NOT NULL default '1',
`characterHash` varchar(32) default NULL,
`server` varchar(25) NOT NULL default '',
`guild` varchar(25) NOT NULL default '',
PRIMARY KEY (`characterID`)
)
You can see some of the basic information included from the guild-info.xml. The important fields will be characterID, active and characterHash. Notice there is a server and guild column for sites that might want to maintain more then one guild. The next table needed is the reputation table. Here the schema of that table:
CREATE TABLE `wow_roster_reputation` (
`characterID` int(10) unsigned NOT NULL default '0',
`categoryName` varchar(25) NOT NULL default '',
`faction` varchar(25) NOT NULL default '',
`reputation` mediumint(9) NOT NULL default '0',
KEY `faction` (`faction`),
KEY `characterID` (`characterID`)
)
We have characterID to link back to the guild table and faction to represent the unique factions for each player. Now for you PHPers out there, you can either marvel at how simple ColdFusion is to use and convert or follow along closely to the concepts and make use of them by creating equivalent PHP code. The first task is to setup your Realm and Guild variables for use later on.
These will be used several times and provide an easy place to change or customize as needed. Next up is the code to pull down the current roster.
UPDATE wow_roster_guild
SET active = 0
WHERE server =
SELECT name, characterID
FROM wow_roster_guild
WHERE server =
SELECT name, characterID
FROM queryCharacters
WHERE name =
UPDATE wow_roster_guild
SET classID =
INSERT INTO wow_roster_guild (classID, genderID, level, name, raceID, rank, url, server, guild)
VALUES (
SELECT name, characterID
FROM queryCharacters
WHERE name =
That is a lot of code so I will give a brief overview. First up, the guild information is pulled and used to provide the outer loop. All current members are deactivated. A list of current characters are pulled back to verify if the character already exists. The wow_roster_guild table is updated according to if the character is new or not. Notice updated users are set active and the default value on the active column takes care of the new members.
The last part is the updating of the reputation information
DELETE FROM wow_roster_reputation
WHERE characterID =
INSERT INTO wow_roster_reputation (characterID, categoryName, faction, reputation)
VALUES (
UPDATE wow_roster_guild
SET characterHash =
This section pulls down the specific character's profile. All the players current reputation is cleared so it can be repopulated as long as it exists in the character sheet.
Now I will mention one small thing, I pulled all this code out of my own code and edited things where appropriate but I haven't tested it. So I may have forgot a couple of things. Let me know and I will update the page accordingly. Have fun.
Recent Comments