Problemă salvare în MySQL

[burnyourfeelings]

Am urmat tutorialul acesta ( https://forums.alliedmods.net/showthread.php?t=132686 ) pentru a crea un plugin de puncte însă e o problemă la salvarea punctelor, dacă un jucător se conectează de mai multe ori pe server îi adaugi de mai multe ori steam id-ul acestuia. Problema cred că ar fi de la :

Code: Select all

public Save_MySql(id)
{
    new szSteamId[32], szName[32], szTemp[512]
    get_user_authid(id, szSteamId, charsmax(szSteamId))
    
    // Here we will update the user hes information in the database where the steamid matches.
    format(szTemp,charsmax(szTemp),"UPDATE `tutorial` SET `exp` = '%i' WHERE `tutorial`.`steamid` = '%s';",iExp[id], szSteamId)
    SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)
}

dar nu prea am idee cum să rezolv, cred că ignoră funcția

Code: Select all

SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)

[JaiLBreaK]

Problema nu este de la UPDATE, sigur ai tu insert de mai multe ori. Lasa sursa completa

[The Kalu]

Trebuie definita o cheie primara in mysql la tabelul tutorial altfel insereaza de mai multe ori aceleasi valori.

[burnyourfeelings]

Trebuie definita o cheie primara in mysql la tabelul tutorial altfel insereaza de mai multe ori aceleasi valori.

Prima oară m-am gândit la lucrul ăsta ( dar degeaba )

Codul este exact ca în tutorial doar că am încercat să editez cu remedierile din topic însă degeaba, problema tot persistă.

Code: Select all

#include <amxmodx>
#include <sqlx>

#define PLUGIN "Tutorial"
#define VERSION "1.0"
#define AUTHOR "Grim"

// Ur Mysql Information
new Host[]     = "hostname"
new User[]    = "username"
new Pass[]     = "password"
new Db[]     = "database"


new Handle:g_SqlTuple
new g_Error[512]


new iExp[33]

// Pcvar's
new cKill
new cHeadshot
new cDeath

public plugin_init() {
    register_plugin(PLUGIN, VERSION, AUTHOR)
    
    register_event("DeathMsg", "Event_DeathMsg", "a") // Register death event
    
    // register the Pcvar's
    cKill = register_cvar("exp_kill", "2")
    cHeadshot = register_cvar("exp_headshot", "4")
    cDeath = register_cvar("exp_death", "-1")
    
    set_task(1.0, "MySql_Init") // set a task to activate the mysql_init
}

public MySql_Init()
{
    // we tell the API that this is the information we want to connect to,
    // just not yet. basically it's like storing it in global variables
    g_SqlTuple = SQL_MakeDbTuple(Host,User,Pass,Db)
   
    // ok, we're ready to connect
    new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,charsmax(g_Error))
    if(SqlConnection == Empty_Handle)
        // stop the plugin with an error message
        set_fail_state(g_Error)
       
    new Handle:Queries
    // we must now prepare some random queries
    Queries = SQL_PrepareQuery(SqlConnection,"CREATE TABLE IF NOT EXISTS points (steamid varchar(32),points INT(11))")

    if(!SQL_Execute(Queries))
    {
        // if there were any problems
        SQL_QueryError(Queries,g_Error,charsmax(g_Error))
        set_fail_state(g_Error)
       
    }
    
    // close the handle
    SQL_FreeHandle(Queries)
   
    // you free everything with SQL_FreeHandle
    SQL_FreeHandle(SqlConnection)   
}

public plugin_end()
{
    // free the tuple - note that this does not close the connection,
    // since it wasn't connected in the first place
    SQL_FreeHandle(g_SqlTuple)
}

public Load_MySql(id)
{
    new szSteamId[32], szTemp[512]
    get_user_authid(id, szSteamId, charsmax(szSteamId))
    
    new Data[1]
    Data[0] = id
    
    //we will now select from the table `points` where the steamid match
    format(szTemp,charsmax(szTemp),"SELECT * FROM `points` WHERE (`points`.`steamid` = '%s')", szSteamId)
    SQL_ThreadQuery(g_SqlTuple,"register_client",szTemp,Data,1)
} 

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_NumResults(Query) < 1) 
    {
        //.if there are no results found
        
        new szSteamId[32]
        get_user_authid(id, szSteamId, charsmax(szSteamId)) // get user's steamid
        
        //  if its still pending we can't do anything with it
        if (equal(szSteamId,"ID_PENDING"))
            return PLUGIN_HANDLED
            
        new szTemp[512]
        
        // now we will insturt the values into our table.
        format(szTemp,charsmax(szTemp),"INSERT INTO `points` ( `steamid` , `points`)VALUES ('%s','0');",szSteamId)
        SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)
    } 
    else 
    {
        // if there are results found
        iExp[id]         = SQL_ReadResult(Query, 1)
    }
    return PLUGIN_HANDLED
} 


