Developing a Web Database Application

data in MySQL databases and typing in PHP programs. Development
has to start with planning. Building the application pieces comes after plan­
ning. The development steps are

1. Develop a plan, listing the tasks that your application will perform.

2. Design the database needed to support your application tasks.

3. Build the MySQL database, based on the database design.

4. Write the PHP programs that perform the application tasks.

I discuss these steps in detail in this chapter.

Planning Your Web Database
Application

Before you ever put finger to keyboard to write a PHP program, you need to plan your Web database application. This is possibly the most important step in developing your application. It’s painful to discover, especially just after you finish the last program for your application, that you left something out

and have to start over from the beginning. It’s also hard on your computer (and your foot) when you take out your frustrations by drop-kicking it across the room.

Good planning prevents such painful backtracking. In addition, it keeps you focused on the functionality of your application, thus preventing you from writing pieces for the application that do really cool things but turn out to have no real purpose in the finished application. And if more than one person is working on your application, planning ensures that all the pieces will fit together in the end.

Identifying what you want from the application

The first step in the planning phase is to identify exactly why you’re develop­ ing your application and what you want from it. For example, your main pur­ pose might be to

Collect names and addresses from users so that you can develop a
customer list.

Deliver information about your products to users, as in a customer
catalog.

Sell products online.

Provide technical support to people who already own your product.

After you clearly identify the general purpose of your application, make a list of exactly what you want that application to do. For instance, if your goal is to develop a database of customer names and addresses for marketing pur­ poses, the application’s list of required tasks is fairly short:

Provide a form for customers to fill out.

Store the customer information in a database.

If your goal is to sell products online, the list is a little longer:

Provide information about your products to the customer.

Motivate the customer to buy the product.

Provide a way for the customer to order the product online.

Provide a method for the customer to pay for the product online.

Validate the payment so you know that you’ll actually get the money.

Send the order to whomever is responsible for filling it and sending the
product to the customer.

At this point in the planning process, the tasks that you want your application to perform are still pretty general. You can accomplish each of these tasks in many different ways. So now you need to examine the tasks closely and detail exactly how the application will accomplish them. For instance, if your goal is to sell products online, you might expand the previous list like this:

Provide information about products to the customer.

• Display a list of product categories. Each category is a link.

• When the customer clicks a category link, the list of products in that category is displayed. Each product name is a link.

• When a customer clicks a product link, the description of the prod­
uct is displayed.

Motivate the customer to buy the product.

• Provide well-written descriptions of the products that communi­
cate their obviously superior qualities.

• Use flattering pictures of the products.

• Make color product brochures available online.

• Offer quantity discounts.

Provide a way for customers to order the product online.

• Provide a button that customers can click to indicate their inten­
tion to buy the product.

• Provide a form that collects necessary information about the prod­
uct the customer is ordering, such as size, color, and so on.

• Compute and display the total cost for all items in the order.

• Compute and display the shipping costs.

• Compute and display the sales tax.

• Provide forms for customers to enter shipping and billing addresses.

Provide a method for customers to pay for the product online.

• Provide a button that customers can click to pay with a credit card.

• Display a form that collects customers’ credit card information.

Validate the payment so you know that you’ll actually get the money.

The usual method is to send the customer’s credit card information to a credit card processing service.

Send the order to whoever is responsible for filling it and sending the product to the customer.

E-mailing order information to the shipping department should do it.

At this point, you should have a pretty clear idea of what you want from your Web database application. However, this doesn’t mean that your goals can’t change. (In fact, your goals are very likely to change as you develop your
Web database application and discover new possibilities.) At the onset of the project, start with as comprehensive of a plan as possible to keep you focused so that you avoid running into a dead end or getting sidetracked.

Taking the user into consideration

Identifying what you want your Web database application to do is only one aspect of planning. You must also consider what your users will want from it. For example, say your goal is to gather a list of customer names and addresses for marketing purposes. Will customers be willing to give up that information?

