Raised This Month: $12 Target: $400
 3% 

Player Data Update SQL - Add More Info


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
DruGzOG
Veteran Member
Join Date: Nov 2007
Location: Unknown
Old 12-06-2019 , 12:02   Player Data Update SQL - Add More Info
Reply With Quote #1

Can someone update this to add amount of times connected as well as the server that the player has connected to?

https://pastebin.com/JPw9RD63
__________________

Last edited by DruGzOG; 12-06-2019 at 13:51.
DruGzOG is offline
Send a message via AIM to DruGzOG
^SmileY
Veteran Member
Join Date: Jan 2010
Location: Brazil [<o>]
Old 12-06-2019 , 12:55   Re: Player Data Update - Add More Info
Reply With Quote #2

Uhn you do not need to get time in plugin, since you can use NOW() function in mysql string to retrieve correct date

PHP Code:
#include < amxmodx >
#include < sqlx >

#define TABLE_NAME "player_info"

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

new MySQL_Query512 ]
new 
Handle:MySQL_Tuple
new Handle:MySQL_Connection

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

public 
client_putinserverid )
{
    
MySQL_Load(id);
}

public 
plugin_precache( )
{
    
MySQL_Tuple SQL_MakeDbTupleHostUserPassDb )
    
#if AMXX_VERSION_NUM >= 183
        
SQL_SetCharset(MySQL_Tuple,"utf8");
    
#endif
    
    
new ErrorCode
    MySQL_Connection 
SQL_ConnectMySQL_TupleErrorCodeMySQL_QuerycharsmaxMySQL_Query ) )
    
    if( 
MySQL_Connection == Empty_Handle )
        
set_fail_stateMySQL_Query )
    
formatexMySQL_QuerycharsmaxMySQL_Query ), "CREATE TABLE IF NOT EXISTS %s (id int NOT NULL AUTO_INCREMENT, username VARCHAR(128), steam_id VARCHAR(20), ip_adress VARCHAR(15), count INT NOT NULL, server VARCHAR(25), date DATE, time TIME, primary key (id) )"TABLE_NAME )

    
SQL_ThreadQueryMySQL_Tuple"SQL_TrashHandler"MySQL_Query )  
}

public 
MySQL_Loadid 
{
    new 
szAuth[32]; 
    
get_user_authid(idszAuth31);
    
    new 
Temp]
    
Temp] = id
    
    formatex
MySQL_QuerycharsmaxMySQL_Query ), "SELECT `steam_id` FROM %s WHERE `steam_id` = '%s'"TABLE_NAMEszAuth )
    
SQL_ThreadQueryMySQL_Tuple"Load_PlayerInfo"MySQL_QueryTempsizeofTemp ) )
}
                                                                                
public 
Load_PlayerInfoFailStateHandle:QueryError[ ], ErrcodeData[ ], DataSize 
{
    if( 
FailState == TQUERY_CONNECT_FAILED ) { return PLUGIN_HANDLED; }            
    else if( 
FailState == TQUERY_QUERY_FAILED ) { return PLUGIN_HANDLED; }

    new 
id Data]
    
    if( !
is_user_connectedid ) )
        return 
PLUGIN_HANDLED;
        
    new 
szAuth[32], szName[32], szIP[23], currentTime[9], currentDate[11], szServer[23]; 
    
get_user_authid(idszAuth31);
    
get_user_name(idszName31);
    
get_user_ip(idszIP221);
    
get_time("%H:%M:%S"currentTime8);
    
get_time("%Y/%m/%d"currentDate10);
    
get_cvar_string("net_address",szServer22);

    if( 
SQL_NumResultsQuery ) < 
    {
        
formatexMySQL_QuerycharsmaxMySQL_Query ), "INSERT INTO %s (`username`, `steam_id`, `ip_adress`, `count`, `date`, `time`, `server`) VALUES ('%s', '%s', '%s', 1, '%s', '%s', '%s')"TABLE_NAMEszNameszAuthszIPcurrentDatecurrentTimeszServer )
        
