Previous Section  < Day Day Up >  Next Section

7.3 Creating a Table

Before you can put any data into or retrieve any data from a database table, you must create the table. This is usually a one-time operation. You tell the database program to create a new table once. Your PHP program that uses the table may read from or write to that table every time it runs. But it doesn't have to re-create the table each time. If a database table is like a spreadsheet, then creating a table is like making a new spreadsheet file. After you create the file, you open it many times to read or change it.

The SQL command to create a table is CREATE TABLE. You provide the name of the table and the names and types of all the columns in the table. Example 7-5 shows the SQL command to create the dishes table pictured in Figure 7-1.

Example 7-5. Creating the dishes table
CREATE TABLE dishes (

    dish_id INT,

    dish_name VARCHAR(255),

    price DECIMAL(4,2),

    is_spicy INT

)

Example 7-5 creates a table called dishes with four columns. The dishes table looks like the one pictured in Figure 7-1. The columns in the table are dish_id, dish_name, price, and is_spicy. The dish_id and is_spicy columns are integers. The price column is a decimal number. The dish_name column is a string.

After the literal CREATE TABLE comes the name of the table. Then, between the parentheses, is a comma-separated list of the columns in the table. The phrase that defines each column has two parts: the column name and the column type. In Example 7-5, the column names are dish_id, dish_name, price, and is_spicy. The column types are INT, VARCHAR(255), DECIMAL(4,2), and INT.

Some column types include length or formatting information in the parentheses. For example, VARCHAR(255) means "a variable length character column that is at most 255 characters long." The type DECIMAL(4,2) means "a decimal number with two digits after the decimal place and four digits total." Table 7-2 lists some common types for database table columns.

Table 7-2. Common database table column types

Column type

Description

VARCHAR(length)

A variable length string up to length characters long.

INT

An integer.

BLOB[1]

Up to 64k of string or binary data.

DECIMAL(total_digits,decimal_places)

A decimal number with a total of total_digits digits and decimal_places digits after the decimal point.

DATETIME[2]

A date and time, such as 1975-03-10 19:45:03 or 2038-01-18 22:14:07.


[1] PostgreSQL calls this BYTEA instead of BLOB.

[2] Oracle calls this DATE instead of DATETIME.

Different database programs support different column types, although all database programs should support the types listed in Table 7-2. The maximum and minimum numbers that the database can handle in numeric columns and the maximum size of text columns varies based on what database program you are using. For example, MySQL allows VARCHAR columns to be up to 255 characters long, but Microsoft SQL Server allows VARCHAR columns to be up to 8,000 characters long. Check your database manual for the specifics that apply to you.

To actually create the table, you need to send the CREATE TABLE command to the database. After connecting with DB::connect( ), use the query( ) function to send the command as shown in Example 7-6.

Example 7-6. Sending a CREATE TABLE command to the database program
require 'DB.php';

$db = DB::connect('mysql://hunter:w)mp3s@db.example.com/restaurant');

if (DB::isError($db)) { die("connection error: " . $db->getMessage( )); }

$q = $db->query("CREATE TABLE dishes (

        dish_id INT,

        dish_name VARCHAR(255),

        price DECIMAL(4,2),

        is_spicy INT

)");

Section 7.4, explains query( ) in much more detail.

The opposite of CREATE TABLE is DROP TABLE. It removes a table and the data in it from a database. Example 7-7 shows the syntax of a query that removes the dishes table.

Example 7-7. Removing a table
DROP TABLE dishes

Once you've dropped a table, it's gone for good, so be careful with DROP TABLE!

    Previous Section  < Day Day Up >  Next Section