AlliedModders

AlliedModders (https://forums.alliedmods.net/index.php)
-   Scripting Help (https://forums.alliedmods.net/forumdisplay.php?f=11)
-   -   Check player name in mysql and load an integer value from mysql (https://forums.alliedmods.net/showthread.php?t=314078)

TheBladerX 02-02-2019 16:16

Check player name in mysql and load an integer value from mysql
 
Hello, I would like to make following:
First, get player name and check if it exists in database (for example in column NAME).
If it does exist, extract integer value from that row (let's say from column ID) and print in into chat as %i.
Thank you.

Natsheh 02-02-2019 18:59

Re: Check player name in mysql and load an integer value from mysql
 
If you are seeking just for mysql syntax here you go

Code:

SELECT Id from `database.table` WHERE `name` like '%s';

TheBladerX 02-02-2019 19:47

Re: Check player name in mysql and load an integer value from mysql
 
Thank you, but my problem is whole pawn function. I have no experience with mysql in pawn.

Bugsy 02-02-2019 20:30

Re: Check player name in mysql and load an integer value from mysql
 
The SQL is covered, and you can follow the framework from info in this thread: https://forums.alliedmods.net/showpo...58&postcount=2

Airkish 02-03-2019 05:46

Re: Check player name in mysql and load an integer value from mysql
 
From bugsy's code, edit it as you need (not tested):

PHP Code:

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

#define PLUGIN  "PLUGIN"
#define VERSION "1.0"
#define AUTHOR  "AUTHOR"

// SQL Stuff
new g_CvarHostg_CvarUserg_CvarPassg_CvarDB;
new 
g_pGlobalQuery 256 ];
new 
HandleTuple;

// VIP Stuff
new g_pName 33 ] [ 32 ];
new 
g_iValue[33];

public 
plugin_precache ( )
{
    
g_CvarHost   =    register_cvar "sqlx_host""" );
    
g_CvarUser    register_cvar "sqlx_user""" );
    
g_CvarPass    =     register_cvar "sqlx_pass""" );
    
g_CvarDB        =   register_cvar "sqlx_db""" );

    
SQLX_Connect ( );
}
    
public 
plugin_init ( )
{
    
register_plugin PLUGINVERSIONAUTHOR );
}

public 
SQLX_Connect ( )
{
    new 
szHost 32 ], szUser 32 ], szPass 32 ], szDB 32 ];
    
get_pcvar_string g_CvarHostszHostcharsmax szHost ) );
    
get_pcvar_string g_CvarUserszUsercharsmax szUser ) );
    
get_pcvar_string g_CvarPassszPasscharsmax szPass ) );
    
get_pcvar_string g_CvarDBszDBcharsmax szDB ) );

    
Tuple SQL_MakeDbTuple szHostszUserszPassszDB10 );
}

public 
client_connect id )
{
    if ( 
is_user_bot id ) || is_user_hltv id ) )
        return;

    
get_user_name idg_pName id ], charsmax g_pName ) );
    
replace_all(g_pName[id], charsmax(g_pName), "'""\'");

    
formatex g_pGlobalQuerycharsmax g_pGlobalQuery ), "SELECT * FROM table WHERE username='%s'" g_pName id ] );
    
    new 
queryData];
    
queryData] = id;
    
SQL_ThreadQueryTuple"ReadInfo"g_pGlobalQuery queryData sizeofqueryData ) );
}

public 
ReadInfo FailStateHandleQueryszErrorErrcodeData [ ], DataSize )
{
    if ( 
FailState == TQUERY_CONNECT_FAILED )
    {
        
log_amx "Nu ne putem conecta la %d [%s]"ErrcodeszError );
        return;
    }
    else if ( 
FailState == TQUERY_QUERY_FAILED )
    {
        
log_amx "Query Error: %d [%s]"ErrcodeszError ); 
        return;
    }

    new 
Player Data ];
    
    
//This should be a check for > 0 instead of > 1
    
