Chapter 8. Writing MySQL Programs Using PHP

PHP is a scripting language for writing Web pages containing embedded code that is executed whenever a page is accessed and that can generate dynamic content to be included as part of the output sent to a client's Web browser. This chapter describes how to write PHP-based Web applications that use MySQL. For a comparison of PHP with the C and Perl DBI APIs for MySQL programming, see Chapter 5, "Introduction to MySQL Programming."

The examples in this chapter draw on our sampdb sample database, using the tables created for the grade-keeping project and for the Historical League in Chapter 1, "Getting Started with MySQL and SQL." The applications described here should run under either PHP 4 or PHP 5.

This chapter was written under the assumption that you'll use PHP in conjunction with the Apache Web server. In addition, PHP must be built with the MySQL C client library linked in, or it will not know how to access MySQL databases. If you need to obtain any of this software, see Appendix A, "Obtaining and Installing Software." That appendix also provides instructions for obtaining the sampdb distribution that contains the example scripts developed in this chapter. You can download the scripts to avoid typing them in yourself. You'll find the scripts pertaining to the chapter under the phpapi directory of that distribution.

Under Unix, PHP may be used as an Apache module or as a standalone interpreter used as a traditional CGI program. Under Windows, PHP can run only as a standalone program unless you use Apache 2.x. In that case, you have the option of running PHP as an Apache module. On either platform, running PHP as a module is preferable for performance reasons.

PHP offers several ways to interface with MySQL:

• With PHP 4 or PHP 5, you can use the mysql module that provides a native interface to MySQL. It consists of functions with names of the form mysql_xxx(). For the most part, these map directly onto the C API functions with the same names.

• In PHP 5, an additional "MySQL improved" module, mysqli, is available. This module provides two calling styles. You can use it as a set of functions with names of the form mysqli_xxx(), or through an object-oriented interface. The mysqli module knows how to take advantage of the binary client/server protocol for prepared statements that was introduced in MySQL 4.1.

• Another interface is provided not by PHP itself but through PEAR DB, the DB module in the PHP Extension and Add-on Repository. The DB module provides a object-oriented database-independent interface similar in design to the Perl DBI module. It uses a two-level architecture in which the top level presents a standard abstract interface and the lower level consists of drivers for various database engines. To switch from one driver to another you just supply a different driver name when you connect to the database server you want to use.

  The DB module works with the mysql module for PHP 4 and with the mysql or mysqli modules for PHP 5. Drivers for other database engines are available as well, but are not covered here.

The scripts developed in this chapter use the DB module. However, they work whether you use PHP 4 or PHP 5, and whether you use the mysql or mysqli driver. By default, the scripts use the mysql driver, but with a trivial change in a configuration file, you can use the mysqli driver instead.

For the most part, this chapter describes only those MySQL-related DB module objects and methods that are needed for the discussion here. For a more comprehensive listing of the DB module interface, see Appendix I, "PHP and PEAR DB API Reference." You'll likely also want to consult the PHP and PEAR manuals, which describe all the functions that PHP and PEAR provide, including those for using databases other than MySQL. The manuals are available from the PHP and PEAR Web sites:

http://www.php.net/
http://pear.php.net/

Another helpful source of information is PHP Functions Essential Reference by Greant et al. (New Riders, 2002).

Filenames for PHP scripts generally end with an extension that allows your Web server to recognize that they should be executed by invoking the PHP interpreter. If you use an extension that isn't recognized, your PHP scripts will be served as plain text. The extension used in this chapter is .php. For instructions on configuring Apache to recognize the extension you want to use, see Appendix A. (If you are not in control of the Apache installation on your machine, check with the system administrator to find out the proper extension to use.) The appendix also describes how to set up Apache to treat any script named index.php as the default page for the directory in which it is located, similar to the way Apache uses files named index.html.

To use the scripts developed in this chapter, you'll need to install them where your Web server can access them. I'll adopt the convention in this chapter that the U.S. Historical League and grade-keeping projects have their own directories called ushl and gp at the top level of the Apache document tree. To set up your Web server that way, you should create those directories now. If the Web site host is www.snake.net, pages in those two directories then will have URLs that begin like this:

http://www.snake.net/ushl/...
http://www.snake.net/gp/...

For example, the home pages in each directory can be called index.php and are accessed as follows:

http://www.snake.net/ushl/index.php
http://www.snake.net/gp/index.php

If you have Apache configured to use index.php as the default page for a directory, the following URLs are equivalent to the preceding ones:

http://www.snake.net/ushl/
http://www.snake.net/gp/

Remember to change the example URLs throughout this chapter to point to your own Web server rather than to www.snake.net.