Case Study: The Front-Page Panel

In this section, we show how to engineer a front-page panel-we call this the panel. The completed panel was shown in Figure 4-1. We use the techniques discussed so far in this chapter to present more attractive HTML <table> formatted results, to process multiple query results, and to customize the output based on the data retrieved. No significant new concepts are introduced in the case study.

The panel case study is a progressive development of a script to display the details of new wines. We show the following details in the panel:

  • Information about the three wines most recently added to the database, including the vintage year, the winery, the wine name, and the varieties

  • The review written by a wine writer

  • How much a bottle costs, how much a case of a dozen bottles costs, and any per-bottle discount users receive if they purchase a case

To achieve the outcome of a functional and attractive panel, you need to query the wine, winery, inventory, grape_variety, and wine_variety tables. You also need to use the structure of the HTML <table> environment to achieve distinct presentation of the three components-the details, the review, and the price-of each newly added wine. Last, you need some mathematics to calculate any savings for buying a case and present these savings to the user.

The panel component developed in this chapter is the basis of the front page of our online winestore. However, shopping cart features that are not discussed in detail here have been added to the production version shown in Figure 4-1. The finalized code that includes the shopping-cart functionality is discussed further in Chapter 5, and the completed code is presented in Chapter 11.

In engineering the panel, we use the following techniques:

  • Querying with the MySQL proprietary LIMIT modifier

  • Using SQL table aliases in querying

  • Using the HTML <table> environment as a presentation tool

  • Producing consolidated HTML output from multiple SQL queries

  • Presenting data based on calculations

  • Using MySQL functions-especially mysql_fetch_array( )-in practice

Script development is an iterative process of adding features. It is almost always easier to start with the skeleton of a component and progressively add functionality to achieve the final goal. The Web is particularly good for this: a click on the Refresh or Reload buttons in a web browser tests a script, without the need for compilation or processing of other components of the system. Moreover, PHP is good at reporting errors to the browser, and the HTML output can easily be viewed. In most browsers, right-clicking on the HTML document in the web browser window offers the option to view the HTML source.