Let's go back to the command line to construct a database from the mysql client. To create a database called sample_db, you just have to say:
Removing it is just as simple:
The number of rows mysql counts as having been affected doesn't include the ones it has removed. You could have just deleted thousands of data entries, but from the information presented here, you'd have no way of knowing! This is all the more reason to use extreme caution—this small, harmless-looking command will totally wipe out the specified database and all the tables in it, and won't even hint at what it's done, let alone ask you for confirmation. You have been warned!
Assuming you just got rid of your sample_db database, CREATE it again. You'll use it rather a lot in the next couple of chapters, and the rest of the examples in this chapter take you through the steps to set it up with a structure and records.
Creating a database table is a little more complicated. Let's say you want to create a table called user. Start off with the obvious syntax:
Okay, you're going to need a little more than that. Although a database can quite happily exist without any tables living under its roof, tables are rather more fussy. Give it some structure by putting a list of the field descriptors within parentheses following the table name. The field descriptors each follow the form:
For example, you could create a very simple two-field table with:
You already had a look at the various types of variables supported by MySQL. You also have the option of specifying attributes for each of the fields—to do so, simply append them to the descriptor. You can use any combination of the attributes described in the following table.
After you've listed all those that you need, you can specify selected fields as holding indexes, primary keys, and unique values. Here's a quick recap of what each of these entails:
-
KEY/INDEX: These are synonymous keywords that specify fields to be used as indexes. The specified field entries are copied to a separate index table, where they're listed against pointers to the corresponding entries in the original table. They're then sorted, producing an index much the same as you'd find in the back of a book. If you specify multiple fields ("field1", "field2",...) for your index, the index sorts first on "field1", and then any duplicate values in "field1" are sorted on "field2", and so on.
-
PRIMARY KEY: You can specify exactly one field in any given table as a primary key; it is then be used as an index of unique values. Consequently, each entry can be used in other tables as a unique reference to the record to which it belongs—it's essentially the glue that holds a relational database together. To use a field as a primary key, it must be declared NOT NULL. Once again, if you pass more than one field name when defining a primary key, the specified field names are combined to create a primary key.
-
UNIQUE: The value of each entry in a unique field must be unique among all other entries in the same field. That is, if one record's entry in the field contains the value 10, it's guaranteed that no other entry in that field will have the same value. As mentioned, uniqueness is a requirement for AUTO_INCREMENT fields.
In the course of the next couple of chapters, you'll see a lot of examples based around a homemade relational database comprising two tables. The first is called user, and it serves as a record of all users registered on an imaginary Web server. The other, access_log, contains relative paths to the Web pages each user accesses, plus counters measuring how many times she visits each page. Both tables are housed in the sample_db database.
The first table, user, is structured as follows:
The usernumber field has an AUTO_INCREMENT attribute, so the field is automatically incremented (that is, its value increased by 1) whenever a new record is inserted with a NULL value in the first field. For example, when a new user registers and his record is added to the user table, the user number assigned will be one more than the largest number that's already in the table. You also declare this field UNIQUE, ensuring that no two rows can have the same value.
The fields userid and userpassword are both variable-length strings (lengths specified as 8 and 20 characters, respectively). They are declared BINARY, and are therefore case-sensitive. By default, character fields are case-insensitive.
The userid field will be defined as a primary key and used to establish a relationship with the access_log table, which also has a userid field.
The userposition field describes the position that a player on the team holds—in the example there are values such as Mid and Link, but you could easily modify this to football positions such as quarterback or wide receiver if your database was used to store information about an American football team.
The second table, access_log, is defined as follows:
A TIMESTAMP type field can always take a NULL value because it stores the current system time in YYYMMDDhhmmss format when a new record is inserted, or an existing record is updated, unless it is explicitly given a date and time to store.
The access_log table has the same userid field as the user table. The userid field serves as the key to define a relationship between the two tables.
Now that you've defined the structures of the tables, let's create them and their home, the sample_db database.
If you're dreading the prospect of typing in the following 12-line SQL statement (because you don't want to have to retype the whole thing when it doesn't work the first time), here are a couple of suggestions.
If you're running PHP from UNIX, use the edit command from the mysql command line, which calls the text editor of your choice (this command doesn't work on Windows). This is held in the $EDITOR environment variable, and is typically the vi editor: mysql> edit. If you make a typo, just reissue the edit command again. Your editor still has what you previously typed in.
The previous command is remembered by the mysql client program. You can go back a single line by pressing the up arrow key.
You can save your lines of SQL in a text file and feed it to the mysql client: > mysql uphpuser pphppass hlocalhost < query.sql
The following CREATE TABLE command creates the user table in the sample_db database:
The following CREATE TABLE command creates the access log table:
The userid in the access_log table isn't declared as a primary key or a unique value because a user can access multiple Web pages, and each visited Web page constitutes a record in the table. Instead, the combination of userid and page is defined as the primary key.
Creating the Sample Database and Tables with PHP
If all has gone according to plan, you are now the proud owner of a couple of tables, user and access_log. All well and good, but is the main topic slipping away? The fact is that it's just as easy to generate these tables from PHP scripts. Let's see how you might create the same database and tables using PHP instead of the mysql client.
First, you need a way to issue queries to the MySQL server from within PHP. The mysql_query() function is for precisely that purpose. It takes a query string as its first argument, and makes that query on the currently selected database, using the link identifier specified as the second argument. (As usual, you can omit the identifier, in which case the last opened link is assumed.) If no link is currently open, it attempts to establish a connection first.
Do not use semicolons to terminate query strings when using them as arguments to PHP functions because they may cause your script to kick up errors.
If the given query is successfully executed, mysql_query() returns a non-zero value pointing to the returned result set, or False upon error. The following call is equivalent to having made the query SHOW DATABASES in the mysql client:
This passes the expected list of databases to a table, returning a pointer to that table. You then just fetch each row and echo it out. It should look like this:
$link_id could have been omitted from the previous code snippet because the mysql_query() function would have defaulted to the last opened connection.
If you want to specify the name of the database to issue a query on, you can use the PHP function mysql_db_query() like this:
This call returns the list of tables available in the sample.db database—even if you haven't used mysql_select_db() to select it explicitly.
You can also use mysql_query() to create and drop databases:
PHP provides you with another alternative—a pair of functions specifically designed for creating and dropping databases: mysql_create_db() and mysql_drop_db(). Both take the name of a database to be created or dropped as the first argument, followed by an optional link identifier, and return True if a database is successfully created/dropped and False upon error. For example:
Try it Out: Create a Database and Tables The following script is an alternative to your earlier exploits at the command line. First, it creates the sample_db database, and then it defines the tables user and access_log as specified previously.
Assuming that you followed along with the command line example, running this script would generate a nice friendly error:
To overcome this, simply go back to the command, and use the mysql client to DROP the sample_db database, and then run the script again. You should see the following output:
How it Works
The script is very straightforward. It begins by including common_db.inc, giving you use of the functions db_connect() and sql_error(), which you defined earlier:
You then define the variables that describe the two tables. This is where all the hard work pays off—you just have to specify the same field descriptors that you did at the command line:
Connect to the server with the following:
Create the sample_db database:
And then select it with mysql_select_db():
Subsequent calls to mysql_query() create the user and access_log tables. If an error occurs during a query, the error number and text are displayed by calling the sql_error() function.
If you make it through to the end of the script, you confirm a successful run:
Recall that the sql_error() function calls the mysql_errno() and mysql_error() functions if the $MYSQL_ERRNO variable is empty:
This ensures that the function returns the error number and text even when a database connection is successful but a subsequent query fails.
Altering Tables
So you've created a database, and populated it with carefully constructed tables. You've used it for a while and not had too much trouble—it's not perfect, but it does the job. Then out of the blue, you realize one day that your little database isn't all it's cracked up to be. You've caught on to the fact that your tables are badly designed. You've found some new data that doesn't quite fit the format you expected. Any number of things can make you sit up and realize that you need to modify the tables in your database.
MySQL provides the ALTER TABLE command to do just that. With this command you can:
-
Add and delete fields or indexes (ADD and DROP).
-
Change the definition of existing fields (ALTER, CHANGE, and MODIFY).
-
Rename fields (CHANGE) or even the table itself (RENAME AS).
For example, if you wanted to change the name of the table test to tested, you could issue the following command:
The AS keyword is optional—this works just as well:
Let's add a new ENUM field to the user table. Call it sex; it can be used to indicate whether a user is male or female:
The new field is added as the last field in the table. You can insert a new field in the middle of a table by means of the keyword AFTER. Now drop the field so that you can insert it again:
To place the new sex field right after the username field, you could say:
To place a new field at the start of the field list, you'd use the FIRST keyword instead, because there would be no preceding field.
If you're running a site aimed at female visitors, you'd probably want the default value of the sex field changed from M to F:
To change the field's name and its definition, you can use the CHANGE keyword:
Adding or dropping indexes or primary keys is just as easy:
Experiment a little with the ALTER TABLE command, and you'll soon master it. As an exercise, change back the structure of the user table.
Altering table structures in PHP is as simple as issuing any other query using the mysql_query() function. Assuming you're connected to the sample_db database, the following line is all it takes to drop the index registerdate:
Inserting Data into a Table
It's time to insert some data into the tables you created. (You'll see how to insert data using PHP in the next chapter, where it's dealt with in great detail.)
Unsurprisingly, you use the SQL command INSERT to insert new records into a table. Try this:
If you're inserting a string value, you have to specify it in quotes.
If you wanted to carry on using this syntax, you'd need to specify all the values to be inserted into every corresponding field in a table. You give NULL as a value for the accessdate field, which is of type TIMESTAMP so that it saves the current system time in the format YYYYMMDDhhmmss. You provide a NULL or 0 value to an integer type field with an AUTO_INCREMENT attribute.
If you want to insert values into only a subset of the fields in a table, you use a slightly different syntax:
Assuming this query is successful, the specified fields are given the values listed, whereas the rest are assigned default values. For example, accessdate is a timestamp field, so its default is the current system time—just what you want:
Escaping Quotes
When you insert a string value into a character or text type fields, make sure no unescaped single quotes are inserted, otherwise you're bound to see an error:
Even if you issue the query terminating it with a semicolon, the mysql client insists on getting more of it because the MySQL server expects another single quote to pair off the last one. That's why there's a quote mark before the prompt. When you supply one more single quote to satisfy the server's expectation, it generates an error. The solution? You can either escape the inside single quote:
Or use double quotes to surround the string value:
Remember that a backslash (which is used to denote a directory in a path on the DOS/Windows platform, for example) also needs to be escaped. In this case, use a double backslash:
If you try to insert a new record that contains the same value for a primary or unique key of an existing record, an error occurs:
The MySQL server complains that you are trying to insert a duplicate record that contains the same value for a primary key, userid. User Pads already exists in the table. Because the userid field is defined as a primary key, no duplicate entries are allowed.
However, you might want to insert a new record overwriting the existing one. Say, for example, that you inserted wrong values for a user record and rather than deleting the record and inserting it again, you want to replace it with a new correct one. In this case, you'd use the REPLACE command. The only difference between the INSERT and REPLACE is this: if the primary key for the new record duplicates an existing one, REPLACE overwrites the existing record, although INSERT won't, as you've seen.
Populating the Database Tables
Now you're all set to put some records into your tables. For example, you can use the following INSERT command to insert the record for the user Pads:
Use the function password() to encrypt the password 12345. This is a MySQL server function that you'll learn more about these in the next chapter.
Give a NULL value to the usernumber field—you'll remember that because it's an AUTO_INCREMENT field, this automatically adds one to the number assigned to each new user.
Here's how I've populated the user table. Please note that a password field isn't included because the values saved by mysql will be encrypted anyway. You should insert whatever values you want for the password field for each user:
The following command inserts an access log record for user Greeny in the access_log table:
Again, the accessdate field would normally be assigned a NULL value. The access_log table need only contain a few dummy records for use in the upcoming examples. It will be populated automatically toward the end of the database chapters by the application you build, which uses your table to log the number of accesses made on your fictional Web site.
Congratulations! You now have a database, populated and ready for use!
