As you may remember from a previous article, I have been using ZOHO creator to store MMO game data while I play the game to keep track of my characters and their loot. I was not happy with the ZOHO solution due to it’s high cost – $15/mo beyond 3 databases [applications]. While I have not yet exceeded that, I desire a solution that will live on my hosting service where I have unlimited storage/databases.
I have found a solution with DHX Quick Tables, and for now I’m using that as we will see. I’ve exported the 2Moons data from Zoho Creator and built a MySQL database with 3 tables: character, armor and weapons. I’ll do the other tables later, but for now everything is working. I’m using MySQL 5 and innoDB tables. One minor glitch is that DHX requires php5, and I had not yet upgraded my site to php5. Once I asked the support group how to upgrade, I was all set.
I’ve discovered the following things that will help you if you decide to do this as well:
First you need to define your MySQL database and table. When testing this locally in an xampp environment, I used MySQL Admin to define the database, and a user with a password and full access to the database. On my hosting service, I used phpMyAdmin, which is provided in the control panels. Once I had the database and user defined, I used phpMyAdmin to create the tables and upload the data. I had exported the data from ZOHO creator as CSV files, so I stripped off the header and used the LOAD function to populate the table. There are several things to watch for as you migrate to DHX quick tables.
- You need an INT autoincrement field in the table. This field will not be displayed in the tables, but is required so that updates work correctly. It is easy to add a field using phpMyAdmin. Set the datatype to INT and enable AutoIncrement and Primary Index.
- Avoid integer fields, unless you want to enter data. Null fields for INT fields do not appear to work. But VARCHAR NULL fields work just fine.
- SETs and ENUMs work, but you cannot leave ENUMs null either, so you may want to use SETs. ZOHO provides a series of checkboxes for ENUMs which is more convenient to edit them. With DHXQT, you must enter them as a series of names with commas. Leave out the spaces like this: pants,armor,helmet.
- You can set a character name using a pull down list.
- Linked fields are not provided. The tables are isolated. This may be a problem for some applications, but in this case it’s not a big deal.
Table Definitions
Unlike ZOHO creator, MS Access, or OpenOffice Base, defining tables in DHXQT requires knowledge of SQL and detailed knowledge of databases. As I will discuss in a later article, DHXQT could form the basis of an easy to use Personal Cloud Database tool, but it’s not there yet. You must be database savvy and comfortable with tools like phpMyAdmin to use DHXQT.
For this application I’ve defined three tables:
- A character table.
- A table with Armor parts.
- A table with Weapons.
Here’s the Characters table:
--
-- Table structure for table `characters`
--
CREATE TABLE IF NOT EXISTS `characters` (
`ID` int(11) NOT NULL auto_increment,
`char_name` varchar(32) NOT NULL,
`level` int(11) NOT NULL,
`class` enum('Aloken','Knight','Summoner','Segnale','Hunter','Magician','Bagi Warrior') NOT NULL,
`server` varchar(16) NOT NULL,
`account_name` varchar(32) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;
The armor and weapon tables are more complicated:
—
— Table structure for table `character_armor`
—CREATE TABLE IF NOT EXISTS `character_armor` (
`ID` int(11) NOT NULL auto_increment,
`char_name` varchar(32) NOT NULL,
`level` int(11) NOT NULL,
`armor_type` varchar(64) NOT NULL,
`armor_parts` set(‘boots’,’pants’,’gloves’,’armor’,’helmet’) default NULL,
`boots_fortified` varchar(64) default NULL,
`pants_fortified` varchar(64) default NULL,
`gloves_fortified` varchar(64) default NULL,
`armor_fortified` varchar(64) default NULL,
`helmet_fortified` varchar(64) default NULL,
`sold` set(‘boots’,’pants’,’gloves’,’armor’,’helmet’) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=80 ;—
— Table structure for table `character_weapons`
—CREATE TABLE IF NOT EXISTS `character_weapons` (
`ID` int(11) NOT NULL auto_increment,
`char_name` varchar(64) NOT NULL,
`level` int(11) NOT NULL,
`weapon_name` varchar(64) NOT NULL,
`have_need` set(‘have’,’need’) default NULL,
`plus_level` varchar(4) default NULL,
`enhancements` varchar(64) default NULL,
`gem_fortifications` varchar(64) default NULL,
`sale_state` set(‘sale’,’sold’) default NULL,
`dil_price` varchar(11) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=115
The quick tables created from these look as follows:
As you see, each instance of DHXQT supports multiple tables from the same database.
You can have as many databases as you like on your system. Just install a new instance of Quick Tables for each database.
Applying New Styles
As you can see, I have set the title tag and adjusted the styles to be the built-in style of black. This must be done by editing the php file for each table manager.
The DHX Skin Builder is used to build the styles and then they are copied to the DHXQT directory and the table file modified to load the new styles.
The Books Database
Two years ago I needed to sell a couple of hundred old science fiction paperback books, so I entered them all into a database table, photographed their covers and divided them into lots by publication years to sell on Ebay. At the time I wanted a way for folks to see the detailed information on the books before they bid. I built a MySQL data table by hand and then a simple php application, again by hand, to sort and display the books in a web page. The web display is trivial using DHXQT, but there are limitations. It does not seem there is an image display widget in the DHX toolkit this is a surprising omission given the power and breadth of the toolkit.
Summary
While Quick Tables does not have linked fields, and some of the other field types that ZOHO supports, Quick Tables have the advantage of being free, so you can build as many tables as you like. They are slightly more difficult to build, but there is no limit to the number or the size of the data you can store. Given the key scheme that Quick Tables uses for security, the data is quite secure from modification.
– windy
Windy Weather 