[SpirT]

So,

What I am trying to do is really simple

Store the name, steamid and IP on the database when he joins the server. But, when connecting I want to the plugin to check if the player is already registered. So, we get the IP that is stored on the database for the player's steamid. If the database player's IP does not match to the actual player's IP, I want it to write a new line with new data, as such the name, steamID and iP.

The problem is that it is not storing any data.

PHP Code:

#pragma semicolon 1

#define DEBUG

#define PLUGIN_AUTHOR "SpirT"
#define PLUGIN_VERSION "1.0"

#include <sourcemod>
#include <sdktools>

Database DB = null;
char Error[256];

#pragma newdecls required

public Plugin myinfo = 
{
    name = "[SpirT - Database Query]",
    author = PLUGIN_AUTHOR,
    description = "",
    version = PLUGIN_VERSION,
    url = ""
};

public void OnPluginStart()
{
    DB = SQL_Connect("spirtdb", true, Error, sizeof(Error));
    
    if(DB == null)
    {
        PrintToServer("[DB] Cannot connect to MySQL Server: %s", Error);
        CloseHandle(DB);
    }
    else
    {
        PrintToServer("[DB] Connection Successful");
        char squery[256];
        Format(squery, sizeof(squery), "CREATE TABLE IF NOT EXISTS players (name varchar(32) NOT NULL, steamid varchar(32) NOT NULL, ip varchar(64) NOT NULL)");
        if(!SQL_FastQuery(DB, squery))
        {
            char terror[256];
            SQL_GetError(DB, terror, sizeof(terror));
            PrintToServer("[DB] Could not create db tables. Error: %s", terror);
        }
        else
        {
            PrintToServer("[DB] Tables Created if they don't exist.");
        }
    }
}

public void OnClientPutInServer(int client)
{
    char iname[32];
    GetClientName(client, iname, sizeof(iname));
    
    char iid[32];
    GetClientAuthId(client, AuthId_Steam2, iid, sizeof(iid));
    
    char iip[64];
    GetClientIP(client, iip, sizeof(iip), true);
    
    char squery[256];
    Format(squery, sizeof(squery), "SELECT ip FROM players WHERE steamid = '%s'", iid);
    DBResultSet select = SQL_Query(DB, squery);
    
    if(select == null)
    {
        PrintToServer("[DB] Could not get data from the database.");
        return;
    }
    
    char dbIP[64];
    SQL_FetchString(DB, 0, dbIP, sizeof(dbIP));
    
    if(StrEqual(dbIP, iip))
    {
        PrintToServer("[DB] Player is already saved on the database.");
        return;
    }
    
    char newquery[256];
    Format(newquery, sizeof(newquery), "INSERT INTO players (name, steamid, ip) VALUES ('%s', '%s', '%s')", iname, iid, iip);
    DBResultSet query2 = SQL_Query(DB, newquery);
    if(query2 == null)
    {
        PrintToServer("[DB] Could not save new player data.");
        return;
    }
    
    PrintToServer("[DB] New player data was save in the database with success.");
    return;
} 


Any solution for the plugin to fix my issue?

OBS: That plugin was storing the player data EVERYTIME he joined the server, so I tried to update it to a more NON-SPAMMING version. This "new update" on the source above is creating the tables but not storing data.

Best Regards,

SpirT.

[8guawong]

Your sql query is always inserting
You should update instead of insert if the player is already in the databse
And you should definetly use threaded query

[SpirT]

Originally Posted by 8guawong Your sql query is always inserting You should update instead of insert if the player is already in the databse And you should definetly use threaded query

Hey! Thanks for your reply. I did some changes and now it is storing the values but keeps storing everytime I join the server. Any idea to just store when he connects for the first time and when he changes the IP?

PHP Code:

#pragma semicolon 1

#define DEBUG

#define PLUGIN_AUTHOR "SpirT"
#define PLUGIN_VERSION "1.0"

#include <sourcemod>
#include <sdktools>

Database DB = null;
char Error[256];

#pragma newdecls required

public Plugin myinfo = 
{
    name = "[SpirT - Database Query]",
    author = PLUGIN_AUTHOR,
    description = "",
    version = PLUGIN_VERSION,
    url = ""
};

public void OnPluginStart()
{
    DB = SQL_Connect("spirtdb", true, Error, sizeof(Error));
    
    if(DB == null)
    {
        PrintToServer("[DB] Cannot connect to MySQL Server: %s", Error);
        CloseHandle(DB);
    }
    else
    {
        PrintToServer("[DB] Connection Successful");
        char squery[256];
        Format(squery, sizeof(squery), "CREATE TABLE IF NOT EXISTS players (name varchar(32) NOT NULL, steamid varchar(32) NOT NULL, ip varchar(64) NOT NULL)");
        if(!SQL_FastQuery(DB, squery))
        {
            char terror[256];
            SQL_GetError(DB, terror, sizeof(terror));
            PrintToServer("[DB] Could not create db tables. Error: %s", terror);
        }
        else
        {
            PrintToServer("[DB] Tables Created if they don't exist.");
        }
    }
}

public void OnClientPutInServer(int client)
{
    char iname[32];
    GetClientName(client, iname, sizeof(iname));
    
    char iid[32];
    GetClientAuthId(client, AuthId_Steam2, iid, sizeof(iid));
    
    char iip[64];
    GetClientIP(client, iip, sizeof(iip), true);
    
    char squery[256];
    Format(squery, sizeof(squery), "SELECT ip FROM players WHERE steamid = '%s'", iid);
    DBResultSet select = SQL_Query(DB, squery);
    
    if(select == null)
    {
        PrintToServer("[DB] Could not get data from the database.");
        return;
    }
    
    if(select.FetchRow())
    {
        char dbip[64];
        int ipColumn;
        select.FieldNameToNum("ip", ipColumn);
        SQL_FetchString(select, ipColumn, dbip, sizeof(dbip));
        
        if(StrEqual(dbip, iip))
        {
            PrintToServer("[DB] Player is already in the Database. Skipping...");
            return;
        }
        else
        {
            char StoreAgain[256];
            Format(StoreAgain, sizeof(StoreAgain), "INSERT INTO players (name, steamid, ip) VALUES ('%s', '%s', '%s')", iname, iid, iip);
            DBResultSet NewStore = SQL_Query(DB, StoreAgain);
            if(NewStore == null)
            {
                PrintToServer("[DB] Could not insert new data to the database.");
                return;
            }
            else
            {
                PrintToServer("[DB] New player data was stored with success on the database.");
                return;
            }
        }
    }
    else
    {
        char newquery[256];
        Format(newquery, sizeof(newquery), "INSERT INTO players (name, steamid, ip) VALUES ('%s', '%s', '%s')", iname, iid, iip);
        DBResultSet query2 = SQL_Query(DB, newquery);
        if(query2 == null)
        {
            PrintToServer("[DB] Could not save new player data.");
            return;
        }
        else
        {
            PrintToServer("[DB] New player data was save in the database with success.");
            return;
        }
    }
} 


Best Regards,

SpirT

[8guawong]

