Build Your Own Database Driven Website Using PHP & MySQL: Pt. 2

Thursday Jan 10th 2002 by Kevin Yank
Share:

This second installment covers an introduction to databases, logging on to MySQL, defining SQL, inserting data in a table, and modifying stored data.

As I've already explained, PHP is a server-side scripting language that lets you insert into your Web pages instructions that your Web server software (be it Apache, IIS, or whatever) will execute before it sends those pages to browsers that request them. In a brief example, I showed how it was possible to insert the current date into a Web page every time it was requested.

Now that's all well and good, but things really get interesting when a database is added to the mix. A database server (in our case, MySQL) is a program that can store large amounts of information in an organized format that's easily accessible through scripting languages like PHP. For example, you could tell PHP to look in the database for a list of jokes that you'd like to appear on your Web site.

In this example, the jokes would be stored entirely in the database. The advantage of this would be twofold. First, instead of having to write an HTML file for each of your jokes, you could write a single PHP file that was designed to fetch any joke out of the database and display it. Second, to add a joke to your Web site would be a simple matter of inserting the joke into the database. The PHP code would take care of the rest, automatically displaying the new joke along with the others when it fetched the list from the database.

Let's run with this example as we look at how data is stored in a database. A database is composed of one or more tables, each of which contains a list of things. For our joke database, we'd probably start with a table called "jokes" which would contain a list of jokes. Each table in a database has one or more columns, or fields. Each column holds a certain piece of information about each item in the table. In our example, our "jokes" table might have columns for the text of the jokes, and the dates on which the jokes were added to the database. Each joke that we stored in this table would then be said to be a row in the table. These rows and columns form a table that looks like this:

A database table

Notice that, in addition to columns for the joke text ("JokeText") and the date of the joke ("JokeDate"), I included a column named "ID". As a matter of good design, a database table should always provide a way to uniquely identify each of its rows. Since it's possible that a single joke could be entered more than once on the same date, the JokeText and JokeDate columns can't be relied upon to tell all the jokes apart. The function of the ID column, therefore, is to assign a unique number to each joke, so we have an easy way to refer to them, and to keep track of which joke is which. Such database design issues will be covered with greater depth in Chapter 5.

So, to review, the above is a three-column table with two rows (or entries). Each row in the table contains a joke's ID, its text, and the date of the joke. With this basic terminology under our belts, we're ready to get started with MySQL.

The standard interface for working with MySQL databases is to connect to the MySQL server software (which you set up in Chapter 1) and type commands one at a time. To make this connection to the server, you'll need the MySQL client program. If you installed the MySQL server software yourself either under Windows or under some brand of UNIX, you already have this program installed in the same location as the server program. Under Linux, for example, the program is called mysql and is located by default in the /usr/local/mysql/bin directory. Under Windows, the program is called mysql.exe and is located by default in the C:\mysql\bin directory.

If you didn't set up the MySQL server yourself (if, for example, you'll be working on your Web host's MySQL server), there are two ways to connect to the MySQL server. The first is to use Telnet or a Secure Shell (SSH) connection to log into your Web host's server, and then run mysql from there. The second is to download and install the MySQL software from http://www.mysql.com/ (available free for Windows and Linux) on your own computer, and use it to connect to the MySQL server over the Internet. Both ways work fine, and your Web host may support one, the other, or both (you'll need to ask).

Whichever method and operating system you use, you'll end up at a command line, ready to run the MySQL client program and connect to your MySQL server. Here's what you should type:

mysql -h hostname -u username -p

You need to replace hostname with the host name or IP address of the computer on which the MySQL server is running. If the client program is run on the same computer as the server, you can actually leave off the -h hostname part of the command instead of typing -h localhost or –h 127.0.0.1. username should be your MySQL user name. If you installed the MySQL server yourself, this will just be root. If you're using your Web host's MySQL server, this should be the MySQL user name they assigned you.

The -p argument tells the program to prompt you for your password, which it should do as soon as you enter the command above. If you set up the MySQL server yourself, this password is the root password you chose in Chapter 1. If you're using your Web host's MySQL server, this should be the MySQL password they gave you.

If you typed everything properly, the MySQL client program will introduce itself and then dump you on the MySQL command line:

mysql>

Now, the MySQL server can actually keep track of more than one database (this allows a Web host to set up a single MySQL server for several of its subscribers to use, for example), so your next step should be to pick a database to work with. First, let's retrieve a list of databases on the current server. Type this command (don't forget the semicolon!), and hit ENTER.

mysql> SHOW DATABASES;

MySQL will show you a list of the databases on the server. If this is a brand new server (i.e. if you installed this server yourself in Chapter 1), the list should look like this:

+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.11 sec)

