need a sql plugin

vikasvishwakarma · **Author**

Need a plugin that can store players data in sql such as their ip address, steam id, Name, time and date.

i want to write a php script afterwards which will allow my community manager to track down the hackers in the server and ban them manually.

if anyone can create it for me please.

edon1337 · 11-25-2017 , 16:01 Re: need a sql plugin

Why not just use log_to_file and log every player into a .txt/.log file?

vikasvishwakarma · 11-25-2017 , 16:49 Re: need a sql plugin

i cannot give the access of the vps to others thats why i am looking into plugin so that way once the data is saved in sql i can pull the data with php and display on the page which will be secured by user and pass

Alber9091 · 11-25-2017 , 17:01 Re: need a sql plugin

Quote:

Originally Posted by vikasvishwakarma

i cannot give the access of the vps to others thats why i am looking into plugin so that way once the data is saved in sql i can pull the data with php and display on the page which will be secured by user and pass

I guess Edon meant to print the .txt file on webpage.

It is possible. By making a simple webpage (in public_html or desired path) and adding server ftp info in it. It will print that .txt file from server to that web page.

edon1337 · 11-25-2017 , 17:04 Re: need a sql plugin

You have to find a PHP scripter.

@Alber9091 Not really what I meant, I'm not sure whether that is possible.

klippy · 11-25-2017 , 17:12 Re: need a sql plugin

Quote:

Originally Posted by edon1337

You have to find a PHP scripter.

@Alber9091 Not really what I meant, I'm not sure whether that is possible.

It is, I've developed some stuff using FTP to connect to the server and retrieve files. Although it will never be better than MySQL, you don't need a database which is nice. However your game server has to also run a FTP server (this is common with Counter-Strike server providers as far as I know) and your host has to allow outgoing FTP requests from PHP, which not every host does.

It's totally alright for small files of fixed size, but with for example logs which OP wants the file will continuously grow which is bad and every other request will take more and more time to complete.

vikasvishwakarma · 11-25-2017 , 17:21 Re: need a sql plugin

if it can be done by ftp what is the process i have to do to get it working ?

Airkish · 11-25-2017 , 19:23 Re: need a sql plugin

Try (make sure you added sql info)

PHP Code:


			
#include < amxmodx >
#include < sqlx >

#define TABLE_NAME "player_info"

/* Database */
new Host[] = "yourInfo"
new User[]  =  "yourInfo"
new Pass[] =  "yourInfo"
new Db[] =  "yourInfo"

new MySQL_Query[ 512 ]
new Handle:MySQL_Tuple
new Handle:MySQL_Connection

public plugin_init( ) {
    register_plugin( "PlayerInfo", "v1", "???" )
}

public client_putinserver( id )
{
    MySQL_Load(id);
}

public plugin_precache( )
{
    MySQL_Tuple = SQL_MakeDbTuple( Host, User, Pass, Db )
    #if AMXX_VERSION_NUM >= 183
        SQL_SetCharset(MySQL_Tuple,"utf8");
    #endif
    
    new ErrorCode
    MySQL_Connection = SQL_Connect( MySQL_Tuple, ErrorCode, MySQL_Query, charsmax( MySQL_Query ) )
    
    if( MySQL_Connection == Empty_Handle )
        set_fail_state( MySQL_Query )
    formatex( MySQL_Query, charsmax( MySQL_Query ), "CREATE TABLE IF NOT EXISTS %s (id int NOT NULL AUTO_INCREMENT, username VARCHAR(128) NOT NULL, steam_id VARCHAR(20), ip_adress VARCHAR(15), date DATE, time TIME )" )

    SQL_ThreadQuery( MySQL_Tuple, "SQL_TrashHandler", MySQL_Query )  
}

