From the Publisher

* An ideal introduction to the entire process of setting up a Web site using PHP (a scripting language), MySQL (a database management system), and Apache (a Web server)
* Programmers will be up and running in no time, whether they're using Linux or Windows servers
* Shows readers step by step how to create several Web sites that share common themes, enabling readers to use these examples in real-world projects
* Invaluable reading for even the experienced programmer whose current site has outgrown the traditional static structure and who is looking for a way to upgrade to a more efficient, user-friendly site
* The companion Web site features sample PHP scripts, as well as two fully functional working applications from the book.

Product Details

• Table of Contents
• Read a Sample Chapter

Table of Contents

Read a Sample Chapter

Beginning PHP, Apache, MySQL Web Development

John Wiley & Sons

Chapter One

In previous chapters, you created a very nice movie review Web site, but now the hand-holding is over, my friend. It's time for us to push you out of the nest. In this chapter, you will have the opportunity to create your own databases, and your own Web site.

We show you how to put together a comic book appreciation Web site, but you can certainly take the concepts we teach you and branch off to create that online auction or antique car site you have always dreamed about. We think the comic book appreciation Web site is cooler, but whatever. You do your thing.

In this chapter, we are going to cover the basics of creating your own database. Topics we discuss include:

* Planning the design of your database

* Database normalization

* Creating your database

* Creating and modifying tables in your database

* Building Web pages to access your data with PHP

Getting Started

You have a great idea for a site, right? Excellent. Open up your PHP editor and start coding! Believe it or not, many people approach the creation of a Web site in just this way. You may be tempted to yourself. It is not impossible to create a good site in this manner, but you are seriously handicapping your chances for greatness. Before you begin, you need a plan.

We're not going to tell you how to plan out an entire Web site, complete with charts and maps and business models. That's not what this book is all about. We are going to assume that you or somebody in your company has already done that by reading other great books on business models, attending seminars, reading great articles on the Web, and perhaps even hiring a business consultant who will help you with everything but building the site (because that's what we're going to teach you how to do).

So you have a great idea for a Web site and a plan. What do you suppose is the first step in creating a successful Web application using PHP, Apache, and MySQL? We'll give you a clue: Look at the title of this chapter.

We need to build the database this site will be based on. Don't worry-one of the great things about relational database design is that you don't have to create every table your site will use. You can start with a few, and build on it. As long as you follow the basic principles of good database design, your database should be quite scalable (that is, expandable to any size).

Nam et Ipsa Scientia Potestas Est!

That is, knowledge is power. Very profound words, coming from a man who wore a big, ruffled collar. Francis Bacon coined the phrase 400 years ago, and it still holds true today.

Of course, information is the foundation of knowledge. Knowledge consists of having information available to you and knowing what to do with it. Data is the building blocks-the facts and figures-that we piece together to create useful sets of information.

We must be sure to store this data in an easily accessible place and in a way that allows us to relate that data to any other data fairly easily. We also want to be able to modify or remove each piece of data quickly and efficiently, without disturbing other data. With proper database design, all of this is possible.

Sound like a daunting task? Don't worry. You see, we know a secret that has been kept hidden like the magician's code: Efficient database design is easy. No, really, we promise! You see, most of us computer geeks like to seem invaluable and very intelligent, and it sounds quite impressive to most interviewers to see on a resume "Designed a comprehensive Web site utilizing an RDBMS backend." When you are done with this chapter, you will be able to put that on your resume as well!

What Is a Relational Database?

Let's first cover a few basics of database design. The relational database is a concept first conceived by E. F. Codd of IBM, in 1970. It is a collection of data organized in tables that can be used to create, retrieve, delete, and update that data in many different ways. This can be done without having to reorganize the tables themselves, especially if the data is organized efficiently.

