AlliedModders

AlliedModders (https://forums.alliedmods.net/index.php)
-   Scripting Help (https://forums.alliedmods.net/forumdisplay.php?f=11)
-   -   help with using sqlx (https://forums.alliedmods.net/showthread.php?t=346095)

mamistikfistik 02-09-2024 15:35

help with using sqlx
 
Hello friends, let me show an example of what I want to do, the library I use is sqlx. I will run a query like this, I will give steamid, if that steamid is found, return; If not, it will insert. I leave the version I made with javascript below, I would appreciate it if you inform me.

const SteamID = '12345';
const Query = `SELECT * FROM players WHERE steam_id = '${SteamID}'`;

connection.query(Query, (error, results) => {
if (error) {
console.log(error.stack);
return;
}

if (results.length > 0) {
console.log('Steam ID found.');
} else {
const query2 = `INSERT INTO players (steam_id) VALUES ('${SteamID}')`;
connection.query(query2, (insertError, insertResults) => {
if (insertError) {
console.log(insertError.stack);
return;
}
}

Bugsy 02-09-2024 17:18

Re: help with using sqlx
 
Not tested
PHP Code:


#include <amxmodx>
#include <sqlx>

new Handle:g_SQLTuple;

new 
g_szBuffer256 ];

public 
plugin_init() 
{
    
SQL_SetAffinity"sqlite" );
    
g_SQLTuple SQL_MakeDbTuple"host" "user" "pass" "DBName" );
}

public 
client_authorizedid )
{
    new 
szSteamID35 ];
    
get_user_authidid szSteamID charsmaxszSteamID ) );

    
formatexg_szBuffer charsmaxg_szBuffer ) , "SELECT * FROM players WHERE steam_id = '%s';" szSteamID );
    
SQL_ThreadQueryg_SQLTuple "SQLCallBack" g_szBuffer szSteamID sizeofszSteamID )  );
}

public 
SQLCallBack(FailStateHandle:QueryError[], Errcode, const Data[], DataSize)
{
    new 
szSteamID35 ];
    
    if ( !
Errcode )
    {
        if ( 
SQL_NumResultsQuery ) )
        {
            
SQL_ReadResultQuery szSteamID charsmaxszSteamID ) );
            
            
console_print"SteamID Found" );
        }
        else
        {
            
formatexg_szBuffer charsmaxg_szBuffer ) , "INSERT INTO players (steam_id) VALUES ('%s');" Data );
            
SQL_ThreadQueryg_SQLTuple "SQLCallBack" g_szBuffer );
            
            
console_print"SteamID Inserted" );
        }
    }



mamistikfistik 02-09-2024 17:43

Re: help with using sqlx
 
Quote:

Originally Posted by Bugsy (Post 2817764)
Not tested
PHP Code:


#include <amxmodx>
#include <sqlx>

new Handle:g_SQLTuple;

new 
g_szBuffer256 ];

public 
plugin_init() 
{
    new const 
szSteamID[] = "STEAM12345";
    
formatexg_szBuffer charsmaxg_szBuffer ) , "SELECT * FROM players WHERE steam_id = '%s';" szSteamID );
    
    
SQL_SetAffinity"sqlite" );
    
    
g_SQLTuple SQL_MakeDbTuple"host" "user" "pass" "DBName" );
    
    
SQL_ThreadQueryg_SQLTuple "SQLCallBack" g_szBuffer szSteamID sizeofszSteamID )  );
}

public 
SQLCallBack(FailStateHandle:QueryError[], Errcode, const Data[], DataSize)
{
    new 
szSteamID35 ];
    
    if ( !
Errcode )
    {
        if ( 
SQL_NumResultsQuery ) )
        {
            
SQL_ReadResultQuery szSteamID charsmaxszSteamID ) );
            
            
console_print"SteamID Found" );
        }
        else
        {
            
formatexg_szBuffer charsmaxg_szBuffer ) , "INSERT INTO players (steam_id) VALUES ('%s');" Data );
            
SQL_ThreadQueryg_SQLTuple "SQLCallBack" g_szBuffer );
            
            
console_print"SteamID Inserted" );
        }
    }



Can you show me how to use this in client_putinserver, I tried to try it myself but I guess it didn't work.

Bugsy 02-09-2024 18:03

Re: help with using sqlx
 
Above code is updated.. authorized should be used opposed to putinserver

mamistikfistik 02-09-2024 18:22

Re: help with using sqlx
 
Quote:

Originally Posted by Bugsy (Post 2817769)
Above code is updated.. authorized should be used opposed to putinserver

When I try the code, it performs a lot of sql queries, including after entering the server. The current number is 4000 for 20 seconds. It also tries to add even if there is a Steam ID.

mamistikfistik 02-09-2024 18:40

Re: help with using sqlx
 
Okay, I solved the error I mentioned, now I have a question. For example, I found the sql data with Steam ID, but I need to get or update other things, for example LastName, can you explain how to do that?

Bugsy 02-09-2024 18:59

Re: help with using sqlx
 
So you need to query for the SteamID and LastName and if no record exists, insert both fields as a new record?

mamistikfistik 02-09-2024 19:31

Re: help with using sqlx
 
Quote:

Originally Posted by Bugsy (Post 2817773)
So you need to query for the SteamID and LastName and if no record exists, insert both fields as a new record?

Let me give an example here. How can I subtract Steam_ID and LastName? Let's say I gave Steam_ID 123, how can I get the equal LastName?

CREATE TABLE IF NOT EXISTS `players` (
`ID` int NOT NULL AUTO_INCREMENT,
`Steam_ID` varchar(32) NOT NULL,
`LastName` varchar(32) DEFAULT 'None',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=421 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

bigdaddy424 02-09-2024 20:24

Re: help with using sqlx
 
PHP Code:

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

new Handle:MYSQL_CONNECTION
new DatabaseID[MAX_PLAYERS 1]
new const 
MYSQL_TABLE[] = "table"

public plugin_cfg()
    
MYSQL_Init()

public 
plugin_end()
    
SQL_FreeHandle(MYSQL_CONNECTION)

public 
MYSQL_Init()
{
    
MYSQL_CONNECTION SQL_MakeStdTuple()

    new 
query[256]
    
formatex(querycharsmax(query), "CREATE TABLE IF NOT EXISTS `%s` (\
        `id` INT(4) NOT NULL AUTO_INCREMENT,\
        `steamid` VARCHAR(32) NOT NULL,\
        `name` VARCHAR(64) NOT NULL,\
        PRIMARY KEY (`id`),\
        UNIQUE (`steamid`));"
MYSQL_TABLE)
    
SQL_ThreadQuery(MYSQL_CONNECTION"IgnoredOutput"query)
}

public 
client_authorized(id)
{
    if (!
is_user_bot(id) && !is_user_hltv(id))
    {
        new 
steamid[32], data[2]
        
data[0] = id
        data
[1] = 0
        DatabaseID
[id] = 0
        get_user_authid
(idsteamidcharsmax(steamid))
        
SQL_ThreadQuery(MYSQL_CONNECTION"DataOutput"fmt("SELECT * FROM `%s` WHERE BINARY `steamid` = '%s';"MYSQL_TABLEsteamid), datasizeof(data))
    }
}

public 
client_disconnected(id)
{
    if (!
is_user_bot(id) && !is_user_hltv(id))
    {
        new 
buffer[MAX_NAME_LENGTH*2]
        
SQL_QuoteStringFmt(Empty_Handlebuffercharsmax(buffer), "%n"id)
        
server_print(buffer)
        
SQL_ThreadQuery(MYSQL_CONNECTION"IgnoredOutput"fmt("UPDATE `%s` SET `name` = '%s' WHERE id = %d;"MYSQL_TABLEbufferDatabaseID[id]))
        
DatabaseID[id] = 0
    
}
}


public 
DataOutput(failStateHandle:queryerror[], errNumdata[])
{
    if(
errNum)
        
server_print("MYSQL Error # (%d)%s"errNumerror)

    else
    {
        new 
id data[0]
        if(
is_user_connected(id))
        {
            if(!
SQL_NumResults(query))
            {
                new 
steamid[MAX_NAME_LENGTH], buffer[MAX_NAME_LENGTH*2]
                
get_user_authid(idsteamidcharsmax(steamid))
                
SQL_QuoteStringFmt(Empty_Handlebuffercharsmax(buffer), "%n"id)
                
SQL_ThreadQuery(MYSQL_CONNECTION"IgnoredOutput"fmt("INSERT INTO `%s` VALUES(NULL, '%s', '%s');"MYSQL_TABLEsteamidbuffer))
                
client_authorized(idNULL_STRING)
            }

            else
                
DatabaseID[id] = SQL_ReadResult(query0)
        }
    }
}

public 
IgnoredOutput(failStateHandle:query, const error[], errNum)
{
    if(
errNum)
        
server_print("MYSQL Error # (%d)%s"errNumerror)



Bugsy 02-09-2024 21:31

Re: help with using sqlx
 
Quote:

Originally Posted by mamistikfistik (Post 2817777)
Let me give an example here. How can I subtract Steam_ID and LastName? Let's say I gave Steam_ID 123, how can I get the equal LastName?

CREATE TABLE IF NOT EXISTS `players` (
`ID` int NOT NULL AUTO_INCREMENT,
`Steam_ID` varchar(32) NOT NULL,
`LastName` varchar(32) DEFAULT 'None',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=421 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

It would be easiest to replace
PHP Code:

SELECT FROM players WHERE steam_id '%s'

with
PHP Code:

SELECT LastName FROM players WHERE steam_id '%s'

I imagine that you do not have a ridiculous number of fields/columns where it's too much work to just specify the column names you want opposed to passing *...("*" means get every column, if you didn't know).

Then in the call back function, this will retrieve the LastName value.
PHP Code:

SQL_ReadResultQuery szLastName charsmaxszLastName ) ); 

If you wanted multiple things, you'd do this:
PHP Code:

SELECT LastNameAgeXP FROM players WHERE steam_id '%s'

Then in the callback you'd do:
PHP Code:

SQL_ReadResultQuery szLastName charsmaxszLastName ) );
new 
iAge SQL_ReadResultQuery );
new 
iXP SQL_ReadResultQuery ); 



All times are GMT -4. The time now is 00:32.

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