On the Web today, content is king. After you've mastered HTML and learned a few neat tricks in JavaScript and Dynamic HTML, you can probably build a pretty impressive-looking Web site design. But then comes the time to fill that fancy page layout with some real information. Any site that successfully attracts repeat visitors has to have fresh and constantly updated content. In the world of traditional site building, that means HTML files--and lots of 'em.

The problem is that, more often than not, the people providing the content for a site are not the same people handling its design. Oftentimes, the content provider doesn't even know HTML. How, then, is the content to get from the provider onto the Web site? Not every company can afford to staff a full-time Webmaster, and most Webmasters have better things to do than copying Word files into HTML templates anyway.

Maintenance of a content-driven site can be a real pain, too. Many sites (perhaps yours?) feel locked into a dry, outdated design because rewriting those hundreds of HTML files to reflect a new design would take forever. Server-side includes (SSI's) can help alleviate the burden a little, but you still end up with hundreds of files that need to be maintained should you wish to make a fundamental change to your site.

The solution to these headaches is database-driven site design. By achieving complete separation between your site's design and the content you are looking to present, you can work with each without disturbing the other. Instead of writing an HTML file for every page of your site, you only need to write a page for each kind of information you want to be able to present. Instead of endlessly pasting new content into your tired page layouts, create a simple content management system that allows the writers to post new content themselves without a lick of HTML!

In this 10-part weekly series of articles, I'll provide a hands-on look at what's involved in building a database-driven Web site. We'll be using two new tools for this: the PHP scripting language and the MySQL relational database. If your Web host provides PHP/MySQL support, you're in great shape. If not, we'll be looking at the set-up procedures under Unix and Windows, so don't sweat it.

These articles are aimed at intermediate or advanced Web designers looking to make the leap into server-side programming. You'll be expected to be comfortable with HTML, as I'll be making use of it without explanation. A teensy bit of JavaScript may serve us well at some point, but I'll be sure to keep it simple for the uninitiated.

By the end of this series, you can expect to have a grasp of what's involved in setting up and building a database-driven Web site. If you follow along with the examples, you'll also learn the basics of PHP (a server-side scripting language that allows you to do a lot more than access a database easily) and Structured Query Language (SQL -- the standard language for interacting with relational databases). Most importantly, you'll come away with everything you need to get started on your very own database-driven site in no time!

Topics we will cover
Part 1: Installation
Part 2: Getting Started with MySQL
Part 3: Getting Started with PHP
Part 4: Using PHP to access a MySQL database
Challenge Solution
Part 5: Relational Database Design
Part 6: A Content Management System
Part 7: Content Formatting and Submission
Part 8: MySQL Administration
Part 9: Advanced SQL
Part 10: Advanced PHP

Part 1: Installation

Welcome to the Show

Hi there, and welcome to the first in ten-part series on building a database-driven Web site! For the next few months, it will be my job to guide you as you take your first steps beyond the HTML-and-JavaScript world of client-side site design. Together we'll learn everything that's needed to build the kind of large, content-driven sites that are so successful today, but which can be a real headache to maintain if they aren't done right.

Before we get started, we need to gather together the tools we'll need for the job. In this first article, we'll download and set up the two software packages we'll be using: PHP and MySQL.

PHP is a server-side scripting language. You can think of it as a "plug-in" for your Web server that will allow it to do more than just send plain Web pages when browsers request them. With PHP installed, your Web server will be able to read a new kind of file (called a "PHP script" that can do things like retrieve up-to-the-minute information from a database and insert it into a Web page before sending it to the browser that requested it. PHP is completely free to download and use.

To retrieve information from a database, you first need to have a database. That's where MySQL comes in. MySQL is a relational database management system, or RDBMS. Exactly what role it plays and how it works we'll get into later, but basically it's a software package that is very good at organizing and managing large amounts of information. MySQL also makes that information really easy to get at using server-side scripting languages like PHP. MySQL is free for non-commercial use on most Unix-based platforms, like Linux. MySQL for Windows 9x/NT/2000 costs about US$200 to buy, but you can download an older version for free if you just want to try it out. For our purposes, the older version will serve just fine, but if you find MySQL for Windows useful and you decide to use it on one of your own sites, you should pay for it.

If you're lucky, your current Web host may already have installed MySQL and PHP on your Web server for you. If that's the case, much of this article will not apply to you, and you can skip straight to If Your Web Host Provides PHP and MySQL to make sure everything is ship shape.

Everything we'll discuss in this article series may be done on a Windows- or Unix-based server. Depending on which type of server you'll be using, the installation procedure will be different. The next section deals with installation on a Windows-based Web server. The section after that deals with installation under Linux (and other Unix-based platforms). Unless you're especially curious, you should only need to read the section that applies to you.

PART 1 CONTINUE-Installation under Windows

As I mentioned above, MySQL for Windows costs about US$200 to buy. For those of us who just want to try it out and see what it can do, T.c.X. (the company that develops MySQL) provides an older version that can be downloaded for free. It can be found by going to http://www.mysql.com/ (or one of its mirrors listed at http://www.mysql.com/mirrors.html) and selecting "Register and download shareware version of MySQL-Win32" in the "Downloads" section under "Downloads for Windows MySQL related software". After downloading the file, unzip it and run the setup.exe program contained therein.

Once installed, MySQL is ready to roll (barring a couple of configuration tasks that we'll look at shortly). Just like your Web server, MySQL is a server that should be run in the background so that it may respond to requests for information at any time. The server program may be found in the "bin" subfolder of the folder where you installed MySQL. If you are using the shareware version of MySQL, the server is called mysqld-shareware.exe. Before proceeding, rename this file to mysqld.exe. From the MS-DOS Prompt, start the server:


C:\mysql\bin> mysqld
To ensure that the server is started whenever Windows starts, you might want to create a shortcut to the program and put it in your Startup folder. If you decide to buy MySQL, it will come with a version that can be installed as a Windows NT/2000 service with the following command:


C:\mysql\bin> mysqld-nt --install
If you have trouble running the shareware version under Windows NT/2000, you can try running the server as a standalone program:


C:\mysql\bin> mysqld --standalone
The next step is to install PHP. At the time of this writing, PHP 4.0 was available as "Release Candidate 2"--or "almost ready but not quite". Personally I use PHP 4.0-RC2 and don't have any trouble with it. Since the final version is slated for release "real soon now" (likely before this series of articles is even finished), I'd recommend you install the latest version of 4.0 so you don't have to change anything when the final version is released.

PHP may be downloaded for free from http://www.php.net/ (or one of its mirrors listed at http://www.php.net/mirrors.php). You want the "binaries for Win32" package. Don't worry about grabbing any of the add-ons; we don't need them. A good installation guide for PHP 3.0 for Windows is available at the following URL: http://www.umesd.k12.or.us/php/win32install.html. It'll probably be updated with instructions for PHP 4.0 when it is finally released, but since installation of 4.0 is pretty much identical to installation of 3.0, you shouldn't have any trouble following the instructions with either version.

Don't worry about any of the optional steps (like choosing extension modules)-we'll work through those things together in a little bit. If you have any trouble following the instructions, feel free to post your question to the SitePoint.com Forums. I will be glad to help if the other helpful people there don't beat me to it!

With MySQL and PHP installed, you're ready to proceed to Post-Installation Setup Tasks.

PART 1 CONTINUE-Installing under Linux

This section covers the exact procedure for installing PHP and MySQL under RedHat Linux 5 or later. If you're using a different flavor of Linux, or another Unix-based operating system, the steps involved will be very similar, if not identical.

As a user of RedHat Linux, you may be tempted to download and install the RPM distributions of PHP and MySQL. RPM's are nice, pre-packaged versions of software that are really easy to install. Unfortunately, they also limit the options you have in choosing how the software is configured. For this reason, I consider the RPM versions of PHP and MySQL to be more trouble than they are worth.

Since a few of the default RedHat Linux install configurations will automatically install PHP for you, your first step should be to remove any old versions of PHP and MySQL from your system. You'll need to be logged in as the root user to issue the commands to do this. Note that in the following commands, "%" represents the shell prompt, and is not something that needs to be typed.


% rpm -e mysql% rpm -e php
If either or both of these commands tell you that the program in question is not installed, don't worry about it. If the second command runs successfully (i.e. no message is displayed), then you did indeed have an older version of PHP installed, and you'll need to do one more thing to get rid of it entirely. Open your Apache configuration file (usually /etc/httpd/conf/httpd.conf) in your favorite text editor and look for the two lines shown here. They usually appear in separate sections of the file, so don't worry if they're not together.


LoadModule php3_module modules/libphp3.soAddModule mod_php3.c
These lines are responsible for telling Apache to load PHP as a plug-in module. Since you just uninstalled that module, you'll need to get rid of these lines to make sure Apache keeps working properly. You can comment out these lines by adding a hash (#) at the beginning of both lines.

To make sure Apache is still in working order, you should now restart it without the PHP plug-in:


% /etc/rc.d/init.d/httpd stop% /etc/rc.d/init.d/httpd start
With everything neat and tidy, you're ready to download and install MySQL and PHP.

PART 1 CONTINUE-Installing MySQL under Linux

MySQL is freely available for Linux from http://www.mysql.com/ (or one of its mirrors listed at http://www.mysql.com/downloads/mirrors.html). Download the latest stable release (listed as "recommended" on the download page). You should grab the "tarball source download" version, with filename mysql-version.tar.gz.

With the program downloaded, you should make sure you're logged in as root before proceeding with the installation, unless you only want to install MySQL in your own home directory. Begin by unpacking the downloaded file and moving into the directory that is created:


% tar xfz mysql-version.tar.gz% cd mysql-version
Next you need to configure the MySQL install. Unless you really know what you're doing, all you should have to do is tell it where to install. I recommend /usr/local/mysql:


% ./configure --prefix=/usr/local/mysql
After sitting through the screens and screens of configuration tests, you'll eventually get back to a command prompt. You're ready to compile MySQL:


% make
After even more screens of compilation, you'll again be returned to the command prompt. You're now ready to install your newly compiled program:


% make install
MySQL is now installed, but before it can do anything useful its database files need to be installed too. Still in the directory you installed from, type the following command:


% scripts/mysql_install_db
With that done, you can delete the directory you've been working in, which just contains all the source files and temporary installation files. If you ever need to reinstall, you can just re-extract the mysql-version.tar.gz file.

With MySQL installed and ready to store information, all that's left is to get the server running on your computer. While you can run the server as the root user, or even as yourself (if, for example, you installed the server in your own home directory), the best idea is to set up a special user on the system that can do nothing but run the MySQL server. This will remove any possibility of someone using the MySQL server as a way to break into the rest of your system. To create a special MySQL user, you'll need to log in as root and type the following commands:


% /usr/sbin/groupadd mysqlgrp% /usr/sbin/useradd -g mysqlgrp mysqlusr
By default, MySQL stores all database information in the var subdirectory of the directory to which it was installed. We want to make it so that nobody can access that directory except our new MySQL user. The following commands will do this (I'm assuming you installed MySQL to the /usr/local/mysql directory):


% cd /usr/local/mysql% chown -R mysqlusr.mysqlgrp var% chmod -R go-rwx var
Everything's set for you to try launching the MySQL server for the first time. From the MySQL directory, type the following command:


% bin/safe_mysqld --user=mysqlusr &
The MySQL server has now been launched by the MySQL user and will stay running (just like your Web or FTP server) until your computer is shut down. To test that the server is running properly, type the following command:


% bin/mysqladmin -u root status
A little blurb with some statistics about the MySQL server should be displayed. If you get an error message, something has gone wrong. If retracing your steps to make sure you did everything described above doesn't solve the problem, a post to the SitePoint.com Forums will probably help you pin it down in no time.

If you want to set up your MySQL server to run automatically whenever the system is running (just like your Web server probably does), you'll have to set it up to do so. In the share/mysql subdirectory of the MySQL directory, you'll find a script called mysql.server that can be added to your system startup routines to do this.

Assuming you've set up a special MySQL user to run the MySQL server, you'll need to edit the mysql.server script before you use it. Open it in your favorite text editor and change the mysql_daemon_user setting to refer to the user you created above:


mysql_daemon_user=mysqlusr
Setting up the script to be run by your system at startup is a highly operating system-dependant task. If you're not using RedHat Linux and you're not sure of how to do this, you'd be best to ask someone who knows. In RedHat Linux, the following commands (starting in the MySQL directory) will do the trick:


% cp share/mysql/mysql.server /etc/rc.d/init.d/% cd /etc/rc.d/init.d% chmod 500 mysql.server% cd /etc/rc.d/rc3.d% ln -s , /init.d/mysql.server S99mysql% cd /etc/rc.d/rc5.d% ln -s , /init.d/mysql.server S99mysql
That's it! To test that this works, you can reboot your system and request the status of the server as before to make sure it runs properly at startup.

Installing PHP under Linux

As mentioned above, PHP is not really a program in and of itself. Rather, it is a plug-in module for your Web server (probably Apache). There are actually three ways you can install the PHP plug-in for Apache:




As a CGI program that Apache runs every time it needs to process a PHP-enhanced Web page.

As an Apache module compiled right into the Apache program.

As an Apache module loaded by Apache each time it starts up.


The first option is the easiest to install and set up, but requires Apache to launch PHP as a program on your computer every time a PHP page is requested. This can really slow down the response time of your Web server, especially if more than one request needs to be processed at a time.

The second and third options are pretty much identical in terms of performance, but since you likely already have Apache installed, you'd probably prefer to avoid downloading, recompiling, and reinstalling it from scratch. For this reason, we'll be using the third option.

Start by downloading the PHP Source package from http://www.php.net/ (or one of its mirrors listed at http://www.php.net/mirrors.php). At the time of this writing, PHP 4.0 was available as "Release Candidate 2"-or "almost ready but not quite". Personally I use PHP 4.0-RC2 and don't have any trouble with it. Since the final version will be out "real soon now" (likely before this series of articles is even finished), I'd recommend you install the latest version of 4.0 so you don't have to change anything when the final version is released. In case you do decide to stick with 3.0, however, I'll be sure to point out any spots in the installation procedure that would differ between the two.

The file you downloaded should be called php-version.tar.gz. We'll start by extracting the files it contains:

% tar xfz php-version.tar.gz
% cd php-version

To install PHP as a loadable Apache module, you'll need the Apache apxs program. This comes with most versions of Apache, but if you're using the copy that was installed by RedHat Linux, you'll need to install the Apache development RPM package to get it. You'll find this package on your RedHat CD or you can download it from http://www.redhat.com/. By default, RedHat will install the program as /usr/sbin/apxs. If you see that file, you know it's installed.

For the rest of this install procedure, you'll need to be logged in as the root user, because it involves making changes to the Apache configuration files.

The next step is to configure the PHP installation program by letting it know what options you want to have enabled and where it should find the programs it needs to know about (like Apache and MySQL). Unless you know what you're doing, you should just type the command like this (all on one line):


% ./configure --prefix=/usr/local/php --with-config-file-path=/usr/local/php --with-apxs=/usr/sbin/apxs --enable-track-vars --enable-magic-quotes --enable-debugger
If you are installing PHP 3.0 (and not 4.0 or later), you'll also need to tell it where to find MySQL on your system with the following additional parameter:


--with-mysql=/usr/local/mysql/
After watching several screens of tests scroll by, you'll be returned to the command prompt. The following two commands will compile and then install PHP:


% make% make install
PHP is now installed in /usr/local/php (unless you specified a different directory with the --prefix option of ./configure above), and expects to find its configuration file, named php.ini, in the same directory (unless you specified a different directory with the --with-config-file-path option of ./configure above). PHP comes with a sample php.ini file called php.ini-optimized (php.ini-dist for PHP 3.0). Copy this file from your installation work directory to where it belongs:


% cp php.ini-optimized /usr/local/php/php.ini
Or for PHP 3.0:


% cp php.ini-dist /usr/local/php/php.ini
We'll worry about fine-tuning php.ini shortly. For now, we need to make sure Apache knows where to find PHP so that it can load it when starting up. Open your Apache httpd.conf configuration file (/etc/httpd/conf/httpd.conf on RedHat Linux) in your favorite text editor. Look for a line like the following:


LoadModule php4_module lib/apache/libphp4.so
If you installed PHP 3.0, the line will read php3 instead of php4. You're looking for a new, uncommented line (no # at the start of the line), not the old line that we commented out earlier. Chances are it will not appear along with the other LoadModule lines in the file. Once you find it, you need to change the path so that it matches all the other LoadModule lines in the file. Under RedHat Linux, this means changing the line so that it looks like this:


LoadModule php4_module modules/libphp4.so
Next, look for the line starting with DirectoryIndex. This line tells Apache what filenames to use when looking for the default page for a given directory. You'll see the usual index.html and so forth, but you need to add index.php and index.php3 to that list:


DirectoryIndex index.html index.cgi , index.php index.php3
Finally, go right to the bottom of the file and add the following line to tell Apache what file extensions should be seen as PHP files:


AddType application/x-httpd-php .phtml .php .php3
That should do it! Save your changes and restart your Apache server. All things going to plan, Apache should start up without any error messages. If you run into any trouble, the helpful folks in the SitePoint.com Forums (myself included) will be happy to help.

If Your Web Host Provides PHP and MySQL

If the host providing you with Web space has already installed and set up MySQL and PHP for you and you're just hoping to learn how to use them, there really isn't a lot you need to do. Now would be a good time to get in touch with your host and request any information you may need to access these services.

Specifically, you'll need a username and password to access the MySQL server they have set up for you. They'll probably have set up an empty database for you to use as well (this prevents you from messing with the databases of other users that share the same MySQL server), and you'll want to know its name.

There are two ways you can access the MySQL server. The first is to use telnet to log into the host and use the MySQL client programs (mysql, mysqladmin, mysqldump, etc.) installed there to interact with the MySQL server directly. The second is to install those client programs on your own computer and have them connect to the MySQL server. Your Web host may support one or both of these methods, so you'll need to ask which.

If they support logging in by telnet to do your work, you'll need a username and password for the telnet login in addition to those you'll use to access the MySQL server (they can be different). Be sure to ask for both sets of information.

If they support remote access to the MySQL server, you'll want to download a program for connecting to and interacting with the server. This article series will assume you've downloaded the set of MySQL client programs from http://www.mysql.com/. Packages are available for Windows or Unix, and are free. Install instructions are fairly simple and are included with the packages. If you prefer something more graphical, you can download something like MySQLWinAdmin for Windows (also available from http://www.mysql.com/). I'd really recommend getting comfortable with the basic client programs first, though, since the commands you use with them will be similar to those you include in your PHP scripts to access MySQL databases.

Your First PHP Script

It would be unfair of me to help you get everything installed and not even give you a taste of what a PHP-driven Web page looks like until next week, so here's a little something to whet your appetite.

Open up your favorite text or HTML editor and create a new file called today.php. Type the following into the file:


<HTML><HEAD><TITLE>Today's Date</TITLE></HEAD><BODY><P>Today's Date (according to this Web server) is<?php
echo( date("l, F dS Y." );
?></BODY></HTML>
Save it and place it on your Web site as you would any regular HTML file, then see what it looks like when you view it in your browser. If you haven't yet had time to set up PHP on your Web server, click here to see the results on our server.

Pretty neat, huh? If you use the view source feature in your browser, all you'll see is a regular HTML file with the date in it. The PHP code (everything between <?php and ?> in the code above) has been interpreted by the Web server and converted to normal text before sending it to your browser. The beauty of PHP (and other server-side scripting languages) is that the Web browser doesn't have to know anything about it!

Don't worry too much about the exact code I used in this example. Before too long you'll know it like the back of your hand.

Wrap-up

All things going to plan, you should now have everything you need to get MySQL and PHP installed on your Web Server. If the little example above didn't work right (for example, if the raw PHP code appeared instead of the date), then something went wrong with the setup. Drop by the SitePoint.com Forums and we'll be glad to help you figure out the problem!

In the next section, we'll learn the basics of relational databases and get started working with MySQL. If you've never even touched a database before, I promise you it'll be a real eye opener!

Great job - keep it up - but dont u think u should slow down to get some responses? maybe u are goin to fast or too slow, but i can still manage to follow - only thing i will add is, try to wrap your codes in the <code> tag so it will be easy for those of us that want to read/debug/criticize your codes - and also for the learners to differentiate the codes from the explanation

Hi, I wanna say you do have a great work set up here. I believe there lies a lot where that came from. Keep 'em coming.

Great work.

Yo - keep up the good job - u are gettin close.

Any questions from viewers before we proceed to the next chapter?

That is some improvement, viewers questions!!! or do u ya all mean that u hav understood everything Me i dont o - problem is - i am not sure where to start asking questions from - just so i dont look foooolish!

Part 2: Getting Started with MySQL

An Introduction to Databases

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 Website.

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 file for each of your jokes, you could write a single PHP file that was designed to fetch any joke from the database and display it. Second, adding a joke to your Website 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 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 in the 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 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. Such database design issues will be covered in greater depth in Chapter 5, Relational Database Design.

So, to review, the 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 our belts, we're ready to get started with MySQL.

Logging On to MySQL
The standard interface for working with MySQL databases is to connect to the MySQL server software (which you set up in Chapter 1, Installation) 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 some brand of UNIX, this program will have been 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're 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, then run mysql from there. The second is to download the MySQL client software from http://www.mysql.com/ (available free for Windows and Linux), install it on your own computer, and use it to connect to the MySQL server over the Internet. Both methods work well, and your Web host may support one, the other, or both—you'll need to ask.

No shell? No direct connection? No problem!

Many Web hosts do not allow direct access to their MySQL servers over the Internet for security reasons. If your host has adopted this policy (you'll have to ask them if you're not sure), installing the MySQL client software on your own computer won't do you any good. Instead, you'll need to install a Web-based MySQL administration script onto your site. phpMyAdmin is the most popular script available; indeed, many Web hosts will configure your account with a copy of phpMyAdmin.

While Web-based MySQL administration systems provide a convenient, graphical interface for working with your MySQL databases, it is still important to learn the basics of MySQL's command-line interface. The commands you use in this interface are the very same commands you'll have to include in your PHP code later in this book. I therefore recommend going back to Chapter 1, Installation and installing MySQL on your own computer so you can complete the exercises in this chapter before you get comfortable with your Web-based administration interface.

Whichever method and operating system you use, you'll end up at a command prompt, 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 would use -h localhost or –h 127.0.0.1, but in this special case you can actually leave off this part of the command entirely. 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 the host 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, Installation. If you're using your Web host's MySQL server, this should be the MySQL password the host gave you.

If you typed everything correctly, the MySQL client program will introduce itself and dump you on the MySQL command prompt:

mysql>

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 next step should be to choose a database with which to work. First, let's retrieve a list of databases on the current server. Type this command (don't forget the semicolon!) and press Enter.

mysql>SHOW DATABASES;

MySQL will show you a list of the databases on the server. If you're working on a brand new server (i.e. if you installed the 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, named mysql, to keep track of users, their passwords, and what they're allowed to do. We'll steer clear of this database for now, though we will revisit it in Chapter 8, MySQL Administration, when we discuss MySQL Administration. The second database, named test, is a sample database. You can actually get rid of this database. I won't be referring to it in this book, 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, displaying "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 a single command!

Before we go any further, let's learn a couple of things 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 haven't finished typing your command, and will let you continue to type 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 functionality allows you to spread long commands over several lines.

If you get 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 completely the command you had begun to type and will return to the 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 that doesn't need a semicolon, but you can use one if you want to.

mysql>quit
Bye

So, What's SQL?
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 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.

PART 2 CONTINUE-Creating a Database

Creating a Database
Those who are working on their Web host's MySQL server are likely already to have been assigned a database with which to work. Sit tight; we'll get back to you in a moment. If you're running a MySQL server that you installed yourself, however, you'll need to create your own database. 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, because that fits with the example we're using. 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, as it will probably 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 difficult 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 our jokes.

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 (
-> column_1_name column_1_type column_1_details,
-> column_2_name column_2_type column_2_details,
-> ,
->;

Let's return to our example joke table. 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). The command to create this table is as follows:

mysql>CREATE TABLE joke (
-> 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 fairly simple; it says that we want to create a new table named joke.
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 the 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 the 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 lowercase, 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 in advance a data type for each column. 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 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 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 ijdb |
+----------------+
| joke |
+----------------+
1 row in set

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

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

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 somewhat cryptic, but if you look at them closely, you should be able to figure out what they 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 task is as frighteningly easy as deleting a database. In fact, the command is almost identical:

mysql>DROP TABLE tableName;

Inserting Data into a Table
Our database is created and our 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 takes two basic forms:

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 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 = "2004-04-01";

mysql>INSERT INTO joke
->(joketext, jokedate) VALUES (
->"Why did the chicken cross the road? To get to the other
"> side!",
->"2004-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 provide 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 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. At this early point in our experience with databases we need only fairly simple lists of results, so we'll just consider the simpler forms of the SELECT command here. This command will list everything that's stored in the joke table:

mysql>SELECT * FROM joke;

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

+----+---------------------------------------------------
------------+------------+
| id | joketext
| jokedate |
+----+---------------------------------------------------
------------+------------+
| 1 | Why did the chicken cross the road? To get to the
other side! | 2004-04-01 |
+----+---------------------------------------------------
------------+------------+
1 row in set (0.05 sec)

The results look a little disorganized because the text in the joketext column is so long that the table can't fit on the screen properly. 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 joke;

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 | 2004-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?

PART 2 CONTINUE- Creating a Database (2)

As well as being able to name specific columns that we want the SELECT command to show us, we can use functions to modify each column's display. One function, called LEFT, lets us tell MySQL to display a column's contents up to a specified maximum number of characters. For example, let's say we wanted to see only the first 20 characters of the joketext column. Here's the command we'd use:

mysql>SELECT ID, LEFT(joketext, 20), jokedate FROM joke;
+----+----------------------+------------+
| id | LEFT(joketext, 20) | jokedate |
+----+----------------------+------------+
| 1 | Why did the chicken | 2004-04-01 |
+----+----------------------+------------+
1 row in set (0.05 sec)

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

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

As you can see, we have just one joke in our table and, so far, all the examples have fetched all the entries in our table. However, we can limit our results to include only those database entries that have the specific attributes we want. We set these restrictions by adding what's called a WHERE clause to the SELECT command. Consider this example:

mysql>SELECT COUNT(*) FROM joke WHERE jokedate >= "2004-01-01";

This query will count the number of jokes that have dates greater than or equal to January 1, 2004. In the case of dates, "greater than or equal to" 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 joke WHERE joketext LIKE "%chicken%";

The above 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 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 2004 only, we could use the following query:

mysql>SELECT joketext FROM joke WHERE
->joketext LIKE "%knock%" AND
->jokedate >= "2004-04-01" AND
->jokedate < "2004-05-01";

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

You can do a lot with the SELECT statement. We'll look at some of its more advanced features later, when we need them.

Modifying Stored Data
Having entered your data into a database table, you might like to change it. 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 elements of the SELECT command that pick out entries for modification. 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 joke SET jokedate="1994-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 used here works just as it did in the SELECT command. This next command, for example, changes the date of all entries that contain the word "chicken:"

mysql>UPDATE joke SET jokedate="1994-04-01"
->WHERE joketext LIKE "%chicken%";

Deleting Stored Data
The deletion of entries in SQL is dangerously easy, which, if you haven't noticed yet, is a recurring theme. Here's the command syntax:

mysql>DELETE FROM table_name WHERE conditions;

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

mysql>DELETE FROM joke WHERE joketext LIKE "%chicken%";

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

mysql>DELETE FROM joke;

Scary, huh?