Conceptual Explanation

The critical aspect of the quiz show is the treatment of questions. A table in a database holds a set of questions. Each question has a category, the text of the question, an answer pattern, and a point or cost value. People inputting questions can specify anything as a category. To put it another way, there are no preset categories. Players are presented with a form (see Figure 15.1) in which they enter their names and pick a category. The drop-down list of categories is created dynamically from the distinct categories in the questions table.

Figure 15.1: Initial screen for quiz show.

The game attempts to find a question to ask the player in the chosen category. The game keeps a history of the questions asked a player, whether the player’s answer was correct or incorrect, and the date. The game has a policy concerning repeating questions. If a person has answered a question correctly, he or she will not be asked it again. If a player has been asked the question on a prior day and answered it incorrectly, the question is put into the pool of acceptable questions. The code selects a question at random from the pool of acceptable questions.

Figure 15.2 shows a screen with a question from the trivia category:

Figure 15.2: Question from trivia category.

If the player types in a correct answer, the response is as shown in Figure 15.3.

Figure 15.3: Response to correct answer.

This background is enough to design the database for the application.

Quiz Show Design

The quiz show requires three tables: questions, players, and history. The questions table is also called the question databank. A question record contains more than just the question. One field in a question record is the text of a question; another field is a string that is an answer pattern. Using patterns provides flexibility in evaluating answers. The entity-relationship diagram in Figure 15.4 was previously shown in Chapter 1, “Introduction.”

Figure 15.4: Entity-relationship diagram for quiz show.

The table that is called the question databank and the players table are not related directly. Instead, each of these tables has a relationship to the history table.

The implementation described in the code in detail supports two main processes: a player plays the game, and someone, called the editor, inserts questions, as shown in Figure 15.5. Two minor processes are showing all the scores and clearing the tables.

Figure 15.5: Process diagram for quiz show.

As was the case in the previous chapter, the level of detail shown in the process diagram can vary. Here, we have chosen to show the three tables of the database explicitly. The diagram shows all the processes, including the cleartables process used for debugging. The storyboard showing the relationships among the scripts is shown in Figure 15.6 for the PHP implementation.

Figure 15.6: Storyboard for quiz show.

The storyboard for the ASP implementation would be essentially the same. You can follow the procedure of the shopping cart example and create the tables using Access in stand-alone mode. You can also clear tables using Access. This would allow for more selective editing of the records. Our implementation puts the JavaScript for obtaining a random choice in a distinct file.

Temporary Tables and LEFT JOINS

The implementation makes use of a temporary table in the askquestion scripts to capture relevant past history on the player. MySQL provides an explicit mechanism for temporary tables. When using Access, the code erases everything in a table called past before populating it with records for the current player.

The askquestion scripts make use of a LEFT JOIN to identify questions that do not have the characteristic of having been asked on the current day or been answered correctly. Review the chapter on SQL if you need more examples of LEFT JOINs.

A question record in the questions table contains the answer as a regular expression pattern. The check is done using the PHP eregi function and the ASP/JavaScript string search method. This is a neat way to make the quiz performance more flexible and less obviously mechanical. However, it does require the person inputting questions to know how to design a regular expression.