. 47
( 132 .)


mary and foreign keys, appear in Part I of this book. This means that your SQL
queries will include joins.

Determining the Scope and Goals
of the Application
The problem with the Web is that it can be mighty impersonal. You surf around,
look at pages, search for information, and have advertisements try to sell you
things. But you don™t see much evidence of other human beings. A survey on a site
gives surfers a clue that other people have been by and that it™s possible to leave a
mark that others will see later.
A survey application can be ultra-simple. If you want only to gather responses
to a single question and return basic statistical information on the responses (how
many votes for choice A, B, and so on), you don™t need a whole lot of code (or a
chapter explaining it). A single table to store answers would do the trick. The ques-
tion can even be hard-coded into the HTML for the Web site. But that would not
make for very interesting learning experience, would it?
262 Part III: Simple Applications

It gets more interesting if there can be any number of questions. Instead of just
one, this application will allow for two, five, ten, or more ” whatever you want.
This survey will also record demographic information (such as age and country of
origin) and enable sorting on the basis of this information. We also decided to add
the ability to pick a winner from those who filled out the personal information. This
might encourage people to give real rather than fictitious answers.
There is one more wrinkle to discuss here. It™s really not possible to create a sur-
vey application that records perfect data. Even if you go to extreme lengths, there
will always be an opportunity for the shrewd and persistent to submit multiple
answers as long as you allow anonymous access to your survey. But in all likeli-
hood your survey will not have to pass muster with the Federal Elections
Commission. A small step to weed out those ruining your survey should do the
trick, and you will see one way to accomplish this step later on in the chapter.

Necessary pages
Entering and viewing survey information will require three pages. The first is where
the questions will be presented and where the user will enter name, address, and
geographic and demographic information. The second page will show the basic
survey results. The third will give a detailed breakdown. Figures 9-1, 9-2, and 9-3
show these respective pages.

Figure 9-1: Page for filling out survey
Chapter 9: Survey 263

Figure 9-2: Basic survey results

Figure 9-3: Detailed survey results

This application, like all others, requires some administrative tools. For starters,
you will need to be able to add, delete, and edit questions. Additionally, another
page selects a winner at random from the database. Figures 9-4 and 9-5 show the
administrative page and the select winner page, respectively.
264 Part III: Simple Applications

Figure 9-4: Survey-administration page

Figure 9-5: Select winner page
Chapter 9: Survey 265

Winners will be notified via email and sent a URL to claim their prize. The page
they get when they open the URL will look like the one in Figure 9-6. Once there,
winners will need to confirm who they are, just so you have an extra level of security.

Figure 9-6: Claim prize page

Preventive measures
In the previous chapter we discussed methods for removing junk information that
people may attempt to send through the form elements. We will continue to use
these functions here. This application will also do some email address validation.
This application will provide you with a simple means of blocking some people
from entering information at your site. It™s nothing terribly sophisticated; a savvy
Internet user would be able to work around it in a minute. Using the form shown in
Figure 9-6 you will be able to enter a domain of origin that will be blocked from
the site. All users who enter data will have their $_SERVER[˜REMOTE_HOST™] and
$_SERVER[˜REMOTE_ADDR™] values checked against a table in the database (they
can turn the presentation of these values off, though). If their host is found, the
application will refuse access to the user. Again, this isn™t perfect. If you really have
sensitive information and need an effective way to block users, you should work
with some sort of login scheme. This is just an example of what you could do with
a database and HTTP header information.
266 Part III: Simple Applications

Email Addresses: To Validate or Not to Validate?
Verifying that an email is in the proper format takes a lot of work. To check a single
address thoroughly takes multiple regular expressions. Given that regular expressions
are fairly slow, you may be wondering if it is even worth running a script like that,
especially if you are running a site with very heavy traffic. You will need to decide
that for yourself, based on the amount of traffic you get and the strength of your
server hardware. Do you need to make sure emails are perfect, or will a simpler, less
robust form of validation be good enough? Even if you make sure the address is in the
proper format, that doesn™t tell you if the address is attached to an actual mailbox. If
you do need to validate your email addresses as much as possible, check around on
the Web. Online code archives contain software that will check an address™s validity
and do rudimentary network lookups to validate domain names and the like.

