A MySQL table is completely different than the normal table that you eat dinner
on. In MySQL and other database systems, the goal is to store information in an orderly
fashion. The table gets this done by making the table up of columns and rows.
The columns specify what the data is going to be, while the rows contain the actual data.
Below is how you could imagine a MySQL table. (C = Column, R = Row)
|C1 (Name)||C2 (Age)||C3 (Weight)
|R1||R1 C1 (John)||R1 C2 (21)||R1 C3 (120)
|R2||R2 C1 (Big Sally)||R2 C2 (27)||R2 C3 (400)
|R3||R3 C1 (Tiny Tim)||R3 C2 (6)||R3 C3 (35)
|R4||R4 C1 (Normal Ned)||R4 C2 (35)||R4 C3 (160)
We added the row and column number (R# C#) so that you can see that a row is side-to-side, while a column is up-to-down. In a real MySQL table only the value would be stored, not the R# and C#!
This table has three categories, or "columns", of data: Name, Age, and Weight. This table has
four entries, or in other words, four rows.
Create Table MySQL
Before you can enter data (rows) into a table, you must first define what kinds of data will be stored (columns).
We are now going to design a MySQL query to summon our table from database land. In future lessons we will
be using this table, so be sure to enter this query correctly!
PHP & MySQL Code:
// Make a MySQL Connection
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());
// Create a MySQL table in the selected database
mysql_query("CREATE TABLE example(
id INT NOT NULL AUTO_INCREMENT,
echo "Table Created!";
Wow! That's a lot of code all at once! Let's get down in the dirt and figure
this stuff out. We will be going through the code line by line.
'mysql_query ("CREATE TABLE example'
The first part of the mysql_query told MySQL that we wanted to create a new
table. The two capitalized words are reserved MySQL keywords.
The word "example" is the name of our table, as it came directly after "CREATE
TABLE". It is a good idea to use descriptive names when creating a table, such as:
employee_information, contacts, or customer_orders. Clear names will ensure that
you will know what the table is about when revisiting it a year after you make it.
'id INT NOT NULL AUTO_INCREMENT'
Here we create a column "id" that will automatically increment each
time a new entry is added to the table. This will result in the first row in the table having an id = 1, the second row id = 2, the third row id = 3, and so on.
The column "id" is not something that we need to worry about
after we create this table, as it is all automatically calculated within MySQL.
Reserved MySQL Keywords:
Here are a few quick definitions of the reserved words used
in this line of code:
- INT - This stands for integer or whole number. 'id' has been defined to be an integer.
- NOT NULL - These are actually two keywords, but they combine together
to say that this column cannot be null. An entry is NOT NULL only if it has some value, while something with no value is NULL.
- AUTO_INCREMENT - Each time a new entry is added the value will
be incremented by 1.
'PRIMARY KEY (id)'
PRIMARY KEY is used as a unique identifier for the rows. Here we have
made "id" the PRIMARY KEY for this table. This means that
no two ids can be the same, or else we will run into trouble. This is why we made
"id" an auto-incrementing counter in the previous line of code.
Here we make a new column with the name "name"! VARCHAR stands for "variable character". "Character" means that you can put in any kind of typed information in this column (letters, numbers, symbols, etc). It's "variable" because it can adjust its size to store as little as 0 characters and up to a specified maximum number of characters.
We will most likely only be using this name column
to store characters (A-Z, a-z). The number inside the parentheses sets the maximum number of characters. In this case, the max is 30.
Our third and final column is age, which stores an integer. Notice that there are no
parentheses following "INT". MySQL already knows what to do with an integer. The
possible integer values that can be stored in an "INT" are -2,147,483,648 to 2,147,483,647,
which is more than enough to store someone's age!
This will print out an error if there is a problem in the table creation process.
Using the MySQL administration tool that your web host has, check to see
if the table was created correctly. Afterwards, try creating a few of your own, with PHP or with a MySQL administration tool, to be sure that you have gotten the hang of it.
Download Tizag.com's MySQL Book
If you would rather download the PDF of this tutorial, check out our
MySQL eBook from the Tizag.com store.
You may also be interested in getting the PHP eBook
Found Something Wrong in this Lesson?
Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time!