SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help > Tutorials

Reply
 
Thread Tools Display Modes
Old 06/04/2014, 08:23 PM   #1
Vince
Spam Machine
 
Vince's Avatar
 
Join Date: Sep 2007
Location: Belgium
Posts: 10,782
Reputation: 2555
Default Saving weapon data (MySQL)

This is, in part, an extension on my previous tutorial regarding the general table structure and foreign keys. If you haven't read that one, I suggest you do that first.

Weapon Information
There are 13 different weapon slots and thus a player can theoretically hold up to 13 different weapons. In practice, though, this is usually more like 4 or 5.

Why this structure?
The table we will be creating will look like this:

This structure allows for a more compact view and allows for the use of aggregate functions such as COUNT(). This allows me to find out how many weapons each player has, how many players have an M4 or even the total amount of ammo currently circulating (for a specific weapon). This may all seem trivial but it is difficult, if not impossible, to achieve with your average non-normalized "weapon1, ammo1 ... weapon13, ammo13" approach.

From the above screenshot we can deduct that the player 1 has 3 weapons: a nite stick (id 3) with 1 ammo, a desert eagle (id 24) with 21 ammo and an M4 (id 31) with 266 ammo.

Creating the table
userid is a reference to the player's unique ID which is stored in another table, along with their name, password, etc. Notice that phpMyAdmin conveniently makes the userid clickable if a foreign key exists. weaponid is simply the weaponid as is returned from functions like GetPlayerWeapon and GetPlayerWeaponData. ammo holds the ammunition associated with aforementioned weaponid.



The options you need to edit are marked in yellow. Note that the type and attributes of the userid may differ in your case: this field needs to be declared with the exact same definition as your main userid. This may mean that you do not need to set that field to unsigned. The userid does need to be declared as an index though. It is neither a primary key, nor an unique key. For the weaponid we choose an unsigned tinyint since we're only dealing with non-negative numbers up to 46. The ammo is declared unsigned as well since there's no such thing as negative ammunition. Lastly, don't forget to set the engine to InnoDB. Click Save to create the table.

Setting limits
You should've been brought to the structures tab of the table you just created. Navigate there if this isn't the case.
Before continuing, we will first impose some extra limits to avoid cluttering the table with useless data later on. Each player (userid) can only hold exactly one specific weapon (weaponid) at once. This is a UNIQUE property. Therefore, tick the checkboxes in front of userid and weaponid. Then click on the UNIQUE button underneath the table.


Update 31/01/2016: click the PRIMARY KEY button instead. Each table should have a primary key.

Creating the foreign key
You should've been brought back to the structures tab. Navigate there if this isn't the case. Underneath the structure definition you should see a link titled Relation view. Click this to be brought to the "relation creator".



I'm creating a link to the id field in the table playerinfo in the database vcnr. Your table and database will be called differently. Select the proper ID. Note that for fields to show up in this list, they need to be defined as a key! We also want any changes made in the main table to be CASCADED into this table. Click Save. This concludes the table creation part in phpMyAdmin. Now back to Pawn.

Saving
For this table, we will only use SELECT, INSERT and DELETE queries. There will be no real UPDATE queries. Instead, we will only use a special insert query:
PHP Code:
INSERT INTO ... ON DUPLICATE KEY UPDATE ... 
This query will try to insert the data as normal. If this fails because the data already exists (duplicate key) it will instead perform an update. In our context this means that we will merely update the ammo if a weaponid is already present for a specific user.

Code:
new
	weaponid,
	ammo;
	
for(new i; i < 13; i++) // looping through all weapon slots (0 - 12)
{
	GetPlayerWeaponData(playerid, i, weaponid, ammo); // get weaponid and ammo

	if(!weaponid) continue; // don't insert if there's no weapon in this slot
	
	mysql_format(userDB, mysqlquery, sizeof(mysqlquery), "INSERT INTO player_weapons VALUES (%d, %d, %d) ON DUPLICATE KEY UPDATE ammo = %d;", PlayerInfo[playerid][pSQLID], weaponid, ammo, ammo);
	mysql_pquery(userDB, mysqlquery); // parallel queries
}

The above snippet will insert or update all the weapons the player currently has. I am using parallel queries for speed: the order in which the inserts are performed isn't at all important. You will need to substitute the userid variable with your own. The same query can also be used stand-alone in other places. You can, for example, write a hook for GivePlayerWeapon which immediately performs an insert as soon as the weapon is given.

Loading
Now to retrieve this data and give the players their weapons back. Our standard select query;
PHP Code:
SELECT weaponidammo FROM player_weapons WHERE userid = %d
Code:
public OnLoadPlayerWeapons(playerid)
{
	new
	    weaponid,
	    ammo;
	
	for(new i, j = cache_get_row_count(userDB); i < j; i++) // loop through all the rows that were found
	{
	    weaponid 	= cache_get_row_int(i, 0, userDB);
	    ammo    	= cache_get_row_int(i, 1, userDB);
		
		if(!(0 <= weaponid <= 46)) // check if weapon is valid (should be)
		{
			printf("[info] Warning: OnLoadPlayerWeapons - Unknown weaponid '%d'. Skipping.", weaponid);
			continue;
		}
		
		GivePlayerWeapon(playerid, weaponid, ammo); 
	}
	return;
}

Purging obsolete data
Whenever a weapon is taken away, do not forget to delete it from the database otherwise it will be returned to the player when they next join. You could write a hook for ResetPlayerWeapons. It may also be possible that weapons that have no ammo are left behind in the table. This doesn't affect anything in-game and can be cleaned up with a query (delete where ammo = 0) when the server starts, or on a cron job.
__________________

