Figure I-3: create_entry.php after submission
VIEWING INFORMATION IN THE DATABASE
This shouldnâ€™t be too tough. You already know that the file needs to include
dbconnect.php. Other than that, weâ€™ve already mentioned that databases store
information in tables. Each row of the table contains information on a specific
person who signed the guestbook, so to view all the information the page needs to
retrieve and print out every row of data. Hereâ€™s the script that can do it (you should
notice that itâ€™s pretty sparse):
<?php include(â€śdbconnect.phpâ€ť); ?>
<h2>View My Guest Book!!</h2>
$result = mysql_query(â€śselect * from guestbookâ€ť)
while ($row = mysql_fetch_array($result))
<h2><a href=â€ťsign.phpâ€ť>Sign My Guest Book!!</a></h2>
The query in the preceding code asks MySQL for every row in the database. Then
the script enters a loop. Each row in the database is loaded into the variable $row,
one row at a time. Rows continue to be accessed until none is left. At that time, the
script drops out of the while loop.
As it works through the loop, each column in that row is displayed. For example,
the following code prints out the email column for the row being accessed:
When run, the simple script at the beginning of this section prints out every row
in the database. Figure I-4 shows what the page will look like.
Figure I-4: view.php
And that about does it for our first application.
WHY YOU SHOULD NOT USE THIS APPLICATION
Again, we strongly recommend against putting the application discussed in this
introduction anywhere that the general public can get to it. If you want a guest-
book, use the application made exclusively for this book, which you find in Chapter
8. We call this application Guestbook 2003. But we cover a lot of ground and pre-
sent a lot of information before we get there.
We hope you enjoy the read!
Working with MySQL
Database Design with MySQL
The Structured Query Language for Creating
and Altering Tables
The Structured Query Language for Inserting,
Editing, and Selecting Data
Database Design with
IN THIS CHAPTER
â—† Identifying the problems that led to the creation of the relational database
â—† Learning the normalization process
â—† Examining advanced database concepts
THE BULK OF THIS CHAPTER is for those of you who have made it to the early twenty-
first century without working with relational databases. If youâ€™re a seasoned data-
base pro, having worked with Oracle, Sybase, or even something like Microsoft
Access or Paradox, you may want to skip this little lesson on database theory.
However, we do suggest that you look at the final section of this chapter, where we
discuss some of MySQLâ€™s weirder points. MySQLâ€™s implementation of SQL is incom-
plete, so it might not support something you want to use.
Why Use a Relational Database?
If youâ€™re still here and are ready to read with rapt attention about database theory
and the wonders of normalization, you probably donâ€™t know much about the his-
tory of the relational database. You may not even care. For that reason, Iâ€™ll keep this
very brief. Dr. E. F. Codd was a research scientist at IBM in the 1960s. A mathe-
matician by training, he was unhappy with the available models of data storage,
finding them all prone to error and redundancy. He worked on these problems and
then, in 1970, published a paper with the rousing title â€śA Relational Model of Data
for Large Shared Data Banks.â€ť In all honesty, nothing has been the same since.
A programmer named Larry Ellison read the paper and started work on software
that could put Dr. Coddâ€™s theories into practice. If youâ€™ve been a resident of this
planet during the past 20 years, you may know that Ellisonâ€™s product and company
took the name Oracle and that he is now one of the richest individuals in the world.
His earliest product was designed for huge mainframe systems. Responding to mar-
ket demands over the years, Oracle, and many other companies that have sprung up
since, have designed systems with a variety of features geared toward a variety of
4 Part I: Working with MySQL
operating systems. Now relational databases are so common that you can get one
that runs on a Palm Pilot.
To understand why Dr. Coddâ€™s theories have revolutionized the data-storage
world, itâ€™s best to have an idea of what the troubles are with other means of data
storage. Take the example of a simple address book â€” nothing too complex, just
something that stores names, addresses, phone numbers, emails, and the like. If you
have no persistent, running program to put this information into, the file system of
whatever OS youâ€™re running becomes the natural choice for storage.
For a simple address book, a delimited text file can be created to store the infor-
mation. If the first row serves as a header and commas are used as delimiters, the
text file might look something like this:
Name, Addr1, Addr2, City, State, Zip, Phone, Email
Jay Greenspan, 211 Some St, Apt 2, San Francisco, CA, 94107,
Brad Bulger, 411 Some St, Apt 6, San Francisco, CA, 94109,
John Doe, 444 Madison Ave, , New York, NY, 11234, 2125556666,
This isnâ€™t much to look at, but it is at least machine-readable. Using whatever
language you wish, you can write a script that opens this file and then parses the
information. You will probably want it in some sort of two-dimensional or associa-
tive array so that youâ€™ll have some flexibility in addressing each portion of each
line of the file. Any way you look at it, thereâ€™s going to be a fair amount of code to
write. If you want this information to be sortable and queryable by a variety of cri-
teria, youâ€™re going to have to write scripts that will, for instance, sort the list alpha-
betically by name or find all people within a certain area code. What a pain.
You might face another major problem if your data needs to be used across a
network by a variety of people. Presumably more than one person is going to need
to write information to this file. What happens if two people try to make changes at
once? For starters, itâ€™s quite possible that one person will overwrite anotherâ€™s
changes. To prevent this from happening, the programmer has to specify file lock-
ing if the file is in use. While this might work, itâ€™s kind of a pain in the neck for the
person who gets locked out. Obviously, the larger the system gets the more unman-
ageable this all becomes.
What you need is something more robust than the file system â€” a program or
daemon that stays in memory seems to be a good choice. Furthermore, youâ€™ll need
a data-storage system that reduces the amount of parsing and scripting that the
programmer needs to be concerned with. No need for anything too arcane here. A
plain, simple table like Table 1-1 should work just fine.
Now this is pretty convenient. Itâ€™s easy to look at and if a running program
accesses this table it should happen pretty quickly. What else might this program
do? First, it should be able to address one row at a time without affecting the oth-
ers. That way, if two or more people want to insert information into this table they
Chapter 1: Database Design with MySQL 5
wonâ€™t be tripping over each other. It would be even spiffier if the program provided
a simple and elegant way to extract information from a table such as this. There
should be a quick way to find all of the people from California that doesnâ€™t involve
parsing and sorting the file. Furthermore, this wondrous program should be able to
accept statements that describe what you want in a language very similar to
English. That way you can just say: â€śGive me all rows where the contents of the
state column equal CA.â€ť
Yes, this program is great, but it isnâ€™t enough. Major problems still need to be
dealt with. These problems, which weâ€™ll discuss in the following pages, are the same
ones that made Dr. Codd write his famous paper, and the same ones that made Larry
Ellison a billionaire.
Dr. Coddâ€™s goal was to have a model of information that was dependable. All of the
data-storage methods available to him had inherent problems. He referred to these
problems as anomalies. There are three types of anomalies: update, delete, and insert.
The update anomaly
Now that you can assume that a table structure can quickly and easily handle mul-
tiple requests, you need to see what happens when the information gets more com-
plex. Adding some more information to the previous table introduces some serious
problems (Table 1-2).
Table 1-2 is meant to store information for an entire office, not just a single per-
son. Since this company deals with other large companies, there will be times when
more than one contact will be at a single office location. For example, in Table 1-2
two contacts are present at 1121 43rd St. At first this may appear to be okay; you
can still get at all the information available relatively easily. The problem comes
when the BigCo Company decides to up and move to another address. In that case,
youâ€™d have to update the address for BigCo in two different rows. This may not
sound like such an onerous task, but consider the trouble if this table has 3,000
rows instead of 3 â€” or 300,000 for that matter. Someone, or some program, has to
make sure the data are changed in every appropriate place.
Another concern is the potential for error. Itâ€™s very possible that one of these
rows could be altered while the other one remained the same. Or, if changes are
keyed in one row at a time, itâ€™s likely that somebody will introduce a typo. Then
youâ€™d be left wondering if the correct address is 1121 or 1211.
The better way to handle this data is to take the company name and address and
put that information in its own table. This process of separating a table out into
multiple new tables is usually called decomposition. The two resulting tables will
resemble Table 1-3 and Table 1-4.
Now the information pertinent to BigCo is in its own table, Companies. If you
look at the next table (Table 1-4), Contacts, youâ€™ll see that weâ€™ve inserted another
TABLE 1-1 SIMPLE TABLE FOR DATA STORAGE
name addr1 addr2 city state zip phone email
Jay Greenspan 211 Some St. Apt. 2 San Francisco CA 94107 4155558888
Brad Bulger 411 Some St. Apt. 6 San Francisco CA 94109 4155552222
John Doe 444 Madison Ave. New York NY 11234 2125556666
Part I: Working with MySQL
TABLE 1-2 PROBLEMATIC TABLE STORAGE
id company_name company_address contact_name contact_title phone email
1 BigCo Company 1121 43rd St. Jay Greenspan Vice President 4155551212
2 BigCo Company 1121 43rd St. Brad Bulger President 4155552222
3 LittleCo Company 4444 44th St. John Doe Lackey 2125556666
TABLE 1-3 COMPANIES
company_id company_name company_address
1 BigCo Company 1121 43rd St.
2 LittleCo Company 4444 44th St.
TABLE 1-4 CONTACTS
contact_id company_id contact_name contact_title phone email
1 1 Jay Greenspan Vice President 4155551212
2 1 Brad Bulger President 4155552222