How to Implement Referral System on MU Server

System Overview

A referral system rewards players who bring new users to the server. The implementation covers three layers: database (tracking and rewards), website (link generation and dashboard), and optionally GameServer integration (granting items or zen via SQL).

This tutorial covers the full implementation using SQL Server and PHP, with ASP.NET notes where applicable.

Step 1: Create Control Tables in SQL Server

Open SQL Server Management Studio and run the scripts below against your MuOnline database (or your accounts database name).

-- Main referral tracking table
USE MuOnline;
GO

CREATE TABLE ReferralCodes (
    CodeID       INT IDENTITY(1,1) PRIMARY KEY,
    AccountID    VARCHAR(10)  NOT NULL,
    Code         VARCHAR(20)  NOT NULL UNIQUE,
    CreatedAt    DATETIME     NOT NULL DEFAULT GETDATE(),
    TotalUses    INT          NOT NULL DEFAULT 0,
    MaxUses      INT          NOT NULL DEFAULT 20,
    IsActive     BIT          NOT NULL DEFAULT 1
);

-- Referral usage records
CREATE TABLE ReferralUses (
    UseID           INT IDENTITY(1,1) PRIMARY KEY,
    ReferrerAccount VARCHAR(10)  NOT NULL,
    ReferredAccount VARCHAR(10)  NOT NULL,
    UsedAt          DATETIME     NOT NULL DEFAULT GETDATE(),
    ReferredIP      VARCHAR(45)  NOT NULL,
    RewardGranted   BIT          NOT NULL DEFAULT 0,
    RewardGrantedAt DATETIME     NULL,
    QualifyingLevel INT          NOT NULL DEFAULT 0
);

-- Reward audit log
CREATE TABLE ReferralRewards (
    RewardID        INT IDENTITY(1,1) PRIMARY KEY,
    AccountID       VARCHAR(10)  NOT NULL,
    RewardType      VARCHAR(20)  NOT NULL, -- 'ZEN', 'WCOIN', 'ITEM'
    RewardValue     INT          NOT NULL,
    GrantedAt       DATETIME     NOT NULL DEFAULT GETDATE(),
    Notes           VARCHAR(200) NULL
);
GO

Step 2: Create Indexes and Stored Procedures

Add indexes for performance and create the validation stored procedures.

-- Indexes for frequent queries
CREATE INDEX IX_ReferralCodes_AccountID ON ReferralCodes(AccountID);
CREATE INDEX IX_ReferralUses_ReferredAccount ON ReferralUses(ReferredAccount);
CREATE INDEX IX_ReferralUses_ReferrerAccount ON ReferralUses(ReferrerAccount);
GO

-- Procedure: Generate a unique code for an account
CREATE PROCEDURE sp_ReferralGenerateCode
    @AccountID VARCHAR(10)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Code VARCHAR(20);
    DECLARE @Exists INT = 1;

    -- Return existing active code if present
    IF EXISTS (SELECT 1 FROM ReferralCodes WHERE AccountID = @AccountID AND IsActive = 1)
    BEGIN
        SELECT Code FROM ReferralCodes WHERE AccountID = @AccountID AND IsActive = 1;
        RETURN;
    END

    -- Generate unique code
    WHILE @Exists > 0
    BEGIN
        SET @Code = UPPER(LEFT(@AccountID, 4)) + LEFT(NEWID(), 8);
        SELECT @Exists = COUNT(*) FROM ReferralCodes WHERE Code = @Code;
    END

    INSERT INTO ReferralCodes (AccountID, Code) VALUES (@AccountID, @Code);
    SELECT @Code AS Code;
END
GO

