t seems that every Web site you go to nowadays has some kind of survey, feedback, or entertainment mechanism to evaluate site visitors and their goals. Surveys are a valuable tool that can help you build an effective community or refine your site content to better please your customers. In this article I'll show you how to build a simple, but highly extensible survey engine that you can use to achieve these ends.
The architecture of a survey engine is simple (see Figure 1). You need to define your surveys, you need to run your surveys, and you need to report on your surveys.
Defining the Survey
For the sample survey in this article I'll be using a MySQL database. You can use any database you like, but MySQL is simple, powerful, and integrates very cleanly with PHP, which will power our survey engine.
The survey uses three tables on the database.
The Questions. This is a simple table of text fields containing the actual questions and an associated ID.
The Options. This is a table containing the text of each option for each question. Each option is associated with a question ID.
The Answers. This table contains the answer to a specific question as provided by a visitor to your site. It contains the QuestionID, the answer that they specified (as the OptionID of the selected option), and their IP address. The IP address is stored to prevent multiple voting. The script to handle this is demonstrated later.
Figure 1. Building a Survey: A high-level architecture of the survey engine is shown.
Figure 2. The Table Definitions. The status of the data after just two responses, each having answered 'Red,' is shown.
To understand how it all works, I'll begin with a simple survey of one question'What is your Favorite Color?'and just three possible answers: 'Red,' 'Green,' and 'Blue.' Assume that two people have answered your survey, each selecting 'Red.' In your database:
- The Questions table would have a single record with QuestionID=1 and QuestionText='What is your Favorite Color?'.
- The Options table would have three records, all with QuestionID=1, and OptionIDs 1, 2, and 3 for 'Red', 'Green,' and 'Blue,' respectively.
- The Answers Table would have two records. Each would have the QuestionID set to '1'; each would have the AnswerValue set to '1' (as they are both for Red). The AnswerIP field for each would be set to the IP address of the person that answered the question.
shows the data above as it appears inside my MySQL database.
| Authors Note: The screen capture in Figure 2 uses MySQLCC, an incredibly useful tool that defines and fills tables in MySQL.