Interfacing Relational Databases to the Web

This document explains how to build a database-backed web site using Apache, the PHP3 module and the PostgreSQL relational database.

Why would one want to link a database to
the web? A better question might be, “Why
wouldn't one want to link a database to the
web?” Static HTML pages are good for many things: documentation,
hypertext books, personal pages and other unchanging information.
However, static pages present a few problems:

Static pages can be a hassle to maintain. If one is
managing a large site with thousands of pages, changing just the
“look and feel” of the site will involve either an inordinate
amount of work or a long evening with CGI and Perl. This gets even
nastier when the content of the site changes.

Static pages don't allow for user input, feedback
or collaboration. Suppose you want to add a message board to your
static web site. You can set up a form that mails user comments to
the webmaster, who manually puts them on a page—you can even set
up a script to do this. However, this presents a few problems, as
we shall discuss later.

Static pages don't allow you to operate a web
service. One can operate a web site with static HTML, but some of
the most useful web sites today, such as Slashdot
(http://slashdot.org/),
CNN (http://www.cnn.com/)
and Amazon.com
(http://www.amazon.com/),
are services offering dynamic database-backed content.

A database management system provides uniform access to structured
data, much like an object-oriented programming language provides a
uniform way to store data and define methods to act on that data.
If you're writing a program in C++ and you need to deal with
strings, would you rather wrap all associated routines in a class
or deal with the fact that your string is really a char
* each time you operate on it? With a database, one is
offered a layer of abstraction between how the data is actually
stored and how one uses it, which is quite useful for several
reasons. You could achieve the same results with C or Perl scripts,
but it wouldn't be as pretty and could get downright ugly.

I hope this quick rundown is enough to convince you that you
really want to use a database for your dynamic web site. In this
article, I will present instructions for installing PHP3
(http://www.php.net/) and
PostgreSQL
(http://www.postgresql.org/),
a little bit of theory, some instructions for using SQL and PHP3,
and an example application.

Installing the Tools

This description assumes you are running Red Hat, but most of
these instructions will be applicable to other distributions; these
tools are fairly painless to install from source, anyway.

Here's a list of what you'll need to run the example
application and develop your own applications:

Apache 1.3 or greater. You will need at least
version 1.3, because 1.2 does not support modules, and PHP is
faster and more secure as a module. On a Red Hat system, you'll
need both the apache and
apache-devel packages. (Make sure
you have the file apxs installed, because we're going to recompile
the PHP3 Apache module.)

The source RPM for PHP3, version 3.0 or
greater.

PostgreSQL 6.4 or greater; the example code will
not run on version 6.3 without a little editing, because 6.4 has an
SQL parser.

To rebuild PHP3 for PostgreSQL support, take the following
steps:

Use su to become
root.

Install the source RPM for PHP3
(rpm -ihv mod_php3-3.0.5-2.src.rpm on
Red Hat 6.0). This will place a “spec file” in the directory
/usr/src/redhat/SPECS and a tar file of the source in
/usr/src/redhat/SOURCES. Since the PHP module that comes with Red
Hat doesn't have database support enabled by default, we'll have to
recompile it. RPM makes this fairly painless.

Because the PHP3 installation process assumes a
default PostgreSQL installation, not the Red Hat one, we'll need to
make some symlinks. Create a directory /usr/local/pgsql and make
symbolic links from /usr/include/pgsql to /usr/local/pgsql/include
and from /usr/lib/ to /usr/local/pgsql/lib.

Invoke your favorite editor on the spec file
(mod_php3.spec) and search for ./configure; then add the configure
option --with-pgsql.

Now build a binary package with rpm:
/rpm -bb mod_php3.spec/

If all goes well, you'll have a binary package in
/usr/src/redhat/RPMS/arch, where /arch is your architecture.
Install it, and you're ready to move on.