[Eyce]

Creating a MySQL database tables using Text Editors

Introduction
This simple tutorial is for servers that use MySQL database for saving data such as player stats, vehicles, and the like. These kinds of databases would more often than not require a lot of fields that could take up much of your time if you do it manually on your host or localhost itself like filling out the fields, scrolling towards the right, you name it. In this tutorial, you'll learn the basics on setting up your database using any text editor that you have.

What is a database?
As described on Wikipedia, a database is an organized collection of data - the simplest way that it could be explained if you have no idea on what it is. MySQL is used for but not limited to retrieving and storing data.

Let's get started
Before anything else, you just need a few things:
* Any text-editor like notepad (I prefer using Notepad++)
* Your localhost or host with a ready-made database (phpMyAdmin)

Things you need to know about creating a MySQL database that will be used in this tutorial:

TABLES	This is where columns or fields are stored, think of it as a shelf for your books.
FIELDS OR COLUMNS	The holders of your data (e.g. "username" or "password")
TYPES	The type of data stored into the fields such as integers, strings, float values.
LENGTH OR VALUES	This determines the limit of the data you store.
DEFAULT	Basically, the default value for each field. If used, it would automatically set the value when inserting data if not specified.
NULL OR NOT NULL	Indicates whether a data value exists or not.
AUTO_INCREMENT	Automatically increases the value of data stored in a field. Commonly used for IDs that are set as primary index.

DATA TYPES
I won't even try to explain much about data types but just to as far as I know and have used them. Just to give you an idea on what type should be used for a specific value.

• NUMERIC - used for numeric values such as integers
  
  Code:

  TINYINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE, and more...

• STRING - obviously for string and text values (e.g. player name)
  
  Code:

  CHAR, VARCHAR, TINYTEXT, TEXT, LONGTEXT, and more...