Take a look at the first table that follows. You can see that we have a very simple collection of data consisting of superheroes' aliases and real names, and their superhero ID. Nothing too amazing, of course, but notice how we relate it to the league table that follows it. Each superhero user has a League_ID that corresponds to an ID in the league table. Through this link, or relationship, you can see that Average Man is a member of the Dynamic Dudes League because the ID in the league table matches his League_ID in the superhero table.

At first glance, it may seem silly to create a table with one data column and an ID. Why not just put the league name in the superhero table? Imagine that you had a database of 10,000 superheroes, and 250 of them were in the Dynamic Dudes league. Now imagine that the Superhero Consortium decided to do a reorganization and "Dynamic Dudes" was changed to the "Incredible Team." If the league name were in the superhero table, you would have to edit 250 records to change the value. With the leagues in a separate, related table, you have to change the name in only one place.

That is the key to a relational database. And speaking of keys ...

Keys

A key is a column that identifies a row of data. In the superhero table, the first column is a key called "ID," as it is in the league table. In both cases, because they are unique, and in the table of the data they represent, they are called primary keys.

Most of the time, the primary key is a single column, but it is not uncommon to use more than one column to make up a primary key. The important distinction is that for each row, the primary key must be unique. Because of that characteristic, we can use the key to identify a specific row of data.

The primary key must contain the following characteristics:

* They cannot be empty (null).

* They will never change in value. Therefore, a primary key cannot contain information that might change, such as part of a last name (for example, smith807).

* They must be unique. In other words, no two rows can contain the same primary key.

The League_ID column in the superhero table is also a key. It matches the primary key of the league table, but it is in a different, or foreign, table. For this reason, it is called a foreign key. Although not a requirement, many programmers will give the foreign key a name that identifies what table it refers to ("League"), and some identifier that marks it as a key ("_ID"). This, along with the fact that keys are usually numeric, makes it fairly clear which column is the foreign key, if one exists in the table at all.

Keys do not have to be purely numeric. Other common values used as primary keys include Social Security numbers (which contain dashes), e-mail addresses, and ZIP Codes. Any value is valid as a primary key as long as it is guaranteed to be unique for each individual record in the table, and will not change over time.

Keys can enable your tables to be recursive. You might, for example, have a sidekick_ID column in the superhero table that we could link to the ID column in the same table. Sidekicks are heroes, too, you know ...

Relationships

In order to be related, the two tables need a column they can use to tie them together. The superhero and league tables are related to each other by the League_ID column in the superhero table, and the ID field in the league table. There is no explicit link created in the database; rather, you create the relationship by linking them with a SQL statement:

SELECT * FROM superhero s, league l WHERE s.League_ID = l.ID

In plain English, this statement tells the MySQL server to "select all records from the superhero table (call it 's') and the league table (call it 'l'), and link the two tables by the superhero League_ID column and the league ID column."

There are three types of relationships: one-to-one (1:1), one-to-many (1:M), and many-to-many (M:N). Our previous example is a one-to-many relationship. To figure out what type of relationship the tables have, ask yourself how many superheroes you can have in a league. The answer is more than one, or "many." How many leagues can a superhero belong to? The answer is "one." That is a one-to-many relationship. (Of course, in some universes, a superhero might belong to more than one league. But for our example, our superheroes exhibit league loyalty.)

One-to-many is the most common database relationship. Such 1:1 relationships don't happen often, and a many-to-many relationship is actually two one-to-many relationships joined together with a linking table. We explore that further later in the chapter.

Although they are more rare, here's an example of a one-to-one (1:1) relationship just so you know. Say you have a link between a company and its main office address. Only one company can have that exact address. In many applications, however, the main office address is included in the company table, so no relationship is needed. That's one of the great things about relational database design. If it works for your needs, then there is no "wrong" way to do it.

Referential Integrity

The concept of referential integrity may be a little lofty for a beginner book like this, but we think it is important to touch on this briefly. If your application has referential integrity, then when a record in a table refers to a record in another table (as the previous example did), the latter table will contain the corresponding record. If the record is missing, you have lost referential integrity.