public Save_MySql(id)
{
    new szSteamId[32], szName[32], szTemp[512]
    get_user_authid(id, szSteamId, charsmax(szSteamId))
    
    // Here we will update the user hes information in the database where the steamid matches.
    format(szTemp,charsmax(szTemp),"UPDATE `points` SET `points` = '%i' WHERE `points`.`steamid` = '%s';",iExp[id], szSteamId)
    SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)
}

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

public client_putinserver(id)
{
    Load_MySql(id)
}

public client_disconnect(id)
{
    Save_MySql(id)
}

public Event_DeathMsg()
{
    new iKiller = read_data(1) // read the data to get the killer and victim
    new iVictim = read_data(2)
    
    if(is_user_alive(iKiller)) // Check if the killer is alive in case he killed himself
    {
        if(read_data(3))
        {
            iExp[iKiller] += get_pcvar_num(cHeadshot) // Add the amount of the Pcvar to iExp
        }
        else
        {
            iExp[iKiller] += get_pcvar_num(cKill)
        }
    }
    iExp[iVictim] -= get_pcvar_num(cDeath) // Decrease the amount of the Pcvar from iExp
} 

[The Kalu]

Code: Select all

CREATE TABLE IF NOT EXISTS points (id INT NOT NULL AUTO_INCREMENT, steamid VARCHAR (32) NOT NULL, points INT, PRIMARY KEY (id))

Code: Select all

SELECT id, steamid, points FROM points WHERE steamid = '%s'

Code: Select all

UPDATE points SET points = '%i' WHERE steamid = '%s'

Inlocuiesti si revino cu un raspuns.

[PedoBearJB]

Salut am incercat sa fac sa functioneze dar a aparut o problema, stocheaza pe mapa cand faci kill, dar daca schimbi mapa / dai retry si intri si faci kill ti le reseteaza si apoi iti da 0+numarul de puncte, nu continua sa stocheze mai departe de unde a ramas.

@The Kalu @JaiLBreaK idei?

Code: Select all

#include <amxmodx>
#include <sqlx>

#define PLUGIN "Tutorial"
#define VERSION "1.0"
#define AUTHOR "Grim"

// Ur Mysql Information
new Host[]     = ""
new User[]    = ""
new Pass[]     = ""
new Db[]     = ""


new Handle:g_SqlTuple
new g_Error[512]


new iExp[33]

// Pcvar's
new cKill
new cHeadshot
new cDeath

public plugin_init() {
    register_plugin(PLUGIN, VERSION, AUTHOR)
    
    register_event("DeathMsg", "Event_DeathMsg", "a") // Register death event
    
    // register the Pcvar's
    cKill = register_cvar("exp_kill", "2")
    cHeadshot = register_cvar("exp_headshot", "4")
    cDeath = register_cvar("exp_death", "-1")
    
    set_task(1.0, "MySql_Init") // set a task to activate the mysql_init
}

public MySql_Init()
{
    // we tell the API that this is the information we want to connect to,
    // just not yet. basically it's like storing it in global variables
    g_SqlTuple = SQL_MakeDbTuple(Host,User,Pass,Db)
   
    // ok, we're ready to connect
    new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,charsmax(g_Error))
    if(SqlConnection == Empty_Handle)
        // stop the plugin with an error message
        set_fail_state(g_Error)
       
    new Handle:Queries
    // we must now prepare some random queries
    Queries = SQL_PrepareQuery(SqlConnection,"CREATE TABLE IF NOT EXISTS points (id INT NOT NULL AUTO_INCREMENT, steamid VARCHAR (32) NOT NULL, points INT, PRIMARY KEY (id))")

    if(!SQL_Execute(Queries))
    {
        // if there were any problems
        SQL_QueryError(Queries,g_Error,charsmax(g_Error))
        set_fail_state(g_Error)
       
    }
    
    // close the handle
    SQL_FreeHandle(Queries)
   
    // you free everything with SQL_FreeHandle
    SQL_FreeHandle(SqlConnection)   
}

public plugin_end()
{
    // free the tuple - note that this does not close the connection,
    // since it wasn't connected in the first place
    SQL_FreeHandle(g_SqlTuple)
}

public Load_MySql(id)
{
    new szSteamId[32], szTemp[512]
    get_user_authid(id, szSteamId, charsmax(szSteamId))
    
    new Data[1]
    Data[0] = id
    
    //we will now select from the table `points` where the steamid match
    format(szTemp,charsmax(szTemp),"SELECT id, steamid, points FROM points WHERE steamid = '%s'", szSteamId)
    SQL_ThreadQuery(g_SqlTuple,"register_client",szTemp,Data,1)
} 

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_NumResults(Query) < 1) 
    {
        //.if there are no results found
        
        new szSteamId[32]
        get_user_authid(id, szSteamId, charsmax(szSteamId)) // get user's steamid
        
        //  if its still pending we can't do anything with it
        if (equal(szSteamId,"ID_PENDING"))
            return PLUGIN_HANDLED
            
        new szTemp[512]
        
        // now we will insturt the values into our table.
        format(szTemp,charsmax(szTemp),"INSERT INTO `points` ( `steamid` , `points`)VALUES ('%s','0');",szSteamId)
        SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)
    } 
    else 
    {
        // if there are results found
        iExp[id]         = SQL_ReadResult(Query, 1)
    }
	SQL_FreeHandle(Query)
    return PLUGIN_HANDLED
} 


public Save_MySql(id)
{
    new szSteamId[32], szName[32], szTemp[512]
    get_user_authid(id, szSteamId, charsmax(szSteamId))
    
    // Here we will update the user hes information in the database where the steamid matches.
    format(szTemp,charsmax(szTemp),"UPDATE points SET points = '%i' WHERE steamid = '%s';",iExp[id], szSteamId)
    SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)
}

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

public client_putinserver(id)
{
    Load_MySql(id)
}

public client_disconnect(id)
{
    Save_MySql(id)
}

public Event_DeathMsg()
{
    new iKiller = read_data(1) // read the data to get the killer and victim
    new iVictim = read_data(2)
    
    if(is_user_alive(iKiller)) // Check if the killer is alive in case he killed himself
    {
        if(read_data(3))
        {
            iExp[iKiller] += get_pcvar_num(cHeadshot) // Add the amount of the Pcvar to iExp
        }
        else
        {
            iExp[iKiller] += get_pcvar_num(cKill)
        }
		Save_MySql(iKiller)
    }
    iExp[iVictim] -= get_pcvar_num(cDeath) // Decrease the amount of the Pcvar from iExp
} 

Si am adaugat aici [Save_MySql(iKiller)] ca sa stocheze imediat:

Code: Select all

public Event_DeathMsg()
{
    new iKiller = read_data(1) // read the data to get the killer and victim
    new iVictim = read_data(2)
    
    if(is_user_alive(iKiller)) // Check if the killer is alive in case he killed himself
    {
        if(read_data(3))
        {
            iExp[iKiller] += get_pcvar_num(cHeadshot) // Add the amount of the Pcvar to iExp
        }
        else
        {
            iExp[iKiller] += get_pcvar_num(cKill)
        }
    }
    iExp[iVictim] -= get_pcvar_num(cDeath) // Decrease the amount of the Pcvar from iExp
}

->>

Code: Select all

public Event_DeathMsg()
{
    new iKiller = read_data(1) // read the data to get the killer and victim
    new iVictim = read_data(2)
    
    if(is_user_alive(iKiller)) // Check if the killer is alive in case he killed himself
    {
        if(read_data(3))
        {
            iExp[iKiller] += get_pcvar_num(cHeadshot) // Add the amount of the Pcvar to iExp
        }
        else
        {
            iExp[iKiller] += get_pcvar_num(cKill)
        }
		Save_MySql(iKiller)
    }
    iExp[iVictim] -= get_pcvar_num(cDeath) // Decrease the amount of the Pcvar from iExp
}

[The Kalu]

Pai este si normal sa reseteze pentru ca la INSERT nu faci verificare sa vezi daca exista un baza de date, de asta.

[PedoBearJB]

Pai este si normal sa reseteze pentru ca la INSERT nu faci verificare sa vezi daca exista un baza de date, de asta.

Pai si mai exact ce trebuie adaugat in cacatu asta de mysql ca sunt paralel cu el :))

[Shadows Adi]

Verifici in tabelul din baza de date dacă există deja intrarea, dacă nu există, creezi una, dacă există, i se atribuie jucătorului datele găsite în coloana respectivă.
Edit: dacă ești Steam off, exista posibilitatea sa tinde atribuie un steamid nou la fiecare conectare pentru că salvarea / încărcarea datelor se face pe baza steamid-ului.

[ZackChoi]

Code: Select all

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

	new GlobalQuery[256];
	
	switch(SaveMode)
	{
		case 0:	formatex(GlobalQuery, charsmax(GlobalQuery), "UPDATE SQL_Points SET PlayerPoints = '%d' WHERE PlayerName = '%s'", Points[id], PlayerName[id]);
		case 1:	formatex(GlobalQuery, charsmax(GlobalQuery), "UPDATE SQL_Points SET PlayerPoints = '%d' WHERE PlayerSteamID = '%s'", Points[id], PlayerSteamID[id]);
	}

	SQL_ThreadQuery(SQLTuple, "SQL_Ignore", GlobalQuery);
}

poti incerca si asta:

Code: Select all

