[CrazY.]

Hello, I made a simple money save/load system with SQL by player authid, but sometimes doesn't load the player money when he join in server. I'm using the following code.

Spoiler

public plugin_cfg()
{
    new szConfigsDir[64];

    get_configsdir(szConfigsDir, charsmax(szConfigsDir));
    server_cmd("exec %s/zp_sql.cfg", szConfigsDir);

    set_task(0.1, "SQLConnect");
}

public plugin_end()
{
    if (g_hSQLTuple != Empty_Handle)
        SQL_FreeHandle(g_hSQLTuple);
}

Spoiler

public SQLConnect()
{
    new iError, szError[96], szDbHost[32], szDbUser[32], szDbPassword[32], szDbName[32];

    get_pcvar_string(cvar_zp_dbhost, szDbHost, charsmax(szDbHost));
    get_pcvar_string(cvar_zp_dbuser, szDbUser, charsmax(szDbUser));
    get_pcvar_string(cvar_zp_dbpassword, szDbPassword, charsmax(szDbPassword));
    get_pcvar_string(cvar_zp_dbname, szDbName, charsmax(szDbName));

    g_hSQLTuple = SQL_MakeDbTuple(szDbHost, szDbUser, szDbPassword, szDbName);
    new Handle:hSQLConn = SQL_Connect(g_hSQLTuple, iError, szError, charsmax(szError));

    if (hSQLConn == Empty_Handle)
        set_fail_state(szError);

    new Handle:hSQLQuery = SQL_PrepareQuery(hSQLConn, "CREATE TABLE IF NOT EXISTS player (auth varchar(32), ammop int(11))");

    if (!SQL_Execute(hSQLQuery))
    {
        SQL_QueryError(hSQLQuery, szError, charsmax(szError));
        set_fail_state(szError);
    }

    SQL_FreeHandle(hSQLQuery);
    SQL_FreeHandle(hSQLConn);
}

public SQLLoadAp(pPlayer)
{
    if (g_hSQLTuple == Empty_Handle)
    {
        set_task(1.0, "SQLLoadAp", pPlayer);
        return;
    }

    new szQuery[200], szAuthId[32], stData[1];

    get_user_authid(pPlayer, szAuthId, charsmax(szAuthId));
    stData[0] = pPlayer;

    formatex(szQuery, charsmax(szQuery), "SELECT * FROM player WHERE auth LIKE '%s'", szAuthId);
    SQL_ThreadQuery(g_hSQLTuple, "SQLSetClientAp", szQuery, stData, sizeof stData);
}

public SQLSetClientAp(iFail, Handle:hSQLQuery, szError[], iError, stData[], iLen, Float:flQueryTime)
{
    switch (iFail)  
    {
        case TQUERY_CONNECT_FAILED: return set_fail_state("Could not connect to database.");
        case TQUERY_QUERY_FAILED: return set_fail_state("Query failed!");
    }

    if (iError)
        return log_amx("Error on query: %s", szError);

    new pPlayer = stData[0];

    if (!SQL_NumResults(hSQLQuery))
    {
        new szAuthId[32], szQuery[200];

        get_user_authid(pPlayer, szAuthId, charsmax(szAuthId));

        if (equali(szAuthId, "STEAM_ID_PENDING"))
            return 0;

        formatex(szQuery, charsmax(szQuery), "INSERT INTO player VALUES('%s', '%i')", szAuthId, g_AmmoPacks[pPlayer]);
        SQL_ThreadQuery(g_hSQLTuple, "SQLIgnoreHandle", szQuery);

        return 0;
    }

    g_AmmoPacks[pPlayer] = SQL_ReadResult(hSQLQuery, 1);
    return 1;
}

Spoiler

public client_putinserver(id)
{
    if (!is_user_bot(id))
        taskLoadAp(id+TASK_LOADAP);
}

public taskLoadAp(iTaskId)
{
    if (g_hSQLTuple == Empty_Handle)
    {
        set_task(1.0, "taskLoadAp", iTaskId);
        return;
    }

    SQLLoadAp(ID_LOADAP);
}

[Natsheh]

Don't use LIKE Unless you want Check if the string contain something use = operator

