DarthNinja, there's something I want to bounce off of you for a possible future schema change.
Have you thought about splitting weapon kills off into their own table, and using a non-associative design?
I was playing with the idea myself, and came up with this as a schema:
Code:
CREATE TABLE `weaponkills` (
`STEAMID` varchar(25) NOT NULL,
`WeaponName` varchar(64) NOT NULL,
`WeaponKills` int(11) NOT NULL,
`WeaponHeadshots` int(11) NOT NULL,
KEY `STEAMID` (`STEAMID`),
KEY `WeaponName` (`WeaponName`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
As it's impossible to have a primary key here, it is a little worse for performance, but that is partially negated by having indexes on the steamid and weaponname (DBMSs are designed with these kinds of situations in mind after all).
It won't be hard to grab weapon kills from the database for the web interface itself either since a multi-table select is easy as pie.
This also comes with the benefit of not having to assign unique column names for each weapon, leaving it so that you can just use a single prepared query in the plugin and just pass in the weapon's name that was received in the player killed event, and the web interface can handle the translation itself. After all, Valve must their weapon names unique already, so there's no real need to change them ourselves.
It may or may not be worth it to also track headshots per-weapon this way, that's something you need to determine if it's possible plugin-side or not.
Also, updating the data that would be stored for a table like this would have to be done in a unique way, which a friend of mine calls an "upsert". Initially, you try an update query, where it's something like "
UPDATE Players SET WeaponKills = WeaponKills + 1 WHERE (STEAMID = 'STEAM_0:0:123456' AND WeaponName = 'bat')". Afterwards, you check the number of rows affected; if it is less than one, you need to run an insert query so that the row exists for future queries, and from there it's just a plain INSERT INTO with WeaponKills being set to 1 (and if it's one or more rows affected btw, you're done and don't need to do anything else).
The huge benefit of doing the database structure this way, though, is that it reduces the amount of code in the plugin (I see a lot of redundant code in there just for queries) and it removes the need for the plugin to be updated with new TF2 updates that add weapons. Only the web interface would need updated to display the new weapon data (since it has to localize the weapon name and add in a kill icon).
E: Also, thanks for the changes that I asked for.
