How to Implement Automatic Ranking System on MU Website

Displaying an automatically updated ranking is one of the most valued features for MU Online private server players. This guide explains how to integrate the SQL Server database from your MuServer installation (Season 6 and above) with a PHP website to generate character, reset, and guild rankings — without any manual intervention.

Prerequisites

Before starting, confirm you have:

• Access to SQL Server Management Studio (SSMS) with read/write permissions on the MuOnline database
• PHP 7.4+ with the sqlsrv or pdo_sqlsrv extension installed (XAMPP or AppServ on Windows Server)
• Access to SQL Server Agent to schedule Jobs
• Permission to create tables and stored procedures in the database

Step 1: Understand the Relevant Table Structure

MuServer stores character data in the Character table inside the MuOnline database. The most commonly used columns for ranking are:

-- Check the Character table structure
USE MuOnline;
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Character'
ORDER BY ORDINAL_POSITION;

Key columns for ranking:

Step 2: Create the Ranking Cache Table

Rather than querying the Character table directly on every website request, create a dedicated cache table. This prevents database slowdowns during traffic peaks.

USE MuOnline;
GO

CREATE TABLE RankingCache (
    RankPosition     INT IDENTITY(1,1) PRIMARY KEY,
    CharacterName    VARCHAR(10)  NOT NULL,
    AccountID        VARCHAR(10)  NOT NULL,
    CharClass        TINYINT      NOT NULL,
    CharLevel        SMALLINT     NOT NULL,
    CharResets       INT          NOT NULL DEFAULT 0,
    CharZen          BIGINT       NOT NULL DEFAULT 0,
    GuildName        VARCHAR(8)   NULL,
    LastUpdate       DATETIME     NOT NULL DEFAULT GETDATE(),
    RankType         VARCHAR(20)  NOT NULL DEFAULT 'level'
);

CREATE INDEX IX_RankingCache_Type ON RankingCache (RankType, RankPosition);
GO

Step 3: Create the Update Stored Procedure

The stored procedure below deletes and rebuilds cache data for each ranking type. Run this in SSMS to create it:

USE MuOnline;
GO

CREATE PROCEDURE sp_UpdateRankingCache
AS
BEGIN
    SET NOCOUNT ON;

    -- Clear previous cache
    DELETE FROM RankingCache;

    -- Ranking by Level and Resets
    INSERT INTO RankingCache (CharacterName, AccountID, CharClass, CharLevel, CharResets, CharZen, GuildName, RankType)
    SELECT TOP 200
        C.Name,
        C.AccountID,
        C.Class,
        C.cLevel,
        ISNULL(C.Resets, 0),
        C.Money,
        G.G_Name,
        'level'
    FROM Character C
    LEFT JOIN GuildMember GM ON GM.Name = C.Name
    LEFT JOIN Guild G ON G.G_Name = GM.G_Name
    WHERE C.cLevel > 0
      AND C.AccountID NOT IN (
          SELECT memb___id FROM MEMB_INFO WHERE memb_stat = 1
      )
    ORDER BY ISNULL(C.Resets, 0) DESC, C.cLevel DESC, C.Money DESC;

    -- Ranking by Zen (wealth)
    INSERT INTO RankingCache (CharacterName, AccountID, CharClass, CharLevel, CharResets, CharZen, GuildName, RankType)
    SELECT TOP 100
        C.Name,
        C.AccountID,
        C.Class,
        C.cLevel,
        ISNULL(C.Resets, 0),
        C.Money,
        G.G_Name,
        'zen'
    FROM Character C
    LEFT JOIN GuildMember GM ON GM.Name = C.Name
    LEFT JOIN Guild G ON G.G_Name = GM.G_Name
    WHERE C.cLevel > 0
      AND C.AccountID NOT IN (
          SELECT memb___id FROM MEMB_INFO WHERE memb_stat = 1
      )
    ORDER BY C.Money DESC;

    -- Refresh timestamp
    UPDATE RankingCache SET LastUpdate = GETDATE();

    PRINT 'RankingCache updated successfully.';
END;
GO

Step 4: Schedule Automatic Updates via SQL Server Agent

Create a Job in SQL Server Agent to run the procedure automatically every 10 minutes:

1. Open SSMS → expand SQL Server Agent → right-click Jobs → New Job
2. General tab: Name = Update MU Ranking
3. Steps tab → New Step:

• Step Name: Run sp_UpdateRankingCache
• Type: Transact-SQL script (T-SQL)
• Database: MuOnline
• Command: EXEC sp_UpdateRankingCache

1. Schedules tab → New Schedule:

• Name: Every 10 minutes
• Frequency: Daily
• Daily frequency: Occurs every 10 minutes
• Start/End: 00:00:00 to 23:59:59

1. Click OK to save.

To test immediately, right-click the Job → Start Job at Step.

Step 5: Configure the PHP Connection to SQL Server

On your website, create the database configuration file. Save it at /web/includes/db_config.php:

<?php
// /web/includes/db_config.php
define('DB_SERVER',   '127.0.0.1');   // SQL Server IP
define('DB_PORT',     '1433');
define('DB_USER',     'mu_web_user'); // user with SELECT permission only
define('DB_PASS',     'YourPasswordHere');
define('DB_NAME',     'MuOnline');