• DATE and TIME - used for date and time values (haven't tried using)
  
  Code:

  DATE, DATETIME, TIMESTAMP, TIME, YEAR

If you would like to know more about data types and a whole of information about MySQL, especially about storage requirements simply visit this link. Now for the structure of the database, I'll be using player accounts as an example.

Step 1 : The text-editor
Open your notepad or any text-editor.

Step 2 : Starting off
Start of with this code:

PHP Code:

CREATE TABLE IF NOT EXISTS `accounts` ( 


This would create a table with the name "accounts" only if it doesn't exist which is the right thing to do. So if there's already a table in your database with the same name, you would get an error. If you've noticed there's an open parenthesis at the end, so now you have to close it, by doing this:

PHP Code:

) ENGINE = InnoDB DEFAULT CHARSET = latin1 AUTO_INCREMENT = 1; 


The storage engine that I'll be using is InnoDB (provides transaction-safe tables) which is I think is the most efficient to be used in these kinds of databases. For more information about MySQL storage engines, click here. And for the character set support, I'll be using latin1, it is also the default character set for MySQL. Finally, set the auto_increment value to 1 - this will determine the start of where the primary key would automatically increase.

Step 3 : Adding fields or columns
Now we can start putting in the different fields for the table, just like so:

PHP Code:

CREATE TABLE IF NOT EXISTS `accounts` (
    // FIELDNAME DATATYPE(LENGTH/VALUE) NULL DEFAULT OR AUTO_INCREMENT
    `ID` int(11) NOT NULL AUTO_INCREMENT,
) ENGINE = InnoDB DEFAULT CHARSET = latin1 AUTO_INCREMENT = 1; 


The code above would create a field or column named "ID" which will be numeric (in this case, I used INT) and set the length or value limit to 11, I used NOT NULL since it exists and indicate that this would be the field for the value to increase automatically.

Then you can add a line for the player's name, password, level, skin, and all that.

PHP Code:

    `playername` varchar(25) NOT NULL,
    `password` varchar(150) NOT NULL,
    `playerlevel` int(10) NOT NULL DEFAULT '0',
    `playerskin` int(5) NOT NULL DEFAULT '271', 


Now you have columns using numeric and string data types. It's simple to set a default value, in this case, the level's default value is 0 and skin is 271 (Ryder skin).

Step 4 : Setting the primary key
Setting the primary key, this indicates the field that would automatically increase (which is ID) as defined. After the last field that of your table, add this line in:

PHP Code:

    PRIMARY KEY (`id`) 


Step 5 : Ready for import
When you're done adding all the fields you need, you would have something like this:

PHP Code:

CREATE TABLE IF NOT EXISTS `accounts` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `playername` varchar(25) NOT NULL,
    `password` varchar(150) NOT NULL,
    `playerlevel` int(10) NOT NULL DEFAULT '0',
    `playerskin` int(5) NOT NULL DEFAULT '271',
    `money` int(11) NOT NULL DEFAULT '500',
    `bankaccount` int(11) NOT NULL DEFAULT '100',
    `gender` varchar(20) NOT NULL,
    `age` int(5) NOT NULL DEFAULT '18',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = latin1 AUTO_INCREMENT = 1; 


And it's ready to be imported onto your database.

Step 6 : Uploading and importing
If you don't have a database yet, then follow this basic tutorial for creating one: Basic MySQL Tutorial

Uploading your table/s by simply using the code or text (running query on the database):

1. Copy your text or code for the table to be created.
2. Select your database where you want the table to be in.
3. Click on SQL at the upper menu and a text-box would appear.
4. Paste it and should look like the image below.
   
5. Click on GO to run the query.
   

Alternatively, you can also import it by saving your text-file as a .SQL file and upload it.

1. Click on IMPORT at the upper menu.
2. Choose your .SQL file from your computer.
3. Make sure that the FORMAT is set to "SQL" before you
4. Click on GO
   

That's about it for this tutorial. It may not be complete as I've intended this tutorial for those who already know even a bit about MySQL and phpMyAdmin. If I missed out on some points especially the important ones, I'd appreciate if you post a reply for me to improve and edit it.

Notes
- This is the first tutorial that I made so any comments or suggestions would be appreciated for the benefit of people reading and also for the tutorial itself.
- I'm not an EXPERT when it comes to MySQL, just experienced enough to suffice my needs for using it.
- I hope this tutorial helps and good luck!

[Luis-]

The player name value should be 24 shouldn't it?

[Eyce]

Yes, but actually it should be 24+1, changed it. That's only recommended though for the latin1 character set since it requires only 1 byte per character plus one.

Quote:

For example, a VARCHAR(255) column can hold a string with a maximum length of 255 characters. Assuming that the column uses the latin1 character set (one byte per character), the actual storage required is the length of the string (L), plus one byte to record the length of the string. For the string 'abcd', L is 4 and the storage requirement is five bytes. If the same column is instead declared to use the ucs2 double-byte character set, the storage requirement is 10 bytes: The length of 'abcd' is eight bytes and the column requires two bytes to store lengths because the maximum length is greater than 255 (up to 510 bytes).

You can get more information from here.

[MaxPierce]

Thank you so much. Appreciate it!

EDIT: What would WHERE `UserID` = '%d mean?

[Eyce]

That depends on how you name your fields or columns, it could be `UserID` or `id` or to whatever. So if you have something like this:

pawn Code:

new string[128];
format(string, sizeof(string), "SELECT `username` FROM `accounts` WHERE `UserID`='%d'", variable);

Pretty simple, it'll select the field "username" from the accounts table where the UserID (or whatever the column is named) matches the variable. For your question, it checks if the value "%d" actually exists as a UserID in your database table.

[Schocc]

nice work ++rep.

[PT]

Very Good, nice work

[Eyce]

Thanks, appreciate it.

[Tuff]

+Reped, Really nice

[Eyce]

Quote:

Originally Posted by Tuff +Reped, Really nice

Thanks! Hope it helps.