AlliedModders

AlliedModders (https://forums.alliedmods.net/index.php)
-   Scripting Help (https://forums.alliedmods.net/forumdisplay.php?f=11)
-   -   Save player data to mysql (https://forums.alliedmods.net/showthread.php?t=307928)

jonatat 05-31-2018 02:25

Save player data to mysql
 
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(idName63);

get_user_authid(idsteam63);

static 
szQuery[3800];
formatexszQuery3799"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());"steamGetSecureName(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. 05-31-2018 11:07

Re: Save player data to mysql
 
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.

Code:
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 05-31-2018 17:52

Re: Save player data to mysql
 
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.

Code:

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 05-31-2018 18:06

Re: Save player data to mysql
 
You can also use REPLACE INTO if your table is created properly.

Code:

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.
Code:

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. 05-31-2018 20:29

Re: Save player data to mysql
 
@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 05-31-2018 20:33

Re: Save player data to mysql
 
Quote:

Originally Posted by CrazY. (Post 2594739)
@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 06-01-2018 04:01

Re: Save player data to mysql
 
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 06-01-2018 04:14

Re: Save player data to mysql
 
Quote:

Originally Posted by Bugsy (Post 2594735)
You can also use REPLACE INTO if your table is created properly.

Code:

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.
Code:

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?

Code:

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. 06-01-2018 10:08

Re: Save player data to mysql
 
Yes, you can. Just do the syntax correctly and will work.

Quote:

Code:

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 06-01-2018 13:52

Re: Save player data to mysql
 
You don't need to update the steamid value


All times are GMT -4. The time now is 04:43.

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