Sql query problem

benjibau · **Author**

Hi all i have read the tutoral of grimvh2 (https://forums.alliedmods.net/showthread.php?t=132686) for create a save mysql and i have an error for load.

L 07/21/2010 - 19

7:54: [RP_sqlatest.amxx] Load Query failed. [1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'STEAM_0:0:15001085 0 0 0 0 0 0' at line 1.

When i change map can't save and load the stats. Can you help me for correct him please it's for creates web stats.

PHP Code:


			
#define MAX_PLAYERS 32

new g_PlayerJob[MAX_PLAYERS+1]
new g_PlayerMoney[MAX_PLAYERS+1]
new g_PlayerMaison[MAX_PLAYERS+1]
new g_PlayerVoiture[MAX_PLAYERS+1]
new g_PlayerHasPermis[MAX_PLAYERS+1]
new g_PlayerIsVip[MAX_PLAYERS+1]
new c_MySqlInfo[SqlInfo]
new Handle:g_SqlTuple
new g_Error[512]

new Host[]     = "hostname"
new User[]    = "username"
new Pass[]     = "password"
new Db[]     = "database"

public plugin_init()
{
    set_task(1.0, "MySql_Init")
}

public MySql_Init()
{
     g_SqlTuple = SQL_MakeDbTuple(Host,User,Pass,Db)
   
    new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,charsmax(g_Error))
    if(SqlConnection == Empty_Handle)

    set_fail_state(g_Error)
    new Handle:Queries
    Queries = SQL_PrepareQuery(SqlConnection,"CREATE TABLE IF NOT EXISTS tutorial (steamid varchar(32),exp INT(11))")

    if(!SQL_Execute(Queries))
    {
        SQL_QueryError(Queries,g_Error,charsmax(g_Error))
        set_fail_state(g_Error)   
    }
    
    SQL_FreeHandle(Queries)
    SQL_FreeHandle(SqlConnection)   
}

public plugin_end()
{
    SQL_FreeHandle(g_SqlTuple)
}

public client_putinserver(id)
{
    Load_MySql(id)
}

public client_disconnect( id )
{
    Save_MySql(id)
}

public Load_MySql(id)
{
    new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,charsmax(g_Error))
    
    if(g_SqlTuple == Empty_Handle)
        set_fail_state(g_Error)
        
    new szSteamId[32], szTemp[512]
    get_user_authid(id, szSteamId, charsmax(szSteamId))
    
    new Data[1]
    Data[0] = id
    
    format(szTemp,charsmax(szTemp),"%s %d %d %d %d %d %d",szSteamId, g_PlayerJob[id], g_PlayerMoney[id], g_PlayerMaison[id], g_PlayerVoiture[id], g_PlayerHasPermis[id], g_PlayerIsVip[id])
    SQL_ThreadQuery(g_SqlTuple,"register_client",szTemp,Data,1)
    
    SQL_FreeHandle(SqlConnection)
}

public register_client(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(FailState == TQUERY_CONNECT_FAILED)
    {
        log_amx("Load - Could not connect to SQL database.  [%d] %s", Errcode, Error)
    }
    else if(FailState == TQUERY_QUERY_FAILED)
    {
        log_amx("Load Query failed. [%d] %s", Errcode, Error)
    }
    new id
    id = Data[0]
    
    if(!SQL_MoreResults(Query)) 
    {
        
        new szSteamId[32]
        get_user_authid(id, szSteamId, charsmax(szSteamId))
        
        if (equal(szSteamId,"ID_PENDING"))
            return PLUGIN_HANDLED;
            
        new szTemp[512]
        
        new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,charsmax(g_Error))
        if(g_SqlTuple == Empty_Handle)
            set_fail_state(g_Error)
        
        format(szTemp,charsmax(szTemp),"%s %d %d %d %d %d %d",szSteamId, g_PlayerJob[id], g_PlayerMoney[id], g_PlayerMaison[id], g_PlayerVoiture[id], g_PlayerHasPermis[id], g_PlayerIsVip[id])
        SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)
        SQL_FreeHandle(SqlConnection)
    } 
    else 
    {
        g_PlayerJob[id] = SQL_ReadResult(Query, 1)
        g_PlayerMoney[id] = SQL_ReadResult(Query, 2)
        g_PlayerMaison[id] = SQL_ReadResult(Query, 3)
        g_PlayerVoiture[id] = SQL_ReadResult(Query, 4)
        g_PlayerHasPermis[id] = SQL_ReadResult(Query, 5)
        g_PlayerIsVip[id] = SQL_ReadResult(Query, 6)
    }
    return PLUGIN_CONTINUE;
}  


public IgnoreHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    SQL_FreeHandle(Query)
    return PLUGIN_HANDLED;
}

public Save_MySql(id)
{
    new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,511)
    if(g_SqlTuple == Empty_Handle)
        set_fail_state(g_Error)
   
    new szSteamId[32], szName[32], szTemp[512]
    get_user_authid(id, szSteamId, charsmax(szSteamId))
    get_user_name(id, szName, charsmax(szName))
    
    format(szTemp,charsmax(szTemp),"%s %s %d %d %d %d %d %d",szName, szSteamId, g_PlayerJob[id], g_PlayerMoney[id], g_PlayerMaison[id], g_PlayerVoiture[id], g_PlayerHasPermis[id], g_PlayerIsVip[id])
    SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)
    SQL_FreeHandle(SqlConnection)
}

grimvh2 · 07-21-2010 , 17:59 Re: Sql query problem

PHP Code:


			
Queries = SQL_PrepareQuery(SqlConnection,"CREATE TABLE IF NOT EXISTS tutorial (steamid varchar(32),exp INT(11))")

that failed, look carefully

benjibau · 07-22-2010 , 11:46 Re: Sql query problem

Quote:

Originally Posted by grimvh2

PHP Code:


			
Queries = SQL_PrepareQuery(SqlConnection,"CREATE TABLE IF NOT EXISTS tutorial (steamid varchar(32),exp INT(11))")

that failed, look carefully

Thank but how use this because in the code you add (number) i have seach in the forum but no succes.

grimvh2 · 07-22-2010 , 16:09 Re: Sql query problem

you have to add the fields at create table, there all INT(11) ur not using strings except steamid, and at loading start with 1 at SQL_ReadResult.

benjibau · 07-23-2010 , 04:36 Re: Sql query problem

like this ?

PHP Code:


			
#define MAX_PLAYERS 32

new g_PlayerJob[MAX_PLAYERS+1]
new g_PlayerMoney[MAX_PLAYERS+1]
new g_PlayerMaison[MAX_PLAYERS+1]
new g_PlayerVoiture[MAX_PLAYERS+1]
new g_PlayerHasPermis[MAX_PLAYERS+1]
new g_PlayerIsVip[MAX_PLAYERS+1]
new c_MySqlInfo[SqlInfo]
new Handle:g_SqlTuple
new g_Error[512]

new Host[]     = "hostname"
new User[]    = "username"
new Pass[]     = "password"
new Db[]     = "database"

public plugin_init()
{
    set_task(1.0, "MySql_Init")
}

public MySql_Init()
{
    g_SqlTuple = SQL_MakeDbTuple(Host,User,Pass,Db)
    
    new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,charsmax(g_Error))
    if(SqlConnection == Empty_Handle)
    {
        set_fail_state(g_Error)
    }
    new Handle:Queries
    Queries = SQL_PrepareQuery(SqlConnection,"CREATE TABLE IF NOT EXISTS (steamid varchar(32), job INT(11), money INT(11), house INT(11), car INT(11), permis INT(11), vip INT(11))")
    
    if(!SQL_Execute(Queries))
    {
        SQL_QueryError(Queries,g_Error,charsmax(g_Error))
        set_fail_state(g_Error)   
    }
    
    SQL_FreeHandle(Queries)
    SQL_FreeHandle(SqlConnection)   
}  

public Load_MySql(id)
{
    new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,charsmax(g_Error))
    
    if(g_SqlTuple == Empty_Handle)
        set_fail_state(g_Error)
    
    new szSteamId[32], szTemp[512]
    get_user_authid(id, szSteamId, charsmax(szSteamId))
    
    new Data[1]
    Data[0] = id
    
    format(szTemp,charsmax(szTemp),"`steamid` = '%s' . `job` = '%d' . `money` = '%d' . `house` = '%d' . `car` = '%d' . `permis` = '%d' . `vip` = '%d';", szSteamId, g_PlayerJob[id], g_PlayerMoney[id], g_PlayerMaison[id], g_PlayerVoiture[id], g_PlayerHasPermis[id], g_PlayerIsVip[id])
    SQL_ThreadQuery(g_SqlTuple,"register_client",szTemp,Data,1)
    
    SQL_FreeHandle(SqlConnection)
}

