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....
