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 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 applications. It has the built-in functionality needed to make writing application programs as painless as possible. Methods were included in the language specifically to access data from forms, to put data into a MySQL database, and to get data from a MySQL database.

Building the database

Building the database means turning the paper database design into a working 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.

Date, time and enumeration 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.

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.

What is 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, and squaring. 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 character 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 is never 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. MySQL provides a specific type of numeric column called an auto-increment column. This type of column is automatically filled with a sequential number when no specific number is provided. For example, when a table row is added with 5 in the auto-increment column, the next row is automatically assigned 6 in the column, unless a different number is specified. Auto-increment columns are useful when unique numbers are needed, such as a product number or an order number.

What is character data?

The most common type of data is character data — data that is stored as strings of characters and can be manipulated only in strings. Most of the information that you store will be character data, such as customer name, address, phone, and pet description. Character data can be moved and printed. Two character strings can be put together (concatenated), 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 or 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 specify a string length, but if the string is shorter than the specified length, MySQL uses only 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 might run to several lines of description. It would be better to store this description in a variable-length format.

how to design member registration process for your web site?

You create the following list of information that you want to store when customers 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 would like to collect the date when the member registers and track how often the member 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:

• Name your database. The database for the Members Only section may be named MemberDirectory or other.
• 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.

• Define and name a table for each object. The MemberDirectory database needs a table called Member.
• 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 have pet stores only in the United States, so you can assume that 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 in to 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 and time when the user logged into the Members Only section. Because each member can have many logins, many dates and 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 (the time includes the date).

• Define and name the columns. The Member table has one row for each member. The columns for the

Member table are

• loginName
  
• 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 value is unique in the Member table but not unique in this table.
• loginTime: The date and time of login.
• Identify the primary key.
• The primary key for the Member table is loginName. Therefore, loginName must be unique.
• The primary key for the Login table is both loginName and loginTime together.
• Define the defaults. No defaults are defined for either table.
• 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.

How to create relationships between tables?

Some tables in a database are related. Most often, a row in one table is related to several rows in another table. A column is needed to connect 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 customer order application. For example, one table contains the customer information, such as name, address, and phone number. Each customer can have from zero to many orders. You could store the order information in the table with the customer information, but a new row would be created each time that the customer placed an order, and each new row would contain all the customer’s information. It would be much more efficient to store the orders in a separate table, named perhaps CustomerOrder. (You can’t name the table Order because that is a reserved word.) The CustomerOrder 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.