Build Your Own Database Driven Web Site Using PHP & MySQL, Part 2: Introducing MySQL

Structured Query Language

The set of commands we’ll use to direct MySQL throughout the rest of this book is part of a standard called Structured Query Language, or SQL (pronounced as either “sequel” or “ess-cue-ell”—take your pick). Commands in SQL are also referred to as queries; I’ll use these two terms interchangeably.

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 use to interact with that database.

Learn SQL in Depth

In this book, I’ll teach you the essentials of SQL that every PHP developer needs to know.

If you decide to make a career out of building database driven web sites, you’ll find that it pays to know some of the more advanced details of SQL, especially when it comes to making your sites run as quickly and smoothly as possible.

If you’d like to dive deeper into SQL, I highly recommend the book Simply SQL by Rudy Limeback (Melbourne: SitePoint, 2008).

Creating a Database

When the time comes to deploy your first database driven web site on the Web, you’ll likely find that your web host or IT department has already created a MySQL database for you to use. Since you’re in charge of your own MySQL server, however, you’ll need to create your own database to use in developing your site.

It’s just as easy to create a database as it is to delete one:

mysql> CREATE DATABASE ijdb;

I chose to name the database ijdb, for Internet Joke Database (With a tip of the hat to the Internet Movie Database), because that fits with the example I gave at the beginning of this chapter—a web site that displays a database of jokes. Feel free to give the database any name you like, though.

Now that you have a database, you need to tell MySQL that you want to use it. Again, the command is easy to remember:

mysql> USE ijdb;

You’re now ready to use your database. Since a database is empty until you add some tables to it, our first order of business will be to create a table that will hold your jokes (now might be a good time to think of some!).

Creating a Table

The SQL commands we’ve encountered so far have been reasonably simple, but as 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 (  
   ->   column1Name column1Type column1Details,  
   ->   column2Name column2Type column2Details,  
   ->   ?  
   -> ) DEFAULT CHARACTER SET charset;

Let’s continue with the joke table I showed you in the previous figure. You’ll recall that it had three columns: id (a number), joketext (the text of the joke), and jokedate (the date on which the joke was entered). This is the command to create that table:

mysql> CREATE TABLE joke (  
   ->   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  
   ->   joketext TEXT,  
   ->   jokedate DATE NOT NULL  
   -> ) DEFAULT CHARACTER SET utf8;

Looks scary, huh? Let’s break it down:

CREATE TABLE joke (

This first line is fairly simple; it says that we want to create a new table named joke. The opening parenthesis (() marks the beginning of the list of columns in the table.

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

This second line says that we want a column called id that will contain an integer (INT), that is, a whole number. The rest of this line deals with special details for the column:

1. First, when creating a row in this table, this column is not allowed to be left blank (NOT NULL).


2. Next, if we omit specifying a particular value for this column when we add a new entry to the table, we want MySQL to automatically pick a value that is one more than the highest value in the table so far (AUTO_INCREMENT).


3. Finally, this column is to act as a unique identifier for the entries in the table, so all values in this column must be unique (PRIMARY KEY).

joketext TEXT,

This third line is super simple; it says that we want a column called joketext, which will contain text (TEXT).

jokedate DATA NOT NULL

This fourth line defines our last column, called jokedate; this will contain a date (DATE), which cannot be left blank (NOT NULL).

) DEFAULT CHARACTER SET utf8;

The closing parenthesis ()) marks the end of the list of columns in the table.

DEFAULT CHARACTER SET utf8 tells MySQL that you will be storing UTF-8 encoded text in this table. UTF-8 is the most common encoding used for web content, so you should use it in all your database tables that you intend to use on the Web.

Finally, the semicolon tells the mysql client program that you’ve finished typing your query.

Note that we assigned a specific data type to each column we created. id will contain integers, joketext will contain text, and jokedate will contain dates. MySQL requires you to specify in advance a data type for each column. This helps to keep your data organized, and allows you to compare the values within a column in powerful ways, as we’ll see later. For a complete list of supported MySQL data types, see Appendix C, MySQL Column Types.

Now, 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 indicate where it had trouble understanding what you meant.

For such a complicated command, “Query OK” is a fairly underwhelming 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_ijdb |  
+----------------+  
| joke           |  
+----------------+  
1 row in set (0.02 sec)

