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.
Keeping Your Data Current

To change field values in records, use UPDATE:

UPDATE tablename SET field-1 =
value-n WHERE qualification

The WHERE is optional, but if you don't specify a WHERE clause, SQL will update all the records, which is clearly the “Wrong Thing”.

Let's say Fred Mbogo wants to change his shell. This script will do it:

UPDATE passwd SET shell = '/bin/tcsh' WHERE username = 'fred';
Deleting Records

To delete records, simply use DELETE:

DELETE FROM tablename WHERE qualifier

Just like UPDATE, the WHERE is optional, but you probably want it anyway. Let's say Fred has offended his sysadmin one too many times:

DELETE FROM passwd WHERE username = 'fred';

All the PHP3 You Need To Know (Not Really)

The on-line PHP3 manual, http://www.php.net/manual/, is an excellent reference and will be necessary reading before you create your own database web application. Furthermore, it is a database-backed web site and has lots of user comments. Here, we will examine just the most basic PHP3 features.

Using PHP3

Here is a simple PHP3 program, which demonstrates some basic features. Note the separate HTML and PHP3 blocks:

<title>Hello, world!</title>
<body>
<?php
  echo("Hello, world!\n");
  echo("<p>\nWhat a <b>bold</b> move this is!\n");
?>
</body>

This program will send the following HTML to the remote browser:

<title>Hello, world!</title>
<body>
Hello, world!
<p>
What a <b>bold</b> move this is!
</body>
A similar program, which takes an argument, would look like this:
<title>Hello, world!</title>
<body>
<?php
  echo("Hello, $name!\n");
  echo("<p>\nWhat a <b>bold</b> move this is!\n");
?>
</body>
You would view this page (assuming you called it hello.php3) like any CGI script: http://yourhost.net/~fred/hello.php3?name=fred. This, of course, assumes you are named Fred and have put this file in your /public_html directory.

Database Connectivity

PHP3 provides a number of useful functions for connecting to databases; the best place to read up on these is www.php.net/manual/ref.pgsql.php3, and we shall examine a few of them.

int pg_connect(host, port, options, tty, dbname);

This function returns an integer, the “connection index”, which you will need for all operations on this connection. If a connection can't be established, it returns zero.

  • int pg_exec(conn, query); Executes the SQL query query on connection conn. Returns a result set index.

  • int pg_numrows(result); Returns the number of tuples in the result set result.

  • array pg_fetch_row(result,Returns an array of values corresponding to the row row of result set result.

  • void pg_close(conn); Closes the connection conn.

Example Application: A Multiuser Address Book

Our example application is an address book that one can access over the Internet. A user logs in with her name and password and is presented with a menu of options, including browsing and searching the address book and adding a new person. For each person in the address book, the database stores an arbitrary number of e-mail addresses, telephone numbers, URLs and postal addresses. This address book also has some nifty features like mailing passwords to new accounts and automatic mailto and href links for e-mail and web addresses.

______________________

Geek Guide
The DevOps Toolbox

Tools and Technologies for Scale and Reliability
by Linux Journal Editor Bill Childers

Get your free copy today

Sponsored by IBM

Webcast
8 Signs You're Beyond Cron

Scheduling Crontabs With an Enterprise Scheduler
On Demand
Moderated by Linux Journal Contributor Mike Diehl

Sign up and watch now

Sponsored by Skybot