The MySQL server uses the first database, called mysql, to keep track of users, their passwords, and what they're allowed to do. We'll steer clear of this database for the time being, and come back to it in Chapter 8 when we discuss MySQL Administration. The second database, called test, is a sample database. You can actually get rid of this database. I won't be referring to it in this series of articles (and we'll create our own example database momentarily). Deleting something in MySQL is called "dropping" it, and the command for doing so is appropriately named:

mysql> DROP DATABASE test;

If you type this command and press Enter, MySQL will obediently delete the database, saying "Query OK" in confirmation. Notice that you're not prompted with any kind of "are you sure?" message. You have to be very careful to type your commands correctly in MySQL because, as this example shows, you can obliterate your entire database—along with all the information it contains—with one single command!

Before we go any further, let's learn a couple of things about the MySQL command line. As you may have noticed, all commands in MySQL are terminated by a semicolon (;). If you forget the semicolon, MySQL will think you haven't finished typing your command, and will let you continue to type on another line:

mysql> SHOW
    -> DATABASES;

MySQL shows you that it's waiting for you to type more of your command by changing the prompt from mysql> to ->. For long commands, this can be handy, as it allows you to spread your commands out over several lines.

If you get halfway through a command and realize you made a mistake early on, you may want to cancel the current command entirely and start over from scratch. To do this, type "\c" and press ENTER:

mysql> DROP DATABASE\c
mysql>

MySQL will completely ignore the command you had begun to type, and will go back to the prompt to wait for another command.

Finally, if at any time you want to exit the MySQL client program, just type "quit" or "exit" (either one will work). This is the only command that doesn't need a semicolon, but you can use one if you want to.

mysql> quit
Bye

The set of commands we'll use to tell MySQL what to do for the rest of this article 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 use 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 use to interact with that 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 your own database. It's just as easy to create a database as it is to delete 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 give the database any name you like, though. Those of you working on your Web host's MySQL server will probably have no choice in what to name your database, since it will usually already have been created for you.

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 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 our jokes.

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 column_1_type column_1_details,
    ->  column_2_name column_2_type column_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
    -> );

It looks pretty scary, 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), that is, a whole number. 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 we add a new entry to the table, we want MySQL to 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 the 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, which will contain text (TEXT).
  • The fourth line defines our last column, called JokeDate, which 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, as 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 they're 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 within a column in powerful ways (as we'll see later). For a complete list of supported MySQL data types, see the MySQL Reference Manual, or refer to Appendix C of the book version of this series.

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 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    | Extra          |
+----------+---------+------+-----+------------+----------------+
| ID       | int(11) |      | PRI | 0          | auto_increment |
| JokeText | text    | YES  |     | NULL       |                |
| JokeDate | date    |      |     | 0000-00-00 |                |
+----------+---------+------+-----+------------+----------------+
3 rows in set

This provides a list of the columns (or 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 a while 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 it is to delete a database. In fact, the command is almost identical:

mysql> DROP TABLE tableName;

Our database is created and our table is built; all that's left is to put some actual jokes into our database. The command for inserting data into our database is called (appropriately enough) INSERT. There are two basic forms of this command:

mysql> INSERT INTO table_name SET
    ->  columnName1 = value1,
    ->  columnName2 = value2,
    ->  ...
    -> ;

mysql> INSERT INTO table_name
    -> (columnName1, columnName2, ...)
    -> VALUES (value1, value2, ...);

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

mysql> INSERT INTO Jokes SET
    -> JokeText = "Why did the chicken cross the road? To get to
the other side!",
    -> JokeDate = "2000-04-01";

mysql> INSERT INTO Jokes
    -> (JokeText, JokeDate) VALUES (
    -> "Why did the chicken cross the road? To get to the other
side!",
    -> "2000-04-01"
    -> );

Note that in the second form 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 doesn't matter, as long as you give values for all required fields.

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

Viewing Stored Data

The command we use to view data stored in your database tables, SELECT, is the most complicated command in the SQL language. The reason for this complexity is that the chief strength of a database is its flexibility in data retrieval and presentation. As, at this point in our experience with databases, we only need fairly simple lists of results, we'll consider only the simpler forms of the SELECT command.

This command will list everything stored in the "Jokes" table:

mysql> SELECT * FROM Jokes;

Read aloud, this command says "select everything from Jokes". If you try this command, your results will resemble this:

+----+-------------------------------------------------
--------------+------------+
| ID | JokeText
              | JokeDate   |
+----+-------------------------------------------------
--------------+------------+
|  1 | Why did the chicken cross the road? To get to th
e other side! | 2000-04-01 |
+----+-------------------------------------------------
--------------+------------+
1 row in set (0.05 sec)

It looks a little messed up, because the text in the JokeText column is too long for the table to fit properly on the screen. For this reason, you might want to tell MySQL to leave out the JokeText column. The command for doing this is as follows:

mysql> SELECT ID, JokeDate FROM Jokes;

This time instead of telling it to "select everything", we told it precisely which columns we wanted to see. The results look like this:

+----+------------+
| ID | JokeDate   |
+----+------------+
|  1 | 2000-04-01 |
+----+------------+
1 row in set (0.00 sec)

Not bad, but we'd like to see at least some of the joke text, wouldn't we? In addition to listing the columns that we want the SELECT command to show us, we can modify those columns with functions. One function, called LEFT, lets us tell MySQL to display up to a specified maximum number of characters when it displays a column. For example, let's say we wanted to see only the first 20 characters of the JokeText column:

mysql> SELECT ID, LEFT(JokeText,20), JokeDate FROM Jokes;
+----+----------------------+------------+
| ID | LEFT(JokeText,20)    | JokeDate   |
+----+----------------------+------------+
|  1 | Why did the chicken  | 2000-04-01 |
+----+----------------------+------------+
1 row in set (0.05 sec)

See how that worked? Another useful function is COUNT, which simply lets us count the number of results returned. So, for example, if we wanted to find out how many jokes were stored in our table, we could use the following command:

mysql> SELECT COUNT(*) FROM Jokes;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.06 sec)

