Examples
On the CD The coding for these two implementations is similar to what you studied in previous chapters. Try to do some of the coding yourself. Statement-by-statement explanations are provided. Two other exercises you can attempt is to look at the column with the code and see if you can explain it yourself, or look at the explanation and see if you can write the code. One cautionary note: copy-and-paste is a good technique, but you must be careful when going back and forth between ASP and PHP that you remove or insert the dollar signs. You also need to extract form data explicitly in the case of ASP.
You will find the code for the quiz show applications on the CD-ROM in the folder named Chapter15code.
The PHP and MySQL Implementation
The 
opendbq.php script, shown in Table 15.1, establishes the connection to the database and is included, using the PHP require function, in all of the other scripts. The example provided here, as in the previous case, has our values for user ID and password. You will need to insert your values.
| <?php | Start PHP |
| global $DBname, $link; | Set these values to be global so they can be used elsewhere, including in functions |
| $host="localhost"; | Sets the host |
| $user="curley"; | Sets the user |
| $password="12345"; | Sets the password |
| $DBname="quiz"; | Sets the database name |
| $link=mysql_connect($host,$user, $password); | Establish the connection |
| mysql_select_db($DBname,$link); | Set the database used. Later code may set this, also |
| ?> | Close PHP |
The createquiztables.php script, shown in Table 15.2, establishes all three tables for the quiz show application.
| <?php | Start PHP |
| function createtable($tname,$fields) { | Function header for createtable. It will be called for each table |
| global $DBname, $link; | Uses the global values |
| $query="CREATE TABLE ".$tname." (".$fields.")"; | Define query using values passed in as parameters |
| if (mysql_db_query($DBname,$query, $link)) { | Invoke the query and do an if test if it worked okay |
| print ("The table, $tname, was created successfully.<br>\n"); | Print success message |
| } | End clause |
| else { | Start else for unsuccessful query |
| print ("The table, $tname, was not created. <br>\n"); | Print message of no success (for example, table already created or bad user ID and password) |
| } | End clause |
| } | End function definition |
| ?> | Stop PHP |
| <html><head><title>Creating quiz tables </title></head><body> | HTML |
| <?php | Start PHP |
| require("opendbq.php"); | Include connecting code |
| $tname = "questions";"questions" | Set variable for table name to be |
| $fields = "question_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, question char(50) NOT NULL, answerpattern char(50) NOT NULL, category char(30) NOT NULL, value INT NOT NULL"; | Set variable for field definitions |
| createtable($tname, $fields); | Call function |
| $tname = "players"; | On to the next table: set variable to "players" |
| $fields ="player_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name char(50) NOT NULL, lastplayed DATE not null, score INT NOT NULL"; | Set field variable |
| createtable($tname,$fields); | Invoke function |
| $tname = "history";"history" | On to last table: set variable to |
| $fields="question_id INT unsigned not null, player_id INT unsigned not null, whenplayed DATE not null, correct TINYINT not null, primary key(question_id, player_id) "; | Set field variable. Notice that correct (since it can be right or wrong) is set as a TINYINT |
| createtable($tname,$fields); | Call function |
| mysql_close($link); | Close link |
| ?> | Stop PHP |
| </body></html> | Closing HTML tags |
The inputquestion.php script, shown in Table 15.3, allows someone to enter new questions into the database. This script is a handler for a form as well as a presenter of a form, and has the usual check on whether or not the submitted variable has been set.
| <html><head><title>Adding questions to quiz db</title></head><body> | Usual HTML |
| <?php | Start PHP |
| require("opendbq.php"); | Include connecting code |
| $tname = "questions"; | Set table name |
| if (@($submitted)) { | If test to see if form has been submitted. The @ prevents a warning message |
| $question = trim($question); | Remove white space (for example, blanks) from the question form input using the trim function |
| $ans = trim($ans); | Trim the answer |
| $ans = AddSlashes($ans); $question = AddSlashes($question); | Add in slashes to “escape” any special characters |
| $cat = trim($cat); | Trim category |
| $value= trim($value); | Trim value. (It could be appropriate to check that value is a number. Alternatively, see the Exercises) |
| $query = "INSERT INTO $tname values ('0','".$question."', '".$ans."', '".$cat."', '".$value."')"; | Create the query. Note that the zero value is used as a placeholder. MySQL will insert the next number for the question ID |
| $result = mysql_db_query($DBname,$query, $link); | Invoke the query |
| if ($result) { | If test to check if the question was added successfully |
| print("The question was successfully added.<br>\n");} | Print positive message |
| else {print (“The question was NOT successfully added. <br>\n”);} | Print negative message |
| $submitted = FALSE; | Set submitted to false to re-show the form |
| mysql_close($link); | Close the link |
| print ("<a href=\"inputquestions.php\">Submit another question </a><br>"); | Print an a tag to form a hyperlink allowing the user/question editor to submit another question |
| } //ends if submitted | Ends the clause for handling the form |
| else { | Starts the clause for presenting a form |
| print ("<h1>Add a question to the databank of questions <br>\n </h1> "); | Print a heading |
| print ("<form action=\"inputquestions. php\" method=post>\n"); | Print form tag |
| print ("Text of question <input type=text name=\"question\" size=50><br>\n"); | Print input tag for question text |
| print ("Answer pattern <input type=text name=\"ans\" size=50><br>\n"); | Print input tag for answer pattern |
| print ("Category <input type=text name=\"cat\" size=30><br>\n"); | Print input tag for category |
| print ("Point Value <input type=text name=\"value\" size=6><br>\n"); | Print input tag for point value |
| print ("<input type=hidden name=\"submitted\" value=\"True\"><br>\n"); | Print the hidden form input tag holding the submitted variable |
| print ("<input type=submit name=\"submit\" value=\"Submit question!\"><br>\n"); | Print the tag for the Submit button |
| print ("</form><br>\n"); | Print the form close |
| } | End the clause for displaying the form |
| ?> | End PHP |
| </body></html> | End HTML |
Now, after programming these preliminary scripts, it is time to start building the scripts implementing the asking of questions. The first one is called choosecategory.php. The code with explanation is provided in Table 15.4. The player enters his or her name and then chooses a category from the drop-down list. Because the handling of this form is fairly complex, it is implemented in a separate script as opposed to making this script both present a form and handle the input to the form.
| <html><head><title>Sign in and select category </title></head><body> | Starting HTML |
| <h1> Welcome to the Quiz </h1><br> | HTML heading |
| <h3> Sign in and select a category for your question </h3> | HTML |
| <form action="askquestion.php" method=post> | Form. Action (handler) is the |
| <p>Name <input type=text name='player' size=30 | Name input tag |
| <?php | Start PHP |
| if (@$currentplayer) { | Check if there is a cookie set for holding the currentplayer |
| print ("VALUE=$currentplayer><br>");} | If so, print it out as the default value of the name input tag |
| else { print ("><br>");} | Else (no cookie) just print out the end of the input tag. It will appear blank |
| ?> | Close PHP |
| <p>Category <select name='pickedcategory'> | Start the select element that will produce the pull-down list of category names |
| <?php | Start PHP |
| require ("opendbq.php"); | Bring in code to make connection to the database |
| $query="SELECT DISTINCT category FROM questions"; | Create a query to get all the different category value; that is, the set of distinct values |
| $categories = mysql_db_query($DBname, $query, $link); | Invoke the query |
| while ($row=mysql_fetch_array($categories)) | While loop: this will iterate through the recordset named $categories set by invoking the query |
| { $cat=$row['category']; | Extract the category (name) |
| print ("<option value=$cat>$cat</option><br>\n"); | Print it as an option |
| } | End the while loop |
| mysql_close($link); | Close the connection |
| print ("</select>"); | Print out the close of the select element |
| print ("<input type=submit name=submit value=\"Choose!\"><br>\n"); | Print out the tag to produce the button labeled Choose! |
| print ("</form>"); | Print out the end of the form |
| ?> | Close PHP |
| <a href="showscores.php">Show scores of players. </a> | An a tag with the option to show players’ scores |
| </body></html> | Closing HTML |
The askquestion.php script, shown in Table 15.5, is invoked as the handler of the form presented by choosecategory.php. That is, it is the file specified by the action attribute in the form tag. The askquestion.php script is the most complex one in this application. It sets a cookie using the player’s name passed from the form. Notice that this is done before any HTML is output. It determines an acceptable question, making use of the past history of the player, a temporary table, and the LEFT JOIN construct. The temporary table is made using an SQL CREATE statement that incorporates a SELECT statement.
| <?php | Start PHP |
| setcookie("currentplayer",$player); | Set a cookie for as long as the browser is open. This could be the cookie already set |
| ?> | End PHP |
| <html><head><title>Fetch appropriate question from category and ask </title></head><body> | Standard HTML |
| <?php | Start PHP |
| $today = Date("Y-m-d"); | Set $today to be today in year-month-day format |
| print ("Today is $today <br>\n"); | Print out date |
| require("opendbq.php"); | Include connecting code |
| $query="SELECT name, player_id, lastplayed, score from players where name='".$player."'"; | Define query to get information on player. Note: this could be used to distinguish players with the same name. However, that is not done in this script. All players with the same name are batched together |
| $result=mysql_db_query($DBname, $query, $link); | Invoke query |
| if (mysql_num_rows($result)==0) { | If there is no such player |
| $query2 = "INSERT INTO players values ('0','".$player."','".$today."','0')"; | Create a query string to add a new player record |
| mysql_db_query($DBname,$query2,$link); | Invoke the query |
| $query="SELECT player_id from players where name='".$player."'"; | Create query to get the player_id for the record just created |
| $result = mysql_db_query($DBname, $query, $link); | Invoke this query |
| } | End clause for no such player |
| $player_id = mysql_result($result,0, "player_id"); // take first 0th record | At this point, no matter what, $result holds a player record. Extract the player_id |
| $query="CREATE temporary TABLE past (item_id INT)"; | Start to build a query string to create a temporary table named past |
| $query=$query . " SELECT question_id FROM history where (player_id='".$player_id; | Continue building query |
| $query=$query . "' AND (whenplayed='".$today."' OR correct))"; | Finish definition of query |
| $result=mysql_db_query($DBname,$query, $link); | Invoke the query |
| $query="SELECT * FROM past"; | Build a query to get a recordset from past |
| $result = mysql_db_query($DBname,$query, $link); | Invoke the query |
| $Num_past = mysql_num_rows($result); | Find out the number of rows in $result (also in past) |
| print ("Number of past relevant actions is $Num_past <br>"); | This is a debugging statement that could be removed |
| if ($Num_past>0) { | If there were relevant past statements |
| $sel = "SELECT questions. question_id, question, answerpattern, value from questions"; | Start to build a query (held in variable $sel) that contains questions that are not in past (that is, not allowed to be asked) |
| $sel =$sel . " LEFT JOIN past ON | Continue definition |
| questions.question_id = past.question_id | |
| WHERE "; | |
| $sel = $sel . " category='" . $pickedcategory . "' AND past.question_id IS NULL";} | Complete definition |
| else {$sel="SELECT question_id, question, answerpattern, value from questions "; | Else clause: if there were no relevant past questions, then start to build a simpler querystring in $sel |
| $sel= $sel . " WHERE category= '" . $pickedcategory. "'"; | Finish definition of $sel |
| } | End the else clause |
| $result=mysql_db_query($DBname, $sel, $link); | Invoke the query |
| $NoR=mysql_num_rows($result); | Find out number of rows |
| print ("<br>Number of appropriate questions is ".$NoR); | This could be considered a debugging statement to remove |
| if ($NoR==0) { | If there are no appropriate questions |
| print ("<br> No appropriate questions in databank. Pick new category. <br>\n");} | …print out that message |
| else | Else (there was at least one appropriate question) |
| { if ($NoR > 1) { | If there is more than one appropriate question, need to make a random choice |
| srand ((double) microtime()*1000000); | Suggested way to seed pseudo-random number generator. |
| $choice=rand(0,$NoR-1); | $choice is a random number from 0 to one less than $NoR |
| } | End clause of more than one acceptable question |
| else { | Else: (only one acceptable question) |
| $choice=0;} | … set $choice to 0 |
| $question_id = mysql_result($result, $choice,"question_id"); | The $choice is used to extract question data from the $result recordset. Extract the question_id |
| $question = mysql_result($result, $choice,"question"); | Extract the question text |
| $ans = mysql_result($result, $choice,"answerpattern"); | Extract the answerpattern |
| $value = mysql_result($result, $choice,"value"); | Extract the value |
| print ("<h3><p> The question is <br> $question ??? <br>\n"); | Print out the question |
| print ("<form action=\"checkanswer. php\" Method=post><br>\n"); | Print out start of a form. The handler will be |
| print ("<input type=text name=player_ans size=50><br>\n"); | Print out the input tag for the player to enter his or her answer |
| print ("<input type=submit name=submit value='Submit answer'><br>\n"); | Print out the tag to produce theSubmit button |
| print ("<input type=hidden name=ans value='$ans'><br>\n"); | Print out as a hidden value the answer pattern |
| print ("<input type=hidden name=value value='$value'><br>\n"); | Print out as a hidden value the point value |
| print ("<input type=hidden name=question_id value='$question_id'><br>\n"); | Print out as a hidden value the question_id |
| print ("<input type=hidden name=player_id value='$player_id'><br>\n"); | Print out as a hidden value the player_id |
| print ("<input type=hidden name=today value='$today'><br>\n"); | Print out as a hidden value today |
| print ("</form>"); | Print out the end of form tag |
| } | Ends clause |
| mysql_close($link); | Close connection |
| print ("<a href=\"choosecategory. php\">Choose category for new question</a>"); | Print out link to choose a new question |
| ?> | End PHP |
| </body></html> | Closing HTML |
The checkanswer.php script, shown in Table 15.6, handles the form presented by askquestion.php. It uses the PHP function for checking that a string, the answer submitted by the player, matches a regular expression, the answer pattern passed on as hidden data from the form. If there is a match, meaning the player is correct, the player record is updated with the score increased by the value of the question. In any case, a record is added to the history file.
| <html><head><title>Check answer </title></head><body> | Usual HTML |
| <? | Start PHP |
| require("opendbq.php"); | Include connecting code |
| $ans = StripSlashes($ans); | Strip out the slashes from the answer pattern |
| if (eregi($ans,$player_ans)) { | If test on the result of a regular expression check |
| $corval='-1'; // anything non-zero stands for correct | True clause: set $corval to –1, indicating a true value |
| print ("<h1> CORRECT! </h1><p>\n"); | Print a message |
| $query="UPDATE players set score = score + $value, lastplayed='".$today."' where player_id='".$player_id."'"; | Set query to update the player record with playerid matching the $player_id value passed from the form. Two changes are made: the score is increased by the $value passed from the form, and the date is changed to $today (also passed in from the form) |
| } | End the true clause |
| else { | Start the negative clause: incorrect answer |
| $corval='0'; // | Zero stands for false |
| print ("<h1> WRONG! </h1><p>\n"); | Print message |
| $query="UPDATE players set lastplayed='".$today."' where player_id='".$player_id."'"; | Set query to update players to change the lastplayed field to $today |
| } | End the wrong answer clause |
| mysql_db_query($DBname,$query,$link); | Invoke the query |
| $query="INSERT INTO history values ('".$question_id."','".$player_id."', '".$today."','".$corval."')"; | Define new query string for inserting record into the history table |
| mysql_db_query($DBname, $query,$link); | Invoke query |
| $query = "Select score from players where player_id='".$player_id."'"; | Define query to obtain player’s score |
| $result=mysql_db_query($DBname, $query,$link); | Invoke query |
| $score = mysql_result($result,0,"score"); | Extract score |
| print ("<h2> The score is now " . $score. ".</h2><br>\n"); | Print score |
| mysql_close($link); | Close link to database |
| ?> | End PHP |
| <a href="choosecategory.php"><h2>Pick category for new question</h2></a> | Link to get a new question: link to |
| </body></html> | Closing HTML tags |
The showscores.php script, shown in Table 15.7, does just that: show all the scores. It can be invoked directly or by using a link from choosecategory.php.
| <html><head><title>Show player scores</title></head><body> | Usual HTML |
| <?php | Start PHP |
| require("opendbq.php"); | Include connecting code |
| $query="Select name, score from players order by score desc"; | Build query to get score information, in descending order (highest to lowest) |
| $rs=mysql_db_query($DBname, $query, $link); | Invoke query |
| ?> | End PHP |
| <table> | Table tag |
| <tr><td> Player Name </td><td> Score </td></tr> | First row of table with column headings |
| <? | Start PHP |
| while ($row=mysql_fetch_array($rs)){ | While loop: will iterate through the $rs recordset |
| print("<tr><td>"); | Print table tags |
| print($row['name']); | Print player name |
| print("</td><td>"); | Print table tags |
| print($row['score']); | Print score |
| print("</td></tr>"); | Print table tags, ending row |
| } | End while |
| print("</table>"); | Print closing table tag |
| ?> | End PHP |
| <br> | Line break |
| <a href="choosecategory.php">Choose category for new question </a> | An a tag to go and choose a new category for a new question. |
| </body></html> | Closing HTML tags |
The cleartables.php script, shown in Table 15.8, is used to clear the tables as the user’s request. To retain valid data, if the players or the questions table are cited for clearing, then the history table is also cleared. This is because the history table records point to each of these tables, and so the history table contents would be meaningless if the contents of either of these tables were deleted. In contrast, if the history table was deleted, the other two tables still would contain meaningful data. This script is a handler and also a form.
| <html><head><title>Get request and clear tables</title></head><body> | Usual HTML |
| <?php | Start PHP |
| if ($submitted){ | If test for is this to be the handler |
| include("opendbq.php"); | Include the connecting code |
| if ($tname=='players' or $tname=='questions') { | Do if test on $tname to check if the history table also needs to be cleared |
| $query="Delete from history"; | True clause: build the query to clear the history table |
| mysql_db_query($DBname, $query,$link); | Invoke the query |
| } | End the clause |
| $query="Delete from $tname"; | Build the query to delete the given table |
| mysql_db_query($DBname,$query,$link); | Invoke the query |
| mysql_close($link); | Close the link |
| Print("The records in the $tname table have been deleted.<br>\n"); | Print a message |
| } | End the clause for handling a submitted form |
| else { | Else (clause for displaying the form) |
| print ("<form action=cleartables.php method=post><br>\n"); | Print the form tag |
| print ("<select name='tname'><br>\n"); | Print select tag |
| print (" <option value='questions'> Questions (also history) </option><br>\n"); | Print as a first option Questions |
| print (" <option value='history'>History of player actions </option><br>\n"); | Print as a second option History |
| print (" <option value='players'>Players (also history) </option><br>\n"); | Print as a third option Players |
| print ("</select><br>\n"); | End the select element to close off the options list |
| print ("<input type=hidden name='submitted' value='TRUE'><br>\n"); | Print the hidden tag for the submitted value |
| print ("<input type=submit value='Submit' name='submit'><br>\n"); | Print the tag for the Submit button |
| print ("</form><br>\n"); | Print the form closing tag |
| } | End the clause for the form display |
| ?> | End PHP |
| <a href="choosecategory.php">Pick category of questions </a><p><p> | An a tag to go to pick a category for a new question |
| <a href="inputquestions.php">Input new questions </a> | An a tag to input new questions |
| </body></html> | Closing HTML tags |
The ASP and Access Implementation
The ASP implementation closely resembles the PHP implementation, with the exceptions noted previously. As has been suggested before, you might want to try to produce one or more of the ASP scripts on your own, using the PHP script and previous ASP scripts as models.
The openconnquiz.asp script, shown in Table 15.9, establishes the connection to the database. This file is included in all the other scripts. Note that the connection mode is set to be read/write in this script.
| <% | Start ASP |
| Conn = Server.CreateObject("ADODB.Connection"); | Create connection object |
| Conn.Mode = 3 ; | Set mode |
| strConnect = "Driver={Microsoft Access Driver (*.mdb)};" + "DBQ=" + Server.MapPath("quizasp.mdb") ; | Define a connection string that holds the driver and specific filename for the database |
| Conn.Open (strConnect, "admin", "") ; | Open the connection, using standard values for user and password |
| %> | End ASP |
The inputquestions.asp script, shown in Table 15.10, allows a user to add questions to the questions table. The script serves as a handler of a form and a presenter of a form.
| <%@ Language=JavaScript %> | Define the language |
| <html><head><title>Input and submit questions to quizasp db</title></head><body> | Standard HTML |
| <!— #include file="openconnquiz.asp" —> | Include the connecting code |
| <% | Start ASP |
| var submitted=String(Request.Form("submitted")); | Extract the submitted flag |
| if (submitted !="undefined") { | If the form has been shown |
| var question = String(Request.Form("question")); | Extract the question text passed as form data |
| var ans = String(Request.Form("ans")); | Extract the ans text |
| var valuex = parseInt(Request.Form("value")); | Extract the point value |
| var cat = String(Request.Form("cat")); | Extract the category |
| var fields = " (question, answerpattern, category, points) "; | Begin formation of the insert query. The fields part is constant |
| var valuesx = " VALUES ('"+question+"', '" + ans +"', '" + cat +"', " + valuex +")"; | Form the values part of the query |
| var query="INSERT INTO questions " + fields + valuesx; | Build the complete query |
| Response.Write("Insert query is: " + query); | For debugging, display the query |
| if (Conn.Execute(query)) | Execute the query. Do a check |
| {Response.Write("<br>Question was successfully entered. <br>");} | Display the message that the question was successfully entered |
| else {Response.Write("Question was NOT entered.<br>"); } | Display the message that the question was not successfully entered |
| Conn.Close(); | Close the connection |
| Response.Write("<a href=\"inputquestions.asp\"> Submit another question </a><br>"); | Display a link to this script to submit another question |
| } // ends if form was submitted. | End the clause for handling a form |
| else { | Start the clause to display the form |
| %> | End ASP |
| <h1>Add a question to the databank of questions. <br></h1> | HTML heading |
| <% | Start ASP |
| var sq ="SELECT * from questions"; | Create a query (sq) to get all the questions |
| Response.Write("Questions in database <br>"); | Display a heading |
| rs=Server.CreateObject("ADODB. RecordSet"); | Create a recordset object |
| rs.Open (sq,Conn); | Invoke the query |
| while (!(rs.EOF)){ | While loop: iterates through all the questions. (See Exercises section) |
| Response.Write("<br>" + String(rs.fields.item("question"))+ " Category: "+ String(rs.fields.item("category"))+ "<br>"); | Display questions, outputting line breaks in between |
| rs.move(1); | Advance to next record |
| } // end while | End while |
| %> | Close ASP |
| <form action="inputquestions.asp" method="POST"><br> | Form tag |
| Text of question <input type=text name="question" size=50><br> | Input tag for question text |
| Answer pattern <input type=text name="ans" size=50><br> | Input tag for answer pattern text |
| Category <input type=text name="cat" size=30><br> | Input tag for category |
| Value <input type=text name="value" size=6><br> | Input tag for value. Note: "value" is our name for this input |
| <input type=hidden name="submitted" value="True"><br> | Input tag for the submitted flag |
| <input type=submit name="submit" value="Submit question!"><br> | Tag for submit button |
| </form> | End form |
| <% | Start ASP |
| } | Close clause for presenting form |
| %> | Close ASP |
| </body></html> | Close HTML |
The choosecategory script, shown in Table 15.11, is the first of three scripts for playing the game. The choosecategory script contains a form that is handled by askquestion as specified by the action attribute of the form. The code checks if a session value has been set with the name of the current player and, if so, displays it as the value of an input box.
| <%@ Language=JavaScript %> | Set language |
| <html><head><title>Sign in and select category </title></head><body> | Usual HTML |
| <h1> Welcome to the Quiz </h1><br> | Heading |
| <h3> Sign in and select a category for your question </h3> | Instructions |
| <form action="askquestion.asp" method=post> | Form tag |
| <p>Name <input type=text name='player' size=30 | Player name tag |
| <% | Start ASP |
| currentplayer=String(Session("currentplayer")); | Extract session value |
| if (currentplayer != "undefined") { | Check if the session value was defined |
| Response.Write("VALUE= "+ currentplayer+"><br>"); | If so, display this as the value for the input tag |
| } | Close the clause |
| else { | Start the else clause |
| Response.Write("><br>"); | Simply close the input tag |
| } | Close the clause |
| %> | Close ASP |
| <p>Pick a category <select name=pickedcategory> | Instructions to player and select tag, which starts the pull-down list |
| <!— #include file="openconnquiz.asp" —> | Include the connecting code |
| <% | Start ASP |
| query="SELECT DISTINCT category FROM | Build a query to get the distinct |
| questions"; | categories |
| rs=Server.CreateObject("ADODB.RecordSet"); | Create a recordset object |
| rs.Open(query,Conn); | Invoke the query |
| while (!(rs.EOF)) | While loop: iterates over recordset holding categories |
| { cat=String(rs.fields.item("category")); | Extract the category value |
| Response.Write("<option value="+cat+">"+cat+"</option><br>\n"); | Display the category as an option |
| rs.Move(1); | Advance to the next category |
| } | Close while loop |
| Conn.close(); | Close connection |
| Response.Write("</select>"); | Output closing select (ending the pulldown list) |
| Response.Write("<input type=submit name=submit value=\"Choose!\"><br>\n"); | Output input tag for submit button: in this case, the label is "Choose!" |
| Response.Write("</form>"); | Output end of form tag |
| %> | Close ASP |
| <br> | Line break |
| <a href="showscores.asp">Show scores of players. </a> | An a tag to allow player to go to showscores script |
| </body></html> | Closing HTML |
The askquestion.asp script, shown in Table 15.12, uses the information passed from the choosecategory script to find a question. This script must apply the policies of the game concerning appropriate questions: a player is not asked any question answered correctly or asked the same day. This requires use of a LEFT JOIN. The ASP script uses a permanent table as a temporary table: all old records are deleted.
| <%@ Language=JavaScript %> | Set language |
| <% | Start ASP |
| player=String(Request.Form("player")); | Extract from the form data the player name |
| Session("currentplayer")=player; | Set the session value currentplayer to be this name |
| pickedcategory=String(Request.Form("pickedcategory")); | Extract category |
| Response.Write("<br>The selected category is: "+pickedcategory); | Display the category. This could be considered a debugging statement |
| %> | Close ASP |
| <html><head><title>Fetch appropriate question from category and ask</title></head><body> | Standard HTML |
| <% | Start ASP |
| dx= new Date(); | Get the date |
| today = dx.getDate()+"-"+dx.getMonth()+"-"+dx.getFullYear(); | Create today with a formatted form of the date. This will be used for immediate display and to compare with records from the history table |
| Response.Write("<br>Today is "+today+" <br>\n"); | Create today with a formatted form |
| Response.Write("Here is your question, "+player+".<br>"); | Display customized instruction |
| %> | Close ASP |
| <!— #include file="openconnquiz.asp" —> | Include the connecting code |
| <!— #include file="random.asp" —> | Include code to get random question |
| <% | Start ASP |
| query="SELECT name, player_id, lastplayed, score from players where name='"+player+"'"; | Build the query to get player information |
| rs=Server.CreateObject("ADODB.RecordSet"); | Create a recordset object |
| rs.Open(query,Conn, 1, 3); | Invoke the query. Need the locking and cursor settings to obtain the count of records. |
| if (rs.RecordCount==0) { | If no information (meaning this player has not played) |
| query2 = "INSERT INTO players (name, lastplayed, score) values ('"+player+"', '"+today+"','0')"; | Build a query to insert a new record into the players table |
| Conn.Execute(query2); | Execute the query |
| query="SELECT player_id from players where name='"+player+"'"; | Build a query to obtain the player_id of the record just created |
| rs1=Server.CreateObject | Create a recordset object (“ADODB.RecordSet”); |
| rs1.Open(query, Conn, 1, 3); | Invoke the query |
| player_id=String(rs1.fields.item("player_id")); | Extract the player_id and store in variable |
| } | Close clause for no prior record for player |
| else { | Start else clause |
| player_id = String(rs.fields. item("player_id")); // take first0th record | Extract the player_id from the recordset |
| } | Close clause |
| query="DELETE * from past"; | Form a query to delete the records from past |
| Conn.Execute(query); | Execute query |
| query= "SELECT question_id FROM history where (player_td" width="38%" align="left"> Start formation of query to get questions from history that were answered correctly or asked today | |
| query= query + " AND (whenplayed=#"+today+"# OR correct=Yes))"; | Continue forming query |
| rs = Server.CreateObject("ADODB.Recordset"); | Create recordset object |
| rs.Open(query, Conn, 1, 3); | Execute query |
| NoR=rs.RecordCount; | Obtain count of records |
| if (NoR>0) { | If there were prior relevant questions |
| query= "SELECT question_id FROM history where (player_td" width="38%" align="left"> Start building query that willpopulate the past table | |
| query= query + " AND (whenplayed=#"+today+"# OR correct=Yes))"; | Continue |
| query="INSERT into past "+query; | Continue |
| Conn.Execute(query); | Execute the query to populate the past table |
| sel = "SELECT questions.question_id, question, answerpattern, points from questions"; | Start to form another query (in the sel string) that will pick up all questions… |
| sel =sel + " LEFT JOIN past ON questions.question_id = past.question_id WHERE "; | Using the LEFT JOIN mechanism |
| sel = sel + " category='" + pickedcategory + "' AND past.question_id IS NULL"; | Questions that are in the category and not in the past table (by the past.question_id being null after the LEFT JOIN) |
| } | Close clause |
| else { | Start clause for no relevant past questions |
| sel="SELECT question_id, question, answerpattern, points from questions "; | Start forming of sel |
| sel= sel + " WHERE category= '" + pickedcategory+ "'"; | Continue with sel |
| } | Close the else clause |
| rs3=Server.CreateObject("ADODB. RecordSet"); | Create yet another recordset object |
| rs3.Open(sel, Conn, 1, 3); | Invoke the query |
| NoR=rs3.RecordCount; | Obtain the recordcount |
| if (NoR==0) { | Check if there were any appropriate questions |
| Response.Write("<br> No appropriate questions in databank. Pick new category. <br>\n"); | If none, write out message |
| } | Close clause |
| else | Else |
| { if (NoR > 1) { | Need to check if there was more than one question. If more than one, apply a random procedure to get question |
| choice=rand(NoR-1); | Invoke rand (in the |
| Response.Write("<br>Choice is: "+choice); | Debugging statement |
| } | Close clause for more than one |
| else { | Start else clause |
| choice=0;} | Set choice to zero for the situation with just one question |
| rs3.Move(choice,1); | Advance in the recordset choice positions, starting from the firstrecord |
| question_id = rs3.fields.item("question_id"); | Extract the question_id |
| question = rs3.fields.item("question"); | Extract the question text |
| ans = rs3.fields.item("answerpattern"); | Extract the answerpattern text |
| points = rs3.fields.item("points"); | Extract the point value |
| Response.Write("<h3><p> The question is <br> "+question+"??? <br>\n"); | Display the question (text) |
| Response.Write("<form action=\"checkanswer.asp\" Method=post><br>\n"); | Output a form, with the action going to |
| Response.Write("<input type=text name=player_ans size=50><br>\n"); | Output the input tag to hold the player’s answer |
| Response.Write("<input type=submit name=submit value='Submit answer'><br>\n"); | Output the Submit button |
| Response.Write("<input type=hidden name=ans value='"+ans+"'><br>\n"); | Output as a hidden tag the actual answer pattern (from the database) |
| Response.Write("<input type=hidden name=pval value='"+points+"'><br>\n"); | Output as a hidden tag the points |
| Response.Write("<input type=hidden name=question_id value='"+question_id+"'><br>\n"); | Output as a hidden tag the question_id. This is for use when creating and inserting the history record |
| Response.Write("<input type=hidden name=player_id value='"+player_id+"'><br>\n"); | Output as a hidden tag the player_id. Used in the history record |
| Response.Write("<input type=hidden name=today value='"+today+"'><br>\n"); | Output as a hidden tag the today value. Used in the hidden record |
| Response.Write("</form>"); | Output the close of form tag |
| } | End clause for appropriate questions |
| Conn.Close(); | Close the connection |
| Response.Write("<a href=\"choosecategory. asp\">Choose category for new question</a>"); | Output an <a> tag to choose a category and get a new question |
| %> | Close ASP |
| </body></html> | Closing HTML |
The checkanswer.asp script, shown in Table 15.13, performs the check on the player’s answer by comparing it to the value given in the database. The regular expression search method of string objects is used. The code makes an insertion into the history table and updates the player’s record in the players table.
| <%@ Language=JavaScript %> | Set language |
| <html><head><title>Check answer </title></head><body> | HTML |
| <!— #include file="openconnquiz.asp" —> | Include the connecting code |
| <% | Start ASP |
| ans=String(Request.Form("ans")); | Extract ans from the form data. This is the database answer pattern |
| player_ans=String(Request.Form("player_ans")); | Extract the player’s answer |
| pval=parseInt(Request.Form("pval")); | Extract the points |
| question_id=parseInt(Request.Form("question_id")); | Extract the question_id |
| today=String(Request.Form("today")); | Extract the date |
| player_id=String(Request.Form("player_id")); | Extract the player_id |
| if (player_ans.search(ans)>-1){ | Do the check. If it is correct, indicated by a return value greater than –1 (remember strings begin with zero) |
| corval='Yes'; // | Access wants yes/no |
| Response.Write("<h1> CORRECT! </h1><p>\n"); | Write out result |
| query="UPDATE players set score = score + "+pval+", lastplayed='"+today+"' where player_td" width="32%" align="left"> Define a query to update the player’s record: increment score and modify lastplayed field | |
| } | End clause |
| else { | Else clause: player got it wrong |
| corval='No'; | Set the variable |
| Response.Write("<h1> WRONG! </h1><p>\n"); | Write out result |
| query="UPDATE players set lastplayed='"+today+"' where player_td" width="32%" align="left"> Define a query to update the player’s record, just changing the lastplayed field | |
| } | End the clause |
| Conn.Execute(query); | Execute the query (could be either one) |
| query="INSERT INTO history (question_id, player_id, whenplayed, correct) values("+question_id+","+player_id+",#"+today+"#,"+corval+")"; | Build a query to insert a record into the history file |
| Conn.Execute(query); | Execute the query |
| Conn.Close(); | Close the connection |
| %> | Close ASP |
| <a href="choosecategory.asp"><h2>Pick category for new question</h2></a> | An a link to pick a category for a new questions |
| </body></html> | Closing HTML |
The random.asp code, shown in Table 15.14, could easily be incorporated into the calling routine.
| <% | Start ASP |
| function rand(number) { | Function header |
| choice= Math.floor(Math. random()*(number+1)) | Use Math methods to get a choice between 0 and the number passed as a parameter (including both 0 and the number as possibilities) |
| return choice; | Return the value |
| } | Close the function |
| %> | End ASP |
The showscores.asp script, shown in Table 15.15, displays the scores of each player.
| <%@ Language=JavaScript %> | Set the language |
| <html><head><title>Show player scores</title></head><body> | Standard HTML |
| <!— #include file="openconnquiz.asp" —> | Include the connecting code |
| <% | Start ASP |
| rs=Server.CreateObject("ADODB. RecordSet"); | Create a recordset object |
| query="Select name, score from players order by score desc"; | Create a query |
| rs.Open(query, Conn); | Invoke the query |
| %> | Close ASP |
| <table><tr><td> Player Name </td><td> Score </td></tr> | Table tags, including column headings |
| <% | Start ASP |
| while (! (rs.EOF)){ | While loop: iterate through all players. |
| Response.Write("<tr><td>"); | Output table tags |
| Response.Write(String(rs.fields. item("name"))); | Output player’s name, extracted from recordset |
| Response.Write("</td><td>"); | Output table tags |
| Response.Write(String(rs.fields. item("score"))); | Output player’s score, extracted from recordset |
| Response.Write("</td></tr>"); | Output table tags |
| rs.move(1); | Advance to next record in recordset |
| } | Close while |
| Response.Write("</table>"); | Output close of table |
| %> | Close ASP |
| <br> | Line break |
| <a href="choosecategory.asp">Choose category for new question </a> | An a tag to choose a category for a new question |
| </body></html> | Closing HTML |
EAN: 2147483647
Pages: 125