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.
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 createdatabase 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
Code:
"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:
Code:
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.
Hybrid Mode
