Home News Links People Catalog

Lecture 9: Database Primer 2

Introduction to Databases Continued

Using your Database on the Server

You can use the phpAdmin tool to create tables and content in your own personal database. I have set up databases for everyone in class using the initials you generated for the dynphp directory on the server. Open your phpAdmin tool within your MAMP installation. You now have the full power of SQL to create tables, fill them with data, and query them using the SQL commands. You can create as many tables as you want for experience in this class. Lets do an example together using the parts database from this tutorial page.

MySQL Field Data Types

Here are most of the available column types for use with MySQL databases.

MySQL Datatypes

T y p e

S i z e

D e s c r i p t i o n


Length bytes

A fixed-length field from 0 to 255 characters long.


String length + 1 bytes

A fixed-length field from 0 to 255 characters long.


String length + 1 bytes

A string with a maximum length of 255 characters.


String length + 2 bytes

A string with a maximum length of 65,535 characters.


String length + 3 bytes

A string with a maximum length of 16,777,215 characters.


String length + 4 bytes

A string with a maximum length of 4,294,967,295 characters.


1 byte

Range of -128 to 127 or 0 to 255 unsigned.


2 bytes

Range of -32,768 to 32,767 or 0 to 65535 unsigned.


3 bytes

Range of -8,388,608 to 8,388,607 or 0 to 16,777,215 unsigned.


4 bytes

Range of -2,147,483,648 to 2,147,483,647 or 0 to 4,294,967,295 unsigned.


8 bytes

Range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 or 0 to 18,446,744,073,709,551,615 unsigned.


4 bytes

A small number with a floating decimal point.

DOUBLE[Length, Decimals]

8 bytes

A large number with a floating decimal point.

DECIMAL[Length, Decimals]

Length + 1 or Length + 2 bytes

A DOUBLE stored as a string, allowing for a fixed decimal point.


3 bytes

In the format of YYYY-MM-DD.


8 bytes

In the format of YYYY-MM-DD HH:MM:SS.


4 bytes

In the format of YYYYMMDDHHMMSS; acceptable range ends inthe year 2037.


3 bytes

In the format of HH:MM:SS


1 or 2 bytes

Short for enumeration, which means that each column can haveone of several possible values.


1, 2, 3, 4, or 8 bytes

Like ENUM except that each column can have more than one ofseveral possible values.

Many of the types can take an optional Length attribute, limiting their size (the square brackets, [], indicate an optional parameter to be put in parentheses, while parentheses themselves indicate required arguments). Further, the number types can be UNSIGNED—limiting the column to positive numbers or zero—or be defined as ZEROFILL, which means that any extra room will be padded with zeroes (ZEROFILLs are also automatically UNSIGNED). The various date types have all sorts of unique behaviors, which are documented in the manual at You'll primarily use the DATE and TIME fields without modification, so you need not worry too much about their intricacies. There are also two extensions of the TEXT types that result in a different behavior—ENUM and SET—which allow you to define a series of acceptable values when creating the table. An ENUM field can have only one of a possible several thousand values, while SET allows for several of up to 64 possible values. There are two caveats with ENUM and SET: These types are not supported by other databases, and their usage undermines normalization.

To choose your data types:

  1. Identify whether a column should be a text, number, or date type.

  2. This is normally an easy and obvious step. You will find that numbers such as ZIP codes and dollar amounts should be text fields if you include their corresponding punctuation (dollar signs, commas, and hyphens), but you'll get better results if you store them as numbers and address the formatting elsewhere.

  3. Choose the most appropriate subtype for each column.

  4. For improved performance, keep in mind two considerations:

    • Fixed-length fields (such as CHAR) are generally faster than variable-length fields (such as VARCHAR), but they also take up more disk space. See the side-bar for more information.

    • The size of any field should be restricted to the smallest possible value, based upon what the largest possible input could be. For example, if the largest a number such as Client ID could be is in the hundreds, set the column as an unsigned three-digit SMALLINT (allowing for up to 999 values).

    • You should keep in mind that if you insert a string five characters long into a CHAR(2) field, the final three characters will be truncated. This is true for any field in which the length is set (CHAR, VARCHAR, INT, etc.).

  5. Set the maximum length for text and number columns as well as other attributes such as UNSIGNED (Table 3.3).

    Rather than going over how I defined all 21 columns and why, I've listed the properties I came up with in Table 3.3. Different developers have different preferences, but the most important factor is to tailor each setting to the information at hand rather than using generic (and inefficient) TEXT and INT types at all times.

  6. The 4 most common SQL statements (SELECT, INSERT, UPDATE, and DELETE)

    Study the SELECT statement within PHP use

    Connecting to a database in PHP and inserting new content