-- Procedure: Register code usage during account registration
CREATE PROCEDURE sp_ReferralRegisterUse
    @Code           VARCHAR(20),
    @ReferredAccount VARCHAR(10),
    @ReferredIP     VARCHAR(45)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ReferrerAccount VARCHAR(10);
    DECLARE @TotalUses INT;
    DECLARE @MaxUses INT;

    -- Fetch code data
    SELECT @ReferrerAccount = AccountID,
           @TotalUses = TotalUses,
           @MaxUses = MaxUses
    FROM ReferralCodes
    WHERE Code = @Code AND IsActive = 1;

    -- Validations
    IF @ReferrerAccount IS NULL
        RETURN -1; -- Invalid code

    IF @ReferrerAccount = @ReferredAccount
        RETURN -2; -- Self-referral

    IF @TotalUses >= @MaxUses
        RETURN -3; -- Limit reached

    IF EXISTS (SELECT 1 FROM ReferralUses WHERE ReferredAccount = @ReferredAccount)
        RETURN -4; -- Account already referred by someone

    -- Anti-abuse: same IP already used this referrer's code
    IF EXISTS (
        SELECT 1 FROM ReferralUses ru
        INNER JOIN ReferralCodes rc ON ru.ReferrerAccount = rc.AccountID
        WHERE rc.AccountID = @ReferrerAccount AND ru.ReferredIP = @ReferredIP
    )
        RETURN -5; -- IP blocked

    -- Record the referral
    INSERT INTO ReferralUses (ReferrerAccount, ReferredAccount, ReferredIP)
    VALUES (@ReferrerAccount, @ReferredAccount, @ReferredIP);

    UPDATE ReferralCodes SET TotalUses = TotalUses + 1 WHERE Code = @Code;

    RETURN 1; -- Success
END
GO

Step 3: Reward Grant Procedure

-- Procedure: Grant reward when referred player reaches minimum level
CREATE PROCEDURE sp_ReferralGrantReward
    @ReferredAccount VARCHAR(10),
    @CurrentLevel    INT,
    @MinLevel        INT = 200
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ReferrerAccount VARCHAR(10);
    DECLARE @UseID INT;

    -- Check for pending reward
    SELECT @UseID = UseID, @ReferrerAccount = ReferrerAccount
    FROM ReferralUses
    WHERE ReferredAccount = @ReferredAccount
      AND RewardGranted = 0
      AND @CurrentLevel >= @MinLevel;

    IF @UseID IS NULL RETURN 0;

    -- Grant WCoin to referrer (adjust table name to match your server)
    UPDATE MEMB_INFO
    SET WCoinC = WCoinC + 500
    WHERE memb___id = @ReferrerAccount;

    -- Optional bonus to the referred player
    UPDATE MEMB_INFO
    SET WCoinC = WCoinC + 100
    WHERE memb___id = @ReferredAccount;

    -- Mark reward as granted
    UPDATE ReferralUses
    SET RewardGranted = 1,
        RewardGrantedAt = GETDATE(),
        QualifyingLevel = @CurrentLevel
    WHERE UseID = @UseID;

    -- Audit log
    INSERT INTO ReferralRewards (AccountID, RewardType, RewardValue, Notes)
    VALUES (@ReferrerAccount, 'WCOIN', 500, 'Referral: ' + @ReferredAccount);

    RETURN 1;
END
GO

Step 4: Website Integration (PHP)

Create referral.php in your site's root directory (e.g., C:/xampp/htdocs/myserver/referral.php).

