[Vince]

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 weaponid, ammo 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.