auth='%s'

Im still not sure why it some times doesnt get the player ammop, btw why are you using so much delay(set_task)

Use semicolon in end of each sql statement.

[CrazY.]

I did some tests in phpMyAdmin and looks like doesn't matter, be with LIKE, be with = operator, with semicolon or without, but thanks anyway for the suggestion.

I will test with vault and see if the problem still is occurring.

[CrazY.]

I tried with vault but players still lose ammop sometimes. Any idea?

[maqi]

Check if you aren't overwriting a current value with a null one somewhere along the way. It's not an sql/vault related issue if it happens with both.

[Bugsy]

I have the below observations, let me know if I am not understanding the flow correctly. You did not post your full plugin so I am making assumptions based on what you posted.

1. You attempt to retrieve the data from the db and if no rows are returned you write a row for the player.
2. Data for a player does not get updated on each disconnect since you are only inserting if the player had no data saved which will only happen on his first time on the server.

You should be using INSERT only if it's the players first time on the server, from then on you should be using UPDATE. You can use REPLACE INTO if you set auth as primary key which will overwrite the same row each time.

CREATE TABLE IF NOT EXISTS player (auth varchar(32) PRIMARY KEY, ammop int(11));

REPLACE INTO player (auth, ammop) VALUES ('STEAM_0:0:12345',77);

Not thoroughly tested

Spoiler

PHP Code:


#include <amxmodx>
#include <amxmisc>
#include <sqlx>

#define MAX_PLAYERS 32

new Handle:g_hSQLTuple;
new cvar_zp_dbhost , cvar_zp_dbuser , cvar_zp_dbpassword , cvar_zp_dbname;
new g_AmmoPacks[ MAX_PLAYERS + 1 ];

public plugin_init() 
{

}

public plugin_cfg()
{
    new szConfigsDir[64];
    
    get_configsdir(szConfigsDir, charsmax(szConfigsDir));
    server_cmd("exec %s/zp_sql.cfg", szConfigsDir);
    
    //set_task(0.1, "SQLConnect");
    SQLConnect();
}

public plugin_end()
{
    if (g_hSQLTuple != Empty_Handle)
        SQL_FreeHandle(g_hSQLTuple);
}

//public client_putinserver(id)
public client_authorized( id )
{
    if (!is_user_bot(id))
        SQLLoadAp( id );
        //taskLoadAp(id+TASK_LOADAP);
}

public client_disconnect( id ) 
{
    if (!is_user_bot(id))
    {
        SQLSaveAp( id );
        g_AmmoPacks[ id ] = 0;
    }
}

//public taskLoadAp(iTaskId)
//{
    //if (g_hSQLTuple == Empty_Handle)
    //{
    //    set_task(1.0, "taskLoadAp", iTaskId);
    //    return;
    //}
    
//    SQLLoadAp(ID_LOADAP);
//}

public SQLConnect()
{
    new iError, szError[96], szDbHost[32], szDbUser[32], szDbPassword[32], szDbName[32];
    
    get_pcvar_string(cvar_zp_dbhost, szDbHost, charsmax(szDbHost));
    get_pcvar_string(cvar_zp_dbuser, szDbUser, charsmax(szDbUser));
    get_pcvar_string(cvar_zp_dbpassword, szDbPassword, charsmax(szDbPassword));
    get_pcvar_string(cvar_zp_dbname, szDbName, charsmax(szDbName));
    
    g_hSQLTuple = SQL_MakeDbTuple(szDbHost, szDbUser, szDbPassword, szDbName);
    
    new Handle:hSQLConn = SQL_Connect(g_hSQLTuple, iError, szError, charsmax(szError));
    
    if (hSQLConn == Empty_Handle)
        set_fail_state(szError);
    
    new Handle:hSQLQuery = SQL_PrepareQuery(hSQLConn, "CREATE TABLE IF NOT EXISTS player (auth varchar(32) PRIMARY KEY, ammop int(11));");
    
    if (!SQL_Execute(hSQLQuery))
    {
        SQL_QueryError(hSQLQuery, szError, charsmax(szError));
        set_fail_state(szError);
    }
    
    SQL_FreeHandle(hSQLQuery);
    SQL_FreeHandle(hSQLConn);
}