This is a list of all the tables in your database (which we named ijdb above). The list contains only one table: the joke table you just created. So far, everything seems fine. Let’s take a closer look at the joke table itself using a DESCRIBE query:

mysql> DESCRIBE joke;  
+----------+---------+------+-----+---------+----------------+  
| Field    | Type    | Null | Key | Default | Extra          |  
+----------+---------+------+-----+---------+----------------+  
| id       | int(11) | NO   | PRI | NULL    | auto_increment |  
| joketext | text    | YES  |     | NULL    |                |  
| jokedate | date    | NO   |     | NULL    |                |  
+----------+---------+------+-----+---------+----------------+  
3 rows in set (0.10 sec)

As you can see, there are three columns (or fields) in this table, which appear as the three rows in this table of results. The details are a little cryptic, but if you look at them closely, you should be able to figure out what they mean. It’s nothing to be too worried about, though. You have better things to do, like adding some jokes to your table!

We need to look at just one more task before you get to that, though: deleting a table. This task is as frighteningly easy as deleting a database. In fact, the command is almost identical. Don’t run this command with your joke table, unless you actually do want to be rid of it!

mysql> DROP TABLE tableName;

Inserting Data into a Table

Your database is created and your table is built; all that’s left is to put some actual jokes into the database. The command that inserts data into a database is called, appropriately enough, INSERT. This command can take two basic forms:

mysql> INSERT INTO tableName SET  
   ->   column1Name = column1Value,  
   ->   column2Name = column2Value,  
   ->   ?  
   -> ;  
 
mysql> INSERT INTO tableName  
   ->   (column1Name, column2Name, …)  
   ->   VALUES (column1Value, column2Value, …);

So, to add a joke to our table, we can use either of these commands:

mysql> INSERT INTO joke SET  
   -> joketext = "Why did the chicken cross the road? To get to  
   ">  the other side!",  
   -> jokedate = "2009-04-01";  
 
mysql> INSERT INTO joke  
   -> (joketext, jokedate) VALUES (  
   -> "Why did the chicken cross the road? To get to the other  
   ">  side!",  
   -> "2009-04-01"  
   -> );

Note that in both forms of the INSERT command, the order in which you list the columns must match the order in which you list the values. Otherwise, the order of the columns is unimportant.

As you typed this query, you’ll have noticed that we used double quotes (") to mark where the text of the joke started and ended. A piece of text enclosed in quotes this way is called a text string, and this is how you represent most data values in SQL. You’ll notice, for instance, that the dates are typed as text strings as well, in the form "YYYY-MM-DD".

If you prefer, you can type text strings surrounded with single quotes (') instead of double quotes:

mysql> INSERT INTO joke SET
-> joketext = 'Why did the chicken cross the road? To get to
'> the other side!',
-> jokedate = '2009-04-01';

You might be wondering what happens when the text of a joke itself contains quotes. Well, if the text contains single quotes, the easiest thing to do is surround it with double quotes. Conversely, if the text contains double quotes, surround it with single quotes.

If the text you want to include in your query contains both single and double quotes, you’ll have to escape the conflicting characters within your text string. You escape a character in SQL by adding a backslash (\) immediately before it. This tells MySQL to ignore any “special meaning” this character might have. In the case of single or double quotes, it tells MySQL not to interpret the character as the end of the text string.

To make this as clear as possible, here’s an INSERT command for a joke containing both single and double quotes:

mysql> INSERT INTO joke  
   -> (joketext, jokedate) VALUES (  
   -> 'Knock-knock! Who\'s there? Boo! "Boo" who?  
   '>  Don\'t cry; it\'s only a joke!',  
   -> "2009-04-01");

As you can see, I’ve marked the start and end of the text string for the joke text using single quotes. I’ve therefore had to escape the three single quotes within the string by putting backslashes before them. MySQL sees these backslashes and knows to treat the single quotes as characters within the string, rather than end-of-string markers.

If you’re especially clever, you might now be wondering how to include actual backslashes in SQL text strings. The answer is to type a double-backslash (\\), which MySQL will see and treat as a single backslash in the string of text.

Now that you know how to add entries to a table, let’s see how we can view those entries.