By Charles Fisher

Historically, the construction of SQL-enabled Web applications has not been easy. Such systems usually required not only expensive database software, but also programmers familiar with C, the CGI specification, and dialects of embedded SQL. PHP 3.0 and PostgreSQL stand to change this state of affairs. PHP allows SQL scripting to be embedded in HTML files, and PostgreSQL brings a reasonably powerful database to the masses. This article presents a complete Linux-based, SQL-enabled Web application, hiding none of the details.

• How You Can Use This Information



• Major Challenges



• Installing PostgreSQL



• Installing PHP



• Establishing a Database



• Using SELECT from the Web



• Using INSERT from the Web



• Conclusion

Questions regarding this article should be directed to the author at cfisher@netexpress.net

How You Can Use This Information

Hypertext Markup Language was a tremendous innovation, but its principle weakness is its static nature. HTML was intended to be a method for sharing documents, not an interactive medium.

However, if your Information Systems needs require general global visibility, the Web is an obvious choice. The Web has achieved such ubiquity that efforts to overcome its limitations are a primary developer concern. Technologies -- such as Java, ActiveX, and XML -- represent the recent attempts to extend the power of the Web.

PostgreSQL is a successor to the Postgres DataBase Management System (DBMS). A DBMS enables the fast storage and retrieval of large amounts of information. The interface that is used to insert, manipulate, and extract data is called the Structured Query Language (SQL), which was developed by IBM for their DB2 database product in the 1970s. PostgreSQL implements a subset of ANSI-standard SQL (most notably, the version discussed here does not implement sub-queries or outer joins). PostgreSQL runs on a variety of Unix platforms. Precompiled binaries for PostgreSQL are now shipped standard in Red Hat Linux 5.0.

Without an interface to link it to the Web, however, a SQL database will be of little use. Middleware software is required to meld HTML and SQL into a single format.

PHP (Professional Home Pages), the successor to PHP/FI, is a utility that has enjoyed a great deal of popularity. It allows a C-like programming language with SQL extensions to be directly embedded within HTML documents. The software has recently been through a complete rewrite with a development emphasis on performance. PHP requires no extensions to the browser as it relies upon the CGI interface.

Please note that PHP and PostgreSQL do not provide a total electronic commerce solution. The Apache Web Server, included with Linux, does not provide SSL encryption. Some sources for SSL extensions for Apache are the commercial Stronghold version of Apache with SSL support and SSLeay. Web sites might also need credit-card validation software. CyberCash is one such vendor.

The information presented within this document is intended for Intel-based systems running Red Hat Linux 5.0. Much of the same information will be applicable, however, to other platforms as all of the software discussed here (PostgreSQL, PHP 3.0) has been ported to a wide variety of Unix implementations.

Major Challenges

If you are running Red Hat Linux 5.0, the task before you is easiest. An older version of the PostgreSQL database is included in the binary distribution. It can easily be loaded when the system is installed by selecting it from the component menu. It is also possible to install PostgreSQL on a running system with RPM commands.

If you are running an earlier release of Red Hat Linux (4.2 or below), you can install an RPM containing PostgreSQL. However, if you are running another Unix variant, you must download and compile PostgreSQL.

PHP is not included in Red Hat Linux 5.0. It must be downloaded and installed regardless of the Unix version that is being used. There are a number of performance options that can be selected at compile time, which will be discussed in a later step.

Installing PostgreSQL

PostgreSQL has a Web site with links to the latest versions of the database and a complete library of documentation. The Web site is in need of funding. That is, although PostgreSQL is a free object-oriented RDBMS, the developers are asking for a small donation to be used to upgrade their development system so they can continue development of this software.

If you are preparing a fresh copy of Red Hat Linux 5.0, installation of PostgreSQL is simple. Just select the SQL server from the ``Components to Install'' menu at setup time, then later in the setup indicate that the PostgreSQL server should be started at boot in the ``Services'' menu. The software will be installed, a ``postgres'' user will be added as a DataBase Administrator (DBA), and startup scripts and links will be installed under /etc/rc.d to spawn the database server in the appropriate system run levels (supported by the Unix init utility).

