MySQL Tables

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:

<?php
// 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, 
PRIMARY KEY(id),
 name VARCHAR(30), 
 age INT)")
 or die(mysql_error());  

echo "Table Created!";

?>

Display:

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.

'name VARCHAR(30),'

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.

'age INT,'

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!

'or die(mysql_error());'

This will print out an error if there is a problem in the table creation process.

Posted in: MySQL