You™ll also need to take some steps to make sure that the wrong people won™t be
claiming prizes. You™ll need to make sure that the people coming to claim prizes are
who they say they are.

Designing the Database
This survey application allows for any number of multiple-choice questions. Each
question can have any number of answers. To create this relationship you™ll need
two tables, one named questions and one named answers, that have a one-to-many
relationship. (Each question (1) can have any (n) number of answers.)
User information is best represented by multiple tables as well, since each user
will answer multiple questions. A table named users will store name and address
information, while a table named responses will tie together a user and an answer.
(User A chose Answer Z to Question 2, for example.) The weekly contest winners will
be represented as a link to the appropriate user record in a table named winners,
where users™ names are listed along with the week in which they won. Two other
tables, states and age_ranges, are used to help us group responses together in dif-
ferent ways.
Finally, two administrative tables have no relationships to the other tables in the
database. The admin table holds usernames and passwords for administrators, and
blocked_domains records domains that have been blocked.
Because we have multiple tables that are linked together, in this database we can
begin to take advantage of the features of the InnoDB table type. One of those is the
ability to create foreign key constraints. (MySQL has always enabled you to declare
these, but they have no meaning for other table formats.) A foreign-key table con-
straint spells out the relationship between the table being defined and another table
Chapter 9: Survey 267

in the database, such as the relationship in this example between the answers and
questions tables. A nice benefit of the foreign key table is the ON DELETE part of the
constraint definition. If you set this to ON DELETE CASCADE, deleting a record in the
master table will automatically cause all dependent records in the child table to be
deleted as well. Therefore, if you delete a question, all of its answers go away too.
Which means that much less code to write (always a good thing).
Figure 9-7 shows a visual representation of the structure of the database. The
create statements for making these tables are shown in Listing 9-1. Note that these
table definitions were copied from the mysqldump utility. If you™re not aware of
mysqldump, or the other mysql utilities, make sure to read Appendix D.

questions users state
question_id state
question statename
answers country
answer_id age_ranges
user_id winners

survey admin

Figure 9-7: Survey database schema
268 Part III: Simple Applications

Listing 9-1: Create statements for survey
-- Table structure for table ˜admin™

username varchar(50) NOT NULL default ˜™,
password varchar(255) NOT NULL default ˜™,
PRIMARY KEY (username)
) TYPE=InnoDB;

-- Table structure for table ˜age_ranges™

CREATE TABLE age_ranges (
min_age int(11) NOT NULL default ˜0™,
max_age int(11) NOT NULL default ˜0™,
age_range varchar(10) default NULL,
PRIMARY KEY (min_age,max_age)
) TYPE=InnoDB;

-- Table structure for table ˜answers™

CREATE TABLE answers (
answer_id int(11) NOT NULL auto_increment,
question_id int(11) NOT NULL default ˜0™,
answer text NOT NULL,
PRIMARY KEY (answer_id),
KEY question_id (question_id,answer_id),
FOREIGN KEY (`question_id`) REFERENCES `survey.questions`
(`question_id`) ON DELETE CASCADE
) TYPE=InnoDB;

-- Table structure for table ˜blocked_domains™

CREATE TABLE blocked_domains (
domain varchar(64) NOT NULL default ˜™,
block_by varchar(50) default NULL,
block_dt datetime NOT NULL default ˜0000-00-00 00:00:00™,
Chapter 9: Survey 269

release_dt datetime default NULL,
notes text,
modify_dt timestamp(14) NOT NULL,
PRIMARY KEY (domain),
KEY block_by (block_by),
FOREIGN KEY (`block_by`) REFERENCES `survey.admin` (`username`) ON
) TYPE=InnoDB;

-- Table structure for table ˜questions™

CREATE TABLE questions (
question_id int(11) NOT NULL auto_increment,
question text NOT NULL,
PRIMARY KEY (question_id)
) TYPE=InnoDB;

-- Table structure for table ˜responses™


. 47
( 132 .)