Setting up Tables in a MySQL Database

When you're about to include a MySQL database in a project you're working on, you're going to want to plan ahead and figure out what kind of layout this database should use. After all, you can't use a database without first creating it!

You could start by creating this database's structure using PHP code, but it would be incredibly hard to get everything right the first time! My recommendation is to do what I always do: start out by using Adminer or PHPMyAdmin to view the database and structure it manually. Adminer and PHPMyAdmin are two of the most common PHP scripts that you can use to access your database without needing to write code. One or both of them are usually included somewhere in your CPanel - if you're using a CMS like WordPress, you should be able to find a plugin for one of them or something.

Let's start with the creation of your MySQL database real quick, just to ensure that you have the basic knowledge of setting up and using a database before we move on. When you go into your CPanel and click on "MySQL", you should see a list of all of your databases. If you haven't created the one you're going to use yet, go ahead and do so. You're going to need the following information:

1. The MySQL host - this might be something like "mysql12345.mywebsite.com".
2. The database - this might be something like "asgsd34g34_database".
3. The user - this might be something like "asgsd34g34_user".
4. The password - this is probably going to be set by you.

Once you have vision of your database in one of the Adminer or PHPMyAdmin scripts, you're ready to start planning how you want it to work. The first thing you're going to need to do is create a table to store all of your information in this database. For most small projects, you will only need 1 table. If you have more than 1 table, one might be for accounts, another for each user's content, and a third for comments or something.

We'll create and use one table in this tutorial, and it will be for storing (bogus) account data:

As you can see, not only do we need to name it, but we also need to determine the number of fields (columns) of data it should store. This can be changed later, but it's much easier to just predetermine this number if possible. Here's some pretend information we would like to know about each user in our accounts system:

1. Account ID number (for indexing purposes)
2. Username
3. Password
4. Real name
5. Register date
6. Last login date
7. A small piece of HTML code for every users' introduction of themselves

Now we have the information we need. We'll want to create a new table titled "accounts" that has 7 fields. After pressing "Go", we arrive at this page:

Not all properties for each field need to be addressed... Here are the relevant ones to us:

1. Field: the name of the field
2. Type: the type of the field
3. Length/values: the number of characters if "varchar" type is selected - otherwise, skip this
4. Attributes: choose "unsigned" for unsigned numbers (if you don't know what this means, don't worry - you won't use it until you do)
5. Default: set the default value for the field
6. Extra: select "auto_increment" to have numbers and such automatically increment every time the row is updated - we will not be using this, and I recommend you don't use it either unless you know what you're doing
7. Index (the second option): select this instead of the default option value if you want to turn indexing on for a field - I'll explain this more later

Based on the 7 pieces of information I listed earlier, here are the field names I'm going to use:

1. id
2. username
3. password
4. name
5. registerDate
6. lastLoginDate
7. introductionCode

Next up is the type property... We can find all about the different variable types in MySQL by going here:

http://dev.mysql.com/doc/refman/5.0/en/data-types.html

Also, here's a really nice "cheat sheet" that I found with information that summarizes most of what you need to know to use MySQL, including types:

http://cse.unl.edu/~sscott/ShowFiles/SQL/CheatSheet/SQLCheatSheet.html

Here are the types I'm going to use for each field:

1. id: SMALLINT (UNSIGNED) - an integer number ranging from 0-65535
2. username: VARCHAR (30) - a string with a varying number of characters and a length from 0-30
3. password: VARCHAR (30) - a string with a varying number of characters and a length from 0-30
4. name: VARCHAR (50) - a string with a varying number of characters and a length from 0-50
5. registerDate: TIMESTAMP - a variable that stores a date and time
6. lastLoginDate: TIMESTAMP - a variable that stores a date and time
7. introductionCode: BLOB - a string with a varying number of characters and a length from 0 to something huge - blob's can contain an incredibly large amount of text

Notice that I'm setting the id's SMALLINT field to have the UNSIGNED attribute. This means that its values will range from 0-65535 instead of -32768 to 32767 (we won't need negative numbers for an id).

Also, I'm turning the indexing property on for the id, username, registerDate, and lastLoginDate fields. This basically means that these fields will take a little longer to respond to newly added data, but searches that involve them will be faster. I turned indexing on for these 4 fields because they are likely to be searched for later on. Here's the final result of the table setup:

Then I clicked "Save" (be careful not to click "Go" - this will just add a new field like it says, and you could lose all of your table setup information). Now our table has been created:

Before I wrap this tutorial up, I would like to briefly address all the different variable types that I have used before:

• VARCHAR: I always use this for strings with lengths between 0-80 or so... Basically, if there's a single-line text box with data that's going into a MySQL database, this will store its data.


• SMALLINT, MEDIUMINT, INT, etc...: I always use integers for when things just need to be counted, whether they be for id's, number of likes, number of views, number of moves made so far, etc.


• FLOAT, DOUBLE, DECIMAL, etc.: I've never actually used these, but they would serve the purpose of storing numbers that are not integers, basically. You can read up more on their differences here and here.


• TIMESTAMP: I always use this for recording specifically when something has happened, such as when a user creates a new post or a user signs up.


• BOOL: I don't think I have used this either, but basically it can store a boolean value (true or false).


• BLOB: I always use this for massive amounts of text or data that has to be stored in a database (this text could for instance come from a multi-lined text box). If you need to use a lot of these, you should consider just storing your data in a file. Sometimes you may want to associate the data directly with the other data it's associated with in the database, and in that case you could use a BLOB. There are probably other reasons for using this that I am not aware of.

Now you know how to set up a database (and a table with fields in it) for later use! In the next tutorial, we'll take a look at writing PHP code to access this database and the new table we just created.