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 newsletter!

Error: Please check your email address.
Rocket to Success - Your 10 Tips for Smarter ERP System Selection
Keep up with the latest tech news, reviews and previews by subscribing to the Good Gear Guide newsletter.

Gavin Sherry

PC World
Show Comments

Cool Tech

Breitling Superocean Heritage Chronographe 44

Learn more >

SanDisk MicroSDXC™ for Nintendo® Switch™

Learn more >

Toys for Boys

Family Friendly

Panasonic 4K UHD Blu-Ray Player and Full HD Recorder with Netflix - UBT1GL-K

Learn more >

Stocking Stuffer

Razer DeathAdder Expert Ergonomic Gaming Mouse

Learn more >

Christmas Gift Guide

Click for more ›

Most Popular Reviews

Latest Articles

Resources

PCW Evaluation Team

Walid Mikhael

Brother QL-820NWB Professional Label Printer

It’s easy to set up, it’s compact and quiet when printing and to top if off, the print quality is excellent. This is hands down the best printer I’ve used for printing labels.

Ben Ramsden

Sharp PN-40TC1 Huddle Board

Brainstorming, innovation, problem solving, and negotiation have all become much more productive and valuable if people can easily collaborate in real time with minimal friction.

Sarah Ieroianni

Brother QL-820NWB Professional Label Printer

The print quality also does not disappoint, it’s clear, bold, doesn’t smudge and the text is perfectly sized.

Ratchada Dunn

Sharp PN-40TC1 Huddle Board

The Huddle Board’s built in program; Sharp Touch Viewing software allows us to easily manipulate and edit our documents (jpegs and PDFs) all at the same time on the dashboard.

George Khoury

Sharp PN-40TC1 Huddle Board

The biggest perks for me would be that it comes with easy to use and comprehensive programs that make the collaboration process a whole lot more intuitive and organic

David Coyle

Brother PocketJet PJ-773 A4 Portable Thermal Printer

I rate the printer as a 5 out of 5 stars as it has been able to fit seamlessly into my busy and mobile lifestyle.

Featured Content

Product Launch Showcase

Latest Jobs

Don’t have an account? Sign up here

Don't have an account? Sign up now

Forgot password?