Chapter 4. Querying Web Databases

This chapter is the first of six that introduce practical web database application development. In Chapter 1, we introduced our case-study application, Hugh and Dave's Online Wines. We use the winestore here to illustrate the basic principles and practice of building commonly used web database components.

In this chapter, we introduce the basics of connecting to the MySQL DBMS with PHP. We detail the key MySQL functions used to connect, query databases, and retrieve result sets, and we present the five-step process for dynamically serving data from a database. Queries that are driven by user input into an HTML <form> or through clicking on hypertext links are the subject of Chapter 5.

We introduce the following techniques in this chapter:

• Using the five-step web database querying approach to develop database-driven queries

• Coding a simple solution to produce HTML <pre> preformatted text

• Using the MySQL library functions for querying databases

• Handling MySQL DBMS errors

• Producing formatted output with the HTML <table> environment

• Using include files to modularize database code

• Adding multiple queries to a script and consolidating the results into one HTML presentation environment

• Performing simple calculations on database data

• Developing basic database-driven scripts incrementally and producing modular code encapsulated in functions

Our case study in this chapter is the front-page panel from the winestore that shows customers the Hot New Wines available at the winestore. The front page of the winestore is shown in Figure 4-1, and the panel is the section of the page that contains the list of the three newest wines that have been added to the database and reviewed by a wine expert.

Figure 4-1. The front page of the winestore, showing the front page panel

We begin by introducing the basic principles of web database querying. Our first examples use a simple approach to presenting result sets using the HTML <pre> preformatted text tag. We then build on this approach and introduce result presentation with the <table> environment. The panel itself is a complex case study, and we follow its development as natural join queries are introduced, conditional presentation of results included, and the HTML <table> environment used for more attractive presentation. We focus on iterative development, starting simply and progressively adding new functionality. The complete code for the front page of the winestore application is presented in Chapter 11.

For completeness, we conclude this chapter with a brief overview of how other DBMSs can be accessed and manipulated with PHP.