[Sajmooooon]

Hi, I'm looking for plugin that will save current player list (player name, steamid, score, time on server) to mysql and update list on player leave/join to server.

Thanks for help.

[Bacardi]

*edit 17.8.2022 Plugin topic MySQL playerlist

There maybe is plugin what do that task.
But I did not search it.


I made example/test plugin. I have not test it with lot of players.


First, in MySQL, need create table with rows manually. Plugin will not do those.

- Select or create database where you want new table
- After you selected database, go to SQL query tab page (I use PHPMyAdmin). We execute SQL queries.
- You see 3 steps of query codes in plugin source code, at top in /* comment */ section. Try use those in PHPMyAdmin sql tab page

-- 1) drop table if it exist
-- 2) create table with default values
-- 3) use for loop to add 65 rows with default values

Now you should have table called "playerlist" with 65 rows empty data (expect increment clientid value)


Add plugin configure ("playerlist") in SM database.cfg

"Databases"
{
    "driver_default"        "mysql"
    
    // When specifying "host", you may use an IP address, a hostname, or a socket file path
    
    "default"
    {
        "driver"            "default"
        "host"                "localhost"
        "database"            "sourcemod"
        "user"                "root"
        "pass"                ""
        //"timeout"            "0"
        //"port"            "0"
    }
    
    "playerlist"
    {
        "driver"            "default"
        "host"                "localhost"
        "database"            "database_test"
        "user"                "root"
        "pass"                ""
        //"timeout"            "0"
        //"port"            "0"
    }
    
    "storage-local"
    {
        "driver"            "sqlite"
        "database"            "sourcemod-local"
    }

    "clientprefs"
    {
        "driver"            "sqlite"
        "host"                "localhost"
        "database"            "clientprefs-sqlite"
        "user"                "root"
        "pass"                ""
        //"timeout"            "0"
        //"port"            "0"
    }
}

plugin new version

PHP Code:

