Raised This Month: $12 Target: $400
 3% 

MySQL Info! [plugin example]


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
TheNewt
Donor
Join Date: Jun 2006
Location: Where I live.
Old 07-02-2006 , 20:28   MySQL Info! [plugin example]
Reply With Quote #1

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.

Last edited by TheNewt; 07-17-2006 at 14:35.
TheNewt is offline
Peli
Veteran Member
Join Date: Mar 2004
Location: San Diego, CA
Old 07-03-2006 , 04:27   Re: MySQL Info!
Reply With Quote #2

Nice job Mystic, great deal of help with your comments.
Peli is offline
Send a message via MSN to Peli
Lord_Destros
Veteran Member
Join Date: Jul 2004
Location: With stupid.
Old 07-03-2006 , 07:59   Re: MySQL Info!
Reply With Quote #3

Thx, needed this .
__________________
Quote:
Originally Posted by Twilight Suzuka
Don't worry m'lord. The turtles day will come.
Lord_Destros is offline
Send a message via AIM to Lord_Destros
TheNewt
Donor
Join Date: Jun 2006
Location: Where I live.
Old 07-03-2006 , 09:48   Re: MySQL Info! [plugin example]
Reply With Quote #4

Your welcome! No clue how much that means to me, seeing as I just started scripting like a week (or 2 weeks I don't remember) ago. haha
TheNewt is offline
Hawk552
AMX Mod X Moderator
Join Date: Aug 2005
Old 07-03-2006 , 09:54   Re: MySQL Info! [plugin example]
Reply With Quote #5

I'm wondering why you're using DBI. It's not bad code, it's just you should probably use SQLx.
__________________
Hawk552 is offline
Send a message via AIM to Hawk552
TheNewt
Donor
Join Date: Jun 2006
Location: Where I live.
Old 07-03-2006 , 09:56   Re: MySQL Info! [plugin example]
Reply With Quote #6

I havn't figured out SQLx. Could you explain why its better for me to probably use SQLx instead?
TheNewt is offline
Hawk552
AMX Mod X Moderator
Join Date: Aug 2005
Old 07-03-2006 , 09:58   Re: MySQL Info! [plugin example]
Reply With Quote #7

Quote:
Originally Posted by MysticDeath
I havn't figured out SQLx. Could you explain why its better for me to probably use SQLx instead?
I'm not going to sit down and write an essay about how to use a specific framework. If you want, I can convert this example into SQLx, but if you want a tutorial read about the ones Bail made.
__________________
Hawk552 is offline
Send a message via AIM to Hawk552
TheNewt
Donor
Join Date: Jun 2006
Location: Where I live.
Old 07-03-2006 , 10:01   Re: MySQL Info! [plugin example]
Reply With Quote #8

Wasn't asking for an essay, just was wondering what the fine points were. I'm gonna go find those doc's by Bail. -Edit: If you want to convert it to SQLx, feel free to do so. I am actually quite curious how it will look.

Edit2: I'm gonna take a swing at converting it too.

Last edited by TheNewt; 07-03-2006 at 10:05.
TheNewt is offline
Xanimos
Veteran Member
Join Date: Apr 2005
Location: Florida
Old 07-03-2006 , 10:37   Re: MySQL Info! [plugin example]
Reply With Quote #9

Also on all your forwards (i.e. connect , disconnect) you need to either check to see if you are connected to database before using dbi_query(). And you should add

Code:
public plugin_end() {     dbi_close(dbc) }
Xanimos is offline
Send a message via AIM to Xanimos Send a message via MSN to Xanimos
TheNewt
Donor
Join Date: Jun 2006
Location: Where I live.
Old 07-03-2006 , 10:47   Re: MySQL Info! [plugin example]
Reply With Quote #10

-*doh*- Is this better?
Code:
/*------------------------------------------------------------------------------------------------*/ public client_disconnect(id) {     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.     dbi_close(dbc); // Close the connection } /*------------------------------------------------------------------------------------------------*/ public client_authorized(id) {     new authid[32]     get_user_authid(id,authid,31)         result = dbi_query(dbc,"SELECT * FROM MySQLTest WHERE steamid = '%s'",authid)     if (result == RESULT_FAILED) {         log_amx("[MySQL Test] MySQL Query Failed!!")         return PLUGIN_CONTINUE // Query failed! Sorry :P     }     else if (result == RESULT_NONE) {         g_ConnectTime[id] = 0 // This will create a new entry in the database         g_PlayerLevel[id] = 0 // If you don't have one.         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!!!     dbi_close(dbc);     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", "Sphinx", "butters", "ocalimysqltest", 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`))")     } } /*------------------------------------------------------------------------------------------------*/
TheNewt is offline
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

Forum Jump


All times are GMT -4. The time now is 14:36.


Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Theme made by Freecode