Examples


This section contains the scripts for the tasks described in the previous sections. Please note that the scripts often contain print or Response.Write statements useful for debugging. You should include them when you try to replicate these examples. You would remove them from any code used for real operations. Do not, either in your experiments or for real projects, copy the style here of squeezing statements together. It is done here to simplify the explanations. Do put in comments and blank lines.

On the CD The application described in this chapter builds on the favorites applications started in Chapter 9, “Connecting to the Database.” You will find the code for this chapter on the CD-ROM in the folder named chapter10code. You will need the openfirstdb.php and sopenconn.asp files from the chapter9code folder.

Altering Field Sizes

The scripts to modify the size of the title field are similar for PHP and ASP/JavaScript, one obvious difference is that the term is “char” for MySQL and “text” for Access. These are programs that are run exactly one time.

The PHP script is shown in Table 10.1.

Table 10.1: PHP Script to Alter Field Size

<html><head><title>Alter table</title></head><body>

Usual HTML start

<?php

Start PHP

require("openfirstdb.php");

Include connection code

$query="Alter table favorites modify title char(30)";

Define $query

print("alter query is: $query");

For debugging

$result = mysql_db_query($DBname,$query, $link);

Send query to MySQL

if ($result) {

Do check on return code

print("The table was successfully altered.<br>\n"); }

Positive result

else {

Start of else clause

print ("The table was NOT successfully altered. <br>\n");}

Negative result

mysql_close($link);

Close connection

?>

End PHP

</body></html>

HTML close

The ASP/JavaScript alter script is shown in Table 10.2.

Table 10.2: ASP/JavaScript to Alter Field Size

<%@ Language=JavaScript %>

Set JavaScript as language

<html><head><title>Alter table</title></head><body>

Usual HTML start

<!— #include file="sopenconn.asp" —>

Include connection code

<%

Start ASP

var query="Alter table favorites alter column title text(30)";

Define query

Response.Write("alter query is: " + query);

Debugging

if (Conn.Execute(query))

Send query to Access and do check on return value

{Response.Write("<br>Table column changed. <br>");}

Positive result

else {Response.Write("Table column was not changed.<br>"); }

Negative result

Conn.Close();

Close connection

%></body></html>

Close ASP and HTML close

Deleting Records

For a change, here are the ASP/JavaScript scripts first for the delete function. After reading this script, you can see if you can do the PHP one by yourself.

Recall that the technique is to use two scripts. For the first (Table 10.3), your code will produce a table with hyperlinks that link to the second script. The second script (Table 10.4) is a handler script that makes use of an id inserted in the query string.

Table 10.3: ASP/JavaScript Script to Display Titles

<%@ Language=JavaScript %>

Set JavaScript as the language

<html><head><title>Form for selecting a favorite for deletion </title></head><body>

Usual HTML start

<h1>Drop a title from the databank of favorite shows. <br></h1>

HTML for heading

<!— #include file="sopenconn.asp" —>

Include connection code

<%

Start ASP

var sq ="SELECT * from favorites";

Define sq to hold query

rs=Server.CreateObject ("ADODB.RecordSet");

Create a recordset object

rs.Open (sq,Conn);

Invoke query

Response.Write("<table border='1'><tr><td> Titles</td><td>Descriptions</td></tr> ");

Output HTML for start of table

