MySQL Info! [plugin example]

TheNewt · **Author**

MySQL Sample Plugin
Updated the code.

Thnx Jtp for the help =P
Played a bit more around,

Code:

dbi_query(dbc,"INSERT INTO table (player, steamid) values (^"%s^", '%s')",player_name,authid)

Code:

dbi_query(dbc,"UPDATE table player=^"%s^" where steamid = '%s'),player_name,authid)

Not

Code:

dbi_query(dbc,"INSERT INTO table (player, steamid) values ('%s', '%s')",player_name,authid)

Code:

dbi_query(dbc,"UPDATE table player='%s' where steamid = '%s'"),player_name,authid)

and the CREATE TABLE should have `player` not ^"player^" like I previously had thought.
Fixes the name saving problem.

Code:

/*------------------------------------------------------------------------------------------------*/
#include <amxmodx>
#include <amxmisc>
#include <dbi>
/*------------------------------------------------------------------------------------------------*/
#define PLUGIN "MySQL Test"
#define VERSION "1.1"
#define AUTHOR "MysticDeath"

#define NUM_OF_LEVELS 5
/*------------------------------------------------------------------------------------------------*/
new g_ConnectTime[33]
new Sql:dbc // The SQL Database connection.
new Result:result // The result handle, holds the query.
new g_PlayerLevel[33]
new g_PlayerXP[33]
new gmsgStatusText
new const LEVELS[NUM_OF_LEVELS] = {
    50,  //1
    150,    //2
    300,    //3
    500,    //4
    750 //5
}
/*------------------------------------------------------------------------------------------------*/
public plugin_init() {
    register_plugin("MySQL Test", "1.1", "MysticDeath")
    gmsgStatusText = get_user_msgid("StatusText");
    register_event("ResetHUD", "ResetHUD", "b");
    register_concmd("say adminmenu", "AdminChat", ADMIN_KICK, "- Admin Menu");
    register_menucmd(register_menuid("menu_AdminMenu"),1023,"MenuAction_AdminMenu");
    set_task(0.1,"sql_init")
}
/*------------------------------------------------------------------------------------------------*/
public AdminChat(id, level, cid) {
    if (!cmd_access(id, level, cid, 2) {
        client_print (id, print_chat, "[MySQL Test] Access Denied"); // Check admin
        return PLUGIN_HANDLED;
    }
    AdminMenu(id);
    return PLUGIN_HANDLED;
}
/*------------------------------------------------------------------------------------------------*/
public ResetHUD(id) {
    ShowHUD(id);
}
/*------------------------------------------------------------------------------------------------*/
AdminMenu(id) { // The menu
    new menu[256]
    new key = MENU_KEY_0|MENU_KEY_1|MENU_KEY_2
    
    new smenu = format(menu, 255, "\yAdmin Menu:")

    sMenu += format (menu[sMenu], 255-sMenu, "^n\w1. +100XP")
    sMenu += format (menu[sMenu], 255-sMenu, "^n\w2. +1 Level")
    sMenu += format (menu[sMenu], 255-sMenu, "l^n^n0. Exit.")

    show_menu(id, key, menu, -1, "menu_AdminMenu")
}
/*------------------------------------------------------------------------------------------------*/
public MenuAction_AdminMenu(id, key) { // Menu Actions
    switch(key) {
        case 0: {
            g_PlayerXP[id] += 100
            client_print(id, print_chat, "[MySQL Test] +100 XP");
            if(g_PlayerXP[id] >= LEVELS[g_PlayerLevel[id]]) {
                ++g_PlayerLevel[id];
                client_print(id, print_chat, "[MySQL Test] Congratulations! You are now level %i!", g_PlayerLevel[id])
            }
            AdminMenu(id);
            ShowHUD(id);
            return PLUGIN_HANDLED;
        }
        case 1: {
            g_PlayerXP[id] = LEVELS[g_PlayerLevel[id]]
            ++g_PlayerLevel[id];
            client_print(id, print_chat, "[MySQL Test] Congratulations! You are now level %i!", g_PlayerLevel[id])
            ShowHUD(id)
            AdminMenu(id);
            return PLUGIN_HANDLED
        }
    }
    return PLUGIN_HANDLED;
}
/*------------------------------------------------------------------------------------------------*/
public ShowHUD(id) { // Blah, you know ShowHUD, bottom left.
    new Message[101]
    format (Message, 100, "Level: %i XP: %i", g_PlayerLevel[id], g_PlayerXP[id]);
    message_begin(MSG_ONE, gmsgStatusText, {0,0,0}, id)
    write_byte(0)
    write_string(Message)
    message_end()
}
/*------------------------------------------------------------------------------------------------*/
public client_disconnect(id) { // When they disconnect we shall check to see if we have a connection
    if (dbc == SQL_FAILED) { // If we don't, then stop the plugin.
        return PLUGIN_HANDLED;
    }
    new player_name[32] // Variable for the name
    new authid[32] // Variable for the STEAM_ID
    new playtime = get_user_time (id) // Checks if your a new player or not.
    get_user_name(id,player_name,31) // Store our name!
    get_user_authid(id,authid,31) // Store our ID
    result = dbi_query(dbc,"SELECT * FROM MySQLTest WHERE steamid = '%s'",authid) // Checking the connection
    if (result == RESULT_FAILED) {
        log_amx("[MySQL Test] MySQL Query Failed!!")
        return PLUGIN_CONTINUE // Query failed, so sorry for your loss.
    }
    else if (g_ConnectTime[id]== 0) { // This will create a brand new entry for this authid!
        result = dbi_query(dbc,"INSERT INTO MySQLTest (player, steamid, connecttime, playerlevel, playerxp, date) values (^"%s^",'%s',%i,%i,%i,NOW())",player_name,authid,playtime,g_PlayerLevel[id],g_PlayerXP[id])
    }
    else {
        new store_time = (playtime + g_ConnectTime[id]) // Other wise it'll just update an old entry for the person.
        result = dbi_query(dbc,"UPDATE MySQLTest SET player=^"%s^", connecttime=%i, playerlevel=%i, playerxp=%i, date=NOW() WHERE steamid='%s'",player_name,store_time, g_PlayerLevel[id],g_PlayerXP[id],authid)
    }
    dbi_free_result(result); // Not entirely sure if this should be here.
    return PLUGIN_CONTINUE;
}
/*------------------------------------------------------------------------------------------------*/
public client_authorized(id) { // This works better then client_connect
    if (dbc == SQL_FAILED) { // Check the connection!
        return PLUGIN_HANDLED;
    }
    new authid[32]
    get_user_authid(id,authid,31)
    result = dbi_query(dbc,"SELECT * FROM MySQLTest WHERE steamid = '%s'",authid) // Checks for the user's steamid in the database and retrieves their data. 
    if (result == RESULT_FAILED) {
        log_amx("[MySQL Test] MySQL Query Failed!!") // If it failed, we can't get the data.
        return PLUGIN_CONTINUE // Query failed! Sorry :P
    }
    else if (result == RESULT_NONE) { // If it didn't fail, but they don't have an entry,
        g_ConnectTime[id] = 0 // This will create a new entry in the database
        g_PlayerLevel[id] = 0
        g_PlayerXP[id] = 0 
    }
    else {
        dbi_nextrow(result) // This retrieves data from your query.
        g_ConnectTime[id] = dbi_result(result,"connecttime")
        g_PlayerLevel[id] = dbi_result(result, "playerlevel")
        g_PlayerXP[id] = dbi_result(result, "playerxp")
    }
    dbi_free_result(result) // You MUST free the result! Or else memory leaks will ensue!!!
    return PLUGIN_HANDLED;
}
/*------------------------------------------------------------------------------------------------*/
public sql_init() {
    new error[256]  // This connects to your database, theres different ways you can create this connection
    dbc = dbi_connect("localhost", "Username", "Password", "database", error,255)
    if (dbc == SQL_FAILED) {
        log_amx("[MySQL Test] SQL Connection Failed = %s", error)
    }
    else {  // dbi_query will now make sure there is a table in your database.
        dbi_query(dbc,"CREATE TABLE IF NOT EXISTS `MySQLTest` (`player` VARCHAR(32) NOT NULL,`steamid` VARCHAR(32) NOT NULL,`connecttime` INT NOT NULL,`playerlevel` INT NOT NULL,`playerxp` INT NOT NULL,`date` TIMESTAMP, PRIMARY KEY(`steamid`))")
    }
}
/*------------------------------------------------------------------------------------------------*/
public plugin_end() {
    dbi_close(dbc); // Close the connection
}

Changing the Table after its been Made...
If you do this it'll create a database table if you don't have one, if you wish to update this table to hold more information, like maybe a class, then you need to alter the table in the mysql database!
To do so, go to your cmd prompt (I assume you know how)
Go to C:\your directory\mysq\bin and type in mysql -u root
type in "use *database name*;"
then type in one of the following, depending on HOW you need to update the table!

Code:

ALTER TABLE `table` CHANGE `entry` `entry` INT NOT
NULL;

the two entry should be identical to the last letter, this is to update the Integer to hold more, or change default.

Code:

UPDATE `table` SET `entry` = '0' WHERE `entry` = '-1';

This one specifically updates one entry inside the database, if you wish to change one persons level while they are offline, you enter that in!
Table should be your table where you store the level and xp, the entry should be like "playerxp" if you were using my plugin up there. Heres an example

Code:

UPDATE 'mysqltest' SET 'playerlevel' = '5' WHERE 'steamid' = 'STEAM_ID:0:1:23456';

Last one is the important if your adding class or such to the database and don't wish to just delete the database table and create a new one!

Code:

ALTER TABLE table ADD newentry INT NOT NULL;

Now if you want to drop an entire collumn of information, you type in

Code:

ALTER TABLE 'table' DROP 'entry';

That is how you alter your database, so that you do not lose XP but you wish to change it!!

Finding a Player's Entry
Adding a third item to this MySQL info. Finding the player's entry.

Code:

SELECT 'player, 'steamid', 'playerlevel', 'playerxp' FROM 'MySQLTest' WHERE 'steamid' = 'STEAM_0:1:23456789';

This will find their entry based on their steam id.

Code:

SELECT 'player, 'steamid', 'playerlevel', 'playerxp' FROM 'MySQLTest' WHERE 'player' = 'MysticDeath';

This will find their entry based on their name. IT HAS TO BE EXACT!

Code:

SELECT 'player, 'steamid', 'playerlevel', 'playerxp' FROM 'MySQLTest' WHERE 'player' like '%ystic%eat%';

This will find their entry based on their name, but it can be missing letters or whole chunks what '%' means is that anything can be there, whole strings or works can be there, even spaces or numbers.

Code:

SELECT 'player, 'steamid', 'playerlevel', 'playerxp' FROM 'MySQLTest' WHERE 'steamid' like '%1:23456789';

It also works for the Steam_ID.

Another cool thing one can use if your connecting multiple tables, on my plugin I store information into seperate tables. =P But You would think to yourself, how would I view this information as if it were one table?
Well its quite simple really.

Code:

INNER JOIN table2 on table2.collumn = table1.collumn

the collumns must have the same name, *cough* steamid*cough*
If you plan to use seperate tables, its important that you have steamid as the primary key in each of the tables, it will make life so much easier for you.
Heres an example of an inner join I use.

Code:

SELECT
 names.playername as 'Player',
 names.steamid as 'Player ID',
 maindb.playerlevel as 'Lvl',
 upgrades.playerup as 'UPs',
 upgrades.playerhp as 'HP'
FROM names
 INNER JOIN maindb
  on maindb.steamid = names.steamid
 INNER JOIN ocaliupgrades
  on upgrades.steamid = names.steamid
WHERE
 names.playername like '%MysticDeath%';

There is a such thing as OUTER JOIN too, but I havn't used it, and I believe its used to combine 2 seperate databases, not just tables but whole databases.
-ONE LAST NOTE-
These table alterations are to be done with mysql running, but your server needs to be OFFLINE! Thank you. Hope this helps somewhat.