if(SQLTuple == INVALID_HANDLE) server_print("debug: invalid sql tuple handle");

si un save-load complet functional

Code: Select all

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

	new GlobalQuery[256], Data[1];	Data[0] = id;
	get_user_name(id, PlayerName[id], charsmax(PlayerName));
	get_user_authid(id, PlayerSteamID[id], charsmax(PlayerSteamID));

	switch(SaveMode)
	{
		case 0:	formatex(GlobalQuery, charsmax(GlobalQuery), "SELECT * FROM SQL_Points WHERE PlayerName = '%s' LIMIT 1", PlayerName[id]);
		case 1:	formatex(GlobalQuery, charsmax(GlobalQuery), "SELECT * FROM SQL_Points WHERE PlayerSteamID = '%s' LIMIT 1", PlayerSteamID[id]);
	}

	SQL_ThreadQuery(SQLTuple, "SQL_Load", GlobalQuery, Data, 1);
}

public SQL_Load(FailState, Handle:Query, Error[], Errcode, Data[], DataSize) {
	if(FailState == TQUERY_CONNECT_FAILED)	log_amx("Load - Could not connect to SQL DB [%d] %s", Errcode, Error);
	else if(FailState == TQUERY_QUERY_FAILED)	log_amx("Load Query Failed [%d] %s", Errcode, Error);
	else if(Errcode)	server_print("Error on query: %s", Error);

	new id = Data[0];

	if(SQL_NumResults(Query) < 1) {
		new a[256];
		formatex(a, charsmax(a), "INSERT INTO `SQL_Points` (`PlayerName`, `PlayerSteamID`, `PlayerPoints`) VALUES ('%s', '%s', '0')", PlayerName[id], PlayerSteamID[id]);
		SQL_ThreadQuery(SQLTuple, "SQL_Ignore", a);
	}
	else
	{
		Points[id] = SQL_ReadResult(Query, 2);
	}
}

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

	new GlobalQuery[256];
	
	switch(SaveMode)
	{
		case 0:	formatex(GlobalQuery, charsmax(GlobalQuery), "UPDATE SQL_Points SET PlayerPoints = '%d' WHERE PlayerName = '%s'", Points[id], PlayerName[id]);
		case 1:	formatex(GlobalQuery, charsmax(GlobalQuery), "UPDATE SQL_Points SET PlayerPoints = '%d' WHERE PlayerSteamID = '%s'", Points[id], PlayerSteamID[id]);
	}

	SQL_ThreadQuery(SQLTuple, "SQL_Ignore", GlobalQuery);
}

// SQL Ignore
public SQL_Ignore(FailState, Handle:Query, Error[], Errcode, Data[], DataSize)
{
	if(FailState == TQUERY_CONNECT_FAILED)	log_amx("Load - Could not connect to SQL DB [%d] %s", Errcode, Error);
	else if(FailState == TQUERY_QUERY_FAILED)	log_amx("Load Query Failed [%d] %s", Errcode, Error);
	else if(Errcode)	server_print("Error on query: %s", Error);

	SQL_FreeHandle(SQLTuple);
}

[Xeno985]

Code: Select all

CREATE TABLE IF NOT EXISTS points (id INT NOT NULL AUTO_INCREMENT, steamid VARCHAR (32) NOT NULL, points INT, PRIMARY KEY (id))

Code: Select all

SELECT id, steamid, points FROM points WHERE steamid = '%s'

Code: Select all

UPDATE points SET points = '%i' WHERE steamid = '%s'

Inlocuiesti si revino cu un raspuns.

Pentru a NU reseta punctele, mai putem face si asa

Code: Select all

UPDATE points SET points = points + '%i' WHERE steamid = '%s'

Adica updatam coloana points cu punctele curente + celelalte adunate

P.S.: Kalu, ti-am dat tie quote ca ai scris ok astea si este mai usor

[EnTeR_]

Code: Select all

CREATE TABLE IF NOT EXISTS points (id INT NOT NULL AUTO_INCREMENT, steamid VARCHAR (32) NOT NULL, points INT, PRIMARY KEY (id))

Code: Select all

SELECT id, steamid, points FROM points WHERE steamid = '%s'

Code: Select all

UPDATE points SET points = '%i' WHERE steamid = '%s'

Inlocuiesti si revino cu un raspuns.

Pentru a NU reseta punctele, mai putem face si asa

Code: Select all

UPDATE points SET points = points + '%i' WHERE steamid = '%s'

Adica updatam coloana points cu punctele curente + celelalte adunate

P.S.: Kalu, ti-am dat tie quote ca ai scris ok astea si este mai usor

Păi si nu o sa le dubleze ? "points" = punctele salvate + variabila care retine numărul total.
Iar daca consum toate punctele si ma reconectez pe sv o sa le am inapoi. Depinde la ce event se face salvarea, dar oricum nu e "safe".