Read an Excerpt

Chapter 5: MySQL

In the last two chapters, you saw how to create dynamic content with PHP and WMLScript. While the combination of PHP and WMLScript is flexible, most Wireless Web sites need an additional component: a database. With a database, Wireless Web sites can store data such as news stories, stock quotes, appointments, or user preferences. PHP provides a host of functions to interface to different databases, including the freely available MySQL database management system (DBMS).

In this chapter, you learn how to use MySQL with PHP to create Wireless Web sites that draw their content directly from a database. I begin by reviewing some basic concepts about databases. Then I show the Structured Query Language (SQL), used to access the majority of today's high-end database systems, MySQL. This chapter closes with the first fully functional version of MobileHelper, building on what you've learned over the previous four chapters.

Database Fundamentals

Almost everyone who has used a computer is familiar with the notion of a database. Many of the things you do on the Web—including logging into a personalized Web site, buying a product from an e-commerce site, or searching for your dream house—require databases, whether you're aware of the databases or not. Closer to home, your computer has database programs installed, such as a special-purpose database like a contact manager or the general-purpose database provided by the operating system to store an application's preferences and other data, such as the Microsoft Windows registry or the Apple Macintosh NetInfo database.

Most Web sites—including Wireless Web sites—make use of a database to store information. For example, an e-commerce site database would have information about the site's products and users, organized so that a user could quickly find and order a product of interest. MobileHelper's database stores information about volunteers, the tasks they carry out, and where they carry out those tasks.

Choosing a Database

As you start to design your application, one of the first questions you face is which database you should use. In fact, if you're not familiar with databases in general, you may even wonder why you need one to begin with. After all, PHP has a bevy of file functions, so if you like, you can store all of your server-side data in files on the server with these functions.

For most Wireless Web applications, however, databases are far superior to flat files for several reasons. They are simpler to use: when using a database you can think in terms of your data's representation, rather than files and file formats. They are reliable because somebody else (the folks that wrote the database) spent the time and effort validating the database code. They are more efficient, providing features you can only emulate with flat files. Most databases provide locks, ensuring that while part of the database is being updated, no one else can change the same data (or read it halfway through an update). More sophisticated databases also provide transactions, enabling you to group a sequence of changes so that either they all will happen, or none will happen, ensuring that no data is lost when the system goes down. In general, flat files within a PHP script are best used only in the simplest of applications such as bare-bones prototypes; any PHP script requiring data storage on the server is better off with a database.

The major factors that determine which database you choose are scale and cost. Of course, these factors are often directly related: most commercial databases like those from companies such as Oracle or Sybase scale well across orders of magnitude in performance and use, and you'll pay for their scalability in software license fees and hardware costs. For large-scale sites, you may have little choice because, with your content in a database, you need the performance of one of these products or it's possible the database won't keep up with the site's requests for data. Of course with these products, in return you get peace of mind and the support of a major company; depending on what you pay, you may be able to outsource your database development to your database vendor or one of their partners.

Recently, open source products like MySQL and PostgreSQL have become attractive alternatives to commercial databases for medium-scale Web development. MySQL, featured in this book, is a freely available server that's being successfully used with hundreds of Web sites. PostgreSQL has grown from an academic experiment to a successful high-performance database capable of taking on its commercial rivals.

All of these products have similar features. Most are relational databases supporting SQL, so you can apply fundamental concepts in database develop-ment as you move from one database to another. All are capable of storing different kinds of information, and let you interface your database to your Web server.

At the smallest scale, PHP even has support for a simple key-value database using successors to the Berkeley DBM library, a simple database system that's been around for over a decade. The DBM module gives you a simple way to store key-value pairs, such as login names and their passwords, or pages and how many times they've been hit. With a bit of time and effort, you can use this module to prototype systems that, in practice, would use a larger database.

Understanding Database Terminology

Let's begin by looking at how MobileHelper uses a database to store volunteer information. For each volunteer, MobileHelper must store three pieces of information:

• The user's name
• The user's Mobile Information Number (MIN), a unique number that the wireless network uses to identify the handset
• The user's Personal Identification Number (PIN), a simple password that the user uses when logging in to MobileHelper

Figure 5-1. The user table...

...Together, these items constitute a record. Figure 5-1 shows records for several volunteers that use MobileHelper; together, these people are called users. As you can see from the figure, these records form a table. In each row is a single record, while the col-umns contain the fields for a given record. For example, the name column contains each of the user's names, while the min field contains each of the user's MINs. Similarly, a second table—the location table—stores the details of each location Mobile-Helper volunteers might need to visit.

There's a third table, too, to store events. This table, the event table, has columns for each field of a record. Each row of this table contains an event's scheduled start and stop times, along with the times at which the event actually started and stopped, the event's name and a note about the event. But how does a row in the event table keep information about an event's location and owner?

MobileHelper could track this relationship in two ways. The simplest would be for each row in the event table to have all of the information about its owner and location. While simple, there's an obvious drawback: if you need to change something about either a user or a location, you're going to have to change it in multiple places, in every row containing the information for that user or location.

Instead, each event refers to a user using the user's MIN, and to a location using a unique identifier for that location. Thus, I link the event table to the user table using the user table's min field, and link the event and location tables using the id column of the location table. For example, if you want to see who is responsible for supporting the first Communications task, you read across that event's row in the event table until you find the min column, and then find the user with that MIN in the user column. Similarly, you can find where the First Night party is by reading across its row to the location column, and then looking for the location with that id in the location table. Figure 5-2 demonstrates this relationship.

Figure 5-2. Links between the event, user, and location tables...

...At this point, you're either thinking this is a clever trick or a stupid idea. It seems clever because each datum occurs in only one place, making it easy to find information and update the database. It also seems stupid because it appears unnecessarily complex. To help sort out this complexity, database designers use schema diagrams to show the columns in each table and the relationships between tables. Figure 5-3 shows a schema of the MobileHelper tables I've already discussed....