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.

Our Back to Business guide highlights the best products for you to boost your productivity at home, on the road, at the office, or in the classroom.

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

Gavin Sherry

PC World
Show Comments

Most Popular Reviews

Latest News Articles

Resources

PCW Evaluation Team

Azadeh Williams

HP OfficeJet Pro 8730

A smarter way to print for busy small business owners, combining speedy printing with scanning and copying, making it easier to produce high quality documents and images at a touch of a button.

Andrew Grant

HP OfficeJet Pro 8730

I've had a multifunction printer in the office going on 10 years now. It was a neat bit of kit back in the day -- print, copy, scan, fax -- when printing over WiFi felt a bit like magic. It’s seen better days though and an upgrade’s well overdue. This HP OfficeJet Pro 8730 looks like it ticks all the same boxes: print, copy, scan, and fax. (Really? Does anyone fax anything any more? I guess it's good to know the facility’s there, just in case.) Printing over WiFi is more-or- less standard these days.

Ed Dawson

HP OfficeJet Pro 8730

As a freelance writer who is always on the go, I like my technology to be both efficient and effective so I can do my job well. The HP OfficeJet Pro 8730 Inkjet Printer ticks all the boxes in terms of form factor, performance and user interface.

Michael Hargreaves

Windows 10 for Business / Dell XPS 13

I’d happily recommend this touchscreen laptop and Windows 10 as a great way to get serious work done at a desk or on the road.

Aysha Strobbe

Windows 10 / HP Spectre x360

Ultimately, I think the Windows 10 environment is excellent for me as it caters for so many different uses. The inclusion of the Xbox app is also great for when you need some downtime too!

Mark Escubio

Windows 10 / Lenovo Yoga 910

For me, the Xbox Play Anywhere is a great new feature as it allows you to play your current Xbox games with higher resolutions and better graphics without forking out extra cash for another copy. Although available titles are still scarce, but I’m sure it will grow in time.

Featured Content

Latest Jobs

Don’t have an account? Sign up here

Don't have an account? Sign up now

Forgot password?