public MySQL_Load( id ) 
{
    new szAuth[32]; 
    get_user_authid(id, szAuth, 31);
    
    new Temp[ 1 ]
    Temp[ 0 ] = id
    
    formatex( MySQL_Query, charsmax( MySQL_Query ), "SELECT `steam_id` FROM %s WHERE `steam_id` = '%s'", TABLE_NAME, szAuth )
    SQL_ThreadQuery( MySQL_Tuple, "Load_PlayerInfo", MySQL_Query, Temp, sizeof( Temp ) )
}
                                                                                
public Load_PlayerInfo( FailState, Handle:Query, Error[ ], Errcode, Data[ ], DataSize ) 
{
    if( FailState == TQUERY_CONNECT_FAILED ) { return PLUGIN_HANDLED; }            
    else if( FailState == TQUERY_QUERY_FAILED ) { return PLUGIN_HANDLED; }

    new id = Data[ 0 ]
    
    if( !is_user_connected( id ) )
        return PLUGIN_HANDLED;
        
    new szAuth[32], szName[32], szIP[23], currentTime[9], currentDate[11]; 
    get_user_authid(id, szAuth, 31);
    get_user_name(id, szName, 31);
    get_user_ip(id, szIP, 22, 1);
    get_time("%H:%M:%S", currentTime, 8) 
    get_time("%Y/%m/%d", currentDate, 10)

    if( SQL_NumResults( Query ) < 1 ) 
    {
        formatex( MySQL_Query, charsmax( MySQL_Query ), "INSERT INTO %s (`username`, `steam_id`, `ip_adress`, `date`, `time`) VALUES ('%s', '%s', '%s', '%s', '%s')", TABLE_NAME, szName, szAuth, szIP, currentDate, currentTime )
        SQL_ThreadQuery( MySQL_Tuple, "SQL_TrashHandler", MySQL_Query )
    }
    else {
        formatex( MySQL_Query, charsmax( MySQL_Query ), "UPDATE %s SET `date` = '%s', `time` = '%s' WHERE `steam_id` = '%s'", TABLE_NAME, currentDate, currentTime, szAuth  )
        SQL_ThreadQuery( MySQL_Tuple, "SQL_TrashHandler", MySQL_Query )
    }
    return PLUGIN_CONTINUE;
}

public SQL_TrashHandler( FailState,Handle:Query, Error[ ], Errcode,Data[ ], DataSize )  
{ 
    if( FailState == TQUERY_CONNECT_FAILED )
        return PLUGIN_HANDLED;
        
    else if( FailState == TQUERY_QUERY_FAILED )
        return PLUGIN_HANDLED;
    
    SQL_FreeHandle( Query )
    return PLUGIN_CONTINUE;
}

public plugin_end( ) 
{   
    SQL_FreeHandle( MySQL_Connection )
}

Time and date marks latest player's connection to server.

P.S. You should set collation for username to avoid random symbols unless you are using amx version lower than 1.8.3

vikasvishwakarma · 11-26-2017 , 05:15 Re: need a sql plugin

Quote:

Originally Posted by Airkish

Try (make sure you added sql info)

PHP Code:


			
#include < amxmodx >

#include < sqlx >



#define TABLE_NAME "player_info"



/* Database */

new Host[] = "yourInfo"

new User[]  =  "yourInfo"

new Pass[] =  "yourInfo"

new Db[] =  "yourInfo"



new MySQL_Query[ 512 ]

new Handle:MySQL_Tuple

new Handle:MySQL_Connection



public plugin_init( ) {

    register_plugin( "PlayerInfo", "v1", "???" )

}



public client_putinserver( id )

{

    MySQL_Load(id);

}



public plugin_precache( )

{

    MySQL_Tuple = SQL_MakeDbTuple( Host, User, Pass, Db )

    #if AMXX_VERSION_NUM >= 183

        SQL_SetCharset(MySQL_Tuple,"utf8");

    #endif

    

    new ErrorCode

    MySQL_Connection = SQL_Connect( MySQL_Tuple, ErrorCode, MySQL_Query, charsmax( MySQL_Query ) )

    

    if( MySQL_Connection == Empty_Handle )

        set_fail_state( MySQL_Query )

    formatex( MySQL_Query, charsmax( MySQL_Query ), "CREATE TABLE IF NOT EXISTS %s (id int NOT NULL AUTO_INCREMENT, username VARCHAR(128) NOT NULL, steam_id VARCHAR(20), ip_adress VARCHAR(15), date DATE, time TIME )" )



    SQL_ThreadQuery( MySQL_Tuple, "SQL_TrashHandler", MySQL_Query )  

}



