PostgreSQL address book

We have looked extensively at interacting with the PostgreSQL relational database; now we look at integrating this into our ongoing project - a database-driven address book. First, we will have to create a table in the PostgreSQL database where we can store the data. This is done with the following query:

Python vs. PHP: Choosing your next project's language

CREATE TABLE addressbook (fn text, ln text, snum text, sname text, sub text, pc text, state text, ac text, pn text, mp text, e text);

This creates a table called 'addressbook' whose columns correspond to the variables we receive from the input form. The following script is a modification of the Address book II (AB2) script to use PostgreSQL and store the data in this structure.

<?
if(isset($submit) && strcmp("Add",$submit) == 0) {
   $conn = pg_pconnect("user=ab dbname=ab");
   if(!$conn) {
      exit("Failed to connect to PostgreSQL");
   }
   $sql = "INSERT INTO addressbook VALUES('$fn','$ln','$snum','$sname','$sub',
      '$pc','$state','$ac','$pn','$mp','$e');";
   if(!pg_exec($conn,"BEGIN;")) {
      exit("Could not begin transaction");
   }

   if(!pg_exec($conn,$sql)) {
      pg_exec($conn,"ABORT;");
      exit("Could not execute query '$sql'");
   }

   if(!pg_exec($conn,"COMMIT;")) {
      pg_exec($conn,"ABORT;");
      exit("Could not commit transaction");
   }
}
?>

This script covers a lot of ground. First, it connects to the database 'ab' as user 'ab' using the function pg_pconnect(). This function returns a connection handle or false on error. The script tests for this. It then compiles the SQL string that is to be executed. Notice that the variables correspond to the columns of the table we created above. These variables will have been submitted from the address book HTML form.

The script then begins a transaction, which will allow the queries we execute to be encapsulated with the possibility of undoing them upon error. If the execution of BEGIN fails, the script exits with an error.

Once the connection is inside a transaction, the INSERT query is executed. We again check for errors. If pg_exec() returns false, the script aborts the transaction with ABORT. It then exits. If all has gone well, however, the transaction is marked as having been completed with the COMMIT command. Notice that if COMMIT fails then the transaction itself fails, and the script again aborts and exits with an error. With PostgreSQL we can finally solve the problems we had with previous address book databases: trivially expanding the searchable fields. Consider the following HTML form:
<form method=POST action="<? echo $PHP_SELF; ?>">
Search for: <input type=text name=q size=10> in <select name=t>
<option value=1>First name</option>
<option value=2>Last name</option>
<option value=3>email address</option>
<input type=submit name=submit value=Search>
</form>

This HTML defines a form variable 't' which determines what field the user wants to search.

The code used to search the database begins on line 27. This script connects to PostgreSQL and checks that the connection handle is valid. It then parses the variable '$t' and builds an SQL qualification string which will be used to search the specified field. For example, if the user sets the search type to 'First name', the value of $t is set to 1. If the user then enters a 'Search for' string of 'Bob', the script sets:

$s = "fn = 'Bob'";

In this way, the query can qualify its range by only returning rows whose 'fn' field is equal to 'Bob'. The entire query string is then built. Given our example above, the find string would look like this:

select * from addressbook where fn = 'Bob'

The script checks that the query was executed successfully. If it returned results, they output to the user by iterating through the result set $res. If no results were returned, the user is informed.

The important aspect of this script is that so little code needs to be added in order to search a new field. For example, to retrieve rows matching a given phone number, you need only add a new <option> to the HTML form and another few lines to the parsing of $t. That's it!

There is one other point the script draws out. It does not use transactions. This is because the query is trivial and need not be encapsulated in case of failure, since it is read only. In a more complex application, even read-only operations should be encapsulated in transactions to ensure data integrity.

Join the PC World newsletter!

Error: Please check your email address.

Struggling for Christmas presents this year? Check out our Christmas Gift Guide for some top tech suggestions and more.

Keep up with the latest tech news, reviews and previews by subscribing to the Good Gear Guide newsletter.

Gavin Sherry

PC World

Most Popular Reviews

Follow Us

Best Deals on GoodGearGuide

Shopping.com

Latest News Articles

Resources

GGG Evaluation Team

Kathy Cassidy

STYLISTIC Q702

First impression on unpacking the Q702 test unit was the solid feel and clean, minimalist styling.

Anthony Grifoni

STYLISTIC Q572

For work use, Microsoft Word and Excel programs pre-installed on the device are adequate for preparing short documents.

Steph Mundell

LIFEBOOK UH574

The Fujitsu LifeBook UH574 allowed for great mobility without being obnoxiously heavy or clunky. Its twelve hours of battery life did not disappoint.

Andrew Mitsi

STYLISTIC Q702

The screen was particularly good. It is bright and visible from most angles, however heat is an issue, particularly around the Windows button on the front, and on the back where the battery housing is located.

Simon Harriott

STYLISTIC Q702

My first impression after unboxing the Q702 is that it is a nice looking unit. Styling is somewhat minimalist but very effective. The tablet part, once detached, has a nice weight, and no buttons or switches are located in awkward or intrusive positions.

Latest Jobs

Shopping.com

Don’t have an account? Sign up here

Don't have an account? Sign up now

Forgot password?