function getDbConnection() {
    $connectionOptions = [
        'Database'               => DB_NAME,
        'Uid'                    => DB_USER,
        'PWD'                    => DB_PASS,
        'CharacterSet'           => 'UTF-8',
        'TrustServerCertificate' => true,
    ];
    $conn = sqlsrv_connect(DB_SERVER . ', ' . DB_PORT, $connectionOptions);
    if ($conn === false) {
        error_log('DB connection failed: ' . print_r(sqlsrv_errors(), true));
        return null;
    }
    return $conn;
}
?>

Step 6: Build the Ranking PHP Page

Save the file at /web/ranking.php:

<?php
require_once 'includes/db_config.php';

$type   = isset($_GET['type']) ? $_GET['type'] : 'level';
$type   = in_array($type, ['level', 'zen']) ? $type : 'level';
$page   = max(1, (int)($_GET['page'] ?? 1));
$limit  = 25;
$offset = ($page - 1) * $limit;

// Season 6 class map
$classNames = [
    0  => 'Dark Wizard',        1  => 'Soul Master',      2  => 'Grand Master',
    16 => 'Dark Knight',        17 => 'Blade Knight',     18 => 'Blade Master',
    32 => 'Fairy Elf',          33 => 'Muse Elf',         34 => 'High Elf',
    48 => 'Magic Gladiator',    64 => 'Dark Lord',
    80 => 'Summoner',           96 => 'Rage Fighter',
];

$conn = getDbConnection();
$sql  = "SELECT CharacterName, CharClass, CharLevel, CharResets, CharZen, GuildName,
                ROW_NUMBER() OVER (PARTITION BY RankType ORDER BY RankPosition) AS Pos
         FROM RankingCache
         WHERE RankType = ?
         ORDER BY RankPosition
         OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";

$params = [$type, $offset, $limit];
$result = sqlsrv_query($conn, $sql, $params);

$lastUpdate = '';
$luSql      = "SELECT TOP 1 LastUpdate FROM RankingCache WHERE RankType = ?";
$luRes      = sqlsrv_query($conn, $luSql, [$type]);
if ($luRow  = sqlsrv_fetch_array($luRes, SQLSRV_FETCH_ASSOC)) {
    $lastUpdate = $luRow['LastUpdate']->format('m/d/Y H:i');
}
?>
<!DOCTYPE html>
<html lang="en">
<head><meta charset="UTF-8"><title>Ranking - ViciadosMU</title></head>
<body>
<h1><?= $type === 'zen' ? 'Zen' : 'Level/Resets' ?> Ranking</h1>
<p>Last updated: <?= htmlspecialchars($lastUpdate) ?></p>
<table border="1" cellpadding="6">
  <tr><th>#</th><th>Character</th><th>Class</th><th>Level</th><th>Resets</th><th>Guild</th></tr>
<?php
$rank = $offset + 1;
while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)):
    $cls = $classNames[$row['CharClass']] ?? 'Unknown';
?>
  <tr>
    <td><?= $rank++ ?></td>
    <td><?= htmlspecialchars($row['CharacterName']) ?></td>
    <td><?= htmlspecialchars($cls) ?></td>
    <td><?= (int)$row['CharLevel'] ?></td>
    <td><?= (int)$row['CharResets'] ?></td>
    <td><?= htmlspecialchars($row['GuildName'] ?? '-') ?></td>
  </tr>
<?php endwhile; ?>
</table>
</body>
</html>

Step 7: Troubleshooting Common Issues

Ranking is not updating

• Check SQL Server Agent service → services.msc → SQL Server Agent (MSSQLSERVER)
• Run manually in SSMS: EXEC MuOnline..sp_UpdateRankingCache
• Review errors at: SSMS → SQL Server Agent → Error Logs

PHP page returns a blank screen

// Add to the top of the file for debugging (remove in production)
ini_set('display_errors', 1);
error_reporting(E_ALL);

Verify the sqlsrv extension is enabled in php.ini:

; In XAMPP's php.ini (C:\xampp\php\php.ini)
extension=php_sqlsrv_74_ts_x64.dll
extension=php_pdo_sqlsrv_74_ts_x64.dll

"Cannot open database" connection error

Run in SSMS:

-- Verify the web user has access
USE MuOnline;
GRANT SELECT ON RankingCache TO mu_web_user;
GRANT EXECUTE ON sp_UpdateRankingCache TO mu_web_user;

Possible Expansions

With the base structure working, you can add:

• Guild ranking: aggregate points by summing member resets via GROUP BY G_Name
• PvP ranking: order by PkCount DESC with an active accounts filter
• Position history: add a RankingHistory table and insert daily snapshots via a separate Job scheduled at midnight
• JSON API: return data as header('Content-Type: application/json'); echo json_encode($rows); to integrate with Discord bots or mobile apps

This system scales well for small to medium servers. For servers with more than 1,000 simultaneous players, consider migrating the cache layer to Redis or memcached and querying SQL Server only during scheduled updates.

Perguntas frequentes