/*    (XAMPP) MySQL PHPMyAdmin sql query steps:1)DROP TABLE IF EXISTS `playerlist`;2)CREATE TABLE `playerlist` ( `clientid` INT NOT NULL AUTO_INCREMENT ,`name` VARCHAR(128) NULL DEFAULT NULL ,`steamid` VARCHAR(128) NULL DEFAULT NULL ,`score` INT NOT NULL DEFAULT '0' ,`time` VARCHAR(15) NULL DEFAULT NULL , PRIMARY KEY (`clientid`));3)DELIMITER $$DROP PROCEDURE IF EXISTS test$$CREATE PROCEDURE test()BEGIN DECLARE count INT DEFAULT 1; WHILE count <= 65 DO   INSERT INTO `playerlist` () VALUES ();   SET count = count + 1; END WHILE;END$$DELIMITER ;CALL test();*/#include <sdktools>#include <cstrike>enum struct PlayerInfo{    char name[MAX_NAME_LENGTH];    char steamid[MAX_NAME_LENGTH];    int score;    char time[15];    bool UpdateThisIndex; // Keep track which client indexs we update (send less queries)    void Clear()    {        if(StrEqual("NULL", this.steamid, true))        {            return;        }        this.UpdateThisIndex = true;        Format(this.name, sizeof(this.name), "NULL");        Format(this.steamid, sizeof(this.steamid), "NULL");        this.score = 0;        Format(this.time, sizeof(this.time), "NULL");    }    void Update(int client)    {        this.UpdateThisIndex = true;        Format(this.name, sizeof(this.name), "%N", client);        GetClientAuthId(client, AuthId_Engine, this.steamid, sizeof(this.steamid));        this.score = CS_GetClientContributionScore(client);        int a = RoundToNearest(GetClientTime(client));        Format(this.time, sizeof(this.time), "%im %is", a / 60, a % 60);    }}PlayerInfo playerinfo[MAXPLAYERS+1];Database DB;Transaction Txn;public void OnPluginStart(){    CreateTimer(10.0, timerrepeat, _, TIMER_REPEAT);    Database.Connect(ConnectDB, "playerlist");}public void ConnectDB(Database db, const char[] error, any data){    if(db == null) SetFailState("Couldn't connect to database (databases.cfg configure 'playerlist')");    DB = db;}enum {    ONCE = -2,    OFF,    TURNOFF,    ON}public Action timerrepeat(Handle timer){    static int IsHumanConnected = ONCE;    for(int i = 1; i < sizeof(playerinfo); i++)    {        if(i > MaxClients || !IsClientConnected(i) || IsClientInGame(i) && IsFakeClient(i))        {            playerinfo[i].Clear();            continue;        }        if(!IsClientInGame(i)) // player connecting        {            playerinfo[i].UpdateThisIndex = true;            playerinfo[i].score = 0;            IsHumanConnected = ON; // keep updating            continue;        }        playerinfo[i].Update(i);        IsHumanConnected = ON; // keep updating    }    if(IsHumanConnected == ON)    {        IsHumanConnected = TURNOFF; // Try in next cycle, stop update if no humans        UpdateTable();    }    else if(IsHumanConnected == TURNOFF || IsHumanConnected == ONCE)    {        IsHumanConnected = OFF;        // Do last update        UpdateTable(true);    }    return Plugin_Continue;}void UpdateTable(bool SetAllRowsToDEFAULT = false){    //PrintToServer("UpdateTable()");    if(DB == null)        return;    Txn = new Transaction();    char query[1024];    // Set all rows to DEFAULT values    if(SetAllRowsToDEFAULT)    {        // clear array        for(int x = 1; x <= MaxClients; x++)        {            playerinfo[x].Clear();            playerinfo[x].UpdateThisIndex = false;        }        SQL_FormatQuery(DB, query, sizeof(query),        "UPDATE playerlist \        SET name=DEFAULT, \        steamid=DEFAULT, \        score=DEFAULT, \        time=DEFAULT");        //PrintToServer("%s", query);        Txn.AddQuery(query);        SQL_ExecuteTransaction(DB, Txn, onSuccess, onError);        return;    }    bool HasQuery = false;    for(int x = 1; x <= MaxClients; x++)    {        if(!playerinfo[x].UpdateThisIndex)        {            continue;        }        playerinfo[x].UpdateThisIndex = false;        // To able to use NULL or DEFAULT value in query and get it to work in SQL,        // I got problems with single 'quotes' if I formatted those in strings before hand.        //    - quick solution is separate those two queries, at this point. Use single quotes in one of those.        if(!StrEqual(playerinfo[x].steamid, "NULL", true))        {            SQL_FormatQuery(DB, query, sizeof(query),            "UPDATE playerlist \            SET name='%s', \            steamid='%s', \            score=%i, \            time='%s' \            WHERE clientid=%i;",            playerinfo[x].name,            playerinfo[x].steamid,            playerinfo[x].score,            playerinfo[x].time,            x);        } else {            SQL_FormatQuery(DB, query, sizeof(query),            "UPDATE playerlist \            SET name=DEFAULT, \            steamid=DEFAULT, \            score=DEFAULT, \            time=DEFAULT \            WHERE clientid=%i;",            x);        }        //PrintToServer("%s", query);        HasQuery = true;        Txn.AddQuery(query, x);    }    if(!HasQuery)    {        delete Txn;        return;    }    SQL_ExecuteTransaction(DB, Txn, onSuccess, onError);}public void onSuccess(Database db, any data, int numQueries, DBResultSet[] results, any[] queryData){    //PrintToServer("- %i onSuccess", numQueries);}public void onError(Database db, any data, int numQueries, const char[] error, int failIndex, any[] queryData){    //PrintToServer("- %i onError %i %s", numQueries, failIndex, error);} 


- NEW! This version send less amount of queries. It update necessary slots only.

plugin old version

PHP Code:

