PHP4 and PostgreSQL: Building Serious Web Applications with Open-Source Software

It wasn't long ago that to build a serious web application with ease meant laying out serious cash for a Cold Fusion license, a proprietary database like Sybase, and a Sun server. Fortunately, those days are long over. With the vast reach of Apache and recent maturation of free databases, there are realistic, perhaps superior, alternatives to proprietary software.

The best of the open-source breed is PHP, a scripting language that could be likened to Perl; and PostgreSQL, a powerful object-relational database. When you put PHP and PostgreSQL together, you can build anything from a simple guest book to a vast web-based accounting application. PHP provides the brains while Postgres provides the brawn.

I'm going to introduce a very basic PHP shopping cart and inventory application that takes advantage of Postgres' transaction ability. You can download the source code and read other tutorials on my web site, PHPBuilder.com.

The first thing I want to cover is the structure of the application. In every web application I build with PHP, I start by setting up a comprehensive library, which gets included on every page throughout the site. This library will be called common.php and stored in a directory called include.

Our library will handle routine tasks for us, such as setting up a connection to the database, figuring out who the user is, setting up the header/footer of the site, etc. By keeping these functions in one place, our application will be much cleaner and easier to maintain. For example, rather than hardcoding a database connection on every page, we code it once in the library.

Listing 1. Sample Library Coding

There, the first rev of our library is already useful. It connects us to the database and provides a simple HTML abstraction for us. As our site HTML grows more complex, we can include most of it in site_header/site_footer and have the changes apply throughout our entire application.

The library also includes a simple abstraction layer for issuing Postgres queries. I do this simply to reduce the amount of code I have to write.

And finally, we make a call to PHP4's built-in session management code. Calling session_start will cause PHP4 to reload any variables that you registered so they are available to the rest of your application.

Each page on our site will look fundamentally like this:

<?php<\n>
//include the common library
require ($DOCUMENT_ROOT.'/include/common.php');
echo site_header('Example Page');
/*
        page logic
*/
echo site_footer();
?>

Generally, when building an application, it's wise to separate the heavy-lifting logic from the actual presentation (in this case, HTML) as much as possible. I generally do this by wrapping logic inside of function calls, which are then included and called from the pages throughout your site. The reason is that you may eventually want to build different interfaces for your applications—perhaps an extremely lightweight interface for wireless applications. If the logic were interwoven with the HTML presentation, you would have to duplicate the logic to create another interface. If it's separated into function libraries, both interfaces can reuse the same logic.

The problem with function calls in PHP is that there is no standard exception-handling process. If there is an internal error in a function, how does the calling code know and pass that information to a user? In other languages, like Java for example, you would throw an exception inside of your functions (methods in Java). When you call the method, you would wrap it in a try/catch statement and handle the problem accordingly.

I generally solve this problem in PHP by always returning either true or false from a function call, and setting a global variable called $feedback. The result is then testable and $feedback can be printed to the screen if needed. There is an effort underway called PEAR (http://pear.php.net/), which is making an attempt at standardizing error handling and database access, among other things, but nothing is concrete yet.

Here is an example of how you can call functions using my true/false method:

<?php<\n>
$result=function_call_name();
if (!$result) {
        //there was an error-display it
        echo $feedback;
} else {
        //continue on with success
}
?>

Now let's start thinking about our shopping cart. We need a few fundamental data structures to let us store our shopping cart data. For starters, we need an inventory database that lists item name, part number, price, and quantity on hand. We'll also need to keep track of our customers and the items the customer is purchasing. That's as complex as we'll get here.

Listing 2. Shopping Cart Data Structures

That should be enough to give us a rudimentary shopping cart. To normalize our database schema, I created a separate table that lists the contents of a customer's cart. This lets a customer have multiple items in their cart while allowing us to easily join the cart contents with the inventory database.

Now we need to think about the actions the various functions of an on-line store. The most basic functions include getting a new cart, adding items to that cart, then checking out. A real on-line store will need a lot more, such as the ability to browse the items, adjust quantities, etc., but I'll leave those features up to you.

I'll start with a simple function call to create a new customer. All that really entails is getting the next value out of the customer sequence we created, inserting that into a new record in the customer table, then registering that number in PHP4's built-in session management code.

Listing 3. Creating a New Customer

That took a little more code than I would have liked, but it shows you how to start and terminate a transaction in Postgres properly and how to check each query for errors. I will use the same routine error checks throughout all my code, and you should as well.

Always plan out how you will handle the situation when a query fails. Will you terminate your script outright, try the query again or just go on as if nothing happened? Carefully consider the consequences of each option. For example, if you fail to get the next customer_id, you can't really go on to create a new customer record. If creating the customer record fails, you can't later update her address information or add items to her cart. Logical, right?

Now let's look at the process for adding items to a cart. This too is relatively easy. Before inserting an item into the cart, we should first check that the item exists in the database. This is considered best practice, since the item number will be coming in from a web browser, and could have been tampered with.

Once we know that the item exists, we can then test to see if it's already in the cart. If it is in the cart, increment the quantity, rather than insert another row. If it is not in the cart already, insert it into the shopping cart with a default quantity of one.

Listing 4. Adding Items to a Cart

Now we can create new customers and they can add items to their carts. Now we just need to be able to check out from the store and reduce the store inventory at the same time. This is the most complex part of the entire store and makes good use of Postgres' transaction and advanced-locking scheme.

To start, we will make use of Postgres' SELECT...FOR UPDATE syntax, which effectively locks selected rows so you can update them and commit your changes within a transaction.

By using this syntax within a transaction, you can guarantee that your data stays consistent. With some databases, like MySQL, you can't easily lock specific rows of data to prevent other processes from decrementing the inventory while you are also trying to do the same. What you wind up with is inaccurate numbers and a useless inventory count.

This query will also make use of subselects, another standard feature on the more powerful databases available. Subselects allow you to basically tie two queries together into one to make your life easier.

After we lock the rows, we need to issue a query to decrement the inventory count for each item in our cart. For simplicity's sake, we won't warn of lack of inventory, but we will successfully set the item_count negative if we fall below a 0 inventory level. You could then set up an admin page to view items with a negative inventory balance and order fresh inventory.

And finally, we will want to update the customer's table with the visitor's credit card, shipping info, and total sale amount, and then destroy the customer's session from PHP4.

Listing 5. Checking Out and Reducing Inventory

Now that's a reasonably complex transaction, each step of which must execute properly. If it doesn't, the entire transaction must be rolled back to put everything back in the proper order.

If you didn't have the luxury of transactions in this example, and a query failed in the middle of updating one of the inventory items, you would be in a lot of trouble. You'd have part of your inventory updated and part not updated. If the visitor retried the page, how would you know which inventory items to decrement again? The answer is, you wouldn't, and you would wind up with an inaccurate inventory.

This article by no means provides a comprehensive shopping cart solution—I could probably write an entire book on that, if I had the time. It does, however, lay the groundwork, and demonstrate the design and execution methodology that I recommend for every web developer. For further information and discussion, visit PHPBuilder.com.

Some of the pieces that I'm leaving out include pages to view the cart contents and the ability to view items in the store. Those pieces, along with all the code here, are available in a .zip file, along with a discussion/comment board; www.phpbuilder.com/columns/linuxjournal200009.php3.