Raised This Month: $ Target: $400
 0% 

SQL querys


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
misterx
Member
Join Date: Jan 2011
Old 06-12-2011 , 16:04   SQL querys
Reply With Quote #1

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.
misterx is offline
Clauu
Senior Member
Join Date: Feb 2008
Location: RO
Old 06-12-2011 , 16:49   Re: SQL querys
Reply With Quote #2

A great tut for sqlx - https://forums.alliedmods.net/showthread.php?t=46779
Clauu is offline
misterx
Member
Join Date: Jan 2011
Old 06-12-2011 , 17:11   Re: SQL querys
Reply With Quote #3

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 is offline
misterx
Member
Join Date: Jan 2011
Old 06-13-2011 , 13:28   Re: SQL querys
Reply With Quote #4

Forgot to say: I'd prefer a DBI solutions as it seems more light-weight.
misterx is offline
Clauu
Senior Member
Join Date: Feb 2008
Location: RO
Old 06-13-2011 , 14:17   Re: SQL querys
Reply With Quote #5

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.

Last edited by Clauu; 06-13-2011 at 15:58.
Clauu is offline
misterx
Member
Join Date: Jan 2011
Old 06-13-2011 , 16:15   Re: SQL querys
Reply With Quote #6

Yeah, but why do I need this QueryHandle? And how can I convert a SELECT query result into array?
misterx is offline
Clauu
Senior Member
Join Date: Feb 2008
Location: RO
Old 06-13-2011 , 16:53   Re: SQL querys
Reply With Quote #7

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.
Clauu is offline
misterx
Member
Join Date: Jan 2011
Old 06-14-2011 , 06:24   Re: SQL querys
Reply With Quote #8

I have. Maybe I should refrase it: how can DBI and also PHP do SQL querys without using queryhandles?
misterx is offline
Clauu
Senior Member
Join Date: Feb 2008
Location: RO
Old 06-14-2011 , 14:26   Re: SQL querys
Reply With Quote #9

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.
Clauu is offline
Reply



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 23:34.


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