This section includes sample code in PHP and ASP for a simple database application. To further your understanding of middleware development, even though the examples are contrived, you will see examples of code that would never be included in a “real” application, and code with mistakes, which we will debug.
On the CD The project demonstrated here is a catalog of favorite TV shows. The CD-ROM contains the code for the projects in the folder named chapter9code. This project is developed further in the next chapter.
The steps suggested for the PHP project are:
Make arrangements with your ISP/Web host for you to have access to a MySQL database.
Write the connection code script. This will be an included file. The require function will be used in the other files to include the connection script.
Write the script to create the table for the database. This will be a test of the included file.
Run the create table file again. This will be a test of the internal error catching.
Write a script to insert a specific TV show. This is what is termed “throw-away code.” It would not be part of the “real” application, but it will demonstrate the basics of insertion.
Write a script that displays the contents of the database.
Write a script that generates a form for a new entry to the favorites table and the handler for the form. Test the program with different titles and descriptions.
Let us assume that a database has been created on the server with the name firstdb and you have access to it as user id=’curley’ and password=’12345’. The host will be “localhost”, because this access will be in PHP scripts run on the same server as the database. Create the following file, with the appropriate changes in user ID and password:
<?php $host="localhost"; $user="curley"; $password="12345"; $DBname="firstdb"; $link=mysql_connect($host,$user,$password); ?>
The code in this file does not use $DBname, but it sets it and also sets $link. These will be the two values used in the code in the files that include this file.
Save this file with the name openfirstdb.php. Upload this file to your folder on the server.
Now you need to write the script to create a table, the only table, in the database. The following script will do the job (after you make the change to go to the name of your folder on the server instead of the name of our folder on our server):
<html> <head> <title>Creating favorite table </title> </head> <body> <h3> Creating the favorites table for firstdb </h3> <br> <?php print ("about to make connection"); require("openfirstdb.php"); $tname = "favorites"; $fields = "favorite_id INT UNSIGNED NOT NULL AUTO_INCREMENT"; $fields = $fields . "PRIMARY KEY, title char(50) NOT NULL, "; $fields = $fields . "description char(50) NOT NULL"; $query="CREATE TABLE $tname ($fields)"; if (mysql_db_query($DBname,$query,$link)) { print ("The table, $tname, was created"); print (" successfully.<br>\n"); } else { print ("The table, $tname, was not created."); print (" <br>\n"); } mysql_close($link); ?> </body></html>
This code makes the assumption that $link has been defined to be the connection to the database. The if construction will let you know if the table was successfully created, because the function call mysql_db_query returns true or false based on what happens in the MySQL driver performing this query.
It is possible to avoid defining $tname and $fields as separate variables. In fact, you could even avoid defining $query and put everything in the mysql_db_query statement. The approach chosen here is to use simpler statements even if there are more of them rather than more complex statements. If you have any problems, PHP displays a line number, and so you are better off with more lines.
You could use a more general approach to the task of creating tables:
Function for creating a table of a given name and with specified fields
Construct query using arguments to function
Invoke query, use if test to check success
(end of function)
Make connection
Construct string representing field definitions
Call function with table name and field string
Close connection
The code for this approach is the following:
<?php function createtable($tname,$fields) { global $DBname, $link; $query="CREATE TABLE $tname ($fields)"; if (mysql_db_query($DBname,$query,$link)) { print ("The table, $tname, was created"); print (" successfully.<br>\n"); } else { print ("The table, $tname, was not created. <br>\n"); } } ?> <html> <head><title>Creating favorite table </title> </head> <body> <h3> Creating the favorites table for firstdb </h3> <br> <?php print ("about to make connection"); require("openfirstdb.php"); $tname = "favorites"; $fields = "favorite_id INT UNSIGNED NOT NULL"; $fields = $fields . " AUTO_INCREMENT PRIMARY KEY, "; $fields = $fields . "title char(50) NOT NULL, "; $fields = $fields . "description char(50) NOT NULL"; createtable($tname, $fields); mysql_close($link); ?>
This last script makes use of a function for creating the table. The statement:
global $DBname, $link;
tells the PHP interpreter to use the existing values for these variables and not define new variables, what are called local variables, for use just within the function. So, why go to the trouble of defining and using a function when it does seem to be more trouble? The answer is not clear-cut. This particular example, with just one table, does not justify using a function. However, in later applications, you will have a database with more than one table, and you can copy this one.
Upload this file to the server and try it out. You should see the message that the table was successfully created. If it does not work, check your code and consult with your server support crew. Assuming that your problems do not involve permissions, you might be able to find a problem by adding print statements to your script. For example, put in the line:
print("The query is: $query");
right before the if statement with the call to mysql_db_query. You will see the query in the HTML statement. If there is a problem with the query, you probably will be able to detect and fix it.
Moving on, run the create table script again. What happens? Hopefully, you will get the message that the table was not created. This is because the MySQL system notices that you already have a table by the name of “favorites” and will not let you create another one. This is a good result. It shows that the if test works both ways.
Now you will write a script for putting a single, specific record into the database. The TV show ER was chosen because it was the shortest possible name. Please make your own selection. The code with explanation is given in Table 9.1.
<html><head><title>Input and submit favorites to firstdb database </title></head><body> | HTML tags |
<?php | Start PHP |
require("openfirstdb.php"); | Connecting code |
$tname="favorites"; | Set name of table |
$title="ER"; | Set name of specific show |
$description="medical drama"; | Set description |
$query = "INSERT INTO $tname VALUES('0', '$title', '$description')"; | Set the query |
$result = mysql_db_query($DBname, $query, $link); | Invoke the query |
if ($result) { | Check result |
print("The favorite was successfully added.<br>\n"); } | Print message indicating success |
else {print (“The favorite was NOT successfully added. <br>\n");} | Print message indicating failure |
mysql_close($link); | Close connection |
?> | End PHP |
</body></html> | Closing HTML tags |
Notice the presence of the single quotation marks in the query statement. The MySQL software requires the quotation marks. Again, you can avoid using variables and put everything into one statement, but the complexity of the syntax makes this a better tactic for avoiding typos. Upload and try this program. Again, if you have problems, check the value of $title, $tname, and $query using print statements. The most likely error is a typo involving the punctuation.
If the program displays the message that the favorite was successfully added, you really cannot be certain that your program works! All you know is that it did not fail with a system error. To determine for sure that you have made an entry to the database, you need to write a script to display the contents. This is the next step.
The plan for the showfavorites script will be to use the simplest select query: select * from favorites. This will produce a recordset made up of one row for each record in the table. You will use special functions for working with recordsets, to be explained later in the context of the script. The script starts out like the previous ones, with HTML and then with PHP. The require command is used to connect to the database.
<html> <head> <title>Showing the contents of the favorites table </title></head> <body> <h1>Favorite shows <br></h1> <? require("openfirstdb.php");
The next lines of code set up and then make the query to the database. The $rs variable holds the recordset.
$sq ="SELECT * from favorites"; $rs = mysql_db_query($DBname,$sq,$link);
The rows (there is probably just one row at this point) are to be displayed as rows of an HTML table. Your code needs to output the HTML to start the table. This means the <table> tag and the first row holding heading information.
print("<table><tr><td> Titles</td>"); print("<td>Descriptions</td> </tr> ");
Now you need to get a row of the recordset, extract from it the title and description fields, and display each of these with <td> and </td> tags of a new row of the table. The function to “get” a row is mysql_fetch_array. Recordsets are special things that come with an implicit pointer to the current row. The mysql_fetch_array function does two things: it returns the current row as an array, and it advances the pointer to the next row. If there are no more rows, the function returns a value that an if statement or a while statement interprets as false. This means that you can use a while loop to go through the rows of the recordset.
The array is an associative array. This means that the elements can be extracted using the names of the fields. The array has three elements, but the code ignores the favorite_id element. You do need to print out the tag to start the row and the tag to end it. It is not strictly necessary, but it is useful to print out this HTML with line breaks for every row of the recordset (which is also every row of the HTML table). The command to do that is:
print("\n");
The "\n" is the ASCII code for line break. Try your script without this and you will see what its function is.
The rest of the show favorites script follows:
while ($row=mysql_fetch_array($rs)) { print("\n"); print("<tr><td>"); print($row["title"]); print("</td><td>"); print( $row["description"]); print("</td> </tr>"); } // end while print("</table>"); ?> </body> </html>
Upload this file and test it. You should now modify your script for adding a specific favorite to add another favorite. Upload and run it, and then run the showfavorite script again. You now have a system for inserting entries into the database and displaying them. However, it does require the services of someone who can write PHP. You will fix that in the next step: creating a script for showing the table, and creating a form for users to enter a new favorite title and description into the database.
Before continuing, you might want to try and write this script yourself. You now know how to do it, making use of the previous scripts in this chapter and copying parts of the state capital quiz in a prior chapter. Think about what you need for this task:
A script that presents a form and then serves as the handler for the form. The state capital quiz scripts serve as models.
The code that extracts form input data. Again, the state capital quiz scripts demonstrate how this is done.
Code for inserting something in a database. The script in this chapter in which we inserted ER is one model. You need to create a query that has constant parts, and parts that are variables that arise from form data.
Code for displaying the contents of a table in the database. This is the show script developed earlier in this section.
The code for saddfavorites.php is provided in Table 9.2.
<html><head><title>Input and submit favorites to firstdb database </title></head> | Usual HTML tags and text |
<body> | HTML body tag |
<?php | Start PHP |
require("openfirstdb.php"); | Function to include the script for connecting to the database |
if (@($submitted)) { | If test to see if this is for handling the form or presenting the form. The true clause is handling the form. The @ prevents an error message if $submitted has not been set |
$valuesx = " VALUES ('0','" . $title . "', '" . $description . "')"; $valuesx | Uses form data to define the variable |
$query="INSERT INTO favorites " . $valuesx; | Defines $query. This could have been combined with the prior step |
print("Insert query is: " . $query); | Just in case there are any problems, displays the query. Remove when everything is working |
$result = mysql_db_query($DBname, $query, $link); | Invokes the MySQL software to do the query. $result will be a return code |
if ($result) { | Check return code |
print("The favorite was successfully added.<br>\n"); | True clause causes a successful message to be displayed |
} | Closes the true clause |
else { | Start of else (not successful) clause |
print ("The favorite was NOT successfully added. <br>\n"); | Print that favorite was not added |
} | End else clause |
$submitted = FALSE; | Set $submitted to false for next time |
mysql_close($link); | Close link to database |
print ("<a href=\"saddfavoriter. php\">Submit another favorite </a><br>"); | Display link (a tag) to enter new favorite. Note direction of slashes |
} //ends if submitted | Ends the true clause checking if this is the handler |
else { | Start else clause: this is the clause to display the form |
?> | Get out of PHP |
<h1>Add a title to the databank of favorite shows. <br></h1> | Regular HTML. You could use a print statement |
<? | Resume PHP code |
$sq ="SELECT * from favorites"; | Define $sq as constant query to get all of favorites table |
print("<h3>Favorites</h3><br>"); | Print heading |
$rs = mysql_db_query($DBname,$sq,$link); | Perform the select query. $rs will be a recordset |
print("<table><tr><td> Titles</td><td>Descriptions</td></tr> "); | Print HTML tags for start of table |
print("\n"); | Put a line break in the HTML source |
while ($row=mysql_fetch_array($rs)) { | Start while loop, which extracts a row at a time from the recordset. This will fail when recordset is exhausted (no more rows, that is, no more records) |
print("\n"); | Put a line break in the HTML source |
print("<tr><td>"); | Print starting row, and starting td tags |
print($row["title"]); | Print the title field of the record |
print("</td><td>"); | Print the closing td tag and a starting td tag |
print($row["description"]); | Print the description field of the record |
print("</td></tr>"); | Print closing td and closing tr tags |
} // end while | End the while |
print("</table>"); | Print the closing table tag |
?> | End PHP code |
<form action="saddfavoriter.php" method="POST"><br> | Regular HTML for a form: form tag |
Title of show <input type=text name="title" size=50><br> | Input tag for title |
Description <input type=text name="description" size=50><br> | Input tag for description |
<input type=hidden name="submitted" value="True"><br> | Input tag for hidden item: this will be the $submitted that is checked to see if a form has been submitted |
<input type=submit name="submit" value="Submit favorite!"><br> | Input tag for Submit button |
</form> | Prints the closing form tag |
<? | Start PHP |
} //ends else clause for submitting form ?> | Ends the else clause (of the if(@$submitted) test End PHP |
</body> | Regular HTML close body tag |
</html> | Regular HTML close HTML tag |
Hopefully, you will copy this script accurately and get it working. Try it with different favorites. If you run into difficulty, continue reading to the end of this section.
To demonstrate what errors can look like, make an error on purpose. Suppose that someone entered the preceding script but made the following two typos:
Change the line outputting the title field to have an extra closing parenthesis:
print($row["title"]));
Omit the line printing the closing table tag:
print ("</table>");
If you named this file saddfavoritesbug.php, uploaded and invoked it, you would see the error message indicated in the next two lines.
Parse error: parse error, unexpected ')' in
D:\inetpub\wwwroot\jeanine\saddfavoritesbug.php on line 32
Count down 32 lines and you will reach (no surprise) the line with the extra parenthesis. This is a very common error. Get into the practice of matching opening and closing parentheses, curly brackets, and tags. Hopefully, if something like this happens, you would examine the statement and notice that the opening and closing parentheses do not match. Fix this error and try again. This time, the problem is subtler. You might see something like Figure 9.9.
Figure 9.9: Screen capture showing misplaced table.
This would vary with different browsers. In this case, the table is misplaced. Instead of being before the form, it is after the form. Sometimes it is helpful to look at the HTML source. You, using a browser to navigate to a PHP or ASP file, cannot examine the file itself. However, you can invoke View Source to look at the HTML that the middleware produces. For this example, it would be the following:
<html> <head> <title>Input and submit favorites to firstdb database </title> </head> <body> <h1>Add a title to the databank of favorite shows. <br></h1> <h3>Favorites</h3> <br> <table> <tr><td> Titles</td> <td>Descriptions</td> </tr> <tr><td>The Sopranos</td> <td>one man's two families</td> </tr> <tr><td>ER</td><td>medical drama</td> </tr> <tr><td>Avonlea</td><td>sweet period piece</td> </tr> <form action="saddfavoritesbug.php" method="GET"><br> Title of show <input type=text name="title" size=50> <br> Description <input type=text name="description" size=50> <br> <input type=hidden name="submitted" value="True"><br> <input type=submit name="submit" value="Submit favorite!"><br> </form> </body> </html>
Note that we have added line breaks to make it more readable.
Again, hopefully, after noticing the misplaced table in the display, when you examine the HTML source (the HTML produced by the PHP script), you will notice the absence of the closing tag </table>. Fix this and try again.
Now we turn to the ASP example. The ASP project has similar, but not exactly the same, steps as the PHP project.
On your own computer, create an Access database. Put a couple of records into the table. Upload it to the server.
Write the code for connecting to the database. You will use the DSN-less method.
Test the connection code by writing a simple show program.
Write a script for inserting a specific new record into the table. This is throwaway code.
Write a form and the handler for the form for inserting new favorites. This will make use of the prior scripts. Test with different examples.
It will turn out that problems arise in the ASP case when the input contains symbols such as apostrophes. We will debug and fix the problem.
Download the database and examine the records. You can make additions, deletions, and changes to the records. Upload the modified file back to the server. This option is more reasonable for Access than for MySQL, given the graphical user interface of Access.
The first step repeats what was described in the previous chapters. Create a database with a single table, called favorites, with three fields:
favorite_id
title
description
You can enter a couple of records. Upload it to the server. The second step is to create the file that will establish the connection:
<% Conn = Server.CreateObject("ADODB.Connection"); Conn.Mode = 3 ; strConnect = "Driver={Microsoft Access Driver (*.mdb)};" + "DBQ=" + Server.MapPath("firstdb.mdb") ; Conn.Open (strConnect, "admin", "") ; %>
The connection mode establishes that you will be doing reading and writing. The Server.MapPath is a way to obtain the complete path to the file. The connection is opened setting the user to be "admin" and with no password. You might need to consult with your service provider. Name the file sopenconn.asp and upload it to the server.
To show all the records in the database, you will send the select * from favorites query to the database and then display the results by putting the information in each row of the recordset into td elements. The commands for manipulating the recordset are different for ASP than for PHP, but the effects are the same. The recordset has an implicit pointer. The code advances the pointer by the method move. So, if result holds the recordset:
result.move(1);
advances to the next record. The property:
result.EOF
will be true if you have reached the end of the recordset, and false, otherwise. Table 9.3 shows the code with explanation.
<%@ Language=JavaScript %> | Set language to JavaScript |
<html><head><title>Show favorites</title></head> | HTML |
<body> | HTML |
<!— #include file="sopenconn.asp" —> | Call to include the connection file. Note that it is in the form of a comment |
<% | Start ASP |
var query="Select * from favorites"; | Define variable query to be the select query |
Response.Write("query is: " + query); | Display it. Remove when everything works |
var result = Server.CreateObject ("ADODB.RecordSet"); | Define the variable result to hold a recordset |
result.Open(query,Conn); | The open method of a recordset is what acquires the records from the database |
Response.Write("<table>"); | Display HTML opening table tag |
while (!result.EOF) { | Start while loop. The loop body will be executed if result is not at the end |
Response.Write("\n"); | Output a line break to the HTML |
Response.Write("<tr><td> " + result.fields.item("title") + "</td><td>"); | Output the title item of the fields at the current record (row) of result with the appropriate table tags |
Response.Write(result.fields.item ("description") + "</td></tr>"); | Output the description item of the fields of the current record of result, with appropriate table tags |
result.move(1); | Advance the pointer for result |
} | Close the while |
Response.Write(" </table>"); | Output the closing table tag |
Conn.Close(); | Close the connection |
%></body></html> | End ASP and then output the closing HTML tags |
The next step is to write a script that inserts a specific title. Hopefully, the following should be clear to you. The insert query is sent to the database using the Execute method of the connection object:
Conn.Execute(query)
In this example, instead of using an assignment statement to put the result in a separate variable, the call is used directly as the condition to an if statement.
<%@ Language=JavaScript %> <html> <head> <title>Input specific title </title> </head> <body> <!— #include file="sopenconn.asp" —> <% var fields = " (title, description) "; var valuesx = " VALUES ('The Princess Bride','silly')"; var query="INSERT INTO favorites " + fields + valuesx; Response.Write("Insert query is: " + query); if (Conn.Execute(query)) { Response.Write ("<br>Title was successfully entered. <br>"); } else { Response.Write("Title was NOT entered.<br>"); } Conn.Close(); %> </body> </html>
At this point, as in the previous case, you could attempt to do the next step on your own: prepare a script to both display the contents of the favorites table and a form for adding a new title, and handle the form by taking the form data and making the insertion. You have the scripts from the state capital quiz, the script just explained for adding a specific title, and the PHP script to use as models.
Table 9.4 shows the script with explanation.
<%@ Language=JavaScript %> | Set language to JavaScript |
<html><head><title>Input and submit favorites to firstdb database </title></head><body> | Usual HTML |
<!— #include file="sopenconn.asp" —> | Include file with connection code |
<% | Start ASP |
var submitted=String(Request.Form ("submitted")); | Define variable submitted from form data. The function String ensures that the data is a string and testable. It can still be undefined |
if (submitted !="undefined") { | if test to see if the invocation is to handle the form or display it. True clause is for handling the form |
var title = String(Request.Form("title")); | Defines title from form data |
var description= String(Request.Form("description")); | Defines description from form data |
var valuesx = " VALUES ('"+title+"', '" + description +"')"; | Defines valuesx from the variables from form data |
var fields = " (title, description) "; | Defines fields as a constant |
var query="INSERT INTO favorites " + fields + valuesx; | Puts it all together to be the query |
Response.Write("Insert query is: " + query); | Writes out query just in case there are problems. Remove this line when everything works |
if (Conn.Execute(query)) | Sends the query to the database and does a check. True clause means the insertion was successful |
{Response.Write("<br>Title was successfully entered. <br>");} | Displays message indicating success |
else {Response.Write("Title was NOT entered.<br>"); } | else clause: displays message indicating failure |
Conn.Close(); | Close the connection |
Response.Write("<a href=\"saddfavorite. asp\"> Submit another title </a><br>"); | Outputs the HTML for a link to enter a new title |
} // ends if form was submitted. | Ends true if clause |
else { | Start else clause (need to display form) |
%> | Close ASP |
<h1>Add a title to the databank of favorite shows. <br></h1> | Regular HTML: heading |
<% | Start ASP |
var sq ="SELECT * from favorites"; | Define sq to be the select query |
Response.Write("<h2>Favorites</h2>"); | Output heading |
rs=Server.CreateObject ("ADODB.RecordSet"); | Define rs to be a recordset object |
rs.Open (sq,Conn); | The Open method gets the data into the recordset |
Response.Write("<table><tr><td> Titles</td><td>Descriptions</td></tr> "); | Output the HTML for the start of the table |
while (!(rs.EOF)){ | while test: it will loop until the end of the recordset |
Response.Write("<tr><td>" + String(rs.fields.item("title"))+ " </td><td>"+ String(rs.fields.item ("description"))+ "</td></tr>"); | Output HTML tags, title field, and description field |
rs.move(1); | Advance the implicit pointer to the next record of the recordset |
} // end while | End the while |
Response.Write("</table>"); | Output the closing table tag |
%> | End ASP |
<form action="saddfavorite.asp" method="POST"><br> | Regular HTML: the opening form tag |
Title of show <input type=text name="title" size=50><br> | The text and input tag for the title |
Description <input type=text name="description" size=50><br> | The text and input tag for the description |
<input type=hidden name="submitted" value="True"><br> | The input tag for the hidden submitted value |
<input type=submit name="submit" value="Submit favorite!"><br> | The tag for the Submit button |
</form> | Closing form |
<% | Start ASP |
} //ends else clause for submitting form | Close the else clause for the test on if a form was submitted |
%> | Close ASP |
</body></html> | Closing HTML tags |
You are done, following along the path the text has shown you. However, if you now try to enter a title such as The Sopranos with the description set to “one man’s two families,” you will see the following lines on the screen, starting with a debugging message we included in the script.
Insert query is: INSERT INTO favorites (title, description) VALUES
(‘The Sopranos', ‘one man’s two families’) Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression ''one man's two families')'. /jeanine/saddfavorite.asp, line 14
The first line is the “just in case” line indicated in the explanation column. It is not part of the error message. This is an indication that the error was detected at runtime, not before. At this point, since you have already tested this script with other entries, it is safe to assume that your code does not have any syntax (punctuation) problems.
Examine the query as displayed by your code. Do you see the problem? The 14th line contains the call to execute the query. The error message detects a syntax error. It turns out that the problem is that the description field has an apostrophe, otherwise known as a single quotation mark, and quotation marks have special meaning to MySQL. Now the question is, what to do about it? You cannot count on the users of your application avoiding special characters such as single quotation marks. The solution is to use a pair of functions called escape and unescape. What they do is put escape symbols in front of all special characters. You need to insert code to “escape” the data coming from the form, and then “unescape” the data coming from the database before displaying it.
The new script in shown in Table 9.5, with calls to the escape and unescape functions.
<%@ Language=JavaScript %> | Choose language |
<title>Input and submit favorites to firstdb database</title></head><body> | Usual HTML |
<!— #include file="sopenconn.asp" —> | Include connecting code |
<% | Start ASP |
var submitted=String(Request.Form ("submitted")); | Acquire form data holding submitted flag |
if (submitted !="undefined") { | if test: has form been submitted |
var title = escape(String(Request. Form("title"))); | Acquire form data for title, escaping the contents |
var description = escape(String (Request.Form("description"))); | Acquire form data for description, escaping the contents |
var valuesx = " VALUES ('"+title+"', '" + description +"')"; | Compose what will be values part of insert query |
var fields = " (title, description) "; | Create part of query designating fields |
var query="INSERT INTO favorites " + fields + valuesx; | Create query |
Response.Write("Insert query is: " + query); | Debugging statement |
if (Conn.Execute(query)) | Execute query and test success |
{ Response.Write("<br>Title was successfully entered. <br>");} | When successful, write out message |
else { Response.Write("Title was NOT entered.<br>");} | When not successful, write out message |
Conn.Close(); | Close connection |
Response.Write("<a href=\"saddfavoritefix.asp\">"); Response.Write(" Submit another favorite </a>"); | Write out link to add another favorite. |
Response.Write(" <br>"); | Write out line break |
} | Ends if form was submitted |
else { | Begins clause for displaying form |
%> | End ASP |
<h1>Add a title to the databank of favorite shows. <br></h1> | Heading |
<% | Restart ASP |
var sq ="SELECT * from favorites"; | Define query to get all the favorites |
Response.Write("<h2>Favorites</h2>"); | Write out heading |
rs=Server.CreateObject ("ADODB.RecordSet"); | Create a recordset object |
rs.Open (sq,Conn); | Invoke query |
Response.Write("<table><tr><td> Titles</td><td>Descriptions</td></tr> "); | Write out table tags |
while (!(rs.EOF)) { | while statement: iteration over all favorites in recordset |
Response.Write("<tr><td>"); | Write out table tags |
Response.Write( unescape(String (rs.fields.item("title")))+ " </td><td>"+ unescape(String(rs.fields.item("description")))+ "</td></tr>"); | Extract title and then description from recordset, unescaping each one, write out with table tags |
rs.move(1); | Advance to next record in recordset |
} | End while iteration |
Response.Write("</table>"); | Write out ending table tag |
%> | Close ASP |
<form action="saddfavoritefix.asp" method="POST"><br> | HTML form tag, with action this script |
Title of show <input type=text name="title" size=50><br> | HTML for title input |
Description <input type=text name="description" size=50><br> | HTML for description input |
<input type=hidden name="submitted" value="True"><br> | Input tag for submitted flag |
<input type=submit name="submit" value="Submit favorite!"><br> | Input tag for Submit button |
</form> | Form end |
<% | Restart ASP |
} | End else clause for submitting form |
%> | End ASP |
</body></html> | Closing HTML tags |
Try the new script with titles and descriptions that contains characters such as single and double quotation marks and slashes. This problem demonstrates the necessity to test your application with varied data.
You might now ask why this problem was not discussed in the PHP case. The answer is that it did not appear on our system because PHP supports a feature named “magic quotes.” Specifically, if the php.ini file in the server code has magic_quotes_qpc on, PHP automatically will escape the quotes and other special characters. You can run a script with the phpinfo() function:
<? phpinfo(); ?>
and check the setting of magic_quotes_gpc. If it is not set to on and if you are not in a position to convince the server crew to accommodate you, you can use the PHP functions that correspond to the ASP/JavaScript escape and unescape; namely, addslashes and stripslashes.
Returning to ASP and MS Access, the last step is to take advantage of the ease of use of Access to examine the database contents directly. Download the database from the server using the ws-ftp program or any equivalent. Open it using Access. Examine the records. In particular, notice the contents of any fields containing quotes or other special characters.