Home ›

Interfacing Relational Databases to the Web

Issue 67

From Issue #67
November 1999

Nov 01, 1999  By Will Benton
 in
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.

______________________

Webcast
More Resources
How to Build an Optimal Hadoop Cluster to Store and Maintain Unlimited Amounts of Data Using Microservers

Realizing the promise of Apache® Hadoop® requires the effective deployment of compute, memory, storage and networking to achieve optimal results. With its flexibility and multitude of options, it is easy to over or under provision the server infrastructure, resulting in poor performance and high TCO. Join us for an in depth, technical discussion with industry experts from leading Hadoop and server companies who will provide insights into the key considerations for designing and deploying an optimal Hadoop cluster.

Learn More

Sponsored by AMD

White Paper
More Resources
Red Hat White Paper: Using an Open Source Framework to Catch the Bad Guy

Built-in forensics, incident response, and security with Red Hat Enterprise Linux 6

Every security policy provides guidance and requirements for ensuring adequate protection of information and data, as well as high-level technical and administrative security requirements for a system in a given environment. Traditionally, providing security for a system focuses on the confidentiality of the information on it. However, protecting the data integrity and system and data availability is just as important. For example, when processing United States intelligence information, there are three attributes that require protection: confidentiality, integrity, and availability.

Learn more about catching the bad guy in this free white paper.

Learn More

Sponsored by DLT Solutions