This include makes use of Wildcat's modifications to Kint's MQ2SQL beta plugin. The code required can be found here:
http://macroquest2.com/phpBB2/viewtopic.php?p=41077
The event will trigger off either a /get or from an officer's changing the motd; there is check to make certain it's a new MOTD prior to executing the INSERT.
Documentation is in the include code and corresponding INI file.
Comments / Suggestions / Flames / Questions / etc, toss here in the thread or PM them to me.
Thanks,
G
edit: minor corrections on update check
Code: Select all
| -- guildmotd.inc
|
| Program by Gumby
| Date: 3/20/2004
| Version: 1.0
|
| Overview: This include updates a MySQL database with the ingame GUILD MOTD.
|
| General Notes: This code is released mostly for informational purposes as the
| backend database table structure will vary widely among guilds
| based on architect and needs. I've attempted to make it as
| generalized as possible (at the expense of clarity) in hopes that
| others may use it directly or be able to modify it with minimal
| effort. Included in the documentation will be general notes
| regarding our database backend; I assume that any database
| admin or developer will be able to make any changes as required.
|
| This macro makes use of Wildcat's updates to Kint's MQ2SQL beta
| plugin. Thank you to both of you as automating guild information
| and functions is now far more feasible.
|
| The specific plugin code may be found at:
| http://macroquest2.com/phpBB2/viewtopic.php?p=41077
|
| As the MQ2SQL code changes I will keep this macro up to date as I can.
|
| Usage: Customize the INI file (guildmotd.ini) for your database information.
| Leave the INI file in the same directory as the macro, though if you wish
| to split you'll need to explicitly put the path in the varset of MOTDINIFile.
| Documentation regarding INI customization is found in the INI file.
|
| Add to your normal macros via #include guildmotd.inc (assuming same path)
| If this include file is not in the same directory as the calling macro,
| you'll need to enter explicit path to guildmotd.inc in your macro.
|
| Database particulars are layed out in the INI file, the code was written to
| support a 'history' through the use of timestamps in the database. If timestamping
| is not a concern, one could issue a DELETE or a REPLACE query based upon needs, otherwise
| how you sort the data to get the latest MOTD is up to you.
|
| As always, if you run into issues with this macro, or would like to request added functionality,
| please feel free to comment on the thread of the code's posting, or via a PM on the forums.
#event MOTD "GUILD MOTD"
Sub Event_MOTD(GuildMOTD)
/declare MOTDPoster local
/declare MOTDMessage local
/declare MOTDINIFile local
/declare MOTDHost local
/declare MOTDUser local
/declare MOTDPassword local
/declare MOTDDatabase local
/declare MOTDTable local
/declare MOTDUserField local
/declare MOTDUserFieldNum local
/declare MOTDMessageField local
/declare MOTDMessageFieldNum local
/declare MOTDTimestampField local
/varset MOTDINIFile guildmotd.ini
/varset MOTDHost "$ini("@MOTDINIFile","MOTD","Host")"
/varset MOTDUser "$ini("@MOTDINIFile","MOTD","User")"
/varset MOTDPassword "$ini("@MOTDINIFile","MOTD","Password")"
/varset MOTDDatabase "$ini("@MOTDINIFile","MOTD","Database")"
/varset MOTDTable "$ini("@MOTDINIFile","MOTD","Table")"
/varset MOTDUserField "$ini("@MOTDINIFile","MOTD","UserField")"
/varset MOTDUserFieldNum "$ini("@MOTDINIFile","MOTD","UserFieldNum")"
/varset MOTDMessageField "$ini("@MOTDINIFile","MOTD","MessageField")"
/varset MOTDMessageFieldNum "$ini("@MOTDINIFile","MOTD","MessageFieldNum")"
/varset MOTDTimestampField "$ini("@MOTDINIFile","MOTD","TimestampField")"
/varset MOTDPoster $arg(3,"@GuildMOTD")
/varset MOTDMessage "$right($calc($strlen("@GuildMOTD")-$calc(15+$strlen("@MOTDPoster"))),"@GuildMOTD")"
/delay 5
/sqlconnect host @MOTDHost @MOTDUser @MOTDPassword @MOTDDatabase
/delay 5
/sqlquery select * from @MOTDTable group by @MOTDTimestampField desc limit 1
/delay 5
/if ("@MOTDPoster"!="$sqlfield(@MOTDUserFieldNum,0)" || "@MOTDMessage"!="$sqlfield(@MOTDMessageFieldNum,0)") {
/echo Updating MOTD
/delay 5
/sqlquery insert into @MOTDTable (@MOTDUserField,@MOTDMessageField) VALUES ('@MOTDPoster','@MOTDMessage')
/delay 5
}
/return
Code: Select all
| --- guildmotd.ini, by Gumby
| Date: 3/20/2004
| Version: 1.0
|
| Customization INI file supporting guildmotd.inc.
|
| Change the values in this file for the database specifics that are being
| used in your instance. The first several are generic, the last couple
| may need to be re-worked dependant on your implementation. As stated
| in the include's documentation, I assume that MySQL admins and
| developers will be able to hack what they need into it.
|
| Host: The host which the MySQL database resides on
| User: Username to access the MySQL database
| Password: Password for the Username to access the MySQL database
| Database: The database name you are connecting to
| Table: The table which the motd resides on
| UserField: The field name for the user who posted the MOTD
| UserFieldNum: The collumn for the UserField.
| MessageField: The field name for the MOTD messages
| MessageFieldNum: The collumn for the MessageField
| TimestampField: The field name for the timestamp.
|
| There are many methods that might be used, but to illustrate the table
| structure that my guild uses and this include file was based upon:
|
| mysql> show fields from Tablename;
| +-------------+---------------+------+-----+---------+-------+
| | Field | Type | Null | Key | Default | Extra |
| +-------------+---------------+------+-----+---------+-------+
| | User | varchar(20) | | | | |
| | Message | varchar(255) | | | | |
| | Time | timestamp(14) | YES | | NULL | |
| +-------------+---------------+------+-----+---------+-------+
| 3 rows in set (0.07 sec)
|
| mysql> select * from Tablename;
| +------------+----------------------------------+----------------+
| | User | Message | Time |
| +------------+----------------------------------+----------------+
| | Gumby | Testing 1 | 20040320122316 |
| | Gumby | Testing 2 | 20040320124006 |
| | Gumby | Testing 3 | 20040320150532 |
| +------------+----------------------------------+----------------+
|
| In this example case, User is the UserField, with a UserFieldNum of 0.
| Message is the MessageField, with a MessageFieldNum of 1 (collumn 1).
| Time is the TimestampField. Collumn number isn't referenced in the macro.
[MOTD]
Host=hostname
User=mysql username
Password=mysql user's password
Database=Database name
Table=Tablename
UserField=User
UserFieldNum=0
MessageField=Message
MessageFieldNum=1
TimestampField=Time