Our Case Study (Web Database Applications with PHP & MySQL)

1.5.1. What Is Hugh and Dave's Online Wines?

Hugh and Dave's Online Wines is a fictional online wine retailer. In this section, we briefly detail the aims and scope of the winestore and then discuss the system requirements derived from these. We also introduce the technical components of the winestore and point to the chapters in the book where these components are discussed in detail. We conclude with a discussion of the shortcomings of the winestore and what isn't covered in this book. The completed winestore described in this section can be accessed via this book's web site.

The winestore is open to the public: anonymous users have limited access to the system, and users can make purchases if they become members. The site aims to be attractive, simple, and usable; however, since it was designed by two computer scientists, we failed to make it attractive! It succeeds better in its technical aims: the winestore manages over 1,000 wines, stock information, and a database of around 1,000 customers and their orders.

Any user with a web browser can access the site, browse or search for wines that are in stock, and view the details. The details of wines include the name, year of release, wine type, grape varieties, and, in some cases, an expert review of the wine. Anonymous users can add selected wines to a shopping cart. Users can also be members, and the membership application process collects details about the customer in the same way as at most online sites.

To purchase wines, users must log in using their membership details. If a user has just joined as a member, he is logged in automatically. After selecting wines for purchase, the user can place an order. An order is shipped immediately and a confirmation sent by email.

Behind the scenes, the system also allows the stock managers of the winestore to add new shipments of wines to the database. The web site manager can also add new wines, wineries, winery regions, and other information to the winestore. Limited reporting features are available.

1.5.1.1. System requirements

The following requirements can typically be gathered from a scope document, customer interviews, and so on. But, of course, this book isn't about software engineering processes, and we present here the general requirements that form a basis for the examples in this book. Some aspects of our requirements are simplified, some aspects of a commercial store are omitted, and some details are real-world and comprehensive.

The requirements listed here are an overview; a real-world commercial application would present these facts in detailed functional and system requirements. A production application would also have an accompanying design document discussing the database design, screen layouts, and information flows.

Here's a summary of the functional and systems requirements:

The online winestore is primarily aimed as an e-commerce site to sell wine.

The system doesn't manage accounting, stock control, payroll, ordering, and other tasks.

Users may select wines and add them to a shopping basket. Users may purchase the items in their shopping baskets for up to one day after the first item is added to the basket. Users have only one shopping basket each and may empty their basket at any time.

Users of the site may be anonymous and can remain anonymous until they agree to purchase the items in the shopping basket.

To purchase items in a shopping basket, the user must log in to the system. To log in, a user must have an account. To get an account, a user must provide at least his surname, first name, one address line, a city, a zip code, a country, his birth date, an email address, and a password. The email address is used as the user's login name. The user may also optionally provide a middle initial, a title, two additional address lines, a state, a telephone number, and a fax number.

When a user purchases wines, his order is archived.

A user may receive a percentage discount on the price of an order. A discount can be levied on a particular day, a minimum threshold quantity, or given to a regular customer.

An order may have a delivery charge that is levied according to the user's location and the delivery mode. Delivery modes include sea mail, regular mail, and express mail. An order may also have a note that is directed to the delivery company; for example, a note might indicate to "leave the wines at the back door of the house."

Wines are classified into broad types of red, white, sparkling, sweet, and fortified. Wines also have a name, a vintage, and a description; descriptions are optional free-form text that are typically a review of the wine similar to that found on the label.

Wines are made with different grape varieties, including Chardonnay, Semillon, Merlot, and so on. A wine can be made of any number of grape varieties, and the order of these grape varieties is important. For example, for a wine made of two varieties, Cabernet and Merlot, a Cabernet Merlot is different from a Merlot Cabernet.

Users may browse wines at the winestore by type or wine region.

Wines are produced by one winery.

Wineries have a description—which is typically a review—as well as a phone and fax number.

Wineries are in one region. A region is an area—for example, the Barossa Valley in South Australia—and each region has a description and, possibly, an image or map of the area.

A shopping basket is an incomplete order that contains items. It can be converted to a completed order after the user logs in. Each item in an order is for a particular wine, a quantity of that wine to be purchased, and a price per bottle. The price of the wine is always the price of the first bottle of wine added to the shopping cart, which in turn is always the cheapest available inventory price.

The quantities of wines in the shopping basket can be updated by the user, and items can be removed from the shopping basket.

The wines available for sale are stored in an inventory. Each inventory record has a date added and is for a particular wine. The inventory contains a stock quantity available at a particular per-bottle and per-case price. There can be several inventory records for a wine, representing different shipments that arrived at the winestore on different dates or that have a different price.

