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.

Building the database

Building the database means turning the paper database design into a working database. Building the database is independent of the PHP programs that your application uses to interact with the database. The database can be accessed using programming languages other than PHP, such as Perl, C, or Java. The database stands on its own to hold the data. You should build the database before writing the PHP programs. The PHP programs are written to move data in and out of the database, so you can’t develop and test them until the database is available. The database design names the database and defines the tables that make up the database. To build the database, you communicate with MySQL by using the SQL language. You tell MySQL to create the database and to add tables to the database. You tell MySQL how to organize the data tables and what format to use to store the data.