public SQLLoadAp( pPlayer )
{
    //if (g_hSQLTuple == Empty_Handle)
    //{
    //    set_task(1.0, "SQLLoadAp", pPlayer);
    //    return;
    //}
    
    new szQuery[200], szAuthId[32], stData[1];
    
    get_user_authid(pPlayer, szAuthId, charsmax(szAuthId));
    stData[0] = pPlayer;
    
    formatex(szQuery, charsmax(szQuery), "SELECT ammop FROM player WHERE auth='%s';", szAuthId);
    SQL_ThreadQuery(g_hSQLTuple, "SQLLoadAp_Result", szQuery, stData, sizeof stData);
}

public SQLLoadAp_Result(iFail, Handle:hSQLQuery, szError[], iError, stData[], iLen, Float:flQueryTime)
{
    switch (iFail)  
    {
        case TQUERY_CONNECT_FAILED: return set_fail_state("Could not connect to database.");
        case TQUERY_QUERY_FAILED: return set_fail_state("Query failed!");
    }
    
    if (iError)
        return log_amx("Error on query: %s", szError);
    
    new pPlayer = stData[0];
    
    if ( pPlayer == -1 )
        return 1;
        
    /*if (!SQL_NumResults(hSQLQuery))
    {
        new szAuthId[32], szQuery[200];
    
        get_user_authid(pPlayer, szAuthId, charsmax(szAuthId));
    
        if (equali(szAuthId, "STEAM_ID_PENDING"))
            return 0;
    
        formatex(szQuery, charsmax(szQuery), "INSERT INTO player VALUES('%s', '%i')", szAuthId, g_AmmoPacks[pPlayer]);
        SQL_ThreadQuery(g_hSQLTuple, "SQLIgnoreHandle", szQuery);
    
        return 0;
    }*/
    
    if ( SQL_NumResults( hSQLQuery ) )
    {
        g_AmmoPacks[ pPlayer ] = SQL_ReadResult( hSQLQuery , 0 );
    }
    
    return 1;
}

public SQLSaveAp(pPlayer)
{
    new szQuery[200], szAuthId[32], stData[1];
    
    get_user_authid(pPlayer, szAuthId, charsmax(szAuthId));
    stData[0] = -1;
    
    formatex(szQuery, charsmax(szQuery), "REPLACE INTO player (auth,ammop) VALUES('%s', %d);", szAuthId , g_AmmoPacks[ pPlayer ] );
    SQL_ThreadQuery(g_hSQLTuple, "SQLLoadAp_Result", szQuery, stData, sizeof stData);
} 


[CrazY.]

I tried with this simple code, but players still lose APs.
@Bugsy, thanks for the suggestion but my client doesn't want database anymore.

Spoiler

public client_authorized(id)
{
    if (!is_user_bot(id))
        vaultLoadData(id);
}

public client_disconnect(id)
{
    remove_task(id+TASK_HIDEMONEY)

    if (!is_user_bot(id))
        vaultSaveData(id);
}

vaultLoadData(this)
{
    new szAuthId[32], szVaultData[32];

    get_user_authid(this, szAuthId, charsmax(szAuthId));

    if (equali(szAuthId, "VALVE_", 6))
        get_user_ip(this, szAuthId, charsmax(szAuthId), 1);

    if (!vaultdata_exists(szAuthId))
    {
        g_AmmoPacks[this] = get_pcvar_num(cvar_starting_ammo_packs);
        return;
    }

    get_vaultdata(szAuthId, szVaultData, charsmax(szVaultData));
    g_AmmoPacks[this] = str_to_num(szVaultData);
}

vaultSaveData(this)
{
    new szAuthId[32], szVaultData[32];

    formatex(szVaultData, charsmax(szVaultData), "%d", g_AmmoPacks[this]);
    get_user_authid(this, szAuthId, charsmax(szAuthId));

    if (equali(szAuthId, "VALVE_", 6))
        get_user_ip(this, szAuthId, charsmax(szAuthId), 1);

    set_vaultdata(szAuthId, szVaultData);
}