In many cases, this is not disastrous. You might have an article written by an author whose name no longer exists in the author table. You still want to keep the article, so losing the referential integrity between authors and articles is okay. However, if you have an order in your database that can't be related to a customer because the customer was deleted, then you might be hard pressed to figure out where to send the product, and who to charge for it!

There are ways to enforce referential integrity in a MySQL database. However, these concepts and procedures are beyond the scope of this book. If you are interested in obtaining more information about referential integrity and foreign keys, visit mysql.com/doc/en/InnoDB_foreign_key_constraints.html.

Normalization

"Database normalization" is one of those big fancy terms that database administrators like to throw around, along with "Boyce-Codd Normal Form," "trivial functional dependency," and "Heisenberg compensator." They aren't really important terms to know to be able to design a good database, but we'll touch on normalization here.

For our purposes, we will simply define normalization as the process of modifying your database table structure so that dependencies make sense, and there is no redundant data. In a moment, we are going to go through this process. The best way to learn is to do!

Designing Your Database

It's time to design your application. This will be a relatively simple application, but it will help you learn important concepts such as normalization and expose you to various SQL commands.

Typically, this is where we would take you through a "Try It Out" section and tell you How It Works. When first designing a database, however, you do not need your computer. All you need is a pad of paper and a pencil. So, go get a pad of paper and a pencil. We'll wait.

Let's draw some tables.

The application you are going to design is a comic book character database. You will store a little bit of information about various characters, such as their alter ego's alias, their real names, the powers they possess, and the location of their lair. (Yes, that's right. I said "lair.")

Creating the First Table

Before we open MySQL and start mucking around with tables, we need to figure out how we are going to store all of the data. For simplicity, let's create one big table with all of the relevant data. You can draw it out on your piece of paper, or if you just can't stay away from your computer, use your favorite spreadsheet program. Copy the information you see in the table that follows.

We'll call that table "zero," because we're not even at the first step yet, and that data is just ugly (from a relational database standpoint).

The first thing you should notice is that there are multiple power columns. What would you do if you had to add a character with more than three powers? You would have to create a new column, and that's not good. Instead, let's combine all the powers into one column, and then separate each power into its own separate row. The other columns are duplicated in these additional rows (so, Clean Freak would have three rows instead of one, each row including a different power in the power column, but the name, address, and so on would remain identical among the three listings). This concept is called atomicity. Each value (cell) is atomic, or has only one item of data.

Let's also create a unique primary key for each character. Yes, you could use the character's name, but remember that a primary key should never be something that could change, and it must be unique. To handle this requirement we'll create an ID column.

Because in this pass we have multiple rows with the same character and the multiple rows are a result of the existence of multiple powers, we'll combine the ID column with the power column to create the primary key. When more than one column makes up the primary key, it is called a composite primary key. We'll mark the primary key columns with an asterisk (*) to highlight them for you.

Your table should look like the one that follows. We'll call this table "one" because it's our first pass at normalizing. (Yes, you are in the middle of a normalization process. We told you it wasn't difficult.)

Looking better, but there is still repeated data in there. In fact, the power column is what is causing the duplicate data. Let's separate out the power column and use a foreign key to relate it to the original table. We will also further normalize the power table so that we get rid of duplicate data. This is pass number "two." See the three tables that follow.

As you can see, we have much less repeated data than we did before. The powers have been separated out, and a link table has been created to link each power to each appropriate character.

It may seem a bit nitpicky, but you still have some duplicate data that you can take care of in the character table. It is quite possible for more than one character to be in the same lair, as is the case with Clean Freak and Soap Stud. Let's create a lair table, and link it to the character table with keys.

Continues...

---

Excerpted from Beginning PHP, Apache, MySQL Web Development by Michael K. Glass Yann Le Scouarnec Elizabeth Naramore Gary Mailer Jeremy Stolz Jason Gerner Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

---