Your application needs to fulfill a purpose for the users as well as for your­ self. Otherwise, they’ll just ignore it. Before users will be willing to give you their names and addresses, for example, they need to perceive that they will benefit in some way from giving you this information. Here are a few exam­ ples of why users might be willing to register their names and addresses at your site:

To receive a newsletter: To be perceived as valuable, the newsletter should cover an industry related to your products. It should offer news and spot trends — and not just serve as marketing material about your products.

To enter a sweepstakes for a nice prize: Who can turn down a chance to win an all-expense-paid vacation to Hawaii or a brand-new SUV?

To receive special discounts: For example, you can periodically e-mail special discount opportunities to customers.

To be notified about new products or product upgrades when they become available: For example, customers might be interested in being notified when a software update is available for downloading.

To get access to valuable information: For instance, you must register at The New York Times Web site in order to gain access to its articles online.

Now add the customer tasks to your list of tasks that you want the applica­ tion to perform. For example, consider this list of tasks that you identified for setting up an online retailer:

Provide a form for customers to fill out.

Store the customer information in a database.

If you take the customer’s viewpoint into account, the list expands a bit:

Present a description of the advantages customers receive by registering with the site.

Provide a form for customers to fill out.

Add customers’ e-mail addresses to the newsletter distribution list.

Store the customer information in a database.

After you have a list of tasks that you want and tasks that your users want, you have a plan for a Web application that is worth your time to develop and worth your users’ time to use.

Making the site easy to use

In addition to planning what your Web application is going to do, you need to consider how it is going to do it. Making your application easy to use is important: If customers can’t find your products, they aren’t going to buy them. And if customers can’t find the information that they need in a pretty short time, they will go look elsewhere. On the Web, customers can always easily go elsewhere.

Making your application easy to use is usability engineering. Web usability includes such issues as

Navigation: What is on your site and where it is located should be imme­
diately obvious to a user.

Graphics: Graphics make your site attractive, but graphic files can be slow to display.

Access: Some design decisions can make your application accessible or not accessible to users who have disabilities such as impaired vision.

Browsers: Different browsers (even different versions of the same browser) can display the same HTML (HyperText Markup Language) file differently.