public MySQL_Load( id ) 

{

    new szAuth[32]; 

    get_user_authid(id, szAuth, 31);

    

    new Temp[ 1 ]

    Temp[ 0 ] = id

    

    formatex( MySQL_Query, charsmax( MySQL_Query ), "SELECT `steam_id` FROM %s WHERE `steam_id` = '%s'", TABLE_NAME, szAuth )

    SQL_ThreadQuery( MySQL_Tuple, "Load_PlayerInfo", MySQL_Query, Temp, sizeof( Temp ) )

}

                                                                                

public Load_PlayerInfo( FailState, Handle:Query, Error[ ], Errcode, Data[ ], DataSize ) 

{

    if( FailState == TQUERY_CONNECT_FAILED ) { return PLUGIN_HANDLED; }            

    else if( FailState == TQUERY_QUERY_FAILED ) { return PLUGIN_HANDLED; }



    new id = Data[ 0 ]

    

    if( !is_user_connected( id ) )

        return PLUGIN_HANDLED;

        

    new szAuth[32], szName[32], szIP[23], currentTime[9], currentDate[11]; 

    get_user_authid(id, szAuth, 31);

    get_user_name(id, szName, 31);

    get_user_ip(id, szIP, 22, 1);

    get_time("%H:%M:%S", currentTime, 8) 

    get_time("%Y/%m/%d", currentDate, 10)



    if( SQL_NumResults( Query ) < 1 ) 

    {

        formatex( MySQL_Query, charsmax( MySQL_Query ), "INSERT INTO %s (`username`, `steam_id`, `ip_adress`, `date`, `time`) VALUES ('%s', '%s', '%s', '%s', '%s')", TABLE_NAME, szName, szAuth, szIP, currentDate, currentTime )

        SQL_ThreadQuery( MySQL_Tuple, "SQL_TrashHandler", MySQL_Query )

    }

    else {

        formatex( MySQL_Query, charsmax( MySQL_Query ), "UPDATE %s SET `date` = '%s', `time` = '%s' WHERE `steam_id` = '%s'", TABLE_NAME, currentDate, currentTime, szAuth  )

        SQL_ThreadQuery( MySQL_Tuple, "SQL_TrashHandler", MySQL_Query )

    }

    return PLUGIN_CONTINUE;

}



public SQL_TrashHandler( FailState,Handle:Query, Error[ ], Errcode,Data[ ], DataSize )  

{ 

    if( FailState == TQUERY_CONNECT_FAILED )

        return PLUGIN_HANDLED;

        

    else if( FailState == TQUERY_QUERY_FAILED )

        return PLUGIN_HANDLED;

    

    SQL_FreeHandle( Query )

    return PLUGIN_CONTINUE;

}



public plugin_end( ) 

{   

    SQL_FreeHandle( MySQL_Connection )

}

Time and date marks latest player's connection to server.

P.S. You should set collation for username to avoid random symbols unless you are using amx version lower than 1.8.3

thanks for the reply. i tried your code but it did not work for some reason.
i checked the phpmyadmin there was no table created in the DB.
i added the correct information in the sma file for the sql.

Airkish · 11-26-2017 , 07:59 Re: need a sql plugin

Try now:

PHP Code:


			
#include < amxmodx >
#include < sqlx >

#define TABLE_NAME "player_info"

/* Database */
new Host[] = "yourInfo"
new User[]  =  "yourInfo"
new Pass[] =  "yourInfo"
new Db[] =  "yourInfo"

new MySQL_Query[ 512 ]
new Handle:MySQL_Tuple
new Handle:MySQL_Connection