Last edited by Vince; 31/01/2016 at 08:53 PM.
Vince is offline   Reply With Quote
Old 06/04/2014, 08:27 PM   #2
vassilis
High-roller
 
vassilis's Avatar
 
Join Date: Nov 2009
Location: Greece,Athens
Posts: 1,857
Reputation: 304
Default Re: Saving weapon data (MySQL)

Aha i got it.. Seems good and readable good job vince.. i wonder if you would make something similar for y_ini it would really help me +rep though!
__________________

My Works:List
Chaos CNR Facebook:Join for Feedback. (91 members)
Working On:Chaos Cops N Robbers(80%)| Sound ID Application (50%)
vassilis is offline   Reply With Quote
Old 06/04/2014, 09:31 PM   #3
Luis-
High-roller
 
Luis-'s Avatar
 
Join Date: Jan 2010
Location: England
Posts: 4,050
Reputation: 339
Default Re: Saving weapon data (MySQL)

Nice on Vince! Going to update my weapon system now!
__________________
Luis- is offline   Reply With Quote
Old 06/04/2014, 11:38 PM   #4
Binx
Big Clucker
 
Join Date: Jul 2013
Posts: 107
Reputation: 2
Default Re: Saving weapon data (MySQL)

This is really helpful! Thanks a lot!
__________________
Don’t argue with idiots because they will drag you down to their level and then beat you with experience.
- Greg King.
Binx is offline   Reply With Quote
Old 06/04/2014, 11:40 PM   #5
iZN
High-roller
 
Join Date: Jun 2010
Location: Pakistan
Posts: 2,490
Reputation: 564
Default Re: Saving weapon data (MySQL)

There need to be more tutorials like that related to MySQL optimization. Good job, I've read your previous thread it was good too, and it gave me knowledge about the structure alot.
__________________
iZN is offline   Reply With Quote
Old 07/04/2014, 06:03 PM   #6
Luis-
High-roller
 
Luis-'s Avatar
 
Join Date: Jan 2010
Location: England
Posts: 4,050
Reputation: 339
Default Re: Saving weapon data (MySQL)

Each time i save my current weapons, none get inserted into the table, it just keeps updating the first row, any idea why?
__________________
Luis- is offline   Reply With Quote
Old 07/04/2014, 06:15 PM   #7
Vince
Spam Machine
 
Vince's Avatar
 
Join Date: Sep 2007
Location: Belgium
Posts: 10,782
Reputation: 2555
Default Re: Saving weapon data (MySQL)

Have you set the keys correctly? userid and weaponid need to be ticked simultaneously to create a composite key. It appears as if you've only created the unique key on the userid field. You can check this by going to the structure tab and clicking Details at the bottom.
__________________
Vince is offline   Reply With Quote
Old 07/04/2014, 08:26 PM   #8
Luis-
High-roller
 
Luis-'s Avatar
 
Join Date: Jan 2010
Location: England
Posts: 4,050
Reputation: 339
Default Re: Saving weapon data (MySQL)

Ah right my mistake, just need to find out why im not getting a weapon when I spawn. Thanks!
__________________
Luis- is offline   Reply With Quote
Old 08/04/2014, 04:14 AM   #9
Niko_boy
High-roller
 
Niko_boy's Avatar
 
Join Date: Aug 2010
Location: Somewhere i belong
Posts: 1,447
Reputation: 138
Default Re: Saving weapon data (MySQL)

ok had to read it twice to get an idea about it! Nice tutorial and thanks!
__________________
$$$ If anyone NEED A SCRIPTER for:
  • DM/TDM/Freeroam/Stunt server, filterscripts or Bug fixing.or some general mapping. Above all any of the logos and banners or signature sorta stuff aswell.At some cheap and worth-full prices.
can Contact me for more info or a deal.
•••[CLOSED]LCS•Freeroam•DM•Stunts•••AutoArena [0.3z][No SkinShot][sixtytiger.com]Want a decent Attack Defend Gamemode?
N/A176.31.229.148:7830Get This! Attack-Defend(v2.3.1)
Niko_boy is offline   Reply With Quote
Old 08/04/2014, 07:05 AM   #10
dusk
High-roller
 
dusk's Avatar
 
Join Date: Jul 2008
Posts: 1,118
Reputation: 46
Default Re: Saving weapon data (MySQL)

Code:
ON DUPLICATE KEY UPDATE ammo = %d;
This part is VERY useful for me... Until today I either send a DELETE query to delete all weapon records for a player and then INSERT new data, or send an UPDATE query and then checked for affected rows.

Concerning the table structure, shouldn't the child table have some kind of unique column? I don't know why but I always add it to any table.. I guess it's for editing rows in pMA. Is that bad practice?

And you should really continue these tutorials. I've seen MANY table structures that have over a hundred columns in one main "player" table.
dusk is offline   Reply With Quote
Reply

Thread Tools
Display Modes

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
The problem with saving data in mysql. MaySee Scripting Help 3 22/07/2013 11:10 AM
MySQL saving Data yanir3 Scripting Help 5 24/06/2013 07:27 PM
Mysql Saving Data Stefand Scripting Help 7 07/05/2013 10:42 AM
Problems with MySQL data saving sobolanux Help Archive 0 22/04/2011 02:54 PM
Not saving to ini file (Weapon Data) Robbin237 Help Archive 5 02/05/2009 10:02 AM


All times are GMT. The time now is 07:38 AM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.