AMX Mod X Moderator
Join Date: Feb 2005
Location: NJ, USA
06-23-2018
, 11:24
Re: [ H3LP ] SQLx load data
#1
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.
Code:
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 ); }
__________________
Last edited by Bugsy; 06-23-2018 at 15:03 .