Here's the basic framework.
PHP Code:
#include <amxmodx>
#include <sqlx>
new const Version[] = "0.1";
#define MAX_PLAYERS 32
//You can/should clean this up with an enumerator and a single array.
new g_iLevel[ MAX_PLAYERS + 1 ];
new g_iExp[ MAX_PLAYERS + 1 ];
new g_iReset[ MAX_PLAYERS + 1 ];
new g_iTicket[ MAX_PLAYERS + 1 ];
new g_iEscape[ MAX_PLAYERS + 1 ];
new g_iHealth[ MAX_PLAYERS + 1 ];
new g_iArmor[ MAX_PLAYERS + 1 ];
new g_iDamage[ MAX_PLAYERS + 1 ];
new g_iCash[ MAX_PLAYERS + 1 ];
new const QUERY_CreateTable[] = "CREATE TABLE IF NOT EXISTS tblPlayerData (SteamID VARCHAR(34) PRIMARY KEY, iLevel INTEGER, iExp INTEGER, iReset INTEGER, iTicket INTEGER, iEscape INTEGER, iHealth INTEGER, iArmor INTEGER, iDamage INTEGER, iCash INTEGER);";
new const QUERY_Connected[] = "SELECT iLevel, iExp, iReset, iTicket, iEscape, iHealth, iArmor, iDamage, iCash FROM tblPlayerData WHERE SteamID='%s';";
new const QUERY_Disconnected_New[] = "INSERT INTO tblPlayerData (iLevel, iExp, iReset, iTicket, iEscape, iHealth, iArmor, iDamage, iCash, SteamID) VALUES (%d,%d,%d,%d,%d,%d,%d,%d,%d,'%s');";
new const QUERY_Disconnected_Return[] = "UPDATE tblPlayerData SET iLevel=%d, iExp=%d, iReset=%d, iTicket=%d, iEscape=%d, iHealth=%d, iArmor=%d, iDamage=%d, iCash=%d WHERE SteamID='%s';";
new Handle:g_SQLTuple , g_szQueryCache[ 1024 ] , g_szSteamID[ MAX_PLAYERS + 1 ][ 34 ] , bool:g_bReturningPlayer[ MAX_PLAYERS + 1 ];
public plugin_init()
{
register_plugin( "SQL Example" , Version , "bugsy" );
SQL_SetAffinity( "sqlite" );
g_SQLTuple = SQL_MakeDbTuple( "" , "" , "" , "YourDatabase" );
new Handle:SqlConnection , ErrorCode;
SqlConnection = SQL_Connect( g_SQLTuple , ErrorCode , g_szQueryCache , charsmax( g_szQueryCache ) );
if( SqlConnection == Empty_Handle )
set_fail_state( g_szQueryCache );
RunQuery( SqlConnection , QUERY_CreateTable );
}
public plugin_end()
{
SQL_FreeHandle( g_SQLTuple );
}
public client_authorized( id )
{
new Data[ 1 ]; Data[ 0 ] = id;
if ( !is_user_bot( id ) )
{
g_bReturningPlayer[ id ] = false;
get_user_authid( id , g_szSteamID[ id ] , charsmax( g_szSteamID[] ) );
formatex( g_szQueryCache, charsmax( g_szQueryCache ) , QUERY_Connected , g_szSteamID[ id ] );
SQL_ThreadQuery( g_SQLTuple , "SelectHandle" , g_szQueryCache , Data , sizeof( Data ) );
}
}
public client_disconnect( id )
{
new Data[ 1 ]; Data[ 0 ] = id;
if ( !is_user_bot( id ) )
{
//Set random values before at disconnect to confirm data is saving
g_iLevel[ id ] = 37;
g_iTicket[ id ] = 22;
get_user_authid( id , g_szSteamID[ id ] , charsmax( g_szSteamID[] ) );
formatex( g_szQueryCache, charsmax( g_szQueryCache ) , g_bReturningPlayer[ id ] ? QUERY_Disconnected_Return : QUERY_Disconnected_New , g_iLevel[ id ],g_iExp[ id ],g_iReset[ id ],g_iTicket[ id ],g_iEscape[ id ],g_iHealth[ id ],g_iArmor[ id ],g_iDamage[ id ],g_iCash[ id ] , g_szSteamID[ id ] );
new Handle:SqlConnection , ErrorCode;
SqlConnection = SQL_Connect( g_SQLTuple , ErrorCode , g_szQueryCache , charsmax( g_szQueryCache ) );
if( SqlConnection == Empty_Handle )
set_fail_state( g_szQueryCache );
RunQuery( SqlConnection , g_szQueryCache );
}
}
public RunQuery( Handle:SQLConnection , const szQuery[] )
{
new Handle:Query = SQL_PrepareQuery( SQLConnection , szQuery );
if( !SQL_Execute( Query ) )
{
SQL_QueryError( Query , g_szQueryCache , 100 );
set_fail_state( g_szQueryCache );
}
}
public SelectHandle( FailState , Handle:Query , Error[] , Errcode , Data[] , DataSize )
{
if( FailState == TQUERY_CONNECT_FAILED )
{
return set_fail_state("Could not connect to SQL database." );
}
else if( FailState == TQUERY_QUERY_FAILED )
{
log_amx( "Query error: %s" , Error );
return set_fail_state( g_szQueryCache );
}
if( Errcode )
return log_amx( "Error on query: %s" , Error );
new id = Data[ 0 ];
if ( SQL_NumResults( Query ) )
{
g_bReturningPlayer[ id ] = true;
g_iLevel[ id ] = SQL_ReadResult( Query , 0 );
g_iExp[ id ] = SQL_ReadResult( Query , 1 );
g_iReset[ id ] = SQL_ReadResult( Query , 2 );
g_iTicket[ id ] = SQL_ReadResult( Query , 3 );
g_iEscape[ id ] = SQL_ReadResult( Query , 4 );
g_iHealth[ id ] = SQL_ReadResult( Query , 5 );
g_iArmor[ id ] = SQL_ReadResult( Query , 6 );
g_iDamage[ id ] = SQL_ReadResult( Query , 7 );
g_iCash[ id ] = SQL_ReadResult( Query , 8 );
//Output the 2 items that dummy data was set to @ disconnect. This confirms data successfully saved\loaded via SQL.
//Output in server console: Loaded 37 22 for player id 2
server_print( "Loaded %d %d for player id %d" , g_iLevel[ id ] , g_iTicket[ id ] , id );
}
return PLUGIN_CONTINUE;
}