Originally Posted by SpirT Hey! Thanks for your reply. I did some changes and now it is storing the values but keeps storing everytime I join the server. Any idea to just store when he connects for the first time and when he changes the IP? PHP Code: #pragma semicolon 1 #define DEBUG #define PLUGIN_AUTHOR "SpirT" #define PLUGIN_VERSION "1.0" #include <sourcemod> #include <sdktools> Database DB = null; char Error[256]; #pragma newdecls required public Plugin myinfo =  {     name = "[SpirT - Database Query]",     author = PLUGIN_AUTHOR,     description = "",     version = PLUGIN_VERSION,     url = "" }; public void OnPluginStart() {     DB = SQL_Connect("spirtdb", true, Error, sizeof(Error));          if(DB == null)     {         PrintToServer("[DB] Cannot connect to MySQL Server: %s", Error);         CloseHandle(DB);     }     else     {         PrintToServer("[DB] Connection Successful");         char squery[256];         Format(squery, sizeof(squery), "CREATE TABLE IF NOT EXISTS players (name varchar(32) NOT NULL, steamid varchar(32) NOT NULL, ip varchar(64) NOT NULL)");         if(!SQL_FastQuery(DB, squery))         {             char terror[256];             SQL_GetError(DB, terror, sizeof(terror));             PrintToServer("[DB] Could not create db tables. Error: %s", terror);         }         else         {             PrintToServer("[DB] Tables Created if they don't exist.");         }     } } public void OnClientPutInServer(int client) {     char iname[32];     GetClientName(client, iname, sizeof(iname));          char iid[32];     GetClientAuthId(client, AuthId_Steam2, iid, sizeof(iid));          char iip[64];     GetClientIP(client, iip, sizeof(iip), true);          char squery[256];     Format(squery, sizeof(squery), "SELECT ip FROM players WHERE steamid = '%s'", iid);     DBResultSet select = SQL_Query(DB, squery);          if(select == null)     {         PrintToServer("[DB] Could not get data from the database.");         return;     }          if(select.FetchRow())     {         char dbip[64];         int ipColumn;         select.FieldNameToNum("ip", ipColumn);         SQL_FetchString(select, ipColumn, dbip, sizeof(dbip));                  if(StrEqual(dbip, iip))         {             PrintToServer("[DB] Player is already in the Database. Skipping...");             return;         }         else         {             char StoreAgain[256];             Format(StoreAgain, sizeof(StoreAgain), "INSERT INTO players (name, steamid, ip) VALUES ('%s', '%s', '%s')", iname, iid, iip);             DBResultSet NewStore = SQL_Query(DB, StoreAgain);             if(NewStore == null)             {                 PrintToServer("[DB] Could not insert new data to the database.");                 return;             }             else             {                 PrintToServer("[DB] New player data was stored with success on the database.");                 return;             }         }     }     else     {         char newquery[256];         Format(newquery, sizeof(newquery), "INSERT INTO players (name, steamid, ip) VALUES ('%s', '%s', '%s')", iname, iid, iip);         DBResultSet query2 = SQL_Query(DB, newquery);         if(query2 == null)         {             PrintToServer("[DB] Could not save new player data.");             return;         }         else         {             PrintToServer("[DB] New player data was save in the database with success.");             return;         }     } }  Best Regards, SpirT

I already gave the answer above
Do an update query instead of insert query
https://www.w3schools.com/sql/sql_update.asp

[Bacardi]

- This create table, which set timestamp on every record.
- When query player IP from database, plugin fetch first row only. Because we order query results by timestamp DESC.
- Plugin compare player IP with database IP. If not match, it makes new record.

PHP Code:



Handle MyDataBase;

public void OnPluginStart()
{
    SQL_TConnect(connect_callback, "test");
}

public void connect_callback(Handle owner, Handle hndl, const char[] error, any data)
{
    MyDataBase = hndl;

    if(MyDataBase == null)
    {
        SetFailState("Error: %s", error);
    }

    SQL_TQuery(MyDataBase, createtable_callback, "CREATE TABLE IF NOT EXISTS `players` (`name` varchar(64),`steamid` varchar(64),`ip` varchar(64),`timestamp` timestamp DEFAULT CURRENT_TIMESTAMP)");
}


public void createtable_callback(Handle owner, Handle hndl, const char[] error, any data)
{
    if(hndl == null)
    {
        LogError("Create table Error: %s", error);
        return;
    }
}

