[Tutorial] Saving weapon data (MySQL)
#1

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.

pawn 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
pawn 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.
Reply
#2

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!
Reply
#3

Nice on Vince! Going to update my weapon system now!
Reply
#4

This is really helpful! Thanks a lot!
Reply
#5

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.
Reply
#6

Each time i save my current weapons, none get inserted into the table, it just keeps updating the first row, any idea why?
Reply
#7

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.
Reply
#8

Ah right my mistake, just need to find out why im not getting a weapon when I spawn. Thanks!
Reply
#9

ok had to read it twice to get an idea about it! Nice tutorial and thanks!
Reply
#10

pawn 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.
Reply
#11

good job
Reply
#12

how do i do this with old MySQL like R4? I put this on disconnect:

pawn Code:
new
                weaponid,
                MySQLQuery[300],
                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

                format(MySQLQuery, sizeof(MySQLQuery), "INSERT INTO ginklai VALUES (%d, %d, %d) ON DUPLICATE KEY UPDATE ammo = %d;", GetPVarInt(playerid, "AccID"), weaponid, ammo, ammo);
                mysql_query(MySQLQuery);
            }
I go to the server, give myself weapons then quit, and nothing appears in the database. and how could i redo, the loading for Queries? Thank you.
Reply
#13

Quote:
Originally Posted by Type-R
View Post
how do i do this with old MySQL like R4? I put this on disconnect:

pawn Code:
new
                weaponid,
                MySQLQuery[300],
                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

                format(MySQLQuery, sizeof(MySQLQuery), "INSERT INTO ginklai VALUES (%d, %d, %d) ON DUPLICATE KEY UPDATE ammo = %d;", GetPVarInt(playerid, "AccID"), weaponid, ammo, ammo);
                mysql_query(MySQLQuery);
            }
I go to the server, give myself weapons then quit, and nothing appears in the database. and how could i redo, the loading for Queries? Thank you.
That's outdated, use R33+, (R39-2)
Reply
#14

1. I dont know R33+
2. I dont have time
3. Its just kind of a free time thing, so for my likings R5 is enough, sorry i put R4..
Reply
#15

Hey Vince.

First off, good job!

Second, I have a question. Why did you put a semicolon at the end of format ?
Code:
ammo = %d;
EDIT: Found an answer! If we put a semicolon that means the end of a query so we can put multiple queries that way, if anyone is wondering.
Reply
#16

I thank you sincerely for the useful information in the database normalization series.
Reply
#17

Sorry for bumping this, but I'm really interested in this. Anyway, I have a question:

Quote:

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.

Couldn't this be solved easily by deleting certain player's weapons info in the table before saving it again? This won't use much memory, as you obviously would save the player's weapons once per possesion (when the player disconnects).
Reply
#18

Nice
Reply
#19

EDIT: Got it, thanks for the tutorial Vince. You saved me a lot of time and effort.
Reply
#20

Great job Vince
Reply


Forum Jump:


Users browsing this thread: 2 Guest(s)