Creating the Database

The steps for setting up your database will depend on the setup of your server, but we'll briefly look at the steps for cpanel users. After logging in you are looking for the databases section:

If the wizard is available, then you may as well use it (2nd option from left). Just be sure to notedown any names or passwords that you enter. The stages for me were:

  1. Choose a name for the database (keep it short but meaningful, like “mmo”). Note there may be a prefix linked to your account - like “account_”. If there is then your database name is actually “account_mmo”.
  2. Create a user and a password. Again the username may have a prefix set for it, so be sure to note down the whole name.
  3. Set privileges. The recommended settings are shown in the image below.

Creating Tables

phpMyAdmin is a great interface for working with your database, so hopefully you have it installed on your server. If you return to cpanel, then you should be able to find the icon to click on (it's the third icon from the left in the picture at the top of this page).

Depending on how your server is setup, clicking on the icon may automatically take you into phpMyAdmin as the administrative user. If you are presented with a login screen, then use the database username and password that you just created.

We are going to start with 3 tables:

  • player details - the people using your game
Field Description
id A unique number assigned to the player
ref The unique ID of the player's device
st Player's current strength
t Player's current toughness
hp Player's current hitpoints (health)
  • monster details - the types of monsters in your game
Field Description
id A unique number assigned to the monster
type Number that matches an ID in the bestiary
st Monster's current strength
t Monster's current toughness
hp Monster's current hitpoints (health)
lat Monster's current latitude (location)
lon Monster's current longitude (location)
  • spawned monsters - details for each monster that actually exists in your game
Field Description
id A unique number assigned to monster type
name The species name of this monster type
desc A description of the monster (optional)
image The URL for an image of the monster
st Monster's initial strength
t Monster's initial toughness
hp Monster's initial hitpoints (health)

When you first go into PHPMyAdmin, you will probably see the name of your database listed in the column on the left. Click on it. Then click on the SQL tab, copy and paste the text below into the largest text box, and click on the Go button.

CREATE TABLE `player` (
        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, 
        `ref` VARCHAR(50) NOT NULL, 
        `st` TINYINT, UNSIGNED NOT NULL, 
        `t` TINYINT UNSIGNED NOT NULL, 
        `hp` TINYINT UNSIGNED NOT NULL);

CREATE TABLE `monster` (
        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        `type` INT UNSIGNED NOT NULL,
        `st` TINYINT, UNSIGNED NOT NULL,
        `t` TINYINT, UNSIGNED NOT NULL,
        `hp` TINYINT, UNSIGNED NOT NULL,
        `lat` FLOAT NOT NULL,
        `lon` FLOAT NOT NULL);
        
CREATE TABLE `bestiary` (
        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        `name` VAHCHAR(30) NOT NULL,
        `desc` VARCHAR(140),
        `image` VARCHAR(60) NOT NULL,
        `st` TINYINT, UNSIGNED NOT NULL,
        `t` TINYINT, UNSIGNED NOT NULL,
        `hp` TINYINT, UNSIGNED NOT NULL);

Creating a Database Connection File

To make things easier for ourselves, We can put the code to connect to the database in a separate file. Copy and paste the code below into a new file, changing the database name, user name, and password to your own settings. $dbhost will stay as locahost in most cases. Save it as dbinfo.php.

<?php
 
// Pre-define connection to the MySQL database, please specify these fields.
$dbhost = "localhost";
$dbdata = "account_database";
$dbuser = "account_username";
$dbpass = "password";
 
// Connect to predefined MySQl database. 
$db = new PDO( "mysql:host=$dbhost; dbname=$dbdata", $dbuser, $dbpass, array(PDO::MYSQL_ATTR_INIT_COMMAND =>  "SET NAMES utf8") );
 
// set the error reporting attribute to Exception.
$db->setAttribute( PDO::ATTR_ERRMODE , PDO::ERRMODE_EXCEPTION );
 
 
?>