/*    (XAMPP) MySQL PHPMyAdmin sql query steps:1)DROP TABLE IF EXISTS `playerlist`;2)CREATE TABLE `playerlist` ( `clientid` INT NOT NULL AUTO_INCREMENT ,`name` VARCHAR(128) NULL DEFAULT NULL ,`steamid` VARCHAR(128) NULL DEFAULT NULL ,`score` INT NOT NULL DEFAULT '0' ,`time` VARCHAR(15) NULL DEFAULT NULL , PRIMARY KEY (`clientid`));3)DELIMITER $$DROP PROCEDURE IF EXISTS test$$CREATE PROCEDURE test()BEGIN DECLARE count INT DEFAULT 1; WHILE count <= 65 DO   INSERT INTO `playerlist` () VALUES ();   SET count = count + 1; END WHILE;END$$DELIMITER ;CALL test();*/#include <sdktools>#include <cstrike>enum struct PlayerInfo{    char name[MAX_NAME_LENGTH];    char steamid[MAX_NAME_LENGTH];    int score;    char time[15];    void Clear()    {        Format(this.name, sizeof(this.name), "NULL");        Format(this.steamid, sizeof(this.steamid), "NULL");        this.score = 0;        Format(this.time, sizeof(this.time), "NULL");    }    void Update(int client)    {        Format(this.name, sizeof(this.name), "%N", client);        GetClientAuthId(client, AuthId_Engine, this.steamid, sizeof(this.steamid));        Format(this.steamid, sizeof(this.steamid), "%s", this.steamid);        this.score = CS_GetClientContributionScore(client);        int a = RoundToNearest(GetClientTime(client));        Format(this.time, sizeof(this.time), "%im %is", a / 60, a % 60);    }}PlayerInfo playerinfo[MAXPLAYERS+1];Database DB;Transaction Txn;public void OnPluginStart(){    CreateTimer(10.0, timerrepeat, _, TIMER_REPEAT);    Database.Connect(ConnectDB, "playerlist");}public void ConnectDB(Database db, const char[] error, any data){    if(db == null) SetFailState("Couldn't connect to database (databases.cfg configure 'playerlist')");    DB = db;}enum {    ONCE = -2,    OFF,    TURNOFF,    ON}public Action timerrepeat(Handle timer){    static int IsHumanConnected = ONCE;    for(int i = 1; i < sizeof(playerinfo); i++)    {        if(i > MaxClients || !IsClientConnected(i) || IsClientInGame(i) && IsFakeClient(i))        {            playerinfo[i].Clear();            continue;        }        if(!IsClientInGame(i)) // player connecting        {            playerinfo[i].score = 0;            IsHumanConnected = ON; // keep updating            continue;        }        playerinfo[i].Update(i);        IsHumanConnected = ON; // keep updating    }    if(IsHumanConnected == ONCE || IsHumanConnected == ON)    {        IsHumanConnected = TURNOFF; // Try in next cycle, stop update if no humans        UpdateTable();    }    else if(IsHumanConnected == TURNOFF)    {        IsHumanConnected = OFF;        // Do last update        UpdateTable();    }    return Plugin_Continue;}void UpdateTable(){    //PrintToServer("UpdateTable()");    if(DB == null)        return;    Txn = new Transaction();    char query[1024];    for(int x = 1; x <= MaxClients; x++)    {        // To able to use NULL value in query and get it to work in SQL,        // I got problems with single 'quotes' if I formatted those in strings before hand.        //    - quick solution is separate those two queries, at this point. Use single quotes in one of those.        if(!StrEqual(playerinfo[x].steamid, "NULL", true))        {            SQL_FormatQuery(DB, query, sizeof(query),            "UPDATE playerlist \            SET name='%s', \            steamid='%s', \            score=%i, \            time='%s' \            WHERE clientid=%i;",            playerinfo[x].name,            playerinfo[x].steamid,            playerinfo[x].score,            playerinfo[x].time,            x);        } else {            SQL_FormatQuery(DB, query, sizeof(query),            "UPDATE playerlist \            SET name=%s, \            steamid=%s, \            score=%i, \            time=%s \            WHERE clientid=%i;",            playerinfo[x].name,            playerinfo[x].steamid,            playerinfo[x].score,            playerinfo[x].time,            x);        }        //PrintToServer("%s", query);        Txn.AddQuery(query, x);    }    SQL_ExecuteTransaction(DB, Txn, onSuccess, onError);}public void onSuccess(Database db, any data, int numQueries, DBResultSet[] results, any[] queryData){    //PrintToServer("- %i onSuccess", numQueries);}public void onError(Database db, any data, int numQueries, const char[] error, int failIndex, any[] queryData){    //PrintToServer("- %i onError %i %s", numQueries, failIndex, error);} 


- Plugin list only human players. It updates data of each 65 rows.
- Plugin update table every 10 seconds, and stop updating when there is no human players in server.

- You can query active players, example using NULL value:

SELECT * FROM `playerlist` WHERE steamid IS NOT NULL;

- name, steamid, time are strings
clientid, score is int

- If table is some reason over 65 rows, you can do those 3 steps again to fix it.