"Simple" server info page to replace WoWs news page on login screen

index.php

<?php
// "Simple" server info page to replace WoWs news page on login screen
// (replace "Breaking News" Article on World of Warcraft login screen)
// By MobCat (2023)
// Preview
//https://cdn.discordapp.com/attachments/1063759326340186172/1129600786637262938/image.png

// Server side this has only been tested with trinity core for WoW 3.3.5 with sql 5.
// This will fubar on newer sql vers, sorry. But wont be to hard to convert mysql_query to $conn->query($sql) and so on
// Also https might brake it, idk.
// On the client side though, this has been tested and appears to work with both enUS and enGB and all vers of wow
// from og WoW 2.4.3.8606 all the way though to the new wow classic engine. So im converdent this should work for all
// vers of wow as long as you get the news redirect corect.
// made a bat script for that too
//https://gist.github.com/MobCat/53aadf4ddb6e072a511aef8ebdc8fc25

// The ingame leaderboard is formatted as
// character name - Race Class - Level (current xp) - X offline or O online.

// [How to setup]
// Client setup (windows)
// 1. goto "C:\Windows\System32\drivers\etc" and edit hosts file with notepad as admin
// 2. add the following lines (without the \\ ofc.) Change 127.0.0.1 to your web server.
//# US WoW redirect (untested)
//127.0.0.127   launcher.worldofwarcraft.com
//# WoW EU EN redirect
//127.0.0.1   status.wow-europe.com
// 3. Save and close the hosts file.
//
// Server setup
// 1. goto your htdocs folder on your server
// 2. for EU EN make a new folder folder structure like this
// htdocs\en\alert\index.php
// for US make
// htdocs\alert\index.php
// 3. Open our new php file with notepad and copy past in this whole file
// Or just download this php file and place it into your alert folder
// 4. If your sql and apache servers where not running, now would be a good time to launch them
// 5. Check the news page. In your browser, goto up http://status.wow-europe.com/en/alert/
// or http://launcher.worldofwarcraft.com/alert for US
// And you should now see some plain text info about your server
// If you didn't get and random sql errors then this should display fine at the login screen as well.

// Max of 32 chars per line of news? befor it overflows into the next line.

// Define your WoW databases and creds
$dbip    = "127.0.0.1";
$dbUser  = "trinity";
$dbPass  = "trinity";
$realmID = 1;
$auth    = "auth";
$char    = "characters";
$world   = "world";

//########################################################################################################################
// Connect and check db.
// Create a database connection
$conn = mysql_connect($dbip, $dbUser, $dbPass);

// Check the connection
if (!$conn) {
    die("Connection failed: " . mysql_error());
}

//########################################################################################################################
// Server stats
//########################################################################################################################
// Select update based on epoch start from auth database
mysql_select_db($auth, $conn);
$uptimeQuery = mysql_query("SELECT * FROM uptime ORDER BY starttime DESC LIMIT 1");
$uptimeRow = mysql_fetch_assoc($uptimeQuery);

// Count accounts and check if they are online or not.
$accountCount = 0;
$accountOnline = 0;
$accountQuery = mysql_query("SELECT online FROM account");

// Loop over account rows
while ($accountRow = mysql_fetch_assoc($accountQuery)) {
    $accountCount++; // Increment accountCount for each row

    if ($accountRow['online'] == 1) {
        $accountOnline++; // Increment accountOnline if online value is 1
    }
}

// Get realm info
$dictIcon = array(
    0 => 'Normal',
    1 => 'PvP',
    4 => 'Normal',
    6 => 'RP',
    8 => 'RP PvP'
);
$realmQuery = mysql_query("SELECT * FROM realmlist WHERE id = ". $realmID);
$realmRow = mysql_fetch_assoc($realmQuery);
// Realm icon set
$realmIcon = isset($dictIcon[$realmRow['icon']]) ? $dictIcon[$realmRow['icon']] : '?';

// Func to format secs elapsed
function formatTime($secIn, $days = false) {
    $hours = floor($secIn / 3600);
    $remainder = $secIn % 3600;
    $minutes = floor($remainder / 60);
    $seconds = $remainder % 60;

    if ($days) {
        $days = floor($hours / 24);
        $hours = $hours % 24;
        return sprintf("%03dd %02d:%02d:%02d", $days, $hours, $minutes, $seconds);
    } else {
        return sprintf("%03d:%02d:%02d", $hours, $minutes, $seconds);
    }
}

// Format info into html
// Please note, there is a zero width space between the last <p> </p> to separate the server info from the leaderboard, 
// as the client just strips unnecessary spaces. If this is missing and your news window doesn't have a gap like the demo pic
// because github or downloading this php brakes it, go to the last <p> </p> and in the gap in the middle of them type alt+0160
//(hold down alt, then type 0160 on your numpad, then let go of alt)
$stats = "<p>(". $realmIcon .") ". $realmRow['name'] .
     "</p><p>IP: ". $realmRow['address'] .
     "</p><p>Uptime: ". formatTime($uptimeRow['uptime'], true) .
     "</p><p>Users Online: ". $accountOnline ."/". $accountCount ." (Max: ". $uptimeRow['maxplayers'] .
     ")</p><p> </p>";


//########################################################################################################################
// Leaderboard
//########################################################################################################################
// Dictionary for race lookup
$dictRace = array(
    0 => '?',
    1 => 'HU', 
    2 => 'OR', 
    3 => 'DW',
    4 => 'NE',
    5 => 'UD',
    6 => 'TR',
    7 => 'GN',
    8 => 'RL',
    9 => 'GB',
    10 => 'BE',
    11 => 'DR'
);

// Dictionary for class lookup
$dictClass = array(
    0 => '?',
    1 => 'WR',
    2 => 'PL',
    3 => 'HN',
    4 => 'RO',
    5 => 'PR',
    6 => 'DK',
    7 => 'SA',
    8 => 'MA',
    9 => 'WA',
    10 => 'MK',
    11 => 'DU'
);

// Select the char database
mysql_select_db($char, $conn);
// Select top 10 characters based on most xp
$result = mysql_query("SELECT * FROM characters ORDER BY level DESC, xp DESC LIMIT 10");

// Generate the leaderboard for each characters found with the sql query
// If less then 10 characters are found then this *should* only display what it finds.
$leaderboard = '';
if (mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_assoc($result)) {
        // Perform lookup for $row['race'] and $row['class']
        $race = isset($dictRace[$row['race']]) ? $dictRace[$row['race']] : '?';
        $class = isset($dictClass[$row['class']]) ? $dictClass[$row['class']] : '?';

        // format this leaderboard row into html
        $leaderboard .= "<p>{$row['name']} - {$race} {$class} - lvl {$row['level']} ({$row['xp']}xp) - " . ($row['online'] == 0 ? 'X' : 'O') . "</p>";
    }
} else {
    // error catch for no characters at all found.
    $leaderboard = "<p>No characters found.</p>";
}

// Close the database connection
mysql_close($conn);


// Now display the formatted html to the wow client. DO NOT EDIT THIS.
// The newlines at the start and end are important.
// When you download this script, make sure the blank newline at the end of this file is intact
// Without it the client will error out and won't display any news?>
SERVERALERT:<html><body><p>[Realm info]</p><?php echo $stats; ?><p>[Leaderboard]</p><?php echo $leaderboard; ?></body></html>