public register_client(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(FailState == TQUERY_CONNECT_FAILED)
    {
        log_amx("Load - Could not connect to SQL database.  [%d] %s", Errcode, Error)
    }
    else if(FailState == TQUERY_QUERY_FAILED)
    {
        log_amx("Load Query failed. [%d] %s", Errcode, Error)
    }
    new id
    id = Data[0]
    
    if(!SQL_MoreResults(Query)) 
    {
        
        new szSteamId[32]
        get_user_authid(id, szSteamId, charsmax(szSteamId))
        
        if (equal(szSteamId,"ID_PENDING"))
            return PLUGIN_HANDLED;
        
        new szTemp[512]
        
        new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,charsmax(g_Error))
        if(g_SqlTuple == Empty_Handle)
            set_fail_state(g_Error)
        
        format(szTemp,charsmax(szTemp),"(`steamid`, `job`, `money`, `house`, `car`, `permis`, `vip`)VALUES( '%s', 'Chomeur', '0', '0', '0', '0', '0')", szSteamId, g_PlayerJob[id], g_PlayerMoney[id], g_PlayerMaison[id], g_PlayerVoiture[id], g_PlayerHasPermis[id], g_PlayerIsVip[id])
        SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)
        SQL_FreeHandle(SqlConnection)
    } 
    else 
    {
        g_PlayerJob[id] = SQL_ReadResult(Query, 1)
        g_PlayerMoney[id] = SQL_ReadResult(Query, 2)
        g_PlayerMaison[id] = SQL_ReadResult(Query, 3)
        g_PlayerVoiture[id] = SQL_ReadResult(Query, 4)
        g_PlayerHasPermis[id] = SQL_ReadResult(Query, 5)
        g_PlayerIsVip[id] = SQL_ReadResult(Query, 6)
    }
    return PLUGIN_CONTINUE;
}  


public IgnoreHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    SQL_FreeHandle(Query)
    return PLUGIN_HANDLED;
}

public Save_MySql(id)
{
    new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,511)
    if(g_SqlTuple == Empty_Handle)
        set_fail_state(g_Error)
    
    new szSteamId[32], szName[32], szTemp[512]
    get_user_authid(id, szSteamId, charsmax(szSteamId))
    get_user_name(id, szName, charsmax(szName))
    
    format(szTemp,charsmax(szTemp),"%s %s SET `job` = '%d' `money` = '%d' `house` = '%d' `car` = '%d' `permis` = '%d' `vip` = '%d';",szName, szSteamId, g_PlayerJob[id], g_PlayerMoney[id], g_PlayerMaison[id], g_PlayerVoiture[id], g_PlayerHasPermis[id], g_PlayerIsVip[id])
    SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)
    SQL_FreeHandle(SqlConnection);
}

grimvh2 · 07-23-2010 , 05:19 Re: Sql query problem

"CREATE TABLE IF NOT EXISTS urtablename (steamid...))"

benjibau · 07-23-2010 , 14:40 Re: Sql query problem

update it work but i have a litle probleme when i change map the variable not load

grimvh2 · 07-23-2010 , 17:40 Re: Sql query problem

PHP Code:


			
 format(szTemp,charsmax(szTemp),"`steamid` = '%s' . `job` = '%d' . `money` = '%d' . `house` = '%d' . `car` = '%d' . `permis` = '%d' . `vip` = '%d';", szSteamId, g_PlayerJob[id], g_PlayerMoney[id], g_PlayerMaison[id], g_PlayerVoiture[id], g_PlayerHasPermis[id], g_PlayerIsVip[id])

from a "," u made a "." please look more carefull next time.

Peoples Army · 07-23-2010 , 19:47 Re: Sql query problem

Good place to learn some MYSQL basics

http://www.mysqltutorial.org/

Heres another one

http://www.tutorialspoint.com/mysql/...troduction.htm

benjibau · 07-25-2010 , 10:40 Re: Sql query problem

PHP Code:


			
public client_authorized( id )
{
    Load_MySql(id)
}

public client_disconnect( id )
{
    Save_MySql(id)
}