public void OnClientPostAdminCheck(int client)
{
    char query[255], auth[30];
    
    if(!GetClientAuthId(client, AuthId_Engine, auth, sizeof(auth))) return;
    
    Format(query, sizeof(query), "SELECT `ip` FROM `players` WHERE `steamid`='%s' ORDER BY `players`.`timestamp` DESC", auth);
    SQL_TQuery(MyDataBase, query_callback, query, GetClientUserId(client));
}


public void query_callback(Handle owner, Handle hndl, const char[] error, any data)
{
    int client = GetClientOfUserId(data);

    if(client == 0 || !IsClientInGame(client)) return;

    char query[255], name[32], auth[30], ip[60];
    
    GetClientName(client, name, sizeof(name));
    char name2[64];
    SQL_EscapeString(MyDataBase, name, name2, sizeof(name2)); // Don't save raw names in database!!
    
    GetClientAuthId(client, AuthId_Engine, auth, sizeof(auth));
    
    GetClientIP(client, ip, sizeof(ip));


    // Query is not UPDATE, INSERT or DELETE
    if(hndl != null && SQL_HasResultSet(hndl))
    {
        if(SQL_GetRowCount(hndl))
        {
            if(!SQL_FetchRow(hndl)) return;

            char buffer[64];
            SQL_FetchString(hndl, 0, buffer, sizeof(buffer)); // fetch first row only (timestamp DESC)
            //PrintToServer("buffer %s", buffer);
            
            if(StrEqual(buffer, ip))
            {
                return;
            }
        }


        Format(query, sizeof(query), "INSERT INTO `players` (`name`,`steamid`,`ip`) VALUES ('%s', '%s','%s')", name2, auth, ip);
        SQL_TQuery(MyDataBase, save_callback, query);

    }

}

public void save_callback(Handle owner, Handle hndl, const char[] error, any data)
{
    PrintToServer("Done");
} 


old

https://www.w3schools.com/sql/sql_primarykey.asp

https://www.w3schools.com/sql/sql_unique.asp

If player SteamID and IP records are important, and you not want duplicate records, you want use either PRIMARY KEY or UNIQUE in table.

I'm hurry, so I give you quick bad example.

PHP Code:

Handle MyDataBase;



public void OnPluginStart()
{
    SQL_TConnect(connect_callback, "test");
}


public void connect_callback(Handle owner, Handle hndl, const char[] error, any data)
{
    MyDataBase = hndl;

    if(MyDataBase == null)
    {
        SetFailState("Error: %s", error);
    }


    SQL_TQuery(MyDataBase, createtable_callback, "CREATE TABLE IF NOT EXISTS players (name varchar(64), steamid varchar(64), ip varchar(64), PRIMARY KEY (steamid,ip))");
}





public void createtable_callback(Handle owner, Handle hndl, const char[] error, any data)
{
    if(hndl == null)
    {
        LogError("Create table Error: %s", error);
        return;
    }
}

public void OnClientPostAdminCheck(int client)
{
    char query[255], name[32], auth[30], ip[60];
    
    GetClientName(client, name, sizeof(name));
    char name2[64];
    SQL_EscapeString(MyDataBase, name, name2, sizeof(name2)); // Don't save raw names in database!!
    
    GetClientAuthId(client, AuthId_Engine, auth, sizeof(auth));
    
    GetClientIP(client, ip, sizeof(ip));
    
    Format(query, sizeof(query), "INSERT INTO players(name,steamid,ip) VALUES('%s','%s','%s')", name2, auth, ip);
    SQL_TQuery(MyDataBase, record_callback, query);
}


public void record_callback(Handle owner, Handle hndl, const char[] error, any data)
{
    if(hndl == null)
    {
        LogError("record Error: %s", error);
        // you going to see "... record Error: UNIQUE constraint failed: players.steamid, players.ip" when save duplicate steamid+IP record
        return;
    }
    
    PrintToServer("success");
} 


This will record different steamid+ip, no duplicates.

[Bacardi]

...unless, you want log history of ip changes, I think you need either to use timestamp to look latest IP record, or look bigest row ID number
*edit
Updated previous post