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.

Table 15.1: PHP Script to Establish Connection with Database

<?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.

Table 15.2: PHP Script to Create the 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.

Table 15.3: PHP Script to Add Questions

<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.

Table 15.4: PHP Script for Signing In and Selecting a Category

<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 askquestion.php script

<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.

Table 15.5: PHP Script that Finds Appropriate Question to Ask

<?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 checkanswer.php. Note: method is post so player will not see the hidden form data

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.

Table 15.6: PHP Script for Checking Answer

<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 choosecategory.php script

</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.

Table 15.7: The PHP Script to Display the Scores of All the Players

<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.

Table 15.8: PHP Script to Clear Tables

<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.

Table 15.9: ASP/JavaScript Script to Establish Connection to Database

<%

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.

Table 15.10: ASP/JavaScript Script to Add a New Question

<%@ 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.

Table 15.11: Initial ASP/JavaScript for Signing In and Selecting a Category

<%@ 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.

Table 15.12: ASP/JavaScript for Choosing an Appropriate Question

<%@ 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 random.asp file) to get choice

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 checkanswer.asp

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.

Table 15.13: ASP/JavaScript Script to Check Answer

<%@ 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.

Table 15.14: ASP/JavaScript File Defining the rand Function

<%

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.

Table 15.15: The ASP/JavaScript Script for Displaying Scores

<%@ 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




Creating Database Web Applications with PHP and ASP
Creating Database Web Applications with PHP and ASP (Charles River Media Internet & Web Design)
ISBN: 1584502649
EAN: 2147483647
Year: 2005
Pages: 125
Authors: Jeanine Meyer

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net