As we can see, we only have one joke in our table.

So far, all our examples have fetched all the entries in the table. But if we add what's called a WHERE clause (for reasons that will become obvious in a moment) to a SELECT command, we can limit which entries are returned as results. Consider this example:

mysql> SELECT COUNT(*) FROM Jokes WHERE JokeDate >= "2000-01-01";

This query will count the number of jokes that have dates "greater than or equal to" January 1st, 2000. "Greater than or equal to", when dealing with dates, means "on or after".

Another variation on this theme lets you search for entries that contain a certain piece of text. Check out this query:

mysql> SELECT JokeText FROM Jokes WHERE JokeText LIKE "%chicken%";

This query displays the text of all jokes that contain the word "chicken" in their JokeText column. The LIKE keyword tells MySQL that the named column must match the given pattern. In this case, the pattern we've used is "%chicken%". The % signs here indicate that the word "chicken" may be preceded and/or followed by any string of text.

Additional conditions may also be combined in the WHERE clause to further restrict results. For example, to display knock-knock jokes from April 2000 only, we could use the following query:

mysql> SELECT JokeText FROM Jokes WHERE
    -> JokeText LIKE "%knock%" AND
    -> JokeDate >= "2000-04-01" AND
    -> JokeDate < "2000-05-01";

Enter a few more jokes into the table and experiment with SELECT statements a little. A good familiarity with the SELECT statement will come in handy later in this series.

There's a lot more you can do with the SELECT statement, but we'll save looking at some of its more advanced features for later, when we need them.

Once you've entered your data into a database table, you might like to change it at some point. Whether you want to correct a spelling mistake, or change the date attached to a joke, such alterations are made using the UPDATE command. This command contains elements of the INSERT command (that set column values) and of the SELECT command (that pick out entries to modify). The general form of the UPDATE command is as follows:

mysql> UPDATE table_name SET
    -> col_name = new_value, ...
    -> WHERE conditions;

So, for example, if we wanted to change the date on the joke we entered above, we'd use the following command:

mysql> UPDATE Jokes SET JokeDate="1990-04-01" WHERE ID=1;

Here's where that ID column comes in handy. It allows us to easily single out a joke for changes. The WHERE clause here works just like it does in the SELECT command. This next command, for example, changes the date of all entries that contain the word "chicken":

mysql> UPDATE Jokes SET JokeDate="1990-04-01"
    -> WHERE JokeText LIKE "%chicken%";

Deleting Stored Data

The deletion of entries in SQL is dangerously easy (if you can't tell by now, this is a recurring theme). Here's the command syntax:

mysql> DELETE FROM table_name WHERE conditons;

So to delete all chicken jokes from your table, you'd use the following query:

mysql> DELETE FROM Jokes WHERE JokeText LIKE "%chicken%";

One thing to note is that the WHERE clause is actually optional. However, you should be very careful if you leave it off, as the DELETE command will then apply to all entries in the table. This command will empty the Jokes table in one fell swoop:

mysql> DELETE FROM Jokes;

Scary, huh?

Summary

There's a lot more to the MySQL database system and the SQL language than the few basic commands we've looked at here, but these commands are by far the most commonly used. So far we've only worked with a single table. To realize the true power of a relational database, we'll also need to learn how to use multiple tables together to represent potentially complex relationships between database entities.

All this and more will be covered in Chapter 5, where we'll discuss database design principles, and look at some more advanced examples. For now, though, we've hopefully accomplished our objective, and you can comfortably interact with MySQL using the command line interface.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved