I expanded everything around this improved death log table, then build a php script to periodically pull the data and save it as an XML file. I am sure all of these could be improved so feel free to do so.
First - here is the SQL to create the expanded table:
- Code: Select all
CREATE TABLE `chars_new_deathlog` (
`ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`Time` INT(10) UNSIGNED NOT NULL,
`PrevTime` INT(10) UNSIGNED NULL DEFAULT '0',
`VictimID` INT(10) UNSIGNED NOT NULL,
`VicFirstName` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`VicLastName` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`VicGuildID` INT(10) UNSIGNED NULL DEFAULT NULL,
`VicGuildRole` INT(10) UNSIGNED NULL DEFAULT NULL,
`VicGuildName` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`KillerID` INT(10) UNSIGNED NOT NULL,
`KillFirstName` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`KillLastName` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`KillerGuildID` INT(10) UNSIGNED NULL DEFAULT NULL,
`KillerGuildName` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`AtWar` TINYINT(3) NULL DEFAULT '0',
`IsKnockout` TINYINT(3) UNSIGNED NULL DEFAULT '0',
PRIMARY KEY (`ID`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=126
;
Next - the SQL to create the trigger that will populate the new death table:
- Code: Select all
/* Create the trigger to populate the new death table */
CREATE DEFINER=`root`@`localhost` TRIGGER `chars_new_deathlog_trigger` AFTER INSERT ON `chars_deathlog` FOR EACH ROW begin
SELECT ID, Name, LastName, GuildID, GuildRoleID into @vid, @vfn, @vln, @vgd, @vgr from `character` where `character`.ID = new.CharID;
if (new.KillerID = 0) then
Set @kfn = 'Animal'; Set @kln = ''; Set @kgd = 0; Set @kgn = 'Mother Nature';
else
SELECT Name, LastName, GuildID into @kfn, @kln, @kgd from `character` where `character`.ID = new.KillerID;
END IF;
set @pt = (select chars_new_deathlog.`Time` from chars_new_deathlog where chars_new_deathlog.VictimID = @vid ORDER BY chars_new_deathlog.ID DESC LIMIT 1);
IF @pt IS NULL THEN
set @pt = UNIX_TIMESTAMP(STR_TO_DATE('Apr 15 2015 12:00AM', '%M %d %Y %h:%i%p'));
END IF;
SET @vgn = (SELECT Name FROM guilds where guilds.ID = @vgd);
SET @kgn = (SELECT Name FROM guilds where guilds.ID = @kgd);
SET @war = (SELECT StandingTypeID FROM guild_standings WHERE (@vgd = GuildID1 AND @kgd = GuildID2));
insert into chars_new_deathlog (chars_new_deathlog.`Time`, chars_new_deathlog.PrevTime,
chars_new_deathlog.VictimID,
chars_new_deathlog.VicFirstName,
chars_new_deathlog.VicLastName,
chars_new_deathlog.VicGuildID,
chars_new_deathlog.VicGuildRole,
chars_new_deathlog.VicGuildName,
chars_new_deathlog.KillerID,
chars_new_deathlog.KillFirstName,
chars_new_deathlog.KillLastName,
chars_new_deathlog.KillerGuildID,
chars_new_deathlog.KillerGuildName,
chars_new_deathlog.AtWar,
chars_new_deathlog.IsKnockout)
values ( new.`Time`, @pt, @vid, @vfn, @vln, IFNULL(@vgd,0), IFNULL(@vgr,0), IFNULL(@vgn,''),
new.KillerID, @kfn, @kln, IFNULL(@kgd,0), IFNULL(@kgn,''), IF(@war=1,1,0), new.IsKnockout);
end;
Next - the php code to extract the data. Note that some changes need to be made here to connect to your database and to store it into an accessible area. I had created a read only user named reporter (towards the end). You will need to do the same and use a password other than 'password'. Finally - you will need to install php to get this to run. There are a lot of resources out there to help you with PHP.
- Code: Select all
<?php
// Set the timestamp
$formated_date = date("F d, Y @ h:i:sa");
$instance = "lif_6";
//------------------------- All Kills -------------------------
// Open the database and retrieve the query for all characters
$result = RunQuery($instance, "SELECT `character`.Name, `character`.Lastname, guilds.ID, guilds.Name as GuildName from `character` left join guilds on `character`.GuildID = guilds.ID");
$outputFile = OpenFile("roster", "C:\inetpub\wwwroot\killboard", ".xml");
// Write the all kills xml file
WriteRoster($outputFile, $result, $formated_date);
// Open the database and retrieve the query for all deaths
$result = RunQuery($instance, "SELECT * from chars_new_deathlog");
// Open the file name
$outputFile = OpenFile("kb_allKills", "C:\inetpub\wwwroot\killboard", ".xml");
// Write the all kills xml file
WriteAllKills($outputFile, $result, $formated_date);
// Build the guild list values
$result = RunQuery($instance, "select ID, Name from Guilds order by ID");
$guilds = array();
while($row = mysqli_fetch_array($result)) {
$guild = array($row["ID"], $row["Name"], 0, 0, 0);
array_push($guilds, $guild);
}
// Retrieve the total kills for each guild
$i = 0;
foreach($guilds as $g) {
$result = RunQuery($instance, "select COUNT(KillerGuildID) as T from chars_new_deathlog where KillerGuildID = " . $g[0]);
$row = mysqli_fetch_array($result);
$guilds[$i][2] = $row["T"];
$i++;
}
// Retrieve the total losses for each guild and calculate K/D percentage
$i = 0;
foreach($guilds as $g) {
$result = RunQuery($instance, "select COUNT(VicGuildID) as T from chars_new_deathlog where VicGuildID = " . $g[0]);
$row = mysqli_fetch_array($result);
$guilds[$i][3] = $row["T"];
$d = ($guilds[$i][3] + $guilds[$i][2]);
if ($d == 0)
$d = 1;
$guilds[$i][4] = round(($guilds[$i][2] / $d),3) * 100;
$i++;
}
// Print out the top Guild Killers
$outputFile = OpenFile("kb_topGuildKillers", "C:\inetpub\wwwroot\killboard", ".xml");
WriteTopGuildList($outputFile, array_sort($guilds, 2, SORT_DESC), $formated_date, 2);
// Print out the top Guild Victims
$outputFile = OpenFile("kb_topGuildVictims", "C:\inetpub\wwwroot\killboard", ".xml");
WriteTopGuildList($outputFile, array_sort($guilds, 3, SORT_DESC), $formated_date, 3);
// Print out the top Guild Kill/Death Winners
$outputFile = OpenFile("kb_topGuildKDs", "C:\inetpub\wwwroot\killboard", ".xml");
WriteTopGuildList($outputFile, array_sort($guilds, 4, SORT_DESC), $formated_date, 4, "%");
// Build the player list values
$result = RunQuery($instance, "select ID, Name, LastName, GuildID from `character` order by ID");
$players = array();
while($row = mysqli_fetch_array($result)) {
$player = array($row["ID"], $row["Name"], $row["LastName"], $row["GuildID"], 0, 0, 0);
array_push($players, $player);
}
// Retrieve the total kills for each player
$i = 0;
foreach($players as $p) {
$result = RunQuery($instance, "select COUNT(KillerID) as T from chars_new_deathlog where KillerID = " . $p[0]);
$row = mysqli_fetch_array($result);
$players[$i][4] = $row["T"];
$i++;
}
// Retrieve the total deaths for each player and calculate K/D percentage
$i = 0;
foreach($players as $p) {
$result = RunQuery($instance, "select COUNT(VictimID) as T from chars_new_deathlog where VictimID = " . $p[0]);
$row = mysqli_fetch_array($result);
$players[$i][5] = $row["T"];
$d = ($players[$i][5] + $players[$i][4]);
if ($d == 0)
$d = 1;
$players[$i][6] = round(($players[$i][4] / $d),3) * 100;
$i++;
}
// Print out the top Player Killers
$outputFile = OpenFile("kb_topPlayerKillers", "C:\inetpub\wwwroot\killboard", ".xml");
WriteTopPlayerList($outputFile, array_sort($players, 4, SORT_DESC), $guilds, $formated_date, 4);
// Print out the top Player Victims
$outputFile = OpenFile("kb_topPlayerVictims", "C:\inetpub\wwwroot\killboard", ".xml");
WriteTopPlayerList($outputFile, array_sort($players, 5, SORT_DESC), $guilds, $formated_date, 5);
// Print out the top Player Kill/Death Winners
$outputFile = OpenFile("kb_topPlayerKDs", "C:\inetpub\wwwroot\killboard", ".xml");
WriteTopPlayerList($outputFile, array_sort($players, 6, SORT_DESC), $guilds, $formated_date, 6, "%");
// Build the player list values
//------------------------- Top 5 Knockouts --------------------------
$result = RunQuery($instance, "select KillFirstName as First, KillLastName as Last, KillerGuildID as ID, Count(ID) as K from chars_new_deathlog where IsKnockout = 1 group by (KillerID) ORDER BY K DESC LIMIT 10;");
$outputFile = OpenFile("kb_topKnockOuts", "C:\inetpub\wwwroot\killboard", ".xml");
WriteTopList($outputFile, $result, $formated_date, $guilds);
//------------------------- Top 5 Unconscious ---------------------------
$result = RunQuery($instance, "select VicFirstName as First, VicLastName as Last, VicGuildID as ID, Count(ID) as K from chars_new_deathlog where IsKnockout = 1 group by (VictimID) ORDER BY K DESC LIMIT 10;");
$outputFile = OpenFile("kb_topUnconscious", "C:\inetpub\wwwroot\killboard", ".xml");
WriteTopList($outputFile, $result, $formated_date, $guilds);
//------------------------- Top 5 Animal Deaths ---------------------------
$result = RunQuery($instance, "select VicFirstName as First, VicLastName as Last, VicGuildID as ID, Count(ID) as K from chars_new_deathlog where KillerID = 0 group by (VictimID) ORDER BY K DESC LIMIT 10;");
$outputFile = OpenFile("kb_topAnimals", "C:\inetpub\wwwroot\killboard", ".xml");
WriteTopList($outputFile, $result, $formated_date, $guilds, 1);
//--------------------------------------------------------------------
// FUNCTIONS
//--------------------------------------------------------------------
function GetGuildName($guilds, $ID)
{
$name = "";
foreach($guilds as $g)
{
if ($g[0] == $ID)
$name = $g[1];
}
return $name;
}
function WriteTopPlayerList($outputFile, $players, $guilds, $date, $index, $per = "")
{
$i = 1;
// Write the header information
fwrite($outputFile, '<?xml version="1.0" encoding="UTF-8" ?>' . "\n");
// Write the opening tag
fwrite($outputFile, "<Players timestamp=\"".$date."\">\n");
foreach($players as $p)
{
if ($p[$index] != 0)
{
fwrite($outputFile, "\t<Player>\n");
fwrite($outputFile, "\t\t<PlayerName>" . $p[1] . " " . $p[2] . "</PlayerName>\n");
if ($p[0] == 0)
fwrite($outputFile, "\t\t<PlayerGuildName>Mother Nature</PlayerGuildName>\n");
else
fwrite($outputFile, "\t\t<PlayerGuildName>".GetGuildName($guilds, $p[3])."</PlayerGuildName>\n");
fwrite($outputFile, "\t\t<Amount>" . $p[$index] . $per . "</Amount>\n");
fwrite($outputFile, "\t</Player>\n");
}
if ($i >= 10)
break;
else
$i ++;
}
fwrite($outputFile, "</Players>\n");
fclose($outputFile);
}
function WriteTopGuildList($outputFile, $guilds, $date, $index, $per = "")
{
$i = 1;
// Write the header information
fwrite($outputFile, '<?xml version="1.0" encoding="UTF-8" ?>' . "\n");
// Write the opening tag
fwrite($outputFile, "<Guilds timestamp=\"".$date."\">\n");
foreach($guilds as $g)
{
if ($g[$index] != 0)
{
fwrite($outputFile, "\t<Guild>\n");
fwrite($outputFile, "\t\t<GuildName>".$g[1]."</GuildName>\n");
fwrite($outputFile, "\t\t<Amount>".$g[$index]. $per ."</Amount>\n");
fwrite($outputFile, "\t</Guild>\n");
}
if ($i >= 10)
break;
else
$i ++;
}
fwrite($outputFile, "</Guilds>\n");
fclose($outputFile);
}
function WriteTopList($outputFile, $result, $date, $guilds)
{
// Write the header information
fwrite($outputFile, '<?xml version="1.0" encoding="UTF-8" ?>' . "\n");
// Write the opening tag
fwrite($outputFile, "<Players timestamp=\"".$date."\">\n");
while ($row = mysqli_fetch_array($result))
{
fwrite($outputFile, "\t<Player>\n");
fwrite($outputFile, "\t\t<PlayerFirstName>".$row{'First'}."</PlayerFirstName>\n");
fwrite($outputFile, "\t\t<PlayerLastName>".$row{'Last'}."</PlayerLastName>\n");
if ($row{'First'} == "Animal")
fwrite($outputFile, "\t\t<PlayerGuildName>Mother Nature</PlayerGuildName>\n");
else
fwrite($outputFile, "\t\t<PlayerGuildName>".GetGuildName($guilds, $row{'ID'})."</PlayerGuildName>\n");
fwrite($outputFile, "\t\t<TotalDeaths>".$row{'K'}."</TotalDeaths>\n");
fwrite($outputFile, "\t</Player>\n");
}
fwrite($outputFile, "</Players>\n");
fclose($outputFile);
}
function WriteTopKillers($outputFile, $result, $date, $guilds)
{
// Write the header information
fwrite($outputFile, '<?xml version="1.0" encoding="UTF-8" ?>' . "\n");
// Write the opening tag
fwrite($outputFile, "<Killers timestamp=\"".$date."\">\n");
while ($row = mysqli_fetch_array($result))
{
fwrite($outputFile, "\t<Killer>\n");
fwrite($outputFile, "\t\t<KillFirstName>".$row{'KillFirstName'}."</KillFirstName>\n");
fwrite($outputFile, "\t\t<KillLastName>".$row{'KillLastName'}."</KillLastName>\n");
fwrite($outputFile, "\t\t<KillerGuildName>".GetGuildName($guilds, $row{'KillerGuildID'})."</KillerGuildName>\n");
fwrite($outputFile, "\t\t<TotalKills>".$row{'K'}."</TotalKills>\n");
fwrite($outputFile, "\t</Killer>\n");
}
fwrite($outputFile, "</Killers>\n");
fclose($outputFile);
}
function WriteAllKills($outputFile, $result, $date)
{
// Write the header information
fwrite($outputFile, '<?xml version="1.0" encoding="UTF-8" ?>' . "\n");
// Write the opening tag
fwrite($outputFile, "<Kills timestamp=\"".$date."\">\n");
while ($row = mysqli_fetch_array($result))
{
fwrite($outputFile, "\t<Kill>\n");
fwrite($outputFile, "\t\t<Time>".gmdate("Y-m-d H:i:s", $row{'Time'})." UTC</Time>");
fwrite($outputFile, "\t\t<VictimID>".$row{'VictimID'}."</VictimID>\n");
fwrite($outputFile, "\t\t<VictimFirstName>".$row{'VicFirstName'}."</VictimFirstName>\n");
fwrite($outputFile, "\t\t<VictimLastName>".$row{'VicLastName'}."</VictimLastName>\n");
fwrite($outputFile, "\t\t<VictimGuildID>".$row{'VicGuildID'}."</VictimGuildID>\n");
fwrite($outputFile, "\t\t<VictimGuildRole>".$row{'VicGuildRole'}."</VictimGuildRole>\n");
fwrite($outputFile, "\t\t<VictimGuildName>".$row{'VicGuildName'}."</VictimGuildName>\n");
fwrite($outputFile, "\t\t<KillerID>".$row{'KillerID'}."</KillerID>\n");
fwrite($outputFile, "\t\t<KillFirstName>".$row{'KillFirstName'}."</KillFirstName>\n");
fwrite($outputFile, "\t\t<KillLastName>".$row{'KillLastName'}."</KillLastName>\n");
fwrite($outputFile, "\t\t<KillerGuildID>".$row{'KillerGuildID'}."</KillerGuildID>\n");
fwrite($outputFile, "\t\t<KillerGuildName>".$row{'KillerGuildName'}."</KillerGuildName>\n");
fwrite($outputFile, "\t\t<AtWar>".$row{'AtWar'}."</AtWar>\n");
fwrite($outputFile, "\t\t<IsKnockout>".$row{'IsKnockout'}."</IsKnockout>\n");
fwrite($outputFile, "\t</Kill>\n");
}
fwrite($outputFile, "</Kills>\n");
fclose($outputFile);
}
function WriteRoster($outputFile, $result, $date)
{
// Write the header information
fwrite($outputFile, '<?xml version="1.0" encoding="UTF-8" ?>' . "\n");
// Write the opening tag
fwrite($outputFile, "<Players timestamp=\"".$date."\">\n");
while ($row = mysqli_fetch_array($result))
{
fwrite($outputFile, "\t<Player>\n");
fwrite($outputFile, "\t\t<FirstName>".$row{'Name'}."</FirstName>\n");
fwrite($outputFile, "\t\t<LastName>".$row{'Lastname'}."</LastName>\n");
fwrite($outputFile, "\t\t<GuildID>".$row{'ID'}."</GuildID>\n");
fwrite($outputFile, "\t\t<GuildName>".$row{'GuildName'}."</GuildName>\n");
fwrite($outputFile, "\t</Player>\n");
}
fwrite($outputFile, "</Players>\n");
fclose($outputFile);
}
function OpenFile($name, $directory, $fileType)
{
$rawdate = date("m-d-Y-H-i-s");
if (file_exists($directory . '\\' . $name . $fileType))
{
copy($directory . '\\' . $name . $fileType, $directory . '\\archives\\' . $name . "_" . $rawdate . $fileType);
}
$outputFile = fopen($directory . '\\' . $name . $fileType, 'w') or exit("Unable to open output file!");
return $outputFile;
}
function OpenConnection()
{
$servername = "localhost";
$username = "reporter";
$password = "password";
// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
return $conn;
}
function RunQuery($db, $query)
{
$conn = OpenConnection();
$selected = mysqli_select_db($conn, $db) or die("Could not select database");
$result = mysqli_query($conn, $query);
mysqli_close($conn);
return $result;
}
function array_sort($array, $on, $order=SORT_ASC)
{
$new_array = array();
$sortable_array = array();
if (count($array) > 0) {
foreach ($array as $k => $v) {
if (is_array($v)) {
foreach ($v as $k2 => $v2) {
if ($k2 == $on) {
$sortable_array[$k] = $v2;
}
}
} else {
$sortable_array[$k] = $v;
}
}
switch ($order) {
case SORT_ASC:
asort($sortable_array);
break;
case SORT_DESC:
arsort($sortable_array);
break;
}
foreach ($sortable_array as $k => $v) {
$new_array[$k] = $array[$k];
}
}
return $new_array;
}
?>
We also had a random loot generator so killing someone outside your guild would always provide one random item. I need to confirm that still works in the new release.
