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.

More about Hewlett-Packard AustraliaHPMIT

Show Comments

Market Place