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 can 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 8-3 shows the SQL command to create the dishes table pictured in Figure 8-1.
Example 8-3. Creating the dishes table
CREATE TABLE dishes (
dish_id INTEGER PRIMARY KEY,
dish_name VARCHAR(255),
price DECIMAL(4,2),
is_spicy INT
)
Example 8-3 creates a table called dishes with four columns. The dishes table looks like the one pictured in Figure 8-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 8-3, the column names are dish_id, dish_name, price, and is_spicy. The column types are INTEGER, VARCHAR(255), DECIMAL(4,2), and INT.
Additionally, the dish_id column’s type has PRIMARY KEY after it. This tells the database program that the values for this column can’t be duplicated in this table. Only one row can have a particular dish_id value at a time. Additionally, this lets SQLite, the database program used in this chapter’s examples, automatically assign new unique values to this column when we insert data. Other database programs have different syntax for automatically assigning unique integer IDs. For example, MySQL uses the AUTO_INCREMENT keyword, PostgreSQL uses serial types, and Oracle uses sequences.
INT and INTEGER can generally be used interchangeably. However, a quirk of SQLite is that in order to get the automatic assign-new-unique-values behavior with PRIMARY KEY, you need to specify the column type INTEGER exactly.
Some column types include length or formatting information in 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 8-2 lists some common types for database table columns.
Different database programs support different column types, although all database programs should support the types listed in Table 8-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 new PDO(), use the exec() function to send the command as shown in Example 8-4.
Example 8-4. Sending a CREATE TABLE command to the database program
try {
$db = new PDO(‘sqtite:/tmp/restaurant.db’);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$q = $db->exec(“CREATE TABLE dishes (
dish_id INT,
dish_name VARCHAR(255),
price DECIMAL(4,2),
is_spicy INT
)”);
} catch (PDOException $e) {
print “Couldn’t create table: ” . $e->getMessage();
}
The next section explains exec() in much more detail. The call to $db- >setAttribute() in Example 8-4 ensures that PDO throws exceptions if there are problems with queries, not just a problem when connecting. Error handling with PDO is also discussed in the next section.
The opposite of CREATE TABLE is DROP TABLE. It removes a table and the data in it from a database. Example 8-5 shows the syntax of a query that removes the dishes table.
Example 8-5. Removing a table
DROP TABLE dishes
Once you’ve dropped a table, it’s gone for good, so be careful with DROP TABLE!
Source: Sklar David (2016), Learning PHP: A Gentle Introduction to the Web’s Most Popular Language, O’Reilly Media; 1st edition.