How to add tables to a database?

You can add tables to any database, whether it’s a new, empty database that you just created or an existing database that already has tables and data in it. You use the CREATE query to add tables to a database. Because a table is created in a database, you must indicate the database name where you want the table created. That is, when using the form , you must type a database name in the top field. If you don’t, you see the error message No Database Selected.
The query to add a table begins with

CREATE TABLE tablename

Next comes a list of column names with definitions. The information for each column is separated from the information for the next column by a comma. The entire list is enclosed in parentheses. Each column name is followed by its data type and any other definitions required. Here are some definitions that you can use:

• NOT NULL: This column must have a value; it can’t be empty.
  
• DEFAULT value: This value is stored in the column when the row is created if no other value is given for this column.
• AUTO_INCREMENT: You use this definition to create a sequence number. As each row is added, the value of this column increases by one integer from the last row entered. You can override the auto number by assigning a specific value to the column.
• UNSIGNED: You use this definition to indicate that the values for this numeric field will never be negative numbers.

The last item in a CREATE TABLE query indicates which column or combination of columns is the unique identifier for the row — the primary key.
Each row of a table must have a field or a combination of fields that is different for each row. No two rows can have the same primary key. If you attempt to add a row with the same primary key as a row already in the table, you get an error message, and the row is not added. The database design identifies the primary key. You specify the primary key by using the following format:

CREATE TABLE Member (
loginName VARCHAR(20) NOT NULL,
createDate DATE NOT NULL),
PRIMARY KEY(columnname) )

The columnname is enclosed in parentheses. If you’re using a combination of columns as the primary key, include all the column names, separated by commas. For instance, you would designate the primary key for the Login table in the MemberDirectory database by using this in the CREATE query:
PRIMARY KEY (loginName,loginTime)
Listing below shows the CREATE TABLE query used to create the Member table of the MemberDirectory database. You could enter this query on a single line if you wanted to. MySQL doesn’t care how many lines you use. However, the format shown in Listing below makes it easier to read. This human-friendly format also helps you spot typos.

CREATE TABLE Member (
loginName VARCHAR(20) NOT NULL,
createDate DATE NOT NULL,
password CHAR(255) NOT NULL,
lastName VARCHAR(50),
firstName VARCHAR(40),
street VARCHAR(50),
city VARCHAR(50),
state CHAR(2),
zip CHAR(10),
email VARCHAR(50),
phone CHAR(15),
fax CHAR(15),
PRIMARY KEY(loginName) )

Notice that the list of column names in Listing above is enclosed in parentheses (one on the first line and one on the last line), and a comma follows each column definition.
Remember not to use any MySQL reserved words for column names. If you do, MySQL gives you an error message that looks
like this:
You have an error in your SQL syntax near ‘order var(20))’ at line 1
Note that this message shows the column definition that it didn’t like and the line where it found the offending definition. However, the message doesn’t tell you much about what the problem is. The error in your SQL syntax that it refers to is the use of the MySQL reserved word order as a column name. After a table has been created, you can query to see it, review its structure, or remove it.

• To see the tables that have been added to a database, use this query: SHOW TABLES
• To see the structure of a table, use this query: DESCRIBE tablename
• To remove any table, use this query: DROP TABLE tablename

Use DROP carefully because it is irreversible. After a table is dropped, it is gone forever. And any data that was in it is gone as well.

How to delete a database?

You can delete any database with this SQL query:
DROP DATABASE databasename
Use DROP carefully because it is irreversible. After a database is dropped, it is gone forever. And any data that was in it is gone as well.

How to create a new database?

To create a new, empty database, use the following SQL query:
CREATE DATABASE databasename where databasename is the name that you give the database. For instance, these two SQL queries create the sample databases used in this book:
CREATE DATABASE PetCatalog
CREATE DATABASE MemberDirectory
Some Web hosting companies don’t allow you to create a new database. You are given one database to use with MySQL, and you can create tables in only this one database. You can try requesting another database, but you need a good reason. MySQL and PHP don’t care that all your tables are in one database instead of organized into databases with meaningful names. It’s just easier for humans to keep track of projects when they’re organized.
To see for yourself that a database was in fact created, use this SQL query:
SHOW DATABASES
After you create an empty database, you can add tables to it.

A quick way to send SQL queries to the MySQL server

When MySQL is installed, a simple, text-based program called mysql (or sometimes the terminal monitor or the monitor) is also installed. Programs that communicate with servers are client software; because this program communicates with the MySQL server, it’s a client. When you enter SQL queries in this client, the response is returned to the client and displayed onscreen. The monitor program can send queries across a network; it doesn’t have to be running on the machine where the database is stored.
To send SQL queries to MySQL by using the mysql client, follow these steps:

• Locate the mysql client. By default, the mysql client program is installed in the subdirectory bin, under the directory where MySQL is installed. In Unix/Linux, the default is /usr/local/mysql/bin or /usr/local/bin. In Windows, the default is c:\Program Files\MySQL\MySQL Server 5.0\bin. However, the client might be installed in a different directory. Or, if you’re not the MySQL administrator, you might not have access to the mysql client. If you don’t know where MySQL is installed or can’t run the client, ask the MySQL administrator to put the client somewhere where you can run it or to give you a copy that you can put on your own computer.
• Start the client. In Unix and Linux, type the path/filename (for example, /usr/local/mysql/bin/ mysql). In Windows, open a command prompt window and then type the path\filename (for example, c:\ Program Files\MySQL\MySQL Server 5.0\bin\mysql). This command will start the client if you don’t need to use an account name or a password. If you need to enter an account or a password or both, use the following parameters:
• u user: user is your MySQL account name.
• p: This parameter prompts you for the password for your MySQL account.

For instance, if you’re in the directory where the mysql client is located, the command might look like this:

mysql -u root -p

• If you’re starting the mysql client to access a database across the network, use the following parameter after the mysql command:
• h host: host is the name of the machine where MySQL is located.

For instance, if you’re in the directory where the mysql client is located, the command might look like this:
mysql -h mysqlhost.mycompany.com -u root -p
Press Enter after typing the command.

• Enter your password when prompted for it. The mysql client starts, and you see something similar to this: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 459 to server version: 5.0.15 Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer. mysql>
• Select the database that you want to use. At the mysql prompt, type the following: use databasename Use the name of the database that you want to query.
• At the mysql prompt, type your SQL query followed by a semicolon (;), and then press the Enter key. The mysql client continues to prompt for input and does not execute the query until you enter a semicolon. The response to the query is displayed onscreen.
• To leave the mysql client, type quit at the prompt and then press the Enter key.

Building SQL queries

SQL (Structured Query Language) is the computer language that you use to communicate with MySQL. SQL is almost English; it is made up largely of English words, put together into strings of words that sound similar to English sentences. In general (fortunately), you don’t need to understand any arcane technical language to write SQL queries that work. The first word of each query is its name, which is an action word (a verb) that tells MySQL what you want to do. The queries that I discuss in this chapter are CREATE, DROP, ALTER, SHOW, INSERT, LOAD, SELECT, UPDATE, and DELETE. This basic vocabulary is sufficient to create — and interact with —databases on Web sites.

The query name is followed by words and phrases — some required and some optional — that tell MySQL how to perform the action. For instance, you always need to tell MySQL what to create, and you always need to tell it which table to insert data into or to select data from.
The following is a typical SQL query. As you can see, it uses English words:

SELECT lastName FROM Member

This query retrieves all the last names stored in the table named Member.
More complicated queries, such as the following, are less English-like:

SELECT lastName,firstName FROM Member WHERE state=”CA” AND city=”Fresno” ORDER BY lastName

This query retrieves all the last names and first names of members who live in Fresno and then puts them in alphabetical order by last name. This query is less English-like but still pretty clear.
Here are some general points to keep in mind when constructing an SQL query, as illustrated in the preceding sample query:

• Capitalization: In this book, I put SQL language words in all caps; items of variable information (such as column names) are usually given labels that are all or mostly lowercase letters. I did this to make it easier for you to read — not because MySQL needs this format. The case of the SQL words doesn’t matter; for example, select is the same as SELECT, and from is the same as FROM, as far as MySQL is concerned. On the other hand, the case of the table names, column names, and other variable information does matter if your operating system is Unix or Linux. When using Unix or Linux, MySQL needs to match the column names exactly, so the case for the column names has to be correct — for example, lastname is not the same as lastName. Windows, however, isn’t as picky as Unix and Linux; from its point of view, lastname and lastName are the same.
• Spacing: SQL words must be separated by one or more spaces. It doesn’t matter how many spaces you use; you could just as well use 20 spaces or just 1 space. SQL also doesn’t pay any attention to the end of the line. You can start a new line at any point in the SQL statement or write the entire statement on one line.
• Quotes: Notice that CA and Fresno are enclosed in double quotes (“) in the preceding query. CA and Fresno are series of characters called text strings, or character strings. You are asking MySQL to compare the text strings in the SQL query with the text strings already stored in the database. When you compare numbers (such as integers) stored in numeric columns, you don’t enclose the numbers in quotes.

Communicating with MySQL

The MySQL server is the manager of your database:

• It creates new databases.
• It knows where the databases are stored.
• It stores and retrieves information, guided by the requests, or queries, that it receives.

To make a request that MySQL can understand, you build an SQL query and send it to the MySQL server.

Writing the programs

Your programs perform the tasks for your Web database application. They create the display that the user sees in the browser window. They make your application interactive by accepting and processing information typed in the browser window by the user. They store information in the database and get information out of the database. The database is useless unless you can move data in and out of it.

The plan that you develop outlines the programs that you need to write. In general, each task in your plan calls for a program. If your plan says that your application will display a form, you need a program that displays a form. If your plan says that your application will store the data from a form, you need a program that gets the data from the form and puts it in the database.

The PHP language was developed specifically to write interactive Web applications. It has the built-in functionality needed to make writing application programs as painless as possible. Methods were included in the language specifically to access data from forms, to put data into a MySQL database, and to get data from a MySQL database.