<?php
// referral.php - ViciadosMU Referral System
// SQL Server connection via PDO (adjust credentials)
$dsn  = "sqlsrv:Server=localhost;Database=MuOnline";
$user = "sa";
$pass = "YOUR_PASSWORD";
$pdo  = new PDO($dsn, $user, $pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

session_start();
$account = $_SESSION['account'] ?? null;
if (!$account) { header("Location: login.php"); exit; }

// Generate or retrieve the player's code
$stmt = $pdo->prepare("EXEC sp_ReferralGenerateCode ?");
$stmt->execute([$account]);
$row    = $stmt->fetch(PDO::FETCH_ASSOC);
$myCode = $row['Code'] ?? '';

$baseURL = "https://yourserver.com/register.php?ref=";
$myLink  = $baseURL . $myCode;

// Fetch statistics
$stmt2 = $pdo->prepare("
    SELECT COUNT(*) AS Total,
           SUM(CASE WHEN RewardGranted = 1 THEN 1 ELSE 0 END) AS Rewarded
    FROM ReferralUses WHERE ReferrerAccount = ?
");
$stmt2->execute([$account]);
$stats = $stmt2->fetch(PDO::FETCH_ASSOC);
?>

<h2>Your Referral Link</h2>
<input type="text" value="<?= htmlspecialchars($myLink) ?>" readonly id="refLink">
<button onclick="navigator.clipboard.writeText(document.getElementById('refLink').value)">
    Copy Link
</button>

<h3>Statistics</h3>
<p>Total referred: <?= (int)$stats['Total'] ?></p>
<p>Rewards received: <?= (int)$stats['Rewarded'] ?></p>

Step 5: Process Referral at Registration

In your registration file (register.php), call the stored procedure immediately after the new account is created:

// After successful account INSERT:
$refCode  = $_GET['ref'] ?? $_POST['ref'] ?? '';
$refCode  = preg_replace('/[^A-Z0-9]/', '', strtoupper(trim($refCode)));
$clientIP = $_SERVER['REMOTE_ADDR'];

if (!empty($refCode) && strlen($refCode) <= 20) {
    $stmt = $pdo->prepare(
        "DECLARE @ret INT; EXEC @ret = sp_ReferralRegisterUse ?, ?, ?; SELECT @ret AS Result"
    );
    $stmt->execute([$refCode, $newAccount, $clientIP]);
    $result = $stmt->fetchColumn();
    // $result: 1=success, -1=invalid code, -2=self-referral,
    //          -3=limit reached, -4=already referred, -5=IP blocked
}

Step 6: Automated Level-Check SQL Agent Job

Create a SQL Server Agent Job to periodically check referred players who have reached the minimum level.

-- Script for SQL Server Agent Job (run every 30 minutes)
USE MuOnline;
GO

DECLARE @Account VARCHAR(10);
DECLARE @Level INT;

DECLARE cur CURSOR FOR
    SELECT ru.ReferredAccount, MAX(c.cLevel)
    FROM ReferralUses ru
    INNER JOIN MuOnline..Character c ON c.AccountID = ru.ReferredAccount
    WHERE ru.RewardGranted = 0
    GROUP BY ru.ReferredAccount;

OPEN cur;
FETCH NEXT FROM cur INTO @Account, @Level;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_ReferralGrantReward @Account, @Level, 200;
    FETCH NEXT FROM cur INTO @Account, @Level;
END

CLOSE cur;
DEALLOCATE cur;

Step 7: Admin Overview Query

Add the following query to your admin panel for a referral overview:

-- Admin referral report
SELECT
    rc.AccountID                          AS Referrer,
    rc.Code                               AS Code,
    rc.TotalUses                          AS TotalReferred,
    SUM(CASE WHEN ru.RewardGranted = 1 THEN 1 ELSE 0 END) AS Rewarded,
    SUM(CASE WHEN ru.RewardGranted = 0 THEN 1 ELSE 0 END) AS Pending,
    rc.IsActive                           AS Active
FROM ReferralCodes rc
LEFT JOIN ReferralUses ru ON rc.AccountID = ru.ReferrerAccount
GROUP BY rc.AccountID, rc.Code, rc.TotalUses, rc.IsActive
ORDER BY rc.TotalUses DESC;

Troubleshooting

Reward not being granted: Verify that the AccountID field in the Character table exactly matches MEMB_INFO.memb___id. Case differences cause silent failures in SQL Server with case-sensitive collation.

Duplicate referral code from NEWID(): The WHILE loop in the stored procedure prevents this, but on servers with high concurrent registration volume, add a UNIQUE index on the Code column (already included in Step 1).

IP check blocking legitimate referrals: On servers behind NAT or CGNAT (multiple players sharing the same public IP), consider disabling or softening the IP check, or implementing additional fingerprinting via the launcher executable.

Perguntas frequentes