AlliedModders

AlliedModders (https://forums.alliedmods.net/index.php)
-   Scripting Help (https://forums.alliedmods.net/forumdisplay.php?f=11)
-   -   SQL querys (https://forums.alliedmods.net/showthread.php?t=159076)

misterx 06-12-2011 16:04

SQL querys
 
Hi,

I've used SQL alot, but both SQLx and DBI modules seem too confusing. Can someone write me some code:
  1. I need to set g_number[id] (a player specific variable) to the one saved in MySQL database (you can use steam_id, nick, ip or even id, I don't care), if it is set. If its not, set it and default to zero.
  2. When player disconnects, I want to save the g_number[id] to MySQL.
If you don't understand, I can write a PHP code that does that.

Clauu 06-12-2011 16:49

Re: SQL querys
 
A great tut for sqlx - https://forums.alliedmods.net/showthread.php?t=46779

misterx 06-12-2011 17:11

Re: SQL querys
 
Yeah, I've read that. It's too confusing. What's with those SQL drivers anyway, they have so many variables and functions etc.

misterx 06-13-2011 13:28

Re: SQL querys
 
Forgot to say: I'd prefer a DBI solutions as it seems more light-weight.

Clauu 06-13-2011 14:17

Re: SQL querys
 
Forget about dbi, dbi is old and unsuported, use sqlx.
So, first of all you need to define the sql data like db user host pass in your plugin_init(for example) - g_SqlTuple = SQL_MakeDbTuple(SQL_HOST,SQL_USER,SQL_PASS,SQ L_DB); where g_SqlTuple is .. you get'it from that tut.
PHP Code:


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

    if(
FailState == TQUERY_CONNECT_FAILED) {
        
server_print" optional a specific error msg " ); 
    }
    
    else 
server_print" optional a ok msg ");

    if(
FailState == TQUERY_QUERY_FAILED) {
        
server_print" optional a specific error msg "); 
    }
    
    if(
Errcode) {
        
server_print(" optional a specific error msg "); 
    }
        
    return 
PLUGIN_CONTINUE; }

public 
SelectHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize) {
        
    if(
FailState == TQUERY_CONNECT_FAILED) {
        
server_print" optional a specific error msg " );
    }
    
    else {
    
    
server_print" optional a ok msg ");
    
    if(
FailState == TQUERY_QUERY_FAILED) {
        
server_print" optional a specific error msg "); 
    }
    
    else {
    
    if(
Errcode) {
        
server_print(" optional a specific error msg "); 
    }
        
    else {
        
        if (
SQL_NumResults(Query) >= 1) {
    
        new 
szQuery[256];
        
formatexszQuerycharsmaxszQuery ), "UPDATE your_table SET g_number = '%s'"authid);
        
SQL_ThreadQuery(g_SqlTuple,"QueryHandle",szQuery); }
        
        else {

        new 
szQuery1[256];
        
formatexszQuery1charsmaxszQuery1 ), "INSERT INTO your_table ( g_number ) VALUES ( '0' )");
        
SQL_ThreadQuery(g_SqlTuple,"QueryHandle",szQuery1);    
    
    } } } }

public 
client_putinserver(id) {

    
get_user_authid(id,authid,charsmax(authid)); // authid must be a static var! like static authid[32];

    
new szQuery[256];
    
formatexszQuerycharsmaxszQuery ), "SELECT g_number FROM your_table WHERE authid = '%s'"authid);
   
SQL_ThreadQuery(g_SqlTuple,"SelectHandle",szQuery);
    }

public 
client_disconnect(id) {

    
get_user_authid(id,authid1,charsmax(authid1)); // again authid var must be a static one.
    
new szQuery[256];
    
formatexszQuerycharsmaxszQuery ), "SELECT g_number FROM your_table WHERE authid = '%s'"authid1);
    
SQL_ThreadQuery(g_SqlTuple,"SelectHandle",szQuery); } 

And now from this example if you want you can do it without the sql threaded way by using sqlconnect but it's better with threaded - in case of a db error gameplay isn't affected at all.

misterx 06-13-2011 16:15

Re: SQL querys
 
Yeah, but why do I need this QueryHandle? And how can I convert a SELECT query result into array?

Clauu 06-13-2011 16:53

Re: SQL querys
 
Quote:

Originally Posted by misterx
Yeah, but why do I need this QueryHandle?

Quote:

Originally Posted by misterx
Yeah, I've read that.

No you don't.

misterx 06-14-2011 06:24

Re: SQL querys
 
I have. Maybe I should refrase it: how can DBI and also PHP do SQL querys without using queryhandles?

Clauu 06-14-2011 14:26

Re: SQL querys
 
sqlx without threaded query's
PHP Code:

    new g_Error[512],ErrorCode,Handle:SqlConnection SQL_Connect(g_SqlTuple,ErrorCode,g_Error,511);
    
    if(
SqlConnection == Empty_Handle) { 
        
server_print" optional a specific error msg " ); }
    
    else {
    
    
server_print" optional a ok msg ");
    
    new 
szQuery[256];
    
formatexszQuerycharsmaxszQuery ), "SELECT something FROM your_table");
    new 
Handle:Query SQL_PrepareQuery(SqlConnectionszQuery);
   
    if(!
SQL_Execute(Query)) { 
        
server_print(" optional a specific error msg "); }
    
    else if(
SQL_Execute(Query)) {
    
new 
something[32];
    
SQL_ReadResult(Query0somethingcharsmax(something));
    
    
SQL_FreeHandle(Query); SQL_FreeHandle(SqlConnection); 

or with dbi but as i said this is the old way
PHP Code:

    new Sql:dbcResult:result;
    
dbc dbi_connect(SQL_HOST,SQL_USER,SQL_PASS,SQL_DB,error,32);
    
    if (
dbc <= SQL_FAILED) {
        
server_print"optional a specific error msg"); }

    else {
        
    new 
sql[512];
    
format(sql,511,"SELECT something FROM your_table");
    
result dbi_query(dbc,sql);
    
    if (
result <= RESULT_FAILED) {
        
server_print(" optional a specific error msg "); }
 
   else {
    
    if (
result == RESULT_NONE) {
        
server_print(" optional a specific error msg "); }
    
   else {

    new 
something[32] = dbi_field(result1);
    
    
dbi_free_result(result); } } } 

Formatex is optional and the query can be used directly if there is no extra arguments.


All times are GMT -4. The time now is 23:34.

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