while (!(rs.EOF)){

while loop: will iterate through the recordset: once of each record (favorite) in the table

Response.Write("<tr><td>");

Output start of row, and first td item

Response.Write("<a href='sdropfavoritefancy.asp?whichtd" width="37%" align="left">

The contents of this item will be a HTML <a>element. This is the beginning of that element, with the href a link including the start of a query string

Response.Write(rs.fields.item ("favorite_id"));

Output the favorite_id field of this favorite to be the rest of the query string

Response.Write("'>");

Output a closing single quote and a closing pointy bracket to complete the <a> tag

Response.Write(unescape(String (rs.fields.item("title"))));

Output the title field of this favorite. Need to remove any escape characters

Response.Write("</a>");

Output the closing </a> tag

Response.Write(" </td><td>"+unescape (String(rs.fields.item ("description"))));

Output the table tags and then the description field of this favorite. Need to remove any escape characters

Response.Write("</td></tr>\n");

Output closing table tags: for item and for row. The \n is a line break for the HTML source

rs.move(1);

ASP code to advance to the next record of the recordset

} // end while

End while

Response.Write(“</table>”);

Output closing table tag

Response.Write("Click on title to be deleted.");

Output instructions

Conn.Close();

Close connection

%>

End ASP

</body></html>

Closing HTML

Table l0.4 shows the handler. It performs the deletion, using the id field sent over via the query string.

Table 10.4: ASP/JavaScript Script to Delete Specified Title

<%@ Language=JavaScript %>

Set JavaScript as the language

<html><head><title>Delete favorites from firstdb database </title></head><body>

Usual HTML start

<!— #include file="sopenconn.asp" —>

Include connection code

<%

Start ASP

var whichid = parseInt(Request.QueryString("whichid"));

Set the variable whichid to be value sent over via the query string

var query="DELETE FROM favorites where favorite_td" width="43%" align="left">

Set the query variable to be the delete query made by concatenating the fixed part with the favorite identifier

Response.Write("delete query is: " + query);

Debugging

if (Conn.Execute(query))

Execute (send over to Access) the delete query and do a test on the result

{Response.Write("<br>Title was successfully deleted. <br>");}

Positive outcome

else {Response.Write("Title was NOT deleted.<br>"); }

Negative outcome

Conn.Close();

Close connection

Response.Write("<a href=\"sdropfavoriteform.asp\"> Remove another title</a><br>");

Output a link to let the user go back and remove another title

%>

Close ASP

</body></html>

Usual HTML close

Now at this point, you could try writing the PHP scripts by yourself. You use the ASP scripts for this task and any PHP scripts from the previous chapter as models. Table 10.5 shows the script for presenting the titles, and Table 10.6 shows the script that performs the deletion.

Table 10.5: PHP Script to Display Titles

<html><head><title>Dropping favorite </title></head><body>

Usual HTML start

<h1>Drop a title to the databank of favorite shows. <br></h1>

Heading

<?

Start PHP

require("openfirstdb.php");

Include connection code

$sq ="SELECT * from favorites";

Define query

print("<h3>Favorites</h3><br>");

Output another heading

$rs = mysql_db_query($DBname,$sq,$link);

Invoke the query

print("<table border='1'><tr><td> Titles</td><td>Descriptions</td></tr> ");

Output table tags

print("\n");

Output line break for the HTML

while ($row=mysql_fetch_array($rs)) {

while loop: this will iterate through the records (rows) of the recordset

print("\n");

Output line break

print("<tr><td>");

Output table tags for start of row and first item

print("<a href='sdropfavoritesfancy.php?whichtd" width="40%" align="left">

Output the start of the a tag

print($row["favorite_id"]);

Output the favorite_id field

print("'>");

Output the rest of the a tag, including a single quote and a closing pointy bracket

print($row["title"]);

Output the title field of this favorite

print("</a>");

Output closing a tag

print("</td><td>");

Output table tags to close this item and start the next one

print($row["description"]);

Output the description field of this favorite

print("</td></tr>");

Output table tags to close the item and the row

} // end while

End the while

print("</table>");

Output closing tag for table

print("Click on favorite to be deleted.");

Output instructions

?>

Close PHP

</body></html>

Usual HTML close

Table 10.6: PHP Script to Drop the Specified Title

<html><head><title>Drop favorites from firstdb database </title></head><body>

Usual HTML start

<?php

Start PHP

require("openfirstdb.php");

Include connection code

$query="DELETE from favorites where favorite_id='$whichid'";

Define query as concatenation of fixed part with the identifier sent over via the query string

print("Delete query is: $query <br>");

Debugging

$result = mysql_db_query($DBname, $query, $link);

Invoke query

if ($result) {

If test on return value

print("The favorite was successfully deleted.<br>\n");}

Positive result

else { print ("The favorite was NOT successfully deleted. <br>\n");}

Negative result

mysql_close($link);

Close connection

print ("<a href=\"sdropfavoritesform. php\">Drop another favorite </a><br>");

Output instructions

?>

Close PHP

</body></html>

Usual HTML close

Updating Records

The task of updating the title and/or description fields of the favorites records is modeled on the delete example, except that it is suggested that you use three scripts. The first presents the user with all the favorites. The second provides the user with a form in which to update the selected favorite. The third script does the updating. Again, the first set of scripts will be for ASP/JavaScript.

The first script (Table 10.7) is for the user to choose what is to be updated: this is not an HTML form, although the user might think of it as one. It resembles the script used for the delete case.

Table 10.7: ASP/JavaScript Displaying Titles for Updating

<%@ Language=JavaScript %>

Set the language

<html><head><title>Form for selecting a favorite for update </title></head><body>

Usual HTML start

<h1>Drop a title from the databank of favorite shows. <br></h1>

HTML header

<!— #include file="sopenconn.asp" —>

Include connection code

<%

Start ASP

var sq ="SELECT * from favorites";

Define query

rs=Server.CreateObject("ADODB.RecordSet");

Set rs to be a recordset

rs.Open (sq,Conn);

Invoke the query

Response.Write("<table border='1'><tr><td> Titles </td><td>Descriptions</td></tr> ");

Output tags for start of table

while (!(rs.EOF)){

while loop: will iterate through the records of the recordset

Response.Write("<tr><td>");

Output table tags

Response.Write("<a href='supdate2.asp?whichtd" width="32%" align="left">

Output start of a tag, with query string

Response.Write(rs.fields.item ("favorite_id"));

Output the favorite_id field

Response.Write("'>");

Output close of a tag

Response.Write(unescape(String(rs.fields.item("title"))));

Output the title field of the favorite. Need to remove any escape characters

Response.Write("</a>");

Output the closing a tag

Response.Write(" </td><td>"+unescape (String(rs.fields.item("description"))));

Output the table tags and the description field of the favorite. Need to remove any escape characters

Response.Write("</td></tr>\n");

Output table tags and line break

rs.move(1);

Advance to next record

} // end while

Close while

Response.Write("</table>");

Output closing table tags

Response.Write("Click on title to be changed.");

Output instructions

Conn.Close();

Close connection

%>

End ASP

</body></html>

Usual HTML close

The next script ( supdate2.asp shown in Table 10.8) “knows” what favorite is to be updated. It presents a form for making the updates. The original contents of the title and description fields are placed in the text boxes. Notice the use of the slash as an escape character for the double quotation marks. This is to ensure that any single quotation marks in the contents of the title or the descriptions are interpreted as content, not as special characters.

The final script ( supdate3.asp shown in Table 10.9) does the work of making the change to the database. The identifier for the record to be updated has been carried over in a hidden form input.

Table 10.8: ASP/JavaScript for Performing Update of Specified Title

<%@ Language=JavaScript %>

Set language

<html><head><title>Update favorites from firstdb database </title></head><body>

Usual HTML

<!— #include file="sopenconn.asp" —>

Include connection code

<%

Start ASP

var whichid = parseInt(Request.QueryString("whichid"));

Set whichid variable from query string

var query="Select * FROM favorites where favorite_td" width="43%" align="left">

Define query using the whichid variable

Response.Write("select query is: " + query);

Debugging

rs=Server.CreateObject ("ADODB.RecordSet");

Set rs to be a recordset

rs.Open (query,Conn);

Invoke the query

Response.Write("<form action='supdate3.asp'>\n");

Output start of form

Response.Write("Title: <input type='text' name='title' value=\""+unescape (rs.fields.item("title"))+"\">");

Output input tag with current value of title. Note use of slashes to “escape”; namely, make JavaScript ignore the quotation marks

Response.Write("Description: <input type='text' name='desc' value=\""+unescape(rs.fields.item ("description"))+"\" size='30'>");

Output input tag with current value of description. See above

Response.Write("<input type='submit' value='Update'>");

Output Submit button

Response.Write("<input type='hidden' name='whichid' value='" + whichid + "'>"); whichid

Output a hidden tag so the value will be sent along to the next script

Response.Write("</form>");

Output close of form

Response.Write("<br> Change fields and click on Update button. ");

Output instructions.

Conn.Close();

Close connection

%>

End ASP

</body></html>

Usual HTML close

Table 10.9: ASP/JavaScript to Perform the Update of the Favorite

<%@ Language=JavaScript %>

Set language

<html><head><title>Complete update of a favorite </title></head><body>

Usual HTML

<!— #include file="sopenconn.asp" —>

Include connection code

<%

Start ASP

var whichid = parseInt(Request ("whichid"));

Set whichid (in this script) to be value sent over from last script

var title = escape(String(Request ("title")));

Set title from form input. Need to escape any special values user has typed

var desc = escape(String(Request ("desc")));

Set desc from form input

var query1="Update favorites set title='" + title +"', ";

Define query is pieces. This is because of the complex syntax

var query2= "description = '" + desc + "'";

Set description field to value of desc

var query3= "where favorite_td" width="49%" align="left">

Set the favorite_id field to value of whichid

var query = query1 + query2 + query3;

Combine parts to make the complete query

Response.Write("update query is: " + query + "<br>");

Debugging

if (Conn.Execute(query)) {

Invoke query and do test

Response.Write("Favorite updated successfully."); }

Positive outcome

else {Response.Write("Favorite not updated."); }

Negative outcome

Conn.Close();

Close connection

Response.Write("<a href='supdate1.asp'> Update another favorite.</a>");

Output link to let user go back and update another field

%>

Close ASP

</body></html>

Usual HTML close

Now is the time for you to see if you can do the PHP version on your own.

Recall that there are three scripts. The first script lets the user select a favorite to be updated. It is shown in Table 10.10.

Table 10.10: PHP Script to Display Titles for Updating

<html><head><title>Form for selecting a favorite for update </title></head><body>

Usual HTML opening

<h2>Select a title from the databank of favorite shows. <br></h2>

HTML heading

<?

Start PHP

require("openfirstdb.php");

Include the connection code

$query="Select * FROM favorites";

Define the variable $query to be the query

print("select query is: " . $query);

For debugging

$rs = mysql_db_query($DBname,$query, $link);

Invoke the query

print("<table border='1'><tr><td> Titles </td><td>Descriptions</td></tr> ");

Output table tags

while ($row=mysql_fetch_array($rs)){

while loop: will iterate through all the records (rows) of the recordset $rs

print("<tr><td>");

Output row table tags

print("<a href='supdate2.php?whichtd" width="44%" align="left">

Output start of a tag, with href holding link and start of query string

print($row["favorite_id"]);

Output favorite_id for this record to be part of the query string

print("'>");

Output close of a tag

print($row["title"]);

Output title field of this record

print("</a>");

Output closing a tag

print(" </td><td>".$row["description"]);

Output table tags plus the description field of this record

print("</td></tr>\n");

Output closing table tags plus line break for the HTML

} // end while

Close of while loop

print("</table>");

Output closing table tag

print("Click on title to be changed.");

Output instructions

mysql_close($link);

Close connection

?>

End PHP

</body></html>

Usual closing HTML

The second script is shown in Table 10.11. It uses the favorite_id passed in the query string to build a form.

Table 10.11: PHP Script to Enter Update Information

<html><head><title>Update favorites from firstdb database </title></head><body>

Usual HTML opening

<?php

Start PHP

require("openfirstdb.php");

Include connection code

$query="Select * FROM favorites where favorite_id=$whichid";

Define $query to be query made by concatenating fixed parts with the favorite_id passed in via the query string

$rs = mysql_db_query($DBname,$query, $link);

Invoke the query

$row=mysql_fetch_array($rs);

Fetch the first (and presumably only) row of the recordset

print("<form action='supdate3.php'>");

Output form header tag

print("Title: <input type='text' name='title' value=\"");

Output start of input tag for title. It will contain the current title. Note the slash in front of the double quote

print($row["title"]);

Output the title field for this favorite

print("\">");

Output close of input tag, including “escaped” double quote

print("Description: <input type='text' name='desc' value=\"");

Output start of input tag for description. See above treatment for title

print($row["description"]);

Output the description field for this favorite

print("\" size='40'>");

Output close of input tag. Give it expanded size

print("<input type='submit' value='Update'>");

Output Submit button

print("<input type='hidden' name='whichid' value='$whichid'>");

Output as a hidden input tag element the $which value

print("</form>");

Output form close

print("<br> Change fields and click on Update button. ");

Output instructions

mysql_close($link);

Close link

?>

End PHP

</body></html>

Usual HTML close

The third and final script (shown in Table 10.12) does the updating of the record in the database.

Table 10.12: PHP Script to Perform Update of Title

<html><head><title>Complete update of a favorite </title></head><body>

Usual HTML start

<?php

Start PHP

require("openfirstdb.php");

Include connection code

$query1="Update favorites set title='$title', ";

Will define query in three parts due to complex syntax. This part gets in form input for title

$query2= "description='$desc' ";

This step uses form input for description.

$query3= "where favorite_id=$whichid";

This step uses form input for the id, which was passed in the hidden input tag

$query = $query1 . $query2 . $query3;

Define $query as the concatenation of these three string variables

print("query is $query <br>");

Debugging

$result = mysql_db_query($DBname,$query, $link);

Invoke query

if ($result) {

If test to check return value

print("Update successful.<br>\n"); }

Positive outcome

else { print ("Update not successful. <br>\n");}

Negative outcome

mysql_close($link);

Close connection

print("<a href='supdate1.php'>Update another favorite.</a>");

Output link to give user a chance to do another update

?>

Close PHP

</body></html>

Usual HTML close

Creating New Tables Using PHP

Now we begin the programming to enhance the application with two new tables. As before, given the command-line interface for MySQL, it is suggested that you use PHP scripts to add the new tables. Since there are two tables to define, you can appreciate that defining a createtable function is a useful thing to do. Table 10.13 shows the script to define the new tables.

Table 10.13: PHP Script for Defining New Tables

<?php

Start PHP

function createtable($tname,$fields) {

Define function called createtable. It has two parameters

global $DBname, $link;

Set up to use global values for $DBname and $link

$query="CREATE TABLE $tname ($fields)";

Define $query

if (mysql_db_query($DBname,$query, $link)) {

Invoke query and test for success

print ("The table, $tname, was created successfully.<br>\n"); }

Positive outcome

else { print ("The table, $tname, was not created. <br>\n");}

Negative outcome

}

End of function definition

?>

End PHP

<html><head><title>Creating new tables</title></head><body>

Usual HTML start

<h3> Creating the added tables for firstdb </h3><br>

HTML heading

<?php

Start PHP

require("openfirstdb.php");

Include connection code. This sets the global variables to be used in the createtable function

$tname = "warnings";

Set $tname for warnings table

$fields = "favorite_id INT NOT NULL, cat_id INT NOT NULL, reason char(50)";

Set $fields. The table has three fields. It does not have a primary key

createtable($tname, $fields);

Call createtable function

$tname = "categories";

Set $tname for categories table

$fields="cat_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cat_desc char(50)";

Set $fields. This table has two fields

createtable($tname,$fields);

Call createtable function

mysql_close($link);

Close connection

?>

End PHP

</body></html>

Usual HTML close

The categories table has a limited number of entries, so instead of creating scripts for adding categories (see the Exercises), Table 10.14 shows an execute one-time only script to “populate” the table with four records.

Table 10.14: PHP Script for Populating the Categories Table

<html><head><title>Adding entries to categories table</title></head><body>

Usual HTML start

<h3> Categories</h3><br>

HTML heading

<?php

Start PHP

require("openfirstdb.php");

Include connection code

$tname="categories";

Set name of table

$query="INSERT INTO $tname VALUES('0','Violence')";

Set $query

$result = mysql_db_query($DBname,$query, $link);

Invoke query

if ($result) {

Check result

print("The violence category was successfully added.<br>\n"); }

Positive result

else {print ("The violence category was NOT successfully added. <br>\n");}

Negative result

$query="INSERT INTO $tname VALUES('0','Sex')";

Repeat for next category

$result = mysql_db_query($DBname,$query, $link);

Invoke query

if ($result) {

Check result

print("The sex category was successfully added.<br>\n"); }

Positive result

else {print ("The sex category was NOT successfully added. <br>\n");}

Negative result

$query="INSERT INTO $tname VALUES('0','Language')";

Repeat for next category

$result = mysql_db_query($DBname,$query, $link);

Invoke query

if ($result) {

Check result

print("The language category was successfully added.<br>\n"); }

Positive result

else {print ("The language was NOT successfully added. <br>\n");}

Negative result

$query="INSERT INTO $tname VALUES('0','Adult Themes')";

Repeat for next category

$result = mysql_db_query($DBname,$query, $link);

Invoke query

if ($result)

Check result.

{print("The adult themes was successfully added.<br>\n"); }

Positive result

else {print ("The adult themes was NOT successfully added. <br>\n");}

Negative result

mysql_close($link);

Close connection

?>

End PHP

</body></html>

Usual HTML close

Using Access to Create New Tables

Now we turn to the ASP system, specifically Access. It has been suggested that you add the new tables and populate the categories table all in stand-alone mode. You need to download the database from the server. You define the new tables the same way you defined the original table, favorites.

Assuming you have the three tables defined, you can use the Access system to specify the relationships between the tables. See Figure 10.4. Click on Tools.

click to expand
Figure 10.4: Access window.

Now, click on Relationships to see the drop-down menu shown in Figure 10.5.

click to expand
Figure 10.5: Options under Tools.

After clicking on Relationships, you see the Window shown in Figure 10.6. Click on View.

click to expand
Figure 10.6: Relationships window: see arrow pointing to view.

Then, click on Show Table. You get yet another window as shown in Figure 10.7.

click to expand
Figure 10.7: Show Table window.

Select and then click on Add for all three of the tables. You will see the window in Figure 10.8.

click to expand
Figure 10.8: Tables showed to use to define relationship.

Now click on Relationships and then Edit Relationships. The screen in Figure 10.9 is displayed. Click Create New….

click to expand
Figure 10.9: Edit Relationships window.

You will see the window shown in Figure 10.10 for specifying the table names and field names.

click to expand
Figure 10.10: Window to define relationships by specifying tables and fields.

The downward arrows list the tables you have added to the Relationships window and, once you select a table, the table’s field names. Figure 10.11 is a completed window for one relationship.

click to expand
Figure 10.11: Completed entry to specify new relationship.

Click OK. You then see the window in Figure 10.12.

click to expand
Figure 10.12: Window to edit information on relationship.

Click the box next to Enforce Referential Integrity and then on Create. You will see something like what is shown in Figure 10.13.

click to expand
Figure 10.13: Relationship shown as partially hidden line.

The line connecting categories and warnings goes behind the favorites. You can click and drag the categories table to be on the other side of the warnings table. This makes the diagram look much better as shown in Figure 10.14.

click to expand
Figure 10.14: Relationship shown after moving table symbols.

Now repeat this process to establish the relationship between favorites and warnings. This is based on the fields named favorite_id. You will see Figure 10.15 holding a complete ER diagram.

click to expand
Figure 10.15: Diagram showing two relationships.

Upload the database to the server.

Inserting Records and Displaying Information

Now, having added tables to the database, you need to implement the scripts to use the tables. What are described here are scripts to add a warning to a selected favorite show, and scripts to display the warnings three different ways.

The logic to insert a warning follows the model of updating the favorites table. The PHP scripts are shown first and then the ASP scripts.

The swarning1.php script is shown in Table 10.15.

Table 10.15: PHP Script for Adding a Warning

<html><head><title>Adding warning</title></head><body>

Usual HTML

<h1>Favorites<br></h1>

HTML heading

<?

Start PHP

require("openfirstdb.php");

Include connection code

$sq ="SELECT * from favorites";

Define query

$rs = mysql_db_query($DBname,$sq,$link);

Invoke the query, setting $rs to be a recordset

print("<table border='1'><tr><td>Titles</td><td>Descriptions</td></tr> ");

Output the table tags

print("\n");

Output a line break for the HTML

while ($row=mysql_fetch_array($rs)) {

while loop: this will iterate through all the records (rows)

print("\n");

Output a line break

print("<tr><td>");

Output table tags

print("<a href='swarning2.php?whichtd" width="44%" align="left">

Output the start of an a tag

print($row["favorite_id"]);

Output the favorite_id to be passed along via the query string

print("'>");

Output the close of the a tag

print($row["title"]);

Output the title field for the favorite

print("</a>");

Output the close of the a tag

print("</td><td>");

Output table tags

print($row["description"]);

Output the description field of the favorite

print("</td></tr>");

Output the table tags

} // end while

End the while loop

print("</table>");

Output the closing table tag

print("Click on favorite to add a warning.") ;

Output instructions

?>

Close PHP

</body></html>

Usual HTML close

The next script ( swarning2.php shown in Table 10.16) “knows” the favorite for which the warning is to be inserted. It generates a form containing an input textbox for the user to enter a reason and a set of radio buttons for selecting the category of the warning.

Table 10.16: PHP Script for Adding Warning to Specified Favorite Title

<html><head><title>Add warning concerning favorite</title></head><body>

Usual HTML start

<?php

Start PHP

require("openfirstdb.php");

Include connection code

$query="Select * FROM favorites where favorite_id=$whichid";

Define query using the favorite_id passed along via the query string

$rs = mysql_db_query($DBname,$query, $link);

Invoke the query setting $rs

$row=mysql_fetch_array($rs);

Fetch the first and only row of $rs

print("Title: " . $row["title"]);

Output the title

print(" Description: ". $row["description"]);

Output the description

?>

End PHP (This is done to illustrate going out of and into PHP)

<form action="swarning3.php">

The form tag

<?

Re-start PHP

print("Reason for warning: <input type='text' name='reason'>");

Output input tag for user to enter the reason

$query="Select * from categories";

Define $query to be a query to get all the categories

$rs = mysql_db_query($DBname,$query, $link);

Invoke the query

while ($row=mysql_fetch_array($rs)) {

while loop: will iterate through all the categories

print("<input type='radio' name='cat' value='" . $row['cat_id']."'>" . $row['cat_desc']);

Output radio button with value the cat_id and the text next to the radio button cat_desc

}

End while loop

print("<input type='submit' value='Insert Warning'>");

Output Submit button

print("<input type='hidden'

Output as hidden tag the favorite_id

name='whichid' value='$whichid'>");

value

print("</form>");

Output close of form

print("<br> Enter reason, choose category and click on Insert Warning button. ");

Output instructions

mysql_close($link);

Close connection

?>

End PHP

</body></html>

Usual HTML close

The last script ( swarning3.php shown in Table 10.17) does the actual insertion using the information passed from the form in swarning2.php. If for some reason the favorite identifier or the category identifier did not point to existing records in their respective tables, the update operation would fail. Presumably that could not occur based on how these values are calculated. However, in an application involving multiple users, this could conceivably happen.

Table 10.17: PHP Script to Insert Warning for Favorite

<html><head><title>Insert warning for a favorite </title></head><body>

Usual HTML start

<?php

Start PHP

require("openfirstdb.php");

Include connection code

$query = "INSERT ignore into warnings values ($whichid,$cat,'$reason')";

Define query using data passed from the previous script

print("query is $query <br>");

For debugging

$result = mysql_db_query($DBname,$query, $link);

Invoke query

if ($result) { print("Update successful.<br>\n"); }

Check return: this is positive outcome

else { print ("Update not successful. <br>\n");}

Negative outcome

mysql_close($link);

Close connection

print("<a href='swarning1.php'>Add another warning</a>");

Output link so user can go back to previous script to enter another warning

?>

Close PHP

</body></html>

Usual HTML close

To display all the warnings with the titles, descriptions, reasons and category description, you need to create a query joining all three tables. This is a constant query. That is, no user input is required. However, it is a complex one. All three tables are being used. The where clause joins the tables together. Alternatively, you could use on clauses. The PHP script is shown in Table 10.18.

Table 10.18: PHP Script to Display All Warnings

<html><head><title>Favorites with warnings </title></head><body>

Usual HTML start

<h2>Favorites with warnings </h2><br>

HTML heading

<table border='1'>

Start of table

<tr><td>Category</td><td>Titles </td><td>Descriptions </td><td>Reason</td></tr>

Table tags continued

<?php

Start PHP

require("openfirstdb.php");

Include connection code

$query="Select c.cat_desc, f.title, f.description, w.reason ";

Define query. This time use the ability to concatenate onto an existing string

$query .=" from favorites as f, warnings as w, categories as c ";

Uses the .= operator to add on

$query .="where c.cat_id = w.cat_id and f.favorite_id = w.favorite_id ";

Add on more

$query .=" order by c.cat_desc";

Add on more

$result=mysql_db_query($DBname, $query, $link);

Invoke query

while ($row=mysql_fetch_array($result)) {

while loop: will iterate through each record in the recordset

print ("<tr><td>");

Output table tags

print($row['cat_desc']);

Output the category description field

print("</td><td>");

Output table tags

print($row['title']);

Output the title field

print("</td><td>");

Output table tags

print ($row['description']);

Output the description field

print("</td><td>");

Output table tags

print($row['reason']);

Output the reason field

print("</td></tr>");

Output the table tags closing the last item and the row

}

Close of while loop

mysql_close($link);

Close connection

?>

End PHP

</table>

Closing table tag

</body></html>

Usual HTML close

Your users might want to see warning information displayed in a different way. The following script shows all favorites with a count of the number of warnings for each favorite. You use one of the same conditions as in the previous case, but in the form of an on condition. Notice that you do not need to mention the categories table. In this script, if a favorite does not have any warnings, it still is included in the display. The trick to getting such records is to use the SQL facilities left join.The SQL uses the group by clause, as explained in the previous section, to aggregate all the records with a single favorite_id. The summary function count uses a field that will have null entries for any records from the other table that do not have matches. The count function does not “count” these records. If you used count(*), it would produce a count of 1 for such records. At this point, we apologize for naming the description field of the categories table cat_desc. The desc that you see in the query refers to descending order. This program (shown in Table 10.19) gives the favorites with the most warnings first and ends with any favorites with no (0) warnings.

Table 10.19: PHP Script to Display All Favorites with Counts of the Warnings

<html><head><title>Current Favorites </title></head><body>

Usual HTML start

<h2>Favorites with warnings </h2>

HTML heading

<table border='1'>

Table tag

<tr><td>Titles </td><td>Descriptions </td><td>Number of warnings</td></tr>

Table tags for top of table

<?php

Start PHP

require("openfirstdb.php");

Include connection code

$query="Select f.title, f.description, count(w.favorite_id) as cnt ";

Define query. It will be done over several statements

$query .=" from favorites as f left join warnings as w ";

Add on to query

$query .="on f.favorite_id=w.favorite_id group by f.favorite_id ";

Add on to query

$query .=" order by cnt desc";

Add on to query

$result=mysql_db_query($DBname, $query, $link);

Invoke the query

while ($row=mysql_fetch_array($result)) {

While loop: this will iterate over each row in the recordset. This will be each favorite with the common title and description values and the calculated cnt field with the count

print ("<tr><td>");

Output table tags

print($row['title']);

Output the title field

print("</td><td>");

Output table tags

print ($row['description']);

Output the description field

print("</td><td>");

Output table tags

print($row['cnt']);

Output the cnt field

print("</td></tr>");

Output table tags

}

Ends while loop

mysql_close($link);

End connection

?>

End PHP

</table>

Closing table tag

</body></html>

Usual HTML close

The last PHP scripts give the user a chance to select the category for the warnings. Two scripts are used. The first script (shown in Table 10.20) presents the categories as radio buttons in a form.

Table 10.20: PHP Script Presenting Chance to Select Category

<html><head><title>Pick category of warning and show any favorites</title></head><body>

Usual HTML start

<h2>Pick category </h2><br>

HTML heading

<form action="spickcat2.php">

HTML form tag

<?php

Start PHP

require("openfirstdb.php");

Include connection code

$query="Select * from categories";

Define $query

$rs = mysql_db_query($DBname,$query, $link);

Invoke query setting $rs

while ($row=mysql_fetch_array($rs)) {

While loop: this will iterate over each category in the categories table

print("<input type='radio' name='cat' value='" . $row['cat_id']."'>" . $row['cat_desc']);

Output a radio button with value the identifier for the category and text next to the radio button the category description

}

End of while loop

print("<input type='submit' value='Show all favorites with the warning'>");

Output submit button tag

print("</form>");

Output close of form

mysql_close($link);

End connection

?>

End PHP

</body></html>

Usual HTML close

The second script, the handler shown in Table 10.21, displays all the favorites with warnings in the selected category. Notice that the script includes several links for the user to choose among for a follow-up operation.

Table 10.21: PHP Script to Show Favorites with Warnings in Selected Category

<html><head><title>Current Favorites </title></head><body>

Usual HTML start

<?php

Start PHP

require("openfirstdb.php");

Include connection code

$query="Select f.title, f.description, w.reason ";

Define $query. This will be done in parts

$query .=" from favorites as f, warnings as w, categories as c ";

Add on

$query .="where c.cat_id = w.cat_id and f.favorite_id = w.favorite_id ";

Add on

$query .=" and c.cat_id=$cat";

Add on. The $cat is the value passed in from the form script

$result=mysql_db_query($DBname, $query, $link);

Invoke query

?>

End PHP

<table border='1'>

Table start

<tr><td>Titles </td><td>Descriptions </td><td>Reason</td></tr>

First row of table

<?

Start PHP

while ($row=mysql_fetch_array($result)) {

While loop: this will iterate over records returned in recordset $result. This will be all the warnings within the category requested

print ("<tr><td>");

Output table tags

print($row['title']);

Output title field

print("</td><td>");

Output table tags

print ($row['description']);

Output description field

print("</td><td>");

Output table tags

print($row['reason']);

Output reason field

print("</td></tr>");

Output table tags

}

End while loop

mysql_close($link);

Close connection

?>

End PHP

</table>

Table closing tag

<ul>

HTML unordered list

<li><a href="showfavoriteswithwarnings. php">Show all favorites with warnings</a>

Link to show all the warnings

<li><a href="showfavorites.php">Show all favorites</a>

Link to show all the favorites

<li><a href="showallfavoriteswith warningcounts.php">Show favorites with count of warnings </a>

Link to show favorites with warning counts

<li><a href="spickcategory1.php">Show favorites with warnings in one category </a>

Link to request a new category of warnings to be displayed

</ul>

End unordered list

</body></html>

Usual HTML close

Turning to the ASP system, here is the implementation for inserting warning. The strategy is the same: use three scripts and follow the update model. You can try to implement these scripts on your own using the PHP scripts just described and the ASP update scripts.

Table 10.22 shows swarning1.asp.

Table 10.22: ASP/JavaScript for Showing Favorites for Adding a Warning

<%@ Language=JavaScript %>

Define language

<html><head><title>Show favorites </title></head><body>

Usual HTML start

<!— #include file="sopenconn.asp" —>

Include connection code

<%

Start ASP

var query="Select * from favorites";

Define query

var result = Server.CreateObject ("ADODB.RecordSet");

Define result to hold a recordset

result.Open(query,Conn);

Invoke query

Response.Write("<table border='1'>");

Output table tag

Response.Write("<tr><td> Titles </td><td>Descriptions</td></tr>");

Output first row of table

while (!result.EOF) {

While loop: it will iterate until recordset is exhausted

Response.Write("\n");

Output line break in the HTML source

Response.Write("<tr><td><a href='swarning2.asp?whichtd" width="31%" align="left">

Output table tags plus start of a tag with start of query string

Response.Write(result.fields.item ("favorite_id"));

Output favorite_id field

Response.Write("'>");

Output close of a tag

Response.Write(unescape(result.fields.item("title")) + "</a></td><td>");

Output title field. Note use of unescape to remove any escape characters

Response.Write(unescape(result.fields.item("description")) + "</td></tr>");

Output description field

result.move(1);

Advance to next record in recordset

}

Close of while loop

Response.Write(" </table>");

Output table closing tag

Conn.Close();

Close connection

%></body></html>

End ASP and usual HTML closing tags

The next script, swarning2.asp shown in Table 10.23, presents a form in which the user can select the category for the warning by selecting one of a set of radio buttons and enter a reason for the warning.

Table 10.23: ASP/JavaScript for Adding a Warning for a Specified Favorite Title

<%@ Language=JavaScript %>

Define language

<html><head><title>Enter warning </title></head><body>

Usual HTML start

<!— #include file="sopenconn.asp" —>

Include connection code

<%

Start ASP

var whichid = parseInt(Request.QueryString("whichid"));

Get the favorite_id, which was passed along via the query string

var query="Select * FROM favorites where favorite_td" width="41%" align="left">

Define query using the whichid variable

var result = Server.CreateObject("ADODB.RecordSet");

Define result to hold a recordset. (Note: there is only one row in this recordset)

result.Open(query,Conn);

Invoke the query

var title=result.fields.item("title");

Define title to be the title item. (It is the field of the record, but the terminology fields.item is what is used in ASP.)

var desc=result.fields.item ("description");

Define desc to be the description item

Response.Write("Title: " + title);

Output title

Response.Write(" Description: "+ desc);

Output description

Response.Write("<form action='swarning3.asp'>");

Output form tag

Response.Write("Reason for warning: <input type='text' name='reason'>");

Output input tag for user to enter a reason

query="Select * from categories";

Set query to be a new query string

var rs = Server.CreateObject ("ADODB.RecordSet");

Define a new variable for this new recordset

rs.Open(query,Conn);

Invoke the query

while (!rs.EOF) {

While loop: it will iterate through all the categories

Response.Write("<input type='radio' name='cat' value='" +rs.fields.item ("cat_id")+"'>");

Output input radio tag holding the category id for the value

Response.Write(rs.fields.item ("cat_desc"));

Output the category description to be the text next to the radio button

rs.move(1);

Advance to the next category

}

Close while loop

Response.Write("<input type='submit' value='Insert Warning'>");

Output submit button

Response.Write("<input type='hidden' name='whichid' value="+whichid+">");

Output hidden tag to hold the value of the favorite_id

Response.Write("</form>");

Output form close

Response.Write("<br> Enter reason, choose category and click on Insert Warning button. ");

Output instructions

Conn.Close();

Close connection

%></body></html>

Close ASP and usual HTML close

The last of the three scripts, swarning3.asp shown in Table 10.24, completes the process by making the insertion into the warnings table.

Table 10.24: ASP/JavaScript to Perform Actual Insertion of Warning

<%@ Language=JavaScript %>

Define language

<html><head><title>Complete insertion </title></head><body>

Usual HTML start

<!— #include file="sopenconn.asp" —>

Include connection code

<%

Start ASP

var whichid = parseInt(Request. QueryString("whichid"));

Define whichid from the hidden form input. This is the favorite_id

var cat = parseInt(Request("cat"));

Define cat from the form input

var reason = escape(String(Request ("reason")));

Define reason from the form input. Note that it needs to be “escaped”

var fields = "(favorite_id, cat_id, reason)";

Define fields. This is to cut down on the complexity of the next line

query = "INSERT into warnings "+fields+" values (" + whichid+", " + cat+",'"+reason+"')";

Define query

if (Conn.Execute(query))

Invoke query and do if test to check on outcome

{Response.Write("<br>Warning was successfully entered. <br>");}

Positive outcome

else {Response.Write("Warning was NOT entered.<br>"); }

Negative outcome

Conn.Close();

Close connection

Response.Write("<a href=\"swarning1. asp\"> Submit another warning. </a><br>");

Output link to go back and submit another warning

%></body></html>

Close ASP. Usual HTML close

The ASP scripts for the display of the warning information follow. Table 10.25 shows the script displaying all the titles with warnings.

Table 10.25: ASP/JavaScript for Displaying All Titles with Warnings

<%@ Language=JavaScript %><html><head><title>Current Favorites </title></head><body>

Define language and Usual HTML start

<h2>Favorites with warnings </h2><br>

HTML heading

<table border='1'>

Start of table

<tr><td>Category</td><td>Titles </td><td>Descriptions </td><td>Reason</td></tr>

First line of table

<!— #include file="sopenconn.asp" —>

Include connection code

<%

Start ASP

var query="Select c.cat_desc, f.title, f.description, w.reason ";

Define query. It is done in steps because of complexity

query +=" from favorites as f, warnings as w, categories as c ";

Add on. Note use of synonyms (single letters)

query +="where c.cat_id = w.cat_id and f.favorite_id = w.favorite_id ";

Add on. These are the two joining conditions

query +=" order by c.cat_desc";

Add on. This is the order condition

var result = Server.CreateObject("ADODB.RecordSet");

Create result to hold a recordset

result.Open(query,Conn);

Invoke the query

while (!result.EOF) {

While loop: this will iterate until the result is exhausted

Response.Write("\n");

Output a line break

Response.Write("<tr><td> " + unescape (result.fields.item("cat_desc")));

Output table tags plus the cat_desc field

Response.Write("</td><td>");

Output table tags

Response.Write(unescape(result.fields. item("title")));

Output the title field

Response.Write("</td><td>");

Output table tags

Response.Write(unescape(result.fields. item("description")));

Output the description field

Response.Write("</td><td>");

Output table tags

Response.Write(unescape(result.fields. item("reason")));

Output the reason field

Response.Write("</td></tr>");

Output table tags

result.move(1);

Advance to the next record

}

Close while loop

Response.Write(" </table>");

Output the closing table tag

Conn.Close();

Close the connection

%></body></html>

Close ASP and usual HTML close

The next script (shown in Table 10.26) is the ASP file to display all favorites with count of warnings. The SQL in this program is slightly different from the PHP version. The SQL of Access does not allow the synonym cnt to be used in the order by clause. The SQL of Access also requires all the fields for grouping to be named in the group by clause, and all fields named in the group by clause to be among the selected fields.

Table 10.26: ASP/JavaScript for Displaying All Favorites with Counts of the Warnings

<%@ Language=JavaScript %>

Define language

<html><head><title>Current Favorites </title></head><body>

Usual HTML start

<h2>Favorites with warnings </h2><br>

HTML header

<table border='1'>

HTML table tag

<tr><td>Titles </td><td>Description </td><td>Number of Warnings</td></tr>

Table tags for first row

<!— #include file="sopenconn.asp" —>

Include connection code

<%

Start ASP

var query="SELECT f.title, f.description, count(w.favorite_id) AS cnt ";

Define query. This will be done over several steps. Note the synonym cnt. This will be used later

query +=" FROM favorites AS f LEFT JOIN warnings as w ";

Add on to the query. This part defines the join as a left join, meaning the non-matched records will be represented in the final recordset

query +=" ON f.favorite_id = w.favorite_id GROUP BY f.title, f.description ";

Add on. This includes the group by clause. (Note that both f.title and f.description need to be mentioned)

query +=" ORDER BY count(w.favorite_id) DESC";

Add on. This includes the order by clause. Note that the complete expression for the aggregate function is cited. The desc stands for descending order

var result = Server.CreateObject ("ADODB.RecordSet");

Create result to hold a recordset

result.Open(query,Conn);

Invoke the recordset

while (!result.EOF) {

While loop: this will iterate over the records, namely one for each favorite

Response.Write("\n");

Output a line break

Response.Write("<tr><td> ");

Output table tags

Response.Write(unescape(result.fields.item("title")));

Output the title field

Response.Write("</td><td>");

Output table tags

Response.Write(unescape(result.fields.item("description")));

Output the description field

Response.Write("</td><td>");

Output table tags

Response.Write(unescape(result.fields.item("cnt")));

Output the cnt field. This is the synonym for the count aggregate operation defined in the query

Response.Write("</td></tr>");

Output table tags

result.move(1);

Advance to the next record

}

Close the while loop

Response.Write(" </table>");

Output table close

Conn.Close();

Close the connection

%></body></html>

End ASP and usual HTML close

The next scripts for the ASP system give the user a chance to select the category of warnings. The first script, shown in Table 10.27, presents a form with radio buttons as before.

Table 10.27: ASP/JavaScript for Displaying Categories

<%@ Language=JavaScript %>

Define language

<html><head><title>Pick category of warning and show any favorites</title></head><body>

Usual HTML start

<h2>Pick category </h2><br>

HTML header

<form action="spickcat2.asp">

Form tag

<!— #include file="sopenconn.asp" —>

Include connection code

<%

Start ASP

query="Select * from categories";

Define query to get all categories

var rs = Server.CreateObject ("ADODB.RecordSet");

Define rs to hold a recordset

rs.Open(query,Conn);

Invoke query

while (!rs.EOF) {

While loop: this will iterate until rs is exhausted

Response.Write("<input type='radio' name='cat' value='" +rs.fields.item ("cat_id")+"'>");

Output radio button. The value will be the cat_id

Response.Write(rs.fields.item ("cat_desc"));

Output cat_desc next to radio button

rs.move(1);

Advance to next category

}

End while loop

Response.Write("<input type='submit' value='Show all favorites with this warning'>");

Output Submit button

Response.Write("</form>");

Output end of form

Conn.Close();

Close connection

%></body></html>

End ASP and usual HTML close

The handler for the form, shown in Table 10.28, presents the favorites with warnings of the specified category. It differs from the PHP version. See the Exercises section for a challenge.

Table 10.28: ASP/JavaScript to Display Favorites with Warnings in Specified Category

<%@ Language=JavaScript %>

Define language

<html><head><title>Current Favorites </title></head><body>

Usual HTML head

<h2>Favorites with warnings </h2><br>

Heading

<table border='1'>

Table tag

<tr><td>Titles </td><td>Descriptions </td><td>Reason</td></tr>

Table first row

<!— #include file="sopenconn.asp" —>

Include connection code

<%

Start ASP

var cat = parseInt(Request("cat"));

Define cat from form input

var query="Select f.title, f.description, w.reason ";

Define query. This will be done in several steps

query +=" from favorites as f, warnings as w, categories as c ";

Add on

query +="where c.cat_id = w.cat_id and f.favorite_id = w.favorite_id ";

Add on

query +=" and c.cat_td" width="32%" align="left">

Add on. This includes the cat_id value passed in from form on prior script

var result = Server.CreateObject ("ADODB.RecordSet");

Define result to hold a recordset

result.Open(query,Conn);

Invoke query

while (!result.EOF) {

While loop: will iterate over all records

Response.Write("\n");

Output line break

Response.Write("<tr><td>");

Output table tags

Response.Write(unescape(result.fields. item("title")));

Output title field

Response.Write("</td><td>"); Response.Write(unescape(result.fields.item ("description")));

Output table tags and description field

Response.Write("</td><td>");

Output table tags

Response.Write(unescape(result.fields.item("reason")));

Output reason field

Response.Write("</td></tr>");

Output table tags

result.move(1);

Advance to next record

}

Close while loop

Response.Write(" </table>");

Output table close

Conn.Close();

Close connection

%></body></html>

End ASP and Usual HTML close




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