Web usability is a large and important subject, and delving into the topic more deeply is beyond the scope of this book. But fear not, you can find lots of helpful information on Web usability on — you guessed it — the Web. Be sure to check out the Web sites of usability experts Jakob Nielsen (www.useit. com) and Jarod Spool (http://world.std.com/~uieweb/). Vincent
Flanders also has a fun site full of helpful information about Web design at WebPagesThatSuck.com. And books on the subject can be very helpful, such as Web Design For Dummies by Lisa Lopuck (Wiley).

Leaving room for expansion

One certainty about your Web application is that it will change over time. Down the line, you might think of new functions for it or just simply want to change something about it. Or maybe Web site software improves so that your Web application can do things that it couldn’t do when you first put it up. Whatever the reason, your Web site will change. When you plan your application, you need to keep future changes in mind.

You can design your application in steps, taking planned change into
account. You can develop a plan in which you build an application today that meets your most immediate needs and make it available as soon as it’s ready. Your plan can include adding functions to the application as quickly as you can develop them. For example, you can build a product catalog and publish it on your Web site as soon as it’s ready. You can then begin work on an
online ordering function for the Web site, which you will add when it’s ready.

You can’t necessarily foresee all the functions that you might want in your application in the future. For instance, you might design your travel Web site with sections for all possible destinations today, but the future could surprise you. Trips to Mars? Alpha Centauri? An alternate universe? Plan your applica­ tion with the flexibility needed to add functionality in the future.

Writing it down

Write your plan down. You will hear this often from me. I speak from the painful experience of not writing it down. When you develop your plan, it’s foremost in your mind and perfectly clear. But in a few short weeks, you will be astonished to discover that it has gone absolutely hazy while your atten­ tion was on other pressing issues. Or you want to make some changes in the application a year from now and won’t remember exactly how the application was designed. Or you’re working with a partner to develop an application
and you discover that your partner misunderstood your verbal explanation and developed functions for the application that don’t fit in your plan. You can avoid these types of problems by writing everything down.

Presenting the Two Running
Examples in This Book

In the next two sections, I introduce the two example Web database applica­ tions that I created for this book. I refer to these examples throughout the book to demonstrate aspects of application design and development.

Stuff for Sale

The first example is an online product catalog. You’re the owner of a pet store, and you want your catalog to provide customers with information about the pets that are for sale. Selling the pets online is not feasible although you’re toying with the idea of allowing customers to “reserve” pets
online — that is, before they come into the store to purchase them. Currently, the application is simply an online catalog. Customers can look through the catalog online and then come into the store to buy the pet. The information about all the pets is stored in a database, and customers can search the data­ base for information on specific pets or types of pets.

Here is your plan for this application:

Allow customers to select which pet they want to see information
about.

Offer two selection methods:

• Selecting from a list of links: Display a list of links that are pet cat­ egories (for example, dog, cat, dinosaur, and so on). When the cus­ tomer clicks a category link, a list of pets is displayed. Each pet in the list is a link to a description of the pet.

• Typing in search terms: Display a search form in which customers can type words that describe the type of pet they’re looking for. The application searches the database for matching words and displays the pet information for any pets that match the search
words. For example, a customer can type cat to see a list of all avail­
able cats. Each cat in the list is a link to a description of that cat.

Display a description of the pet when the customer clicks the link.

The description is stored in a database.

Members Only

The second example Web database application is related to the preceding pet store example. In addition to the online catalog, you also want to put up a section on your pet store Web site for members only. In order to access this area of the site, customers have to register — providing their names and addresses. In this Members Only section, customers can order pet food at a discount, find out about pets that are on order but haven’t arrived yet, and also gain access to articles with news and information about pets and pet care.

This is your plan for this application:

Display a description of what special features and information are available in the Members Only section.

Provide an area where customers can register for the Members Only section.

• Provide a link to the registration area.

• Display a form in the registration area where customers can type their registration information.

The form should include space for a user login name and password as well as the information that you want to collect.

• Validate the information that the user entered.

For example, verify that the ZIP code is the correct length, the e-mail address is in the correct format, and so on.

• Store the information in the database.

Provide a login section for customers who are already registered for the Members Only section.

• Display a login form that asks for the customer’s user name and password.

• Compare the user name and password that are entered with the user names and passwords in the database.

If no match is found, display an error message.

Display the Members Only Web page after the customer has success­
fully logged in.

Designing the Database

After you determine exactly what the Web database application is going to do (see the beginning part of this chapter if you haven’t done this yet), you’re ready to design the database that holds the information needed by the appli­ cation. Designing the database includes identifying the data that you need
and organizing the data in the way required by the database software.

Choosing the data

First, you must identify what information belongs in your database. Look at the list of tasks that you want the application to perform and determine what information you need to complete each of those tasks.

Here are a few examples:

An online catalog needs a database containing product information.

An online order application needs a database that can hold customer information and order information.

A travel Web site needs a database with information on destinations,
reservations, fares, schedules, and so on.

In many cases, your application might include a task that collects information from the user. You’ll have to balance your urge to collect all the potentially useful information that you can think of against your users’ reluctance to give out personal information — as well as their avoidance of forms that look too time-consuming. One compromise is to ask for some optional information.
The users who don’t mind can enter it, but users who object can leave it blank. Another possibility is to offer an incentive: The longer the form is, the stronger the incentive that you’ll need to motivate the user to fill out the form. A user might be willing to fill out a very short form to enter a sweep­ stakes that offers two sneak-preview movie tickets for a prize. But if the form is long and complicated, the prize needs to be more valuable, such as a free trip to California and a tour of a Hollywood movie studio.

In the first example application, your customers search the online catalog for information on pets that they might want to buy. You want customers to see information that will motivate them to buy a pet. The information that you want to have available in the database for the customer to see is

The name of the pet

For example, poodle, unicorn, and so on

A description of the pet

A picture of the pet

The cost of the pet

In the second example application, the Members Only section, you want to store information about registered members. The information that you want to store in the database is

Member name

Member address

Member phone number

Member fax number

Member e-mail address

Take the time to develop a comprehensive list of the information that you need to store in your database. Although you can change and add informa­ tion to your database after it’s developed, including the information from the beginning is easier. Also, if you add information to the database later — after it’s in use — the first users in the database will have incomplete information. For example, if you change your form so that it now asks for the user’s age, you won’t have the age for the people who have already filled out the form and are already in the database.

Organizing the data

MySQL is a RDBMS (Relational Database Management System), which means that the data is organized into tables. (See Chapter 1 for more on MySQL.) You can establish relationships between the tables in the database.

Organizing data in tables
RDBMS tables are organized like other tables that you’re used to — in rows and columns, as shown in Figure 3-1. The place where a particular row and column intersect, the individual cell, is a field.

The focus of each table is an object (a thing) that you want to store informa­
tion about. Here are some examples of objects:

Customers

Products

Companies

Animals

Cities

Rooms

Books

Computers

Shapes

Documents

Projects

Weeks

You create a table for each object. The table name should clearly identify the objects that it contains with a descriptive word or term. The name must be a character string with no spaces in it. The table name can contain letters, numbers, underscores (_), or dollar signs ($). It’s customary to name the table in the singular. Thus, a name for a table of customers might be Customer, and a table containing customer orders might be named CustomerOrder. Upper- and lowercase is significant on Linux/Unix but not
on Windows: CustomerOrder and Customerorder are the same to Windows —
but not to Linux or Unix.

In database talk, an object is an entity, and an entity has attributes. In the table, each row represents an entity, and the columns contain the attributes of each entity. For example, in a table of customers, each row contains infor­
mation for a single customer. Some of the attributes contained in the columns might be first name, last name, phone number, age, and so on.

Here are the steps for organizing your data into tables:

1. Name your database.

Assign a name to the database for your application. For instance, a data­ base containing information about households in a neighborhood might be named HouseholdDirectory.

2. Identify the objects.

Look at the list of information that you want to store in the database.
(If you haven’t done this yet, check out the section, “Choosing the data,” earlier in this chapter.) Analyze your list and identify the objects. For instance, the HouseholdDirectory database might need to store the following:

• Name of each family member

• Address of the house

• Phone number

• Age of each household member

• Favorite breakfast cereal of each household member

When you analyze this list carefully, you realize that you’re storing infor­ mation about two objects: the household and the household members. That is, the address and phone number are for the household in general, but the name, age, and favorite cereal are for a particular household member.

3. Define and name a table for each object.

For instance, the HouseholdDirectory database needs a table called
Household and a table called HouseholdMember.

4. Identify the attributes for each object.

Analyze your information list and identify the attributes that you need to store for each object. Break the information to be stored into its smallest reasonable pieces. For example, when storing the name of a person in a table, you can break down the name into first name and last name. Doing this enables you to sort by the last name, which would be more difficult
if the first and last name were stored together. In fact, you can even break down the name into first name, middle name, and last name, although not many applications need to use the middle name separately.

5. Define and name columns for each separate attribute that you identi­
fied in Step 4.

Give each column a name that clearly identifies the information in that column. The column names should be one word, with no spaces. For example, you might have columns named firstName and lastName or first_name and last_name.

Some words are reserved by MySQL or SQL for its own use and can’t be used as column names. The words are currently used in SQL statements or are reserved for future use. For example, ADD, ALL, AND, CREATE, DROP, GROUP, ORDER, RETURN, SELECT, SET, TABLE, USE, WHERE, and many, many more can’t be used as column names. For a complete list of reserved words, see the online MySQL manual at www.mysql.com/doc/en/ Reserved_words.html.

6. Identify the primary key.

Each row in a table needs a unique identifier. No two rows in a table should be exactly the same. When you design your table, you decide which column holds the unique identifier, called the primar y key. The primary key can be more than one column combined. In many cases, your object attributes will not have a unique identifier. For example, a customer table might not have a unique identifier because two cus­ tomers can have the same name. When there is no unique identifier column, you need to add a column specifically to be the primary key. Frequently, a column with a sequence number is used for this purpose. For example, in Figure 3-2, the primary key is the cust_id field because each customer has a unique ID number.

7. Define the defaults.

You can define a default that MySQL will assign to a field when no data is entered into the field. A default is not required but is often useful. For example, if your application stores an address that includes a country, you can specify US as the default. If the user does not type a country, US will be entered.

8. Identify columns with required data.

You can specify that certain columns are not allowed to be empty (also called NULL). For instance, the column containing your primary key can’t be empty. That means that MySQL will not create the row if no value is stored in the column. The value can be a blank space or an empty string (for example, “”), but some value must be stored in the column. You can set other columns, as well as the primary key, to be in error if they are empty.

Well-designed databases store each piece of information in only one place. Storing it in more than one place is inefficient and creates problems if infor­ mation needs to be changed. If you change information in one place but
forget to change it in another place, your database can have serious problems.

If you find that you’re storing the same data in several rows, you probably need to reorganize your tables. For example, suppose you’re storing data about books, including the publisher’s address. When you enter the data, you realize that you’re entering the same publisher’s address in many rows. A more efficient way to store this data would be to store the book informa­ tion in one table and the book publisher information in a separate table.
You can define two tables: Book and BookPublisher. In the Book table, you would have the columns title, author, pub_date, and price.
In the BookPublisher table, you would have columns such as name,
streetAddress, city, and so on.

Creating relationships between tables
Some tables in a database are related to one another. Most often, a row in one table is related to several rows in another table. A column is needed to con­ nect the related rows in different tables. In many cases, you include a column in one table to hold data that matches data in the primary key column of another table.

A common application that needs a database with two related tables is a cus­ tomer order application. For example, one table contains the customer infor­ mation, such as name, address, phone, and so on. Each customer can have from zero to many orders. You could store the order information in the table with the customer information, but a completely new row would be created each time that the customer placed an order, and each new row would con­ tain all the customer’s information. It would be much more efficient to store the orders in a separate table. The Order table would have a column that contains the primary key from a row in the Customer table so that the order is related to the correct row of the Customer table. The relationship is shown in the tables in Figures 3-2 and 3-3.

The Customer table in this example looks like Figure 3-2 (see the preceding section). Notice the unique cust_id for each customer.

The related Order table is shown in Figure 3-3. Notice that it has the same cust_id column that appears in the Customer table. In this way, the order information in the Order table is connected to the related customer’s name and phone number in the Customer table.

In this example, the columns that relate the Customer table and the Order table have the same name. They could have different names as long as the data in the columns is the same.

Designing the Sample Databases

In the following two sections, I design the two databases for the two example applications used in this book.

Pet Catalog design process

You want to display the following list of information when customers search your pet catalog:

The name of the pet

For example, poodle, unicorn, and so on

A description of the pet

A picture of the pet

The cost of the pet

In the Pet Catalog plan, a list of pet categories is displayed. This requires that each pet be classified into a pet category and that the pet category be stored in the database.

You design the PetCatalog database by following the steps presented in the
“Organizing data in tables” section, earlier in this chapter:

1. Name your database.

The database for the Pet Catalog is named PetCatalog.

2. Identify the objects.

The information list is

• The name of the pet (for example, poodle, unicorn, and so on)

• A description of the pet

• A picture of the pet

• The cost of the pet

• The category for the pet

All this information is about pets, so the only object for this list is Pet.

3. Define and name a table for each object.

The Pet Catalog application needs a table called Pet.

4. Identify the attributes for each object.

Now you look at the information in detail:

• Name of the pet: A single attribute — for example, poodle, uni­ corn, and so on. However, it seems likely that your pet shop might have more than one poodle for sale at a time. Therefore, your table needs a unique identifier to serve as the primary key.

• Pet identification number: A sequence number assigned to each pet when it’s added to the table. This number is the primary key.

• Description of the pet: Two attributes: the written description of the pet as it would appear in a printed catalog and the color of the pet.

• Picture of the pet: A path name to a graphic file containing a beau­
tiful picture of the pet.

• Cost of the pet: The dollar amount that the store is asking for the pet.

• Category for the pet: Two attributes: a category name that includes the pet — for example, dog, horse, dragon — and a description of the category.

It would be inefficient to include two types of information in the Pet
table:

• The category information includes a description of the category. Because each category can include several pets, including the category description in the Pet table would result in the same description appearing in several rows. It is more efficient to define Pet Category as an object with its own table.

• If the pet comes in several colors, all the pet information will be repeated in a separate row for each color. It is more efficient to define Pet Color as an object with its own table.

The added tables are named PetType and PetColor.

5. Define and name columns.

The Pet table has one row for each pet. The columns for the Pet
table are

• petID: Unique sequence number assigned to each pet.

• petName: Name of the pet.

• petType: The category name. This is the column that connects the pet to the correct row in the PetType table.

• petDescription: The description of the pet.

• price: The price of the pet.

• pix: The filename of a graphics file that contains a picture of the pet.

The PetType table has one row for each pet category. It has the follow­
ing columns:

• petType: The category name of a type of pet. This is the primary key for this table. Notice that the Pet table has a column with the same name. These columns link this table with the Pet table.

• typeDescription: The description of the pet type.

The PetColor table has one row for each pet color. It has the following columns:

• petName: The name of the pet. This is the column that connects the color row to the correct row in the Pet table.

• petColor: The color of the pet.

6. Identify the primary key.

• The primary key of the Pet table is petID.

• The primary key of the PetType table is petType.

• The primary key of the PetColor table is petName and petColor
together.

7. Define the defaults.

No defaults are defined for either table.

8. Identify columns with required data.

The following columns should never be allowed to be empty:

• petID

• petName

• petColor

• petType

These columns are the primary key columns. A row without these values should never be allowed in the tables.

Members Only design process

You create the following list of information that you want to store when cus­
tomers register for the Members Only section of your Web site:

Member name

Member address

Member phone number

Member fax number

Member e-mail address

In addition, you also would like to collect the date when the member regis­ tered and track how often the member actually goes into the Members Only section.

You design the Members Only database by following the steps presented in the “Organizing data in tables” section, earlier in this chapter:

1. Name your database.

The database for the Members Only section is named
MemberDirectory.

2. Identify the objects.

The information list is

• Member name

• Member address

• Member phone number

• Member fax number

• Member e-mail address

• Member registration date

• Member logins

All this information pertains to members, so the only object for this list is member.

3. Define and name a table for each object.

The MemberDirectory database needs a table called Member.

4. Identify the attributes for each object.

Look at the information list in detail:

• Member name: Two attributes: first name and last name.

• Member address: Four attributes: street address, city, state, and ZIP code. Currently, you only have pet stores in the United States, so you can assume the member address is an address in the U.S. mailing address format.

• Member phone number: One attribute.

• Member fax number: One attribute.

• Member e-mail address: One attribute.

• Member registration date: One attribute.

Several pieces of information are related to member logins:

• Logging into the Members Only section requires a login name and a password. These two items need to be stored in the database.

• The easiest way to keep track of member logins is to store the date/time when the user logged into the Members Only section.

Because each member can have many logins, many date/times for logins need to be stored. Therefore, rather than defining the login time as an attribute of the member, define login as an object, related to the
member, but requiring its own table.

The added table is named Login. The attribute of a login object is its login time (time includes date).

5. Define and name columns.

The Member table has one row for each member. The columns for the
Member table are

• loginName

Each login name must be unique. The programs in the application make sure that no two members ever have the same login name.

• password

• createDate

• firstName

• lastName

• street

• city

• state

• zip

• email

• phone

• fax

The Login table has one row for each login: that is, each time a member logs into the Members Only section. It has the following columns:

• loginName: The login name of the member who logged in. This is the column that links this table to the Member table. This is a unique value in the Member table but not a unique value in this table.

• loginTime: The date and time of login.

6. Identify the primary key.

• The primary key for the Member table is loginName.

• The primary key for the Login table is loginName and loginTime
together.

7. Define the defaults.

No defaults are defined for either table.

8. Identify columns with required data.

The following columns should never be allowed to be empty:

• loginName

• password

• loginTime

These columns are the primary key columns. A row without these values should never be allowed in the tables.

Types of Data

MySQL stores information in different formats based on the type of informa­ tion that you tell MySQL to expect. MySQL allows different types of data to be used in different ways. The main types of data are character, numerical, and date/time data.

Character data

The most common type of data is character data — data that is stored as strings of characters and can only be manipulated in strings. Most of the information that you store will be character data, such as customer name, address, phone number, pet description, and so on. Character data can be moved and printed. Two character strings can be put together (concate­ nated), a substring can be selected from a longer string, and one string can be substituted for another.

Character data can be stored in a fixed-length format or a variable-length format.

Fixed-length format: In this format, MySQL reserves a fixed space for the data. If the data is longer than the fixed length, only the characters that fit are stored — the remaining characters on the end are not stored. If the string is shorter than the fixed length, the extra spaces are left empty and wasted.

Variable-length format: In this format, MySQL stores the string in a field that is the same length as the string. You still specify a length for the string, but if the string is shorter than the specified length, MySQL only uses the space required rather than leaving the extra space empty. If the string is longer than the space specified, the extra characters are not stored.

If a character string length varies only a little, use the fixed-length format. For example, a length of 10 works for all ZIP codes, including those with the ZIP+4 number. If the ZIP code does not include the ZIP+4 number, only five spaces are left empty. However, if your character string can vary more than a few characters, use a variable-length format to save space. For example, your pet description might be Small bat or it might run to several lines of description. So it would be better to store this description in a variable-length format.

Numerical data

Another common type of data is numerical data — data that is stored as a number. Decimal numbers (for example, 10.5, 2.34567, 23456.7) can be stored as well as integers (for example, 1, 2, 248). When data is stored as a number,
it can be used in numerical operations, such as adding, subtracting, squaring, and so on. If data isn’t used for numerical operations, however, storing it as a character string is better because the programmer will be using it as a char­ acter string. No conversion is required. For example, you probably won’t
want to add the digits in the users’ phone numbers, so phone numbers should be stored as character strings.

MySQL stores positive and negative numbers, but you can tell MySQL to store only positive numbers. If your data will not be negative, store the data as unsigned (without using a + or – sign before the number). For example, a city population or the number of pages in a document can never be negative.

Date and time data

A third common type of data is date and time data. Data stored as a date can be displayed in a variety of date formats. It can also be used to determine the length of time between two dates or two times — or between a specific date or time and some arbitrary date or time.

Enumeration data

Sometimes data can have only a limited number of values. For example, the only possible values for a column might be yes or no. MySQL provides a data type called enumeration for use with this type of data. You tell MySQL what

values can be stored in the column (for example, yes, no), and MySQL will not store any other values in the column.

MySQL data type names

When you create a database, you tell MySQL what kind of data to expect in a particular column by using the MySQL names for data types. Table 3-1 shows the MySQL data types used most often in Web database applications.

MySQL allows many other data types, but they’re less frequently needed. For a description of all the available data types, see the documentation on the MySQL documentation at www.mysql.com/doc/C/o/Column_types.html.

Writing it down

Here’s my usual nagging: Write it down. You probably spent considerable time making the design decisions for your database. At this point, the decisions
are firmly fixed in your mind. You don’t believe that you can forget them. However, suppose that a crisis intervenes, and you don’t get back to this pro­ ject for two months. You will have to analyze your data and make all the design decisions again. You can avoid this by writing down the decisions now.

Document the organization of the tables, the column names, and all other design decisions. A good format is a document that describes each table in table format, with a row for each column and a column for each design deci­ sion. For example, your columns would be column name, data type, and description.

Taking a Look at the Sample
Database Designs

This section contains the database designs for the two example Web data­
base applications.

Stuff for Sale database tables

The database design for the Pet Catalog application includes three tables: Pet, PetType, and PetColor. Tables 3-2 through 3-4 show the organization of these tables. The table definition is not set in concrete; MySQL allows you to change tables pretty easily. For example, if you set the data type for a vari­ able to CHAR(20) and find that isn’t long enough, you can easily change the data type.

The database design is as follows:

Database name: PetCatalog

Table 3-2 Database Table 1: Pet

Variable Name Type Description

petID
INT(5)
Sequence number for pet (primary key)

petName
CHAR(25) Name of pet

petType
CHAR(15) Category of pet

petDescription
VARCHAR(255) Description of pet

price
DECIMAL(9,2) Price of pet

pix
CHAR(15) Path name to graphic file that contains picture of pet

Table 3-3 Database Table 2: PetType

Variable Name Type Description

petType
CHAR(15)
Name of pet category (primary key)

typeDescription
VARCHAR(255) Description of category

Table 3-4 Database Table 3: PetColor

Variable Name Type Description

petName
CHAR(25)
Name of pet (primary key 1)

petColor
CHAR(15) Color name (primary key 2)

Members Only database tables

The database design for the Members Only application includes two tables named Member and Login. Tables 3-5 and 3-6 document the organization of these tables. The table definition is not set in concrete; MySQL allows you to change tables pretty easily. If you set the data type for a variable to CHAR(25) and find that it isn’t long enough, it’s easy to change the data type.

The database design is as follows:

Database name: MemberDirectory

Table 3-5 Database Table 1: Member

Variable Name Type Description

loginName
VARCHAR(20)
User-specified login name (primary key)

password
CHAR(255) User-specified password

createDate
DATE Date member registered and created login account

lastName
VARCHAR(50) Member’s last name

firstName
VARCHAR(40) Member’s first name

street
VARCHAR(50) Member’s street address

city
VARCHAR(50) Member’s city

state
CHAR(2) Member’s state

zip
CHAR(10) Member’s ZIP code

email
VARCHAR(50) Member’s e-mail address

phone
CHAR(15) Member’s phone number

fax
CHAR(15) Member’s fax number

Table 3-6 Database Table 2: Login

Variable Name Type Description

loginName
CHAR(20)
Login name specified by user (primary key 1)

loginTime
DATETIME Date and time of login (primary key 2)

Developing the Application

After you develop a plan listing the tasks that your application is going to perform and you develop a database design, you’re ready to create your

application. First, you build the database; then, you write your PHP pro­ grams. You are moments away from a working Web database application. Well, perhaps that’s an exaggeration. But you are making progress.

Building the database

Building the database means turning the paper database design into a work­ ing database. Building the database is independent of the PHP programs that your application uses to interact with the database. The database can be accessed using programming languages other than PHP, such as Perl, C, or Java. The database stands on its own to hold the data.

You should build the database before writing the PHP programs. The PHP programs are written to move data in and out of the database, so you can’t develop and test them until the database is available.

The database design names the database and defines the tables that make up the database. To build the database, you communicate with MySQL by using the SQL language. You tell MySQL to create the database and to add tables to the database. You tell MySQL how to organize the data tables
and what format to use to store the data. Detailed instructions for building the database are provided in Chapter 4.

Writing the programs

Your programs perform the tasks for your Web database application. They create the display that the user sees in the browser window. They make your application interactive by accepting and processing information typed in the browser window by the user. They store information in the database and get information out of the database. The database is useless unless you can move data in and out of it.

The plan that you develop (as I discuss in the earlier sections in this chapter) outlines the programs that you need to write. In general, each task in your plan calls for a program. If your plan says that your application will display a form, you need a program that displays a form. If your plan says that your application will store the data from a form, you need a program that gets the data from the form and puts it in the database.

The PHP language was developed specifically to write interactive Web appli­ cations. It has the built-in functionality needed to make writing application programs as painless as possible. It has methods that were included in the language specifically to access data from forms. It has methods to put data into a MySQL database, and it has methods to get data from a MySQL data­ base. Detailed instructions for writing PHP programs are provided in Part III of this book.