Part 2: Creating Tables within the Database

Now that you have created a database, it’s time to create a table to hold data. In order to do this, we use the phpMyAdmin icon, which is the third figure from the left in the figure shown below.

Database Menu

When you click on the icon, you should see the following main page appear (or something similar, since there are many versions of phpMyAdmin):

phpMyAdmin

As a simple example, we’ll create a table that will hold the names, e-mail address, and social security number for a small business. I will show you how to do this in two different ways. The first involves using the structured query language, and the other method involves using the GUI (graphical user interface). The name of the table that will be created will be called “employees”. Visually, the table would look something like this:

Table of Sample Data

Method 1: Using the SQL Window

The first thing we want to do is to select the database wherein we will be creating our table. On the left hand side of the screen, choose the database you created earlier, using the drop down menu.

Choose Database

As mentioned before, we are going to first create the table manually using the MySQL query window. Click on the SQL Button icon, and the following query screen will appear:

SQL Window

To create and initialize the table, type in the following command into the window:


CREATE TABLE employees
(
emp_id int unsigned not null auto_increment primary key,
name varchar(40),
email varchar(40),
social varchar(11)
);

Once you do this, you will be informed that the query was completed successfully.
SQL Confirmation

What does all this mean?

emp_id is a special field that we will discuss at the end of this section.
name is the field where the name of the employee is stored (maximum of 40 characters).
email is the field where the email of the employee is stored(max of 40 characters).
social is the field where the social security number is stored (max of 11 characters).

Method 2: Using the Graphical User Interface

The second way to create and initialize tables is through the graphical user interface of phpMyAdmin. Some people may find using phpMyAdmin to manage their databases much easier than using the SQL window. First, select the database you want to work with.

Choose Database

Next, you enter in the name of the table you want to create, and how many data fields you want to have. We will attempt to create the same table as before, using a different method. Let’s name this table employees2 and set the number of fields to 4.

Create Table

Next, you’ll see a screen that looks like this:

Set Attributes

Don’t be intimidated by all the options, and fill it in accordingly:

Fill In Attributes

In addition, the emp_id row needs some more tweaks:

Extra Attributes

Once this is complete, save the table. You should see the following screen:

Query Completed

Congratulations, you have just created and initialized a table. Now we are ready to enter in data! But before we do that, we will address why we added the emp_id field.

The emp_id field assigns a unique number to each record. Over the course of several years, it is very possible that two employees might have the same name. Now, when that happens, how can we distinguish the records of these two employees unless we give them unique identification numbers? If we have a column with unique values, we can easily distinguish the two records. Luckily, MySQL does this for us automatically by auto incrementing this field everytime a new record is added!

Pages: 1 2 3