SQL_ThreadQueryMySQL_Tuple"SQL_TrashHandler"MySQL_Query )
    }
    else {
        
formatexMySQL_QuerycharsmaxMySQL_Query ), "UPDATE %s SET `date` = '%s', `time` = '%s', `count` = `count` + 1, `server` = '%s', WHERE `steam_id` = '%s'"TABLE_NAMEcurrentDatecurrentTimeszServerszAuth  )
        
SQL_ThreadQueryMySQL_Tuple"SQL_TrashHandler"MySQL_Query )
    }
    return 
PLUGIN_CONTINUE;
}

public 
SQL_TrashHandlerFailState,Handle:QueryError[ ], Errcode,Data[ ], DataSize )  

    if( 
FailState == TQUERY_CONNECT_FAILED )
        return 
PLUGIN_HANDLED;
        
    else if( 
FailState == TQUERY_QUERY_FAILED )
        return 
PLUGIN_HANDLED;
    
    
SQL_FreeHandleQuery )
    return 
PLUGIN_CONTINUE;
}

public 
plugin_end( ) 
{   
    
SQL_FreeHandleMySQL_Connection )

I recommend only to use inserts instead of update, ,since you can use mysql COUNT function to get the number of connections of a player. Also use steamid as primary key is a good choice in your case
__________________
Projects:

- See my Git Hub: https://github.com/SmileYzn
PHP Code:
set_pcvar_num(pCvar, !get_pcvar_num(pCvar)); 

Last edited by ^SmileY; 12-06-2019 at 13:52.
^SmileY is offline
Send a message via MSN to ^SmileY Send a message via Skype™ to ^SmileY
DruGzOG
Veteran Member
Join Date: Nov 2007
Location: Unknown
Old 12-06-2019 , 13:31   Re: Player Data Update - Add More Info
Reply With Quote #3

Quote:
Originally Posted by ^SmileY View Post
Uhn you do not need to get time in plugin, since you can use NOW() function in mysql string to retrieve correct date

PHP Code:
#include < amxmodx >
#include < sqlx >

#define TABLE_NAME "player_info"

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

new MySQL_Query512 ]
new 
Handle:MySQL_Tuple
new Handle:MySQL_Connection

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

public 
client_putinserverid )
{
    
MySQL_Load(id);
}

public 
plugin_precache( )
{
    
MySQL_Tuple SQL_MakeDbTupleHostUserPassDb )
    
#if AMXX_VERSION_NUM >= 183
        
SQL_SetCharset(MySQL_Tuple,"utf8");
    
#endif
    
    
new ErrorCode
    MySQL_Connection 
SQL_ConnectMySQL_TupleErrorCodeMySQL_QuerycharsmaxMySQL_Query ) )
    
    if( 
MySQL_Connection == Empty_Handle )
        
set_fail_stateMySQL_Query )
    
formatexMySQL_QuerycharsmaxMySQL_Query ), "CREATE TABLE IF NOT EXISTS %s (id int NOT NULL AUTO_INCREMENT, username VARCHAR(128), steam_id VARCHAR(20), ip_adress VARCHAR(15), count INT NOT NULL, date DATE, time TIME, primary key (id) )"TABLE_NAME )

    
SQL_ThreadQueryMySQL_Tuple"SQL_TrashHandler"MySQL_Query )  
}

public 
MySQL_Loadid 
{
    new 
szAuth[32]; 
    
get_user_authid(idszAuth31);
    
    new 
Temp]
    
Temp] = id
    
    formatex
MySQL_QuerycharsmaxMySQL_Query ), "SELECT `steam_id` FROM %s WHERE `steam_id` = '%s'"TABLE_NAMEszAuth )
    
SQL_ThreadQueryMySQL_Tuple"Load_PlayerInfo"MySQL_QueryTempsizeofTemp ) )
}
                                                                                
public 
Load_PlayerInfoFailStateHandle:QueryError[ ], ErrcodeData[ ], DataSize 
{
    if( 
FailState == TQUERY_CONNECT_FAILED ) { return PLUGIN_HANDLED; }            
    else if( 
FailState == TQUERY_QUERY_FAILED ) { return PLUGIN_HANDLED; }

    new 
id Data]
    
    if( !
is_user_connectedid ) )
        return 
PLUGIN_HANDLED;
        
    new 
szAuth[32], szName[32], szIP[23], currentTime[9], currentDate[11]; 
    
get_user_authid(idszAuth31);
    
get_user_name(idszName31);
    
get_user_ip(idszIP221);
    
get_time("%H:%M:%S"currentTime8
    
get_time("%Y/%m/%d"currentDate10)

    if( 
SQL_NumResultsQuery ) < 
    {
        
formatexMySQL_QuerycharsmaxMySQL_Query ), "INSERT INTO %s (`username`, `steam_id`, `ip_adress`, `count`, `date`, `time`) VALUES ('%s', '%s', '%s', 1, '%s', '%s')"TABLE_NAMEszNameszAuthszIPcurrentDatecurrentTime )
        
SQL_ThreadQueryMySQL_Tuple"SQL_TrashHandler"MySQL_Query )
    }
    else {
        
formatexMySQL_QuerycharsmaxMySQL_Query ), "UPDATE %s SET `date` = '%s', `time` = '%s', `count` = `count` + 1 WHERE `steam_id` = '%s'"TABLE_NAMEcurrentDatecurrentTimeszAuth  )
        
SQL_ThreadQueryMySQL_Tuple"SQL_TrashHandler"MySQL_Query )
    }
    return 
PLUGIN_CONTINUE;
}

public 
SQL_TrashHandlerFailState,Handle:QueryError[ ], Errcode,Data[ ], DataSize )  

    if( 
FailState == TQUERY_CONNECT_FAILED )
        return 
PLUGIN_HANDLED;
        
    else if( 
FailState == TQUERY_QUERY_FAILED )
        return 
PLUGIN_HANDLED;
    
    
SQL_FreeHandleQuery )
    return 
PLUGIN_CONTINUE;
}

public 
plugin_end( ) 
{   
    
SQL_FreeHandleMySQL_Connection )

I recommend only to use inserts instead of update, ,since you can use mysql COUNT function to get the number of connections of a player. Also use steamid as primary key is a good choice in your case
Yeah, not too familiar with sql at all. But thank you, will test and update/edit my post on the results.

Edit: Can you implement what server IP it also connects to?
__________________

Last edited by DruGzOG; 12-06-2019 at 13:47. Reason: Removed a part cause I manually adjusted it
DruGzOG is offline
Send a message via AIM to DruGzOG
^SmileY
Veteran Member
Join Date: Jan 2010
Location: Brazil [<o>]
Old 12-06-2019 , 13:50   Re: Player Data Update - Add More Info
Reply With Quote #4

yes, i will edit my post one moment.

Edit done
__________________
Projects:

- See my Git Hub: https://github.com/SmileYzn
PHP Code:
set_pcvar_num(pCvar, !get_pcvar_num(pCvar)); 

Last edited by ^SmileY; 12-06-2019 at 13:53.
^SmileY is offline
Send a message via MSN to ^SmileY Send a message via Skype™ to ^SmileY
DruGzOG
Veteran Member
Join Date: Nov 2007
Location: Unknown
Old 12-06-2019 , 18:34   Re: Player Data Update - Add More Info
Reply With Quote #5

Quote:
Originally Posted by ^SmileY View Post
yes, i will edit my post one moment.

Edit done
Hmm, for some reason it won't set the appropriate server IP. Adding the server column also just shows null, doesn't update the current
Used this but not efficiently updating the column for some reason:
PHP Code:
ALTER TABLE `player_infoADD `serverVARCHAR(25CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL AFTER `count`; 
__________________

Last edited by DruGzOG; 12-06-2019 at 18:40.
DruGzOG is offline
Send a message via AIM to DruGzOG
DruGzOG
Veteran Member
Join Date: Nov 2007
Location: Unknown
Old 12-07-2019 , 17:29   Re: Player Data Update SQL - Add More Info
Reply With Quote #6

Sorry to bump, but the count no longer works. Reads as one but does not update after a player connects to the server again
__________________
DruGzOG is offline
Send a message via AIM to DruGzOG
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 07:18.


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