If you installed a copy of Red Hat Linux 5.0 without loading the database components, you can install them at a later time if you have the RPMs containing PostgreSQL. You can find the RPMs on your distribution CD or off the Red Hat FTP site. If you have them, they can be installed and configured by running the following commands as root:

#
#
#
#

rpm -Uvh postgresql-6.2.1-7.i386.rpm
rpm -Uvh postgresql-devel-6.2.1-7.i386.rpm
rpm -Uvh postgresql-data-6.2.1-7.i386.rpm
ntsysv

The ntsysv command will allow you to select which system services are started at boot time (it creates links from files in /etc/rc.d/init.d to files in /etc/rc.d/rcX.d). Make sure that both PostgreSQL and the Apache Web server are selected.

If you are running an earlier release of Red Hat Linux (4.2 or below), you can still install an RPM containing PostgreSQL. Look for postgresql-6.2.1-1.i386.rpm in the contrib areas on the ftp.redhat.com site.

If you are running another Unix variant, you must download and compile PostgreSQL from their Web site's download areas. This may not be such a bad thing because a newer version of the database is available that now supports a larger subset of ANSI SQL (specifically, it now implements subselects).

An ANSI C compiler is required for the preparation of PostgreSQL. Most modern versions of commercial UNIX do not include such compilers (down with the un-bundling of UNIX!). If you do not have a C compiler for your proprietary UNIX system, Linux presents a simpler solution.

GNU Gcc is available for many platforms, and it can be used to prepare PHP. Check the Frequently Asked Questions list (FAQ) for your operating system to see if Gcc is available (such FAQs are easily found with the common internet search engines).

Please also note that binaries produced from C code compiled by Gcc will rarely be as fast as binares produced from the same code but compiled by the OS vendor's native compiler. Performance under high utilization conditions may increase with such native compilers.

Installing PHP

PHP is available from the PHP 3.0 Site. The code only recently emerged from beta testing, and is available for download. The name of the file is php-3.0RC3.tar.gz. You must obtain this file or a later version, if one is available.

PHP supports a number of database servers in addition to PostgreSQL. These include Adabas, mSQL, MySQL, Oracle, Sybase, and ODBC. PHP now also supports LDAP directory services and the IMAP mail protocol. More information about PHP is available in the FAQ at their site.

There are two ways to configure PHP. It can be used either as a CGI binary, or as a module loaded at run-time for a supported Web server (Apache, the Netscape servers, and Microsoft IIS).

Loading PHP as an Apache module has a number of benefits. With the CGI approach, the entire PHP parser is loaded, executed, then terminated every time a browser accesses PHP on the server. When loaded as a module, the PHP parser becomes an integrated part of the Apache run-time environment. Because the PHP memory image is not loaded and destroyed by each access, the performance of the module configuration is significantly greater.

However, there have been a number of updates to the Apache Web server issued by Red Hat. Each time such an update occurs, the PHP-enabled Web server must be rebuilt from source. Such an arrangement can quickly become tedious.

In compiling PHP as a CGI binary, PHP is built in such a way that it is entirely separate from the Web server installation. Updates to the Web server can be applied without fear of disturbing PHP. This configuration should be appropriate for all but the most high-traffic Web sites.

Running PHP as a CGI binary could create a serious security problem. Apache allows files -- named .htaccess by default -- to limit access to Web pages to specific IP addresses or to users who supply a valid username-password combination. PHP does not honor .htaccess files, and thus, if installed as a CGI binary, it can be used to read every Web page under the server's document root or users' directories, regardless of the access control that has been specified in .htaccess. Luckily, this is not an issue with the Apache module version of PHP.

Ultimately, users who are not overly concerned with the security of their Web server's document root directory tree and who do not anticipate high volume usage of PHP-enabled Web pages, should install the CGI version of the parser. All others should take the extra time to install PHP as a module.

To install PHP as a CGI binary on a Red Hat Linux 5.0 system, issue the following commands as root (superuser):

#
#
#
>
#
#

tar xvzf php-3.0RC3.tar.gz
cd php-3.0RC3
CFLAGS="-s -O3 -I/usr/include/pgsql" ./configure --disable-debug \
--with-pgsql=yes --with-system-regex
make
cp php /home/httpd/cgi-bin

The rest of this document assumes the use of the CGI version.

