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.
| <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.
| <%@ 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.
| <%@ 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.
| <%@ 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.
| <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 |
| <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.
| <%@ 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.
| <%@ 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 |
| <%@ 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.
| <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.
| <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.
| <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.
| <?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.
| <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.
Figure 10.4: Access window.
Now, click on Relationships to see the drop-down menu shown in Figure 10.5.
Figure 10.5: Options under Tools.
After clicking on Relationships, you see the Window shown in Figure 10.6. Click on View.
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.
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.
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….
Figure 10.9: Edit Relationships window.
You will see the window shown in Figure 10.10 for specifying the table names and field names.
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.
Figure 10.11: Completed entry to specify new relationship.
Click OK. You then see the window in Figure 10.12.
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.
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.
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.
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.
| <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.
| <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.
| <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.
| <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.
| <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.
| <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.
| <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.
| <%@ 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.
| <%@ 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.
| <%@ 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.
| <%@ 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.
| <%@ 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.
| <%@ 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.
| <%@ 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 |
EAN: 2147483647
Pages: 125