Creating a Database

Creating a Database

Databases are described by a very specific organization scheme. To illustrate database concepts, I will create and view a simple phone list. The basic structure of the phone list is shown in Table 7.1.

















The phone list shows a very typical simple data table. Database people like to give special names to the parts of the database, so I'll use this simple phone list to illustrate. Each row of the table is called a record. Records describe discrete entities. The list of records is called a table. Each record in a table has the same elements, which are called fields, (or sometimes simply columns). Every record in the table has the same field definitions, but records can have different values in the fields. The fields in a table are defined in specific ways. Because of the way database tables are stored in files, the computer must always know how much room to allocate for each field, so the size and type of each field is important. This particular database is defined with five fields. The id field is an integer. All the other fields contain string data.

Creating a Table

RDBMS programs use a special language called Structured Query Language(SQL) to create and manipulate databases. SQL is usually pretty easy to understand, compared to full-blown programming languages. You can usually guess what's going on even without a lot of knowledge. As an example, look at the following SQL code:

USE chapter7;

CREATE TABLE phoneList (
  firstName VARCHAR(15),
  lastName VARCHAR (15),
  email VARCHAR(20),
  phone VARCHAR(15)

DESCRIBE phonelist;

This code is an SQL script. It is like a PHP program in the sense it is a set of instructions for the computer to follow. However, the PHP interpreter doesn't directly interact with the SQL language. Instead, these commands are sent to another program. As a PHP programmer, you'll also be able to write code that sends commands to a database language. Just as your PHP code often writes code in HTML format for the browser to interpret, you'll be writing SQL code for the MySQL interpreter to use.

When this code is sent to an SQL-compliant database program (such as MySQL) it will create the database structure shown in Table 7.1.

Using a Database

It is possible that you will have several database projects working in the same relational database system. In my case, each chapter of this book that uses SQL has its own database. Sometimes your system administrator will assign you a database. In any case, you will probably need to invoke that database with the USE command.


The syntax of SQL is not exactly like that of PHP. SQL has a different culture, and it makes sense to respect the way SQL code has historically been written. SQL is generally not case-sensitive, but most SQL coders put all SQL commands in all uppercase. Also, when a bunch of SQL commands are placed in a file as this code will be, you usually end each line with a semicolon.


If you don't already have a database to USE, you can create one with the CREATE command. For example, to create a database called "myStuff," use these commands:

USE mystuff;

Creating a Table

To create a table, you must indicate the name of the table as well as each field in the table. For each field, you must list what type of data is held in the field, and (at least for text data) how many characters long the field will be. As an example, the following code creates the phoneList table:

CREATE TABLE phoneList (
  firstName VARCHAR(15),
  lastName VARCHAR (15),
  email VARCHAR(20),
  phone VARCHAR(15)

You can think of fields as being much like variables, but while PHP is easy-going about what type of data is in a variable, SQL is very picky about the type of data in fields. In order to create an efficient database, MySQL needs to know exactly how many bytes of memory to set aside for every single field in the database. The primary way it does this is to require the database designer to specify the type and size of every field in each table. Table 7.2 lists a few of the primary data types supported by MySQL.


Data type



Standard integer 2 billion (roughly)


Big integer 9 x 10 ^18th


Floating point decimal number 38 digits


Double precision floating point 308 digits


text with n digits. If actual value is less than n, field will be padded with trailing spaces


Text with n digits. Trailing spaces will automatically be culled


Date in YYYY-MM-DD format


Time in HH:MM:SS format


Year in YYYY format


While the data types listed in Table 7.2 are by far the most commonly used, MySQL supports many other data types as well. Look in the online help that ships with MySQL if you need a more specific data type. Other databases will have a very similar list of data types.

You might notice that it is not necessary to specify the length of numeric types (although you can determine a maximum size for numeric types as well as the number of digits you want stored in float and double fields). The storage requirements for numeric variables are based on the type itself.

Working with String Data in MySQL

Text values are usually stored in VARCHAR fields. These fields must include the number of characters allocated for the field. Both CHAR and VARCHAR fields have fixed lengths. The primary difference between them is what happens when the field contains a value shorter than the specified length. If you declare a CHAR field to have a length of 10 with

firstName VARCHAR(10);

and then later store the value 'Andy' into the field, the field will actually contain 'Andy ' (that is, Andy followed by six spaces). CHAR fields pad any remaining characters with spaces. The VARCHAR field type removes any padded spaces. Usually you will use the VARCHAR field type to store all your string data.

Finishing up the CREATE TABLE Statement

Once you understand field data types, the CREATE TABLE syntax makes a lot of sense. There are only a few more details to understand. Once you specify CREATE TABLE, use a pair of parentheses to indicate the field list. Each field has a name followed by its type (and length, if it's a CHAR or VARCHAR). The fields are separated by commas. You do not have to put each field on its own line or indent the field definitions, but I prefer to do so, because these practices make the code much easier to read and debug.

Creating a Primary Key

You might be curious about the very first field in the phone list database. Just to refresh your memory, the line that defines that field looks like this:


Most database tables have some sort of field like this that holds a numeric value. This special field is called the primary key.

Start example

A very simple database like the phone list could theoretically go without a primary key, but such fields are so important to more sophisticated databases that you might as well start putting them into even your first table. It's traditional to put a primary key in every table. In Chapter 9 "Data Normalization," you'll learn more about the relational data model. In that discussion you'll learn how keys are used to build powerful databases, and you'll learn more about creating proper primary keys. In fact, the adventure program you've already seen heavily relies on a key field even though there's only one table in the database.

End example

The code presented so far can be entered directly into the MySQL program. You can see the code and its results in Figure 7.6.

Click To expand
Figure 7.6: The MySQL command line tool after I created the phonelist table.

Using the DESCRIBE Command to Check the Structure of a Table

It can be useful to check the structure of a table, especially if somebody else created it or you don't remember exactly what types or sizes of fields are in the table. The DESCRIBE command lets you view the structure of a table.

Inserting Values

Once you've created a table, you can begin to add data to it. The primary tool for adding records to a table is the INSERT command.

  0, 'Andy', 'Harris', '', '123-4567'

The INSERT statement allows you to add a record into a database. The values must be listed in exactly the same order the fields were defined. Each value is separated by a comma, and all VARCHAR and CHAR values must be enclosed in single quotes.

If you have a large amount of data to load into a database, you can also use the LOAD DATA command. This command accepts a tab-delimited text file with one row per record and fields separated by tabs. It then loads that entire file into the database. This is often the fastest way to load a database with test data. The following line loads data from a file called "addresses.txt" into the phoneList table:

LOAD DATA LOCAL INFILE "addresses.txt" INTO TABLE phonelist;

Figure 7.7 shows the MySQL tool after I have added one record to the table.

Click To expand
Figure 7.7: MySQL tells you the operation succeeded, but you don't get a lot more information.
Start example

As you are building a database, you will need to populate the database with test values. You don't want to use actual data at this point, because your database will not work correctly until you've messed with it for some time. However, your test values should be reflective of the kinds of data your database will actually house. This will help you spot certain problems like field lengths that are too small or fields that are missing.

End example

Selecting Results

Of course, you'll want to see the results of all your table-building activities. If you want to see the data in a table, you can use the SELECT command. This is perhaps the most powerful command in SQL, but its basic use is quite simple. To see all of the data in the phonelist database, use this command:

SELECT * FROM phonelist

This command grabs all fields of all records of the phonelist database and displays them in a table format.

Figure 7.8 shows what happens after I add a SELECT statement to get the results of the phone list.

Click To expand
Figure 7.8: The result of the SELECT statement is a table just like the original plan.