The user will always be advertised prices from the cheapest inventory for each wine. When a user adds a wine to her shopping basket, she is guaranteed this price.

A user can purchase only wines that are in stock.

When a user converts his shopping basket to an order, the availability of sufficient inventory to complete the order is checked. If insufficient wine is available, the user is alerted, and the quantities in the shopping basket are updated; this situation can occur if a user adds more wine to his basket than is available.

When sufficient inventory is available to complete an order, the quantity of wine in the inventory is reduced as the order is finalized. The inventory reduced is always the oldest inventory of that wine.

1.5.2. Components of the Winestore

This section outlines where the principles and practical techniques to develop each component of the winestore are covered throughout this book. The completed winestore application is the subject of Chapter 10 through Chapter 13.

1.5.2.1. Database-driven querying

In Chapter 4, we introduce the techniques to connect to a DBMS, run a moderately complex SQL query, retrieve results, and process these results. To illustrate these techniques, we implement the Hot New Wines panel on the front page of the winestore. The completed panel is shown in Figure 1-5. The panel shows the newest three wines added to the database that have been reviewed by a wine expert. The completed shopping cart component is described in Chapter 11 and includes the panel code developed in Chapter 4.

Figure 1-5. The completed front page panel with the Hot New Wines panel

1.5.2.2. User-driven querying and browsing

Users can display selected wines stocked at the winestore by entering simple search criteria.

The result of clicking Search after selecting wines of type "Red" in the "Margaret River" region is shown in Figure 1-6. The results screen shows the first 12 of 38 wines that match the criteria and has links at the base of the screen to allow users to move through the results.

Figure 1-6. Links at the bottom of the browse page allow users to move through the results set

The techniques for collecting user input with HTML <form> widgets, query formulation with user input, and results browsing are presented in Chapter 5. There we also introduce the basics of securing a web database application by preprocessing user input. The completed code for this module is in Chapter 13.

1.5.2.3. Data entry and saving records to a database

Chapter 6 introduces techniques to write data to a database. We illustrate the principles of writing data by developing a simple customer membership <form> in Chapter 6, Chapter 7, and Chapter 8. The complete implementation of the customer membership process is discussed in Chapter 10, and the completed <form> is shown in Figure 1-7.

Figure 1-7. The customer <form> collects and updates member information

Writing data requires careful consideration of how other users are interacting with the database at the same time. We introduce the theory and practice of writing to databases in Chapter 6, as well as the PHP functions to manage and report on the writing process.

1.5.2.4. Validation in the client and middle tiers

We continue our development of the simplified customer <form> in Chapter 7, where we introduce validation in the client and middle tiers. Validation in both tiers is important. Client-side validation with JavaScript lightens the web-server load in the middle tier, is fast for the user, and has no network overhead. Server-side validation is also important: users can bypass client-tier validation or may not have it configured correctly, it may not be supported by the browser, and complete and complex validation might be possible only in the middle tier.

Figure 1-8 shows a customer <form> validation error message produced using the client-side JavaScript techniques discussed in Chapter 7.

Figure 1-8. A JavaScript validation error for the winestore customer <form>

1.5.2.5. User tracking and session management

Adding state to HTTP is the subject of Chapter 8, where we introduce the PHP session-management techniques that manage the transaction processes of a user at the winestore. We discuss the merits of these session management techniques and illustrate when they should and should not be used. In Appendix D, we discuss alternatives to session management that use the database tier for state maintenance.

We illustrate PHP sessions by extending the customer <form> example from Chapter 6 and Chapter 7. We show a practical example of storing and redisplaying data when the user returns to fix data entry errors from the validation process. At the conclusion of Chapter 8, the simple customer data entry <form> is complete. A full implementation of the winestore customer <form> using the same techniques is the subject of Chapter 10, and sessions are used throughout the code examples in Chapter 10 through Chapter 13.

1.5.2.6. Authentication

Authentication is the identification of two communicating parties. We discuss the principles of security and authentication in Chapter 9.We illustrate the principles with examples from the customized customer login and logout process at the winestore. The completed login and logout process at the winestore is discussed in Chapter 10.

1.5.2.7. The complete application

The winestore includes several complete components that are the subjects of Chapter 10 through Chapter 13:

The full implementation of the shopping cart is covered in Chapter 11.
Presentation of a finalized order, email confirmation, and delivery of a receipt are covered in Chapter 12.
Updating quantities in the shopping cart is discussed in Chapter 11.
The full membership application process, amending of customer details, and logging in and out are covered in Chapter 10.
Implementing the complex ordering process that manages the inventory is discussed in Chapter 12.
Housekeeping, separating presentation from content, and searching are presented in Chapter 13.

1.5. Our Case Study