if ( SQL_NumResults Query ) > )
    {
        
g_iValue[Player] = SQL_ReadResult(Query0); //Where 0 change to column number
    
}
    else
    {
        
//No records returned which means there was no username
    
}
        



TheBladerX 02-03-2019 07:40

Re: Check player name in mysql and load an integer value from mysql
 
Quote:

Originally Posted by Airkish (Post 2637920)
From bugsy's code, edit it as you need (not tested):

PHP Code:

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

#define PLUGIN  "PLUGIN"
#define VERSION "1.0"
#define AUTHOR  "AUTHOR"

// SQL Stuff
new g_CvarHostg_CvarUserg_CvarPassg_CvarDB;
new 
g_pGlobalQuery 256 ];
new 
HandleTuple;

// VIP Stuff
new g_pName 33 ] [ 32 ];
new 
g_iValue[33];

public 
plugin_precache ( )
{
    
g_CvarHost   =    register_cvar "sqlx_host""" );
    
g_CvarUser    register_cvar "sqlx_user""" );
    
g_CvarPass    =     register_cvar "sqlx_pass""" );
    
g_CvarDB        =   register_cvar "sqlx_db""" );

    
SQLX_Connect ( );
}
    
public 
plugin_init ( )
{
    
register_plugin PLUGINVERSIONAUTHOR );
}

public 
SQLX_Connect ( )
{
    new 
szHost 32 ], szUser 32 ], szPass 32 ], szDB 32 ];
    
get_pcvar_string g_CvarHostszHostcharsmax szHost ) );
    
get_pcvar_string g_CvarUserszUsercharsmax szUser ) );
    
get_pcvar_string g_CvarPassszPasscharsmax szPass ) );
    
get_pcvar_string g_CvarDBszDBcharsmax szDB ) );

    
Tuple SQL_MakeDbTuple szHostszUserszPassszDB10 );
}

public 
client_connect id )
{
    if ( 
is_user_bot id ) || is_user_hltv id ) )
        return;

    
get_user_name idg_pName id ], charsmax g_pName ) );
    
replace_all(g_pName[id], charsmax(g_pName), "'""\'");

    
formatex g_pGlobalQuerycharsmax g_pGlobalQuery ), "SELECT * FROM table WHERE username='%s'" g_pName id ] );
    
    new 
queryData];
    
queryData] = id;
    
SQL_ThreadQueryTuple"ReadInfo"g_pGlobalQuery queryData sizeofqueryData ) );
}

public 
ReadInfo FailStateHandleQueryszErrorErrcodeData [ ], DataSize )
{
    if ( 
FailState == TQUERY_CONNECT_FAILED )
    {
        
log_amx "Nu ne putem conecta la %d [%s]"ErrcodeszError );
        return;
    }
    else if ( 
FailState == TQUERY_QUERY_FAILED )
    {
        
log_amx "Query Error: %d [%s]"ErrcodeszError ); 
        return;
    }

    new 
Player Data ];
    
    
//This should be a check for > 0 instead of > 1
    
if ( SQL_NumResults Query ) > )
    {
        
g_iValue[Player] = SQL_ReadResult(Query0); //Where 0 change to column number
    
}
    else
    {
        
//No records returned which means there was no username
    
}
        



It pops out error in console every time I connect to server. Server works, but the console does not show anything.
So the case remains, if "username" is in database, extract the integer value of "days" from that row and client_print it into %i. No idea how to continue.

https://i.ibb.co/Rpz2rzG/xix.png

Airkish 02-03-2019 08:22

Re: Check player name in mysql and load an integer value from mysql
 
Quote:

Originally Posted by TheBladerX (Post 2637945)
It pops out error in console every time I connect to server. Server works, but the console does not show anything.
So the case remains, if "username" is in database, extract the integer value of "days" from that row and client_print it into %i. No idea how to continue.

https://i.ibb.co/Rpz2rzG/xix.png

Make sure you edited database info:
PHP Code:

    g_CvarHost   =    register_cvar "sqlx_host""YOUR_HOST_ADDRESS" );
    
g_CvarUser    register_cvar "sqlx_user""YOUR_DB_USER" );
    