If you wish to prepare the Apache module version of PHP, you must obtain the source for the latest Apache RPM (<URL:ftp://ftp.redhat.com/pub/redhat/redhat-5.0/updates/SRPMS/apache-1.2.5-1.src.rpm>). To compile and install the modified httpd, stop your Web server, then place the Apache source RPM and the PHP package in the same directory and issue the following commands as root:

#
#
#
#
#
#
#
#
#
>
>
#
#
#
#
#
>
>
#
#
#
#
#
#
#
#
#

mkdir apache
cd apache
rpm2cpio ../apache-1.2.5-1.src.rpm | cpio -i
tar xvzf apache_1.2.5.tar.gz
sed 's/apache-1\.2\.4/apache_1.2.5/' apache_1.2.4-rh.patch | patch
cd ..
tar xvzf php-3.0RC3.tar.gz
cd php-3.0RC3
CFLAGS="-s -O3 -I/usr/include/pgsql" ./configure --disable-debug \
--with-pgsql=yes --with-system-regex \
--with-apache=../apache/apache_1.2.5
make
make install
cd ../apache/apache_1.2.5/src
echo 'Module php3_module mod_php3.o' >> Configuration
sed 's/^EXTRA_LIBS.*$/EXTRA_LIBS=-L. -Lmodules\/extra -L..\/modules\/extra -lphp3 -lgdbm -ldb -lm -ldl -lcrypt -lpq/' Configuration | \
sed 's/Rule WANTHSREGEX=default/Rule WANTHSREGEX=no/' | \
sed 's/EXTRA_CFLAGS=/EXTRA_CFLAGS=-s -O3/' > Configuration1
cp Configuration1 Configuration
./Configure
make
strip httpd
cp -f httpd /usr/sbin
chmod 755 /usr/sbin/httpd
echo 'AddType application/x-httpd-php3 .php3' >> /etc/httpd/conf/srm.conf
echo 'AddType application/x-httpd-php3 .phtml' >> /etc/httpd/conf/srm.conf
echo 'AddType application/x-httpd-php3-source .phps' >> /etc/httpd/conf/srm.conf

A slightly smaller binary image will be created if the -O2 level of optimization is used rather than -O3 (although the binary will not run as fast. Here, the -O3 option tells the compiler to generate inline functions where appropriate).

Establishing a Database

The following set of examples will create a database that implements a Web-enabled grocery shopping list.

Once PostgreSQL is installed, users should be created that will maintain the databases. Those users must also be registered with the PostgreSQL server.

The commands below will add a user named luser to a Red Hat system, then register both luser and nobody with the database server.

If you want to use a different account name than luser, just substitute the modified name for luser in the rest of this document.

As superuser, issue the following commands:

# useradd luser
# su - postgres
# createuser luser
Enter user's postgres ID or RETURN to use unix user ID: 500 -> (enter)
Is user "luser" allowed to create databases (y/n) y
Is user "luser" allowed to add users? (y/n) n
createuser: luser was successfully added
# createuser nobody
Enter user's postgres ID or RETURN to use unix user ID: 99 -> (enter)
Is user "nobody" allowed to create databases (y/n) n
Is user "nobody" allowed to add users? (y/n) n
createuser: nobody was successfully added
don't forget to create a database for nobody
# exit

(The text in boldface above indicates the commands you must enter, while the text in the normal typeface indicates the system's response).

Don't forget to set the UNIX password for the ``luser'' account with the passwd command.

The user nobody needs to be registered with the database because the Web server runs under this userid. This action will enable the Web server to run queries against the database server.

Next, login as ``luser'' and run createdb grocery. This command creates a database. Older DBMS applications, like dBase and its derivatives, called tabular collections of data databases. Relations could be established between these databases. This is not so in PostgreSQL. In this new paradigm, databases contain tables, and these tables contain the tabular data. Relations are established between tables that lie within a database. The previous command only creates the database; the tables are created in a later step.

Now, while logged in as ``luser,'' enter the command to initiate the interactive SQL interpreter, namely: psql grocery.

The SQL interpreter will print a welcome message:

Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: grocery
grocery=>

SQL commands may be entered directly at the prompt. They may span many lines if necessary. The command is not issued until a semicolon is encountered.

Enter the following text at the prompt to create a table to hold the grocery list:

CREATE TABLE list (
item TEXT,
vendorcode INT,
quantity INT );

This command creates a table named ``list,'' which is composed of three fields. The fields correspond to columns in a spreadsheet (although a PostgreSQL database can be magnitudes larger than the largest spreadsheets). The names of the fields are ``item,'' ``vendorcode,'' and ``quantity.'' The fields have an associated data type. For instance, the vendorcode and quantity fields are both defined as type integer (they can only contain numbers, not text). The item field is of type text, and its size can be of any length.

SQL is not case-sensitive so any combination of upper- and lower-case characters can be used with its commands. The style used here follows that of most popular tutorial texts on database design, which allows one to easily distinguishe the SQL reserved words from the variable names.

The following command creates another table named ``vendors'' with two fields, one for integers, the other for text.

CREATE TABLE vendors (
vendorcode INT,
vendorname TEXT );

The tables have been created and they are empty. The SQL INSERT command can be used to populate the tables with several values, as shown:

INSERT INTO vendors VALUES (100, 'Super Grocer');
INSERT INTO vendors VALUES (101, 'General Department Store');
INSERT INTO vendors VALUES (102, 'General Auto Parts');
INSERT INTO list VALUES ('Root Beer', 100, 3);
INSERT INTO list VALUES ('Ice Cream', 100, 1);
INSERT INTO list VALUES ('Napkins', 101, 50);
INSERT INTO list VALUES ('Spark Plugs', 102, 4);

The data can be examined with the SQL SELECT command, shown here:

SELECT item, vendorcode, quantity FROM list;

The SQL interpreter should respond with:

item |vendorcode|quantity
-----------+----------+--------
Root Beer | 100| 3
Ice Cream | 100| 1
Napkins | 101| 50
Spark Plugs| 102| 4
(4 rows)

Fields can be rearranged or omitted from the SELECT command by modifying the field names:

SELECT item, quantity FROM list;
item |quantity
-----------+--------
Root Beer | 3
Ice Cream | 1
Napkins | 50
Spark Plugs| 4
(4 rows)

As an alternative, an asterisk can be used to indicate that all fields are desired from the SELECT query:

SELECT * FROM list;
item |vendorcode|quantity
-----------+----------+--------
Root Beer | 100| 3
Ice Cream | 100| 1
Napkins | 101| 50
Spark Plugs| 102| 4
(4 rows)

Notice in the previous table that the numerical vendorcodes are printed rather than the more useful vendornames. The latter are actually contained within the ``vendors'' table:

SELECT * FROM vendors;
vendorcode|vendorname
----------+------------------------
100|Super Grocer
101|General Department Store
102|General Auto Parts
(3 rows)

In the case of these two tables, however, the vendorcode field is not very useful. It would be much more appropriate to list the vendorname field from the vendors table when printing the list table. Such a thing is possible by establishing a relation:

SELECT list.item, vendors.vendorname, list.quantity
FROM list, vendors
WHERE list.vendorcode = vendors.vendorcode;
item |vendorname |quantity
-----------+------------------------+--------
Root Beer |Super Grocer | 3
Ice Cream |Super Grocer | 1
Napkins |General Department Store| 50
Spark Plugs|General Auto Parts | 4
(4 rows)

In the example above, the FROM clause specifies that two tables are to be used. The WHERE clause specifies the conditions for the relation. Relations such as these are called joins in SQL.

When two tables are used in a SELECT statement, such as the one above, the tablename.fieldname syntax is used to distinguish between the tables and fields.

There are many more options to the SQL SELECT statement; so many that SELECT is the most powerful command in the SQL language. Here is a slight variation on the previous example:

SELECT list.item, vendors.vendorname, list.quantity
FROM list, vendors
WHERE list.vendorcode = vendors.vendorcode
ORDER BY item;
item |vendorname |quantity
-----------+------------------------+--------
Ice Cream |Super Grocer | 1
Napkins |General Department Store| 50
Root Beer |Super Grocer | 3
Spark Plugs|General Auto Parts | 4
(4 rows)

Here the ORDER BY clause causes the output to be sorted alphabetically by the item field.

One interesting point about SQL join operations is that records in one table that will not join with records in the other are omitted. If you run the following INSERT command:

INSERT INTO list VALUES ('African Violet', 103, 1);

And then immediately follow it with the previous SELECT statement:

SELECT list.item, vendors.vendorname, list.quantity
FROM list, vendors
WHERE list.vendorcode = vendors.vendorcode
ORDER BY item;

You will notice that the ``African Violet'' row in the ``list'' table was not printed. However, if you run the following command (and then re-run the SELECT):

INSERT INTO vendors VALUES (103, 'ACME Plant Store');

It will appear.

If you wish to delete rows from the database, you can use the SQL DELETE command:

DELETE FROM list WHERE item = 'African Violet';
DELETE FROM vendors WHERE vendorcode = 103;

Be careful, because ``DELETE FROM list;'' would wipe out all the data, leaving the table empty.

In a production environment -- where tables contain a large number of rows -- SQL operations may be faster if an ``index'' is defined. In this case, the commands to create the indexes are:

CREATE INDEX listtab ON list (vendorcode);
CREATE UNIQUE INDEX vendortab ON vendors (vendorcode);

Notice that a unique index is created on the ``vendors'' table, because each vendor will have a unique vendor code.

PostgreSQL keeps copies of modified or deleted rows in a database. This allows the database to be restored to the state it had at a previous date. Unfortunately, this also means that a large amount of storage could be consumed by inactive data. To clean your database of such inactive data, use the commands:

VACUUM list;
VACUUM vendors;

In the next section, the Web server will be connecting to the database to perform SELECT operations. Under Red Hat Linux, the Web server process runs under user identity ``nobody.'' To grant this user permission to SELECT from the database, run the following commands:

REVOKE ALL ON list FROM nobody;
GRANT SELECT ON list TO nobody;
REVOKE ALL ON vendors FROM nobody;
GRANT SELECT ON vendors TO nobody;

The REVOKE ALL commands above remove all access permissions. Using such a REVOKE before a GRANT ensures that no previous permissions remain to the user.

If you are finished with the SQL interpreter, you can log out of it by typing: \q.

The pg_dump command is a useful utility that allows easy backup and transport of PostgreSQL databases. It generates a text file composed of the SQL commands required to recreate a database. To dump the grocery database, use the shell command line: pg_dump > db.out

This file can be manipulated with a normal text editor. To reload the database, enter the shell command: psql -e grocery < db.out

As a last point, psql uses the GNU Readline library. It is configured by default to accept Emacs keystrokes to edit the command line (that is, the up and down arrows cycle through the previous and next commands, Control-A moves to the beginning of the line, and so forth.). However, if you write the single line: set editing-mode vi into a file named .inputrc located in your account home directory, then Vi commands can be used instead. Be warned that this changes all programs that use GNU Readline, including Bash and Gdb.

Using SELECT from the Web

This section assumes that you have a firm working knowledge of HTML. If this is not the case, you might want to review the NCSA Beginner's Guide to HTML.

If you have installed your PHP binary and have entered the database commands described above, you are ready to build Web pages that use SQL.

PHP, and its earlier namesake, PHP/FI, use a C-like structured programming language that is embedded directly within HTML. PHP is used as a document preprocessor (much like the preprocessing stage of a C compiler, except that it is much more powerful).

Copy the following HTML into a file on your system named /home/httpd/html/dbprint.phtml (if you are not running Red Hat Linux, place the file in your Web server's document-root directory:

<HTML>
<HEAD>
<TITLE>View Database Records</TITLE>
</HEAD>
<BODY>
<DIV ALIGN="center">
<P>View Database Records</P>
<TABLE BORDER="0">
<TR>
<TH>item</TH>
<TH>vendorname</TH>
<TH>quantity</TH>
</TR>
<?PHP
$conn = pg_Connect("localhost", "5432", "", "", "grocery");
if (!$conn) {
echo "An error occurred.\n";
exit;
}
$result = pg_Exec($conn,
"SELECT list.item, vendors.vendorname, list.quantity
FROM list, vendors
WHERE list.vendorcode = vendors.vendorcode
ORDER BY list.item;");
if (!$result) {
echo "An error occurred.\n";
exit;
}
$num = pg_NumRows($result);
$i = 0;
while ($i < $num) {
echo "<TR><TD>";
echo pg_Result($result, $i, "item");
echo "</TD><TD>";
echo pg_Result($result, $i, "vendorname");
echo "</TD><TD>";
echo pg_Result($result, $i, "quantity");
echo "</TD></TR>";
$i++;
}
pg_FreeResult($result);
pg_Close($conn);
?>
</TABLE>
</BODY>
</HTML>

If you are running Red Hat Linux and you have installed PHP as /home/httpd/cgi-bin/php, you can now use a Web browser to view your database by opening the URL: http://localhost/cgi-bin/php/dbprint.phtml

If you have installed the Apache module version of PHP, instead use the URL: http://localhost/dbprint.phtml.

You can substitute your Fully Qualified Domain Name (FQDN) for localhost if you want to view the page from browsers that are running on different hosts.

Assuming that everything runs smoothly, You should see an HTML table that looks like this:

The ``.phtml'' file-name extension is a normal convention for files marked with PHP tags. It is not, however, a requirement in this case; the files could have had an .html extension with no effect upon the operation of the PHP parser.

PHP also has a directive that is similar to Server Side Includes (SSI) supported by most popular Web servers. If the command include("/some/path/to/a/file.html") is encountered in a PHP block, the specified file will be inserted and parsed by PHP. There is even a phpIncludePath variable that can be set that will allow the Web developer to dispense with path names. Conventional SSI directives will not be processed with the CGI version of PHP.

PHP will also read files in users' home directories, following the normal conventions of Unix Web servers. For example, if you copied dbprint.html to ~luser/public_html/, you could open the document in its new location with the URL: http://localhost/cgi-bin/php/~luser/dbprint.phtml

The algorithm and syntax of the above HTML example are relatively simple. They are taken almost directly from the PHP/FI documentation.

Notice first the enclosing <?PHP and ?> tags that surround the non-HTML language. These mark the beginning and end respectively of PHP language statements. You may insert PHP statements delimited by these markers as many times as you like in your HTML file.

Here, these PHP statements do the following:

• The pg_Connect() call establishes a connection to the grocery database on the local server. It would be possible to place the PostgreSQL database server on a separate host. If such a thing were done, the target host would be entered here.



• The pg_Exec() call executes a SQL query and stores the output in a variable named $result.



• The pg_NumRows() call returns the number of rows extracted from the table by the previous SELECT.



• The while loop extracts and prints (using echo) the results, row by row, with the pg_Result() function call.



• pg_FreeResult() discards the contents of the previous pg_Exec, which can be important when running several consecutive large queries.



• And pg_Close() closes the connections and releases buffer memory.

Using INSERT from the Web

This section assumes that you have a firm working knowledge of HTML forms. If this is not the case, you might want to review the NCSA Guide to Fill-Out Forms.

There are two main sections of HTML that will be used to add records to the database. First, an HTML form must be constructed so that the information can be easily entered by the browser. Second, the information must be returned and processed by PHP in order to add it to the table.

The implementation of the form, and specifically the vendor field, presents a design dilemma. The SELECT form tag is the most obvious HTML form element to use to present a pre-defined vendor list.

Should this list be hard-coded in the HTML? If another vendor is added to the ``vendors'' table, the HTML will not be automatically updated, and users will not be able to enter data against the new vendor.

The form elements for the vendor SELECT tag could alternately be generated by PHP each time the form is accessed. The drawback to this method is that the server load will be increased.

Another approach would be to generate the HTML for the form whenever the ``vendors'' table is updated. This could be accomplished in a variety of ways, some of which stem from the fact that PHP can be called from a Unix shell prompt as well as from a CGI environment.

Below, I'm presenting an alternative; each time the page for the data-entry form is accessed, PHP inserts data from the ``vendors'' table to build the SELECT list.

Copy the following HTML into a file on your system named /home/httpd/html/dbform.phtml (if you are not running Red Hat Linux, place the following in your Web server's document-root directory:

<HTML>
<HEAD>
<TITLE>Insert Database Record</TITLE>
</HEAD>
<BODY>
<DIV ALIGN="center">
<H1>Add Database Record</H1>
<FORM METHOD="post" ACTION="/cgi-bin/php/dbinsert.phtml">
<TABLE BORDER="0">
<TR>
<TD>New Item:</TD>
<TD><INPUT NAME="item">
</TR>
<TR>
<TD>Vendor:</TD>
<TD><SELECT NAME="vendor" SIZE="1">
<?PHP
$conn = pg_Connect("localhost", "5432", "", "", "grocery");
if (!$conn) {
echo "An error occurred.\n";
exit;
}
$result = pg_Exec($conn,
"SELECT vendorname
FROM vendors
ORDER BY vendorname;");
if (!$result) {
echo "An error occurred.\n";
exit;
}
$num = pg_NumRows($result);
$i = 0;
while ($i < $num) {
echo "<OPTION>";
echo pg_Result($result, $i, "vendorname");
$i++;
}
pg_FreeResult($result);
pg_Close($conn);
?>
</SELECT></TD>
</TR>
<TR>
<TD>Quantity:</TD>
<TD><INPUT NAME="quantity"></TD>
</TR>
</TABLE>
<INPUT TYPE="submit">
</FORM>
</BODY>
</HTML>

Noticing the ORDER BY clause in the above SELECT statement, an index on ``vendors.vendorname'' might be useful if the ``vendors'' table grows large.

The above PHP code extracts each vendorname in alphabetical order and prints it to the browser with a prefix of OPTION (as is required by the SELECT tag.

You can now view your form with the URL: http://localhost/cgi-bin/php/dbform.phtml

Assuming that everything runs smoothly, you should see an HTML form that looks like this:

(This form is not active, and is not attached to a database server.)

Now that the ``front end'' is complete, a ``back end'' must be implemented.

Copy the following HTML into a file on your system named /home/httpd/html/dbinsert.phtml (if you are not running Red Hat Linux, place the file in your Web server's document-root directory:

<HTML>
<HEAD>
<TITLE>Confirm Database Insert</TITLE>
</HEAD>
<BODY>
<DIV ALIGN="center">
<?PHP
$conn = pg_Connect("localhost", "5432", "", "", "grocery");
if (!$conn) {
echo "An error occurred.\n";
exit;
}
$result = pg_Exec($conn,
"SELECT vendorcode
FROM vendors
WHERE vendorname='$vendor';");
if (!$result) {
echo "An error occurred.\n";
exit;
}
$vendorcode = pg_Result($result, 0, "vendorcode");
pg_FreeResult($result);
pg_Exec($conn,
"INSERT INTO list
VALUES ('$item', $vendorcode, $quantity);");
pg_Close($conn);
?>
Database Updated
</BODY>
</HTML>

The first pg_Exec() call above runs a SELECT statement that locates the vendorcode given the vendorname. The resulting code is stored in the PHP variable $vendorcode.

Notice that the variable name given to the HTML SELECT tag in dbform.phtml is available to PHP in dbinsert.phtml. The same is true of the ``item'' and ``quantity'' form elements that are used in the next step.

With complete information for the item, vendorcode and quantity fields of the ``list'' table, the data can then be inserted by the last pg_Exec() call.

The last sections of code wrap up the PHP database transactions and send a confirmation message to the browser. If these .phtml files were describing a high-production data entry interface, it might be useful to copy dbform.phtml onto the end of dbinsert.phtml so that the form is continually presented.

Conclusion

In spite of claims made by Microsoft, a Linux database server is actually much less expensive than SQL under NT:

A Windows NT server costs approximately $1,000 (or more, depending upon user licenses). The Microsoft Backoffice package can add between $5,000 to $10,000 to the price, and what you've bought is a proprietary system with standards dictated by this monopolistic vendor.

The GNU Public License (GPL) release of Red Hat Linux is available from Linux Systems Labs for the cost of the distribution media ($1.95). If you don't like Linux, all of the software mentioned above can be ported to other Unix platforms (some of it even runs on NT).

Linux also includes an unlimited-seat SMB server, SQL server, dialup-server capability, FTP, WWW, Telnet, X Window System, NFS, and electronic mail servers. Some of these items are very pricey on Windows NT.

If you need an inexpensive, reliable Web database solution that offers the flexibility of Unix, our combination of Red Hat Linux, PHP, and PostgreSQL is one excellent way to go.

Author Biography

Charles Fisher is a writer and consultant who specializes in Linux. He has a home page that describes his personal and professional interests.