Article
About the Author
Kevin Yank
Kevin began developing for the Web in 1995 and is a highly respected technical author. He wrote Build your own Database Driven Website using PHP and MySQL, a practical step-by-step guide published by SitePoint, and he's co-author of the SitePoint Tech Times, a bi-weekly newsletter for technically-minded web developers. Kev believes that any good webmaster should have seen at least one episode of MacGyver.
Build Your Own Database Driven Web Site Using PHP & MySQL, Part 2: Introducing MySQL
In Chapter 1: Installation, we installed and set up two software programs: the Apache web server with PHP, and the MySQL database server. In this chapter, we'll learn how to work with MySQL databases using Structured Query Language (SQL). If you'd rather read this tutorial offline, you can download the chapters in PDF format.
As I explained in that chapter, PHP is a server-side scripting language that lets you insert into your web pages instructions that your web server software (in most cases, Apache) 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 become interesting when a database is added to the mix. In this chapter, we’ll learn what a database is, and how to work with your own MySQL databases using Structured Query Language.
An Introduction to Databases
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 programming 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 advantages of this approach would be twofold: First, instead of having to write an HTML page for each of your jokes, you could write a single PHP script that was designed to fetch any joke from the database and display it by generating an HTML page for it on the fly. Second, adding 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 items, or things. For our joke database, we’d probably start with a table called joke that 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 joke table might have one column for the text of the jokes, and another for the dates on which the jokes were added to the database. Each joke stored in this way would then be said to be a row or entry in the table. These rows and columns form a table that looks like the figure below.
Notice that, in addition to columns for the joke text (joketext) and the date of the joke (jokedate), I’ve included a column named id. As a matter of good design, a database table should always provide a means by which we can identify each of its rows uniquely. 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 that we have an easy way to refer to them and to keep track of which joke is which. We’ll take a closer look at database design issues like this in Chapter 5, Relational Database Design.
So, to review, the table above is a three-column table with two rows, or entries. Each row in the table contains three fields, one for each column in the table: the joke’s ID, its text, and the date of the joke. With this basic terminology under your belt, you’re ready to dive into using MySQL.
Logging On to MySQL
Just as a web server is designed to respond to requests from a client (a web browser), the MySQL database server responds to requests from client programs. Later in this book, we’ll write our own MySQL client programs in the form of PHP scripts, but for now we can use some of the client programs that come included with the MySQL server.
mysqladmin is an example of a MySQL client program. If you followed the instructions in Chapter 1, Installation, after setting up a MySQL server of your own, you used the mysqladmin client program to connect to the server, establish a password for the root user, and view basic statistics about the running server.
Another client program that comes with the MySQL server is called mysql. This program provides the most basic interface for working with a MySQL server, by establishing a connection to the server and then typing commands one at a time.
The mysql program can be found in the same place as mysqladmin, so if you followed the instructions in Chapter 1: Installation to add this location to your system path, you should be able to open a Terminal window (or Command Prompt if you’re using a Windows system) and type this command to run the mysql client program:
mysql --version
If everything is set up right, this command should output a one-line description of the version of the mysql client program that you’ve installed. Here’s what this looks like on my Mac:
mysql Ver 14.14 Distrib 5.1.31, for apple-darwin9.5.0 (i386) using readline 5.1
If instead you receive an error message complaining that your computer is unable to recognize the mysql command, you should probably revisit the installation instructions provided in Chapter 1: Installation. Once you’re able to run the mysqladmin commands in that chapter, the mysql command should work too. If you’re still stuck, drop by the SitePoint Forums and ask for some help.
Assuming the mysql program is running for you, you can now use it to connect to your MySQL server. First, make sure that server is running, then type this command and hit Enter:
mysql -u root -p
The -u root and -p parameters perform the same function for this program as they did for mysqladmin in Chapter 1: Installation. -u root tells the program you wish to connect to the server using the root user account, and -p tells it you’re going to provide a password.
What you should see next is an Enter password: prompt. Enter the root password you chose for yourself in Chapter 1, and hit Enter.
If you typed everything correctly, the MySQL client program will introduce itself and dump you on the MySQL command prompt:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.31 MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
Let’s use a few simple commands to take a look around your MySQL server.
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 use by several of its subscribers, for example. So, your first step after connecting to the server should be to choose a database with which to work. First, let’s retrieve a list of databases on the current server.
Connecting to a Remote MySQL Server
The instructions in this chapter assume you’re working with a MySQL server running on your own computer. Of course, when it comes time to publish your first PHP-and-MySQL-powered web site, you will need to know how to work with the MySQL server provided by your web host, or by your company’s IT department.
Technically, the mysql program we’re using in this chapter can connect to remote MySQL servers too. You just have to add an additional parameter when running it:
mysql -h hostname -u username -p
The -h hostname parameter (where hostname is the host name of the MySQL server to which you want to connect) tells the program to connect to a remote MySQL server instead of one running on the same computer. If you do this, you’ll probably also need to specify a username other than root, since the administrator responsible for the MySQL server will probably want to keep the root password secret for security reasons.
In practice, most remote MySQL servers will block connections from client programs running on untrusted computers like yours. Disallowing this type of connection is a common security measure for MySQL servers used in production.
To work with a remote MySQL server, you might be able to connect to a trusted computer and run the mysql program from there, but a far more common approach is to use a program called phpMyAdmin to manage your remote databases. phpMyAdmin is a sophisticated PHP script that lets you work with your MySQL databases using a web-based interface in your browser. phpMyAdmin connects to the remote MySQL server in the same way as the PHP scripts we’ll be writing later in this book.
I’ll show you how to install and use phpMyAdmin in Chapter 10, MySQL Administration. For now, let’s focus on learning to work with the MySQL server you’ve installed on your computer.
Type this command (including the semicolon!) and press Enter (As in Chapter 1: Installation, the mysql> prompt should already be visible on your screen; just type the command that comes after it):
mysql> SHOW DATABASES;
MySQL will show you a list of the databases on the server. If you’re working on a brand new server, the list should look like this:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
The MySQL server uses the first database, named information_schema, to keep track of all the other databases on the server. Unless you’re doing some very advanced stuff, you’ll probably leave this database alone.
The second database, mysql, is special too. MySQL uses it to keep track of users, their passwords, and what they’re allowed to do. We’ll steer clear of this for now, though we’ll revisit it in Chapter 10, MySQL Administration, when we discuss MySQL administration.
The third database, named test, is a sample database. You can actually delete this database because I’ll show you how to create your own database in a moment.
No test on WampServer
As of this writing, WampServer’s initial MySQL database has no test database in it. No need to be alarmed though; the developers of WampServer just thought it was as useless as I do, I guess!
Deleting stuff 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, displaying “Query OK” in confirmation. Notice that there’s no confirmation prompt like “Are you sure?”. You have to be very careful to type your commands correctly in the mysql client program because, as this example shows, you can obliterate your entire database—along with all the information it contains—with a single command!
Before we go any further, let’s learn a couple of fundamentals about the MySQL command prompt. As you may have noticed, all commands in MySQL are terminated by a semicolon (;). If you forget the semicolon, MySQL will think you’re still typing your command, and will let you continue on another line:
mysql> SHOW
-> DATABASES;
MySQL shows that it’s waiting for you to type more of your command by changing the prompt from mysql> to ->. This handy feature allows you to spread long commands over several lines.
Case Sensitivity in SQL Queries
Most MySQL commands are not case-sensitive, which means you can type SHOW DATABASES, show databases, or ShOw DaTaBaSeS, and it will know what you mean. Database names and table names, however, are case-sensitive when the MySQL server is running on an operating system with a case-sensitive file system (like Linux or Mac OS X, depending on your system configuration).
Also, table, column, and other names must be spelled exactly the same when they’re used more than once in the same command.
For consistency, this book will respect the accepted convention of typing database commands in all capitals, and database entities (databases, tables, columns, and so on) in all lowercase.
If you’re halfway through a command and realize that 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 ignore the command you had begun to type and will return to the mysql> prompt to await another command.
Finally, if at any time you want to exit the MySQL client program, just type quit or exit (either will work). This is the only command where the semicolon is unnecessary, but you can use one if you want to.
mysql> quit
Bye
Now that we've successfully connected to our MySQL server it's time to create our data.