[mug1wara]

Sup, recently got into sql, but there's one thing here I don't understand yet.

Why does the stats get reset when server restart? It works fine on mapchanges...

PHP Code:

#include <sourcemod>

#pragma semicolon 1
#pragma newdecls required

Database g_hDatabase = null;

int g_iKills[MAXPLAYERS + 1];
int g_iDeaths[MAXPLAYERS + 1];
int g_iAssists[MAXPLAYERS + 1];
int g_iHeadshots[MAXPLAYERS + 1];

char g_sSteamid[MAXPLAYERS + 1][32];

public void OnPluginStart()
{
    HookEvent("player_death", Event_Death);
    
    Database.Connect(SQL_Connection, "test");
}

public Action Event_Death(Event hEvent, const char[] sName, bool bDontBroadcast)
{
    int iAttacker = GetClientOfUserId(hEvent.GetInt("attacker"));
    int iVictim = GetClientOfUserId(hEvent.GetInt("userid"));
    
    if (iAttacker != iVictim)
    {
        int iAssister = GetClientOfUserId(hEvent.GetInt("assister"));
        
        g_iKills[iAttacker]++;
        g_iDeaths[iVictim]++;
        g_iAssists[iAssister]++;
        
        if (hEvent.GetBool("headshot"))
            g_iHeadshots[iAttacker]++;
        
        PrintToChat(iAttacker, "You've got %d kills, %d deaths, %d assists and %d headshots.", g_iKills[iAttacker], g_iDeaths[iAttacker], g_iAssists[iAttacker], g_iHeadshots[iAttacker]);
        
        SQL_Update(iAttacker);
        SQL_Update(iVictim);
        SQL_Update(iAssister);
    }
}

public void SQL_Connection(Database hDatabase, const char[] sError, any iData)
{
    if (hDatabase == null)
        SetFailState(sError);
    
    else
    {
        g_hDatabase = hDatabase;
        
        g_hDatabase.Query(SQL_Error, "CREATE TABLE IF NOT EXISTS `players` (`steam_id` VARCHAR(32) NOT NULL PRIMARY KEY, `kills` int(20) NOT NULL, `deaths` int(20) NOT NULL, `assists` int(20) NOT NULL, `headshots` int(20) NOT NULL)");
    }
}

public void SQL_Error(Database hDatabase, DBResultSet hResults, const char[] sError, any iData)
{
    if (hResults == null)
        ThrowError(sError);
}

public void OnClientAuthorized(int iClient)
{
    if (IsValidClient(iClient))
    {
        GetClientAuthId(iClient, AuthId_Steam2, g_sSteamid[iClient], sizeof(g_sSteamid[]));
        
        char [] sQuery = new char[256];
        Format(sQuery, 256, "SELECT * FROM `players` WHERE `steam_id` = \"%s\"", g_sSteamid[iClient]);
        
        g_hDatabase.Query(SQL_Authorization, sQuery, GetClientUserId(iClient));
    }
}

public void SQL_Authorization(Database hDatabase, DBResultSet hResults, const char[] sError, any iData)
{
    if (hResults == null)
        ThrowError(sError);
    
    int iClient = GetClientOfUserId(iData);
    
    if (IsValidClient(iClient))
    {
        if (hResults.RowCount == 1)
        {
            hResults.FetchRow();
            
            g_iKills[iClient] = hResults.FetchInt(2);
            g_iDeaths[iClient] = hResults.FetchInt(3);
            g_iAssists[iClient] = hResults.FetchInt(4);
            g_iHeadshots[iClient] = hResults.FetchInt(5);
        }
        
        else
        {
            char [] sQuery = new char[256];
            Format(sQuery, 256, "INSERT INTO `players` (`steam_id`, `kills`, `deaths`, `assists`, `headshots`) VALUES (\"%s\", '%d', '%d', '%d', '%d')", g_sSteamid[iClient], g_iKills[iClient], g_iDeaths[iClient], g_iAssists[iClient], g_iHeadshots[iClient]);
            
            g_hDatabase.Query(SQL_Error, sQuery);
        }
    }
}

public void SQL_Update(int iClient)
{
    if (IsValidClient(iClient))
    {
        char [] sQuery = new char[256];
        Format(sQuery, 256, "UPDATE `players` SET `kills` = '%d', `deaths` = '%d', `assists` = '%d', `headshots` = '%d' WHERE `steam_id` = \"%s\"", g_iKills[iClient], g_iDeaths[iClient], g_iAssists[iClient], g_iHeadshots[iClient], g_sSteamid[iClient]);
        
        g_hDatabase.Query(SQL_Error, sQuery);
    }
}

stock bool IsValidClient(int iClient)
{
    if (!(0 < iClient <= MaxClients) || !IsClientInGame(iClient) || IsFakeClient(iClient))
        return false;
    
    return true;
} 


[Ilusion9]

PHP Code:


        g_iKills[iAttacker]++; 
        g_iDeaths[iVictim]++; 
        g_iAssists[iAssister]++; 


You should use new ints for update. When player connects initialise the ints with 0.

PHP Code:


        Format(sQuery, 256, "UPDATE players SET kills = kills + %d where etc.", g_iKills[iClient]); 


Or you should do some tricks in player_death event:

PHP Code:


        if (hEvent.GetBool("headshot")) 
                Format(sQuery, 256, "UPDATE players SET headshots = headshots + 1, kills = kills + 1 etc"); // update hs and kills
        else
                Format(sQuery, 256, "UPDATE players SET kills = kills + 1 etc"); // only kills 


[Rachnus]

Try changing OnClientAuthorized to OnClientPostAdminCheck, the client is only in game during map change in OnClientAuthorized (Which you are checking in IsValidClient func)

[Ilusion9]

Originally Posted by Rachnus Try changing OnClientAuthorized to OnClientPostAdminCheck, the client is only in game during map change in OnClientAuthorized (Which you are checking in IsValidClient func)

It's irrelevant. He's fetching the data from database and changing the data during game. It's not safe. The old data should not be used in updating the database. You have to update the database with only the new data collected from player_death event:

My code example for stats:

PHP Code:


public Action Event_PlayerDeath(Event event, const char[] name, bool dontBroadcast)
{
    int attacker = GetClientOfUserId(event.GetInt("attacker"));
    int client = GetClientOfUserId(event.GetInt("userid"));
        
    if(attacker != 0)
    {
        if(attacker != client) 
        {
            if(event.GetBool("headshot"))
            {
                IntHeadshots[attacker]++;
            }
                
            IntKills[attacker]++;
        }
    }

    IntDeaths[client]++;
}

public void OnClientDisconnect(int Client)
{    
    if(!IsFakeClient(Client))
    {
        int Steam = GetSteamAccountID(Client);

        if(Steam != 0)
        {
            char Query[200];    
            FormatEx(Query, sizeof(Query), "INSERT INTO rank_table VALUES (%d, %d, %d, %d) ON DUPLICATE KEY UPDATE kills = kills + %d, deaths = deaths + %d, hs = hs + %d;", Steam, IntKills[Client], IntDeaths[Client], IntHeadshots[Client], IntKills[Client], IntDeaths[Client], IntHeadshots[Client]);
            db.Query(Handle_FastQuery, Query);
        }
    }
}