AlliedModders

AlliedModders (https://forums.alliedmods.net/index.php)
-   Scripting (https://forums.alliedmods.net/forumdisplay.php?f=107)
-   -   [ISSUE] Problem while storing data in the database (https://forums.alliedmods.net/showthread.php?t=324186)

SpirT 05-08-2020 15:45
[ISSUE] Problem while storing data in the database
 
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 05-09-2020 10:03
Re: [ISSUE] Problem while storing data in the database
 
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 05-09-2020 12:29
Re: [ISSUE] Problem while storing data in the database
 
Quote:
Originally Posted by 8guawong (Post 2699041)
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 05-09-2020 13:30
Re: [ISSUE] Problem while storing data in the database
 
Quote:
Originally Posted by SpirT (Post 2699069)
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 05-09-2020 13:45
Re: [ISSUE] Problem while storing data in the database
 
- 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

Bacardi 05-09-2020 13:52
Re: [ISSUE] Problem while storing data in the database
 
...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


All times are GMT -4. The time now is 00:06.

Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.