[jonatat]

Hello all. I have simple SQL request from server to save player data into mysql. Like kills, deaths, steam_id and etc. Then player join in the server, made some kills SQL not update existing table row, but creating a new one without steam id, nickname and etc... Whats wrong? My code:

PHP Code:

public SavePlayer(id)
{
if(!pLoaded[id]) 
return PLUGIN_HANDLED;

if(pPoints[id] < 0)
pPoints[id] = 0;

new Name[64], steam[64];
get_user_name(id, Name, 63);

get_user_authid(id, steam, 63);

static szQuery[3800];
formatex( szQuery, 3799, "REPLACE INTO `users` (`steam_id`, `player_name`, `kills`, `deaths`, `headshots`, `aces`, `m_aces`, `mix_played`, `mix_lost`, `mix_won`, `mix_draw`, `points`, `last_online`) VALUES ('%s', '%s', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', NOW());", steam, GetSecureName(Name), pKills[id], pDeaths[id], pHeadshots[id], pAces[id], pMiniAces[id], pMixPlayed[id], pMixLost[id], pMixWon[id], pMixDraw[id], pPoints[id]);
#if AMXX_VERSION_NUM >= 183
SQL_SetCharset(g_hTuple,"utf8");
#endif

SQL_ThreadQuery(g_hTuple, "QuerySetData", szQuery);

return PLUGIN_CONTINUE;
} 


[CrazY.]

Use update instead of replace.
https://www.w3schools.com/sql/sql_update.asp

Also, you do not need to specify all columns if you set values in correct order, and format with %i for integers instead of %d.

INSERT INTO 'users' VALUES ('%s', '%s', '%i', '%i', '%i', '%i', '%i', '%i', '%i', '%i', '%i', '%i', NOW());", steam, GetSecureName(Name), pKills[id], pDeaths[id], pHeadshots[id], pAces[id], pMiniAces[id], pMixPlayed[id], pMixLost[id], pMixWon[id], pMixDraw[id], pPoints[id]); 

More info about sql: https://forums.alliedmods.net/showth...p?t=172936#SQL

[Bugsy]

CrazY your query is wrong. You are not specifying the fields for the data you are trying to set.

My bad crazy, you're right. As long as all fields are supplied and in the correct order, your method will work. I've never personally used INSERT without specifying field names and I literally write SQL at work almost every day. Still, I think it's good practice to include field names, but thats just my opinion.

jonatat, when a player connects then use a query to retrieve their data and store it in an array. On disconnect, if data exists in the array, use an UPDATE statement. If the array is null, use an INSERT statement.

INSERT INTO tblTest (SteamID, Val1, Val2) VALUES ('STEAM:0:12345',12,34);

UPDATE tblTest SET Val1=55, Val2=66 WHERE SteamID='STEAM:0:12345';

[Bugsy]

You can also use REPLACE INTO if your table is created properly.

REPLACE works exactly like INSERT , except that if an old row in the table has 
the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is 
deleted before the new row is inserted. See Section 12.2.5, “ INSERT Syntax”. 
REPLACE is a MySQL extension to the SQL standard.

You can make the steam ID your primary key since this value is static.

CREATE TABLE IF NOT EXISTS tblPlayerData (SteamID VARCHAR(34) PRIMARY KEY , PlayerName VARCHAR(32), PlayTime INTEGER);

REPLACE INTO tblPlayerData (SteamID, PlayerName, PlayTime) VALUES ('STEAM123','bugsy',1);

[CrazY.]

@Bugsy, yes, this is a prefer of each programmer, both will work. I did not found results about REPLACE INTO in w3c, only about replace strings. Are you sure that this will work?

[Bugsy]

Originally Posted by CrazY. @Bugsy, yes, this is a prefer of each programmer, both will work. I did not found results about REPLACE INTO in w3c, only about replace strings. Are you sure that this will work?

I'm not sure what you mean but yes, what I posted works and I tested it. The key thing (no pun intended) is defining the primary key and then as long as that value already exists in a row, the other fields will be updated. In my example, steam id.

[jonatat]

Thanks guys for helping me out! Can i use UPDATE instead of REPLACE? Becouse i'm using same users table in server and website

[jonatat]

Originally Posted by Bugsy You can also use REPLACE INTO if your table is created properly. REPLACE works exactly like INSERT , except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 12.2.5, “ INSERT Syntax”. REPLACE is a MySQL extension to the SQL standard. You can make the steam ID your primary key since this value is static. CREATE TABLE IF NOT EXISTS tblPlayerData (SteamID VARCHAR(34) PRIMARY KEY , PlayerName VARCHAR(32), PlayTime INTEGER); REPLACE INTO tblPlayerData (SteamID, PlayerName, PlayTime) VALUES ('STEAM123','bugsy',1);

You really helped me with PRIMARY_KEY!

Does it okay?

formatex( szQuery, 3799, "UPDATE `users` SET `steam_id` = '%s', `player_name` = '%s', `kills` = '%d', `deaths` = '%d', `headshots` = '%d', `aces` = '%d', `m_aces` = '%d', `mix_played` = '%d', `mix_lost` = '%d', `mix_won` = '%d', `mix_draw` = '%d', `points` = '%d' WHERE `steam_id` = '%s';", steam, GetSecureName(Name), pKills[id], pDeaths[id], pHeadshots[id], pAces[id], pMiniAces[id], pMixPlayed[id], pMixLost[id], pMixWon[id], pMixDraw[id], pPoints[id], steam);

[CrazY.]

Yes, you can. Just do the syntax correctly and will work.

formatex( szQuery, 3799, "UPDATE `users` SET `steam_id` = '%s', `player_name` = '%s', `kills` = '%d', `deaths` = '%d', `headshots` = '%d', `aces` = '%d', `m_aces` = '%d', `mix_played` = '%d', `mix_lost` = '%d', `mix_won` = '%d', `mix_draw` = '%d', `points` = '%d' WHERE `steam_id` = '%s';", steam, GetSecureName(Name), pKills[id], pDeaths[id], pHeadshots[id], pAces[id], pMiniAces[id], pMixPlayed[id], pMixLost[id], pMixWon[id], pMixDraw[id], pPoints[id], steam);

Looks like it's all fine, test it.

[Bugsy]

You don't need to update the steamid value