Building a Database-Driven Web Site Using PHP and MySQL

So what's SQL?

The set of commands we'll be using for the rest of this article to tell MySQL what to do is part of a standard called Structured Query Language, or SQL (pronounced either "sequel" or "ess-cue-ell"--take your pick). Commands in SQL are also called queries (I'll be using these two terms interchangeably in this article series).

SQL is the standard language for interacting with most databases, so even if you move from MySQL to a database like Microsoft SQL Server in the future, you'll find that most of the commands are identical. It's important that you understand the distinction between SQL and MySQL. MySQL is the database server software that you're using. SQL is the language that you're using to interact with the database.

Creating a Database

Those of you working on your Web host's MySQL server have probably already been assigned a database to work with. Sit tight, we'll get back to you in a moment. Those of you running a MySQL server that you installed yourselves will need to create a database for yourselves. Creating a database is just as easy as deleting one:

mysql> CREATE DATABASE jokes;

I chose to name the database jokes, since that fits with the example we're working with. Feel free to name the database anything you like, though. Those of you working on your Web host's MySQL server will likely have no choice in what to name your database, since it will usually already be created for you.

So now that we have a database, we need to tell MySQL that we want to use it. Again, the command isn't too hard to remember:

mysql> USE jokes;

You're now ready to start using your database. Since a database is empty until you add some tables to it, creating a table to hold our jokes will be our first order of business.

Creating A Table

The SQL commands we've encountered so far have been pretty simple, but since tables are so flexible it takes a more complicated command to create them. The basic form of the command is as follows:

mysql> CREATE TABLE <table name> (
    ->  <column 1 name> <col. 1 type> <col. 1 details>,
    ->  <column 2 name> <col. 2 type> <col. 2 details>,
    ->  ...
    -> );

Let's return to our example "Jokes" table. Recall that it had three columns: ID (a number), JokeText (the text of the joke), and JokeDate (the date the joke was entered). The command to create this table looks like this:

mysql> CREATE TABLE Jokes (
    ->  ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->  JokeText TEXT,
    ->  JokeDate DATE NOT NULL
    -> );

Pretty scary-looking, huh? Let's break it down:

The first line is pretty simple; it says that we want to create a new table called Jokes.
The second line says that we want a column called ID that will contain an integer (INT). The rest of this line deals with special details for this column. First, this column is not allowed to be left blank (NOT NULL). Next, if we don't specify any value in particular when adding a new entry to the table, MySQL should pick a value that is one more than the highest value in the table so far (AUTO_INCREMENT). Finally, this column is to act as a unique identifier for entries in this table, so all values in this column must be unique (PRIMARY KEY).
The third line is super simple; it says that we want a column called JokeText that will contain text (TEXT).
The fourth line defines our last column, called JokeDate, that will contain data of type DATE and which cannot be left blank (NOT NULL).

Note that, while you're free to type your SQL commands in upper or lower case, a MySQL server running on a Unix-based system will be case sensitive when it comes to database and table names, since these correspond to directories and files in the MySQL data directory. Otherwise, MySQL is completely case insensitive but for one exception: table, column, and other names must be spelled exactly the same when used more than once in the same command.

Note also that we assigned a specific type of data to each column we created. ID will contain integers, JokeText will contain text, and JokeDate will contain dates. MySQL requires you to specify a data type for each column in advance. Not only does this help keep your data organized, but it allows you to compare the values in a column in powerful ways (as we'll see later). For a complete list of supported MySQL data types, see the MySQL Reference Manual.

Anyway, if you typed the above command correctly, MySQL will respond with Query OK and your first table will be created. If you made a typing mistake, MySQL will tell you there was a problem with the query you typed and will try to give you some indication of where it had trouble understanding what you meant.

For such a complicated command, Query OK is pretty a pretty boring response. Let's have a look at your new table to make sure it was created properly. Type the following command:

mysql> SHOW TABLES;

The response should look like this:

+-----------------+
| Tables in jokes |
+-----------------+
| Jokes           |
+-----------------+
1 row in set

This is a list of all the tables in our database (which I named jokes above). The list contains only one table: the Jokes table we just created. So far everything looks good. Let's have a closer look at the Jokes table itself:

mysql> DESCRIBE Jokes;
+----------+---------+------+-----+------------+- -
| Field    | Type    | Null | Key | Default    | ...
+----------+---------+------+-----+------------+- -
| ID       | int(11) |      | PRI | 0          | ...
| JokeText | text    | YES  |     | NULL       |
| JokeDate | date    |      |     | 0000-00-00 |
+----------+---------+------+-----+------------+- -
3 rows in set

This provides a list of the columns (also known as fields) in the table. As we can see, there are three columns in this table, which appear as the 3 rows in this table of results. The details are somewhat cryptic, but if you look at them closely for awhile you should be able to figure out what most of them mean. Don't worry about it too much, though. We've got better things to do, like adding some jokes to our table!

We need to look at just one more thing before we get to that, though: deleting a table. This is just as frighteningly easy to do as deleting a database. In fact, the command is almost identical:

mysql> DROP TABLE <tableName>;

Adding some Jokes

SitePoint.com is a fast growing Web Developer Community. Kevin Yank is the Editor of the SitePoint TechTimes, a fresh, technically oriented newsletter for the serious Webmaster.

Related Pages:

Building a Database-Driven Web Site Using PHP and MySQL