public MySql_Init()
{
    g_SqlTuple = SQL_MakeDbTuple(Host,User,Pass,Db)
    
    new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,charsmax(g_Error))
    if(SqlConnection == Empty_Handle)
    {
        set_fail_state(g_Error)
    }
    new Handle:Queries
    Queries = SQL_PrepareQuery(SqlConnection,"CREATE TABLE IF NOT EXISTS roleplay (name VARCHAR(35), steamid varchar(32), job TEXT(11), money INT(11), house INT(11), voiture INT(11), permis INT(11), vip INT(11))")
    
    if(!SQL_Execute(Queries))
    {
        SQL_QueryError(Queries,g_Error,charsmax(g_Error))
        set_fail_state(g_Error)   
    }
    
    SQL_FreeHandle(Queries)
    SQL_FreeHandle(SqlConnection)   
}

public Load_MySql(id)
{
    new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,charsmax(g_Error))
    
    if(g_SqlTuple == Empty_Handle)
        set_fail_state(g_Error)
    
    new szSteamId[32], szTemp[512]
    get_user_authid(id, szSteamId, charsmax(szSteamId))
    
    new Data[1]
    Data[0] = id
    
    format(szTemp,charsmax(szTemp),"SELECT`steamid` = '%s' . `job` = '%d' . `money` = '%d' . `house` = '%d' . `car` = '%d' . `permis` = '%d' . `vip` = '%d' FROM `roleplay` WHERE `steamid` = '%s';", g_PlayerJob[id], g_PlayerMoney[id], g_PlayerMaison[id], g_PlayerVoiture[id], g_PlayerHasPermis[id], g_PlayerIsVip[id], szSteamId)  
    SQL_ThreadQuery(g_SqlTuple,"register_client",szTemp,Data,1)
    SQL_FreeHandle(SqlConnection)
}

public register_client(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(FailState == TQUERY_CONNECT_FAILED)
    {
        log_amx("Load - Could not connect to SQL database.  [%d] %s", Errcode, Error)
    }
    else if(FailState == TQUERY_QUERY_FAILED)
    {
        log_amx("Load Query failed. [%d] %s", Errcode, Error)
    }
    new id = Data[0]
    
    if(SQL_NumResults(Query) < 1) 
    {       
        new szSteamId[32]
        get_user_authid(id, szSteamId, charsmax(szSteamId))
        
        if (equal(szSteamId,"ID_PENDING"))
            return PLUGIN_HANDLED;
        
        new szTemp[512]
        
        new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,charsmax(g_Error))
        if(g_SqlTuple == Empty_Handle)
            set_fail_state(g_Error)
        
        new name[33]
        get_user_name(id, name, 32)
        format(szTemp,charsmax(szTemp),"INSERT INTO `roleplay`(`name`, `steamid`, `job`, `money`, `house`, `voiture`, `permis`,  `vip`) VALUES ('%s', '%s', '%d', '0', '0', '0', '0', '0')", name,  szSteamId, g_PlayerJob[id], g_PlayerMoney[id], g_PlayerMaison[id],  g_PlayerVoiture[id], g_PlayerHasPermis[id], g_PlayerIsVip[id])
        SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)
        SQL_FreeHandle(SqlConnection)
    } 
    else 
    {
        g_PlayerJob[id] = SQL_ReadResult(Query, 1)
        g_PlayerMoney[id] = SQL_ReadResult(Query, 2)
        g_PlayerMaison[id] = SQL_ReadResult(Query, 3)
        g_PlayerVoiture[id] = SQL_ReadResult(Query, 4)
        g_PlayerHasPermis[id] = SQL_ReadResult(Query, 5)
        g_PlayerIsVip[id] = SQL_ReadResult(Query, 6)
    }
    return PLUGIN_CONTINUE;
}  

public IgnoreHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    SQL_FreeHandle(Query)
    return PLUGIN_HANDLED;
}

public Save_MySql(id)
{
    new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,511)
    if(g_SqlTuple == Empty_Handle)
        set_fail_state(g_Error)
    
    new szSteamId[32], szTemp[512]
    get_user_authid(id, szSteamId, charsmax(szSteamId))
    
    format(szTemp,charsmax(szTemp),"UPDATE `roleplay` SET `job` = '%d' . `money` = '%d' . `house` = '%d' . `voiture` = '%d' . `permis` = '%d' . `vip` =  '%d' FROM `roleplay` WHERE `steamid` = '%s';", szSteamId, g_PlayerJob[id],  g_PlayerMoney[id], g_PlayerMaison[id], g_PlayerVoiture[id],  g_PlayerHasPermis[id], g_PlayerIsVip[id])
    SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)
    SQL_FreeHandle(SqlConnection);
}

In my phpadmin the table apear but the value not change and yet with change map.
In my console the error no longer appear.

Sorry for my bad english i'm french