public plugin_init( ) {
    register_plugin( "PlayerInfo", "v1", "???" )
}

public client_putinserver( id )
{
    MySQL_Load(id);
}

public plugin_precache( )
{
    MySQL_Tuple = SQL_MakeDbTuple( Host, User, Pass, Db )
    #if AMXX_VERSION_NUM >= 183
        SQL_SetCharset(MySQL_Tuple,"utf8");
    #endif
    
    new ErrorCode
    MySQL_Connection = SQL_Connect( MySQL_Tuple, ErrorCode, MySQL_Query, charsmax( MySQL_Query ) )
    
    if( MySQL_Connection == Empty_Handle )
        set_fail_state( MySQL_Query )
    formatex( MySQL_Query, charsmax( MySQL_Query ), "CREATE TABLE IF NOT EXISTS %s (id int NOT NULL AUTO_INCREMENT, username VARCHAR(128), steam_id VARCHAR(20), ip_adress VARCHAR(15), date DATE, time TIME, primary key (id) )", TABLE_NAME )

    SQL_ThreadQuery( MySQL_Tuple, "SQL_TrashHandler", MySQL_Query )  
}

public MySQL_Load( id ) 
{
    new szAuth[32]; 
    get_user_authid(id, szAuth, 31);
    
    new Temp[ 1 ]
    Temp[ 0 ] = id
    
    formatex( MySQL_Query, charsmax( MySQL_Query ), "SELECT `steam_id` FROM %s WHERE `steam_id` = '%s'", TABLE_NAME, szAuth )
    SQL_ThreadQuery( MySQL_Tuple, "Load_PlayerInfo", MySQL_Query, Temp, sizeof( Temp ) )
}
                                                                                
public Load_PlayerInfo( FailState, Handle:Query, Error[ ], Errcode, Data[ ], DataSize ) 
{
    if( FailState == TQUERY_CONNECT_FAILED ) { return PLUGIN_HANDLED; }            
    else if( FailState == TQUERY_QUERY_FAILED ) { return PLUGIN_HANDLED; }

    new id = Data[ 0 ]
    
    if( !is_user_connected( id ) )
        return PLUGIN_HANDLED;
        
    new szAuth[32], szName[32], szIP[23], currentTime[9], currentDate[11]; 
    get_user_authid(id, szAuth, 31);
    get_user_name(id, szName, 31);
    get_user_ip(id, szIP, 22, 1);
    get_time("%H:%M:%S", currentTime, 8) 
    get_time("%Y/%m/%d", currentDate, 10)

    if( SQL_NumResults( Query ) < 1 ) 
    {
        formatex( MySQL_Query, charsmax( MySQL_Query ), "INSERT INTO %s (`username`, `steam_id`, `ip_adress`, `date`, `time`) VALUES ('%s', '%s', '%s', '%s', '%s')", TABLE_NAME, szName, szAuth, szIP, currentDate, currentTime )
        SQL_ThreadQuery( MySQL_Tuple, "SQL_TrashHandler", MySQL_Query )
    }
    else {
        formatex( MySQL_Query, charsmax( MySQL_Query ), "UPDATE %s SET `date` = '%s', `time` = '%s' WHERE `steam_id` = '%s'", TABLE_NAME, currentDate, currentTime, szAuth  )
        SQL_ThreadQuery( MySQL_Tuple, "SQL_TrashHandler", MySQL_Query )
    }
    return PLUGIN_CONTINUE;
}

public SQL_TrashHandler( FailState,Handle:Query, Error[ ], Errcode,Data[ ], DataSize )  
{ 
    if( FailState == TQUERY_CONNECT_FAILED )
        return PLUGIN_HANDLED;
        
    else if( FailState == TQUERY_QUERY_FAILED )
        return PLUGIN_HANDLED;
    
    SQL_FreeHandle( Query )
    return PLUGIN_CONTINUE;
}

public plugin_end( ) 
{   
    SQL_FreeHandle( MySQL_Connection )
}