g_CvarPass    =     register_cvar "sqlx_pass""YOUR_DB_PASS" );
    
g_CvarDB        =   register_cvar "sqlx_db""YOUR_DB_NAME" ); 


Here a little debug to check:

PHP Code:

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

#define PLUGIN  "PLUGIN"
#define VERSION "1.0"
#define AUTHOR  "AUTHOR"

// SQL Stuff
new g_CvarHostg_CvarUserg_CvarPassg_CvarDB;
new 
g_pGlobalQuery 256 ];
new 
HandleTuple;

// VIP Stuff
new g_pName 33 ] [ 32 ];
new 
g_iValue[33];

public 
plugin_precache ( )
{
    
g_CvarHost   =    register_cvar "sqlx_host""" );
    
g_CvarUser    register_cvar "sqlx_user""" );
    
g_CvarPass    =     register_cvar "sqlx_pass""" );
    
g_CvarDB        =   register_cvar "sqlx_db""" );

    
SQLX_Connect ( );
}
    
public 
plugin_init ( )
{
    
register_plugin PLUGINVERSIONAUTHOR );
    
register_clcmd("say /debug""Debug");
}

public 
SQLX_Connect ( )
{
    new 
szHost 32 ], szUser 32 ], szPass 32 ], szDB 32 ];
    
get_pcvar_string g_CvarHostszHostcharsmax szHost ) );
    
get_pcvar_string g_CvarUserszUsercharsmax szUser ) );
    
get_pcvar_string g_CvarPassszPasscharsmax szPass ) );
    
get_pcvar_string g_CvarDBszDBcharsmax szDB ) );

    
Tuple SQL_MakeDbTuple szHostszUserszPassszDB10 );
}

public 
client_connect id )
{
    if ( 
is_user_bot id ) || is_user_hltv id ) )
        return;

    
get_user_name idg_pName id ], charsmax g_pName ) );
    
replace_all(g_pName[id], charsmax(g_pName), "'""\'");

    
formatex g_pGlobalQuerycharsmax g_pGlobalQuery ), "SELECT * FROM table WHERE username='%s'" g_pName[id]);
    
    new 
queryData];
    
queryData] = id;
    
SQL_ThreadQueryTuple"ReadInfo"g_pGlobalQuery queryData sizeofqueryData ) );
}

public 
ReadInfo FailStateHandleQueryszErrorErrcodeData [ ], DataSize )
{
    if ( 
FailState == TQUERY_CONNECT_FAILED )
    {
        
log_amx "Nu ne putem conecta la %d [%s]"ErrcodeszError );
        return;
    }
    else if ( 
FailState == TQUERY_QUERY_FAILED )
    {
        
log_amx "Query Error: %d [%s]"ErrcodeszError ); 
        return;
    }

    new 
Player Data ];
    
    
//This should be a check for > 0 instead of > 1
    
if ( SQL_NumResults Query ) > )
    {
        new 
iDay SQL_FieldNameToNum(Query,"days");

        
g_iValue[Player] = SQL_ReadResult(QueryiDay);
    }
    else
    {
        
g_iValue[Player] = -1//For testing purposes
    
}
}

public 
Debug(id) {
    
client_print(idprint_chat"Day: %d"g_iValue); 
    
//Once in server type /debug. 
    //If "Day: -1" no results found


Type /debug in chat to check results

TheBladerX 02-03-2019 08:40

Re: Check player name in mysql and load an integer value from mysql
 
Quote:

Originally Posted by Airkish (Post 2637953)
Make sure you edited database info:
PHP Code:

    g_CvarHost   =    register_cvar "sqlx_host""YOUR_HOST_ADDRESS" );
    
g_CvarUser    register_cvar "sqlx_user""YOUR_DB_USER" );
    
g_CvarPass    =     register_cvar "sqlx_pass""YOUR_DB_PASS" );
    
g_CvarDB        =   register_cvar "sqlx_db""YOUR_DB_NAME" ); 


Here a little debug to check:

PHP Code:

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

#define PLUGIN  "PLUGIN"
#define VERSION "1.0"
#define AUTHOR  "AUTHOR"

// SQL Stuff
new g_CvarHostg_CvarUserg_CvarPassg_CvarDB;
new 
g_pGlobalQuery 256 ];
new 
HandleTuple;

// VIP Stuff
new g_pName 33 ] [ 32 ];
new 
g_iValue[33];

public 
plugin_precache ( )
{
    
g_CvarHost   =    register_cvar "sqlx_host""" );
    
g_CvarUser    register_cvar "sqlx_user""" );
    
g_CvarPass    =     register_cvar "sqlx_pass""" );
    
g_CvarDB        =   register_cvar "sqlx_db""" );

    
SQLX_Connect ( );
}
    
public 
plugin_init ( )
{
    
register_plugin PLUGINVERSIONAUTHOR );
    
register_clcmd("say /debug""Debug");
}

public 
SQLX_Connect ( )
{
    new 
szHost 32 ], szUser 32 ], szPass 32 ], szDB 32 ];
    
get_pcvar_string g_CvarHostszHostcharsmax szHost ) );
    
get_pcvar_string g_CvarUserszUsercharsmax szUser ) );
    
get_pcvar_string g_CvarPassszPasscharsmax szPass ) );
    
get_pcvar_string g_CvarDBszDBcharsmax szDB ) );

    
Tuple SQL_MakeDbTuple szHostszUserszPassszDB10 );
}

public 
client_connect id )
{
    if ( 
is_user_bot id ) || is_user_hltv id ) )
        return;

    
get_user_name idg_pName id ], charsmax g_pName ) );
    
replace_all(g_pName[id], charsmax(g_pName), "'""\'");

    
formatex g_pGlobalQuerycharsmax g_pGlobalQuery ), "SELECT * FROM table WHERE username='%s'" g_pName[id]);
    
    new 
queryData];
    
queryData] = id;
    
SQL_ThreadQueryTuple"ReadInfo"g_pGlobalQuery queryData sizeofqueryData ) );
}

public 
ReadInfo FailStateHandleQueryszErrorErrcodeData [ ], DataSize )
{
    if ( 
FailState == TQUERY_CONNECT_FAILED )
    {
        
log_amx "Nu ne putem conecta la %d [%s]"ErrcodeszError );
        return;
    }
    else if ( 
FailState == TQUERY_QUERY_FAILED )
    {
        
log_amx "Query Error: %d [%s]"ErrcodeszError ); 
        return;
    }

    new 
Player Data ];
    
    
//This should be a check for > 0 instead of > 1
    
if ( SQL_NumResults Query ) > )
    {
        new 
iDay SQL_FieldNameToNum(Query,"days");

        
g_iValue[Player] = SQL_ReadResult(QueryiDay);
    }
    else
    {
        
g_iValue[Player] = -1//For testing purposes
    
}
}

public 
Debug(id) {
    
client_print(idprint_chat"Day: %d"g_iValue); 
    
//Once in server type /debug. 
    //If "Day: -1" no results found


Type /debug in chat to check results

Well, that one works better, but not properly. Every time I type /debug, it pops Day: 0, even if I change value of days in database.
Also I changed
Code:

SELECT * FROM table WHERE username='%s'
to table name.
And I would like to extract value from it, for example 10.

//edit:
Got it, thank you many times. The issue was
Code:

client_print(id, print_chat, "Day: %d", g_iValue[id]);
[id]

Bugsy 02-03-2019 11:14

Re: Check player name in mysql and load an integer value from mysql
 
You should use steam id instead of name. Also, if the name has a ' character in it, it will make your SQL lookup fail.

SELECT * FROM Table WHERE name='bugsy 'test''

Airkish 02-03-2019 12:27

Re: Check player name in mysql and load an integer value from mysql
 
Quote:

Originally Posted by Bugsy (Post 2637971)
You should use steam id instead of name. Also, if the name has a ' character in it, it will make your SQL lookup fail.

SELECT * FROM Table WHERE name='bugsy 'test''

I have escaped ' character in the code I gave


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

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