Conceptual Explanation

This section presents the concepts behind several important facilities you might need to use in your database application. The following section contains examples of the code for the ASP and PHP implementations.

Alter, Delete, and Update

An unfinished piece of business remains from the last chapter. As you will recall, the single quote caused a problem, noticeable in the ASP system. The fix was to use so-called escape characters to indicate that the single quote was to be viewed as simple data and not with any special meaning. We had to call for escape characters explicitly in the ASP system. The escaping was done for us in PHP. Escaping also applied to blank spaces. In any case, this means that the data going into the text (char) fields in the database potentially were bigger than anticipated. This leads to the common situation that people decide after the initial design and, even, deployment of the project that the size of certain fields is inadequate. The SQL Alter query is the answer. The syntax is slightly different for Access versus MySQL.

You might decide that a show is no longer a favorite. This leads to the use of the SQL Delete command. Needless to say, you need to be very careful that your code is deleting only the record you want deleted. If your code contains the statement delete from tablename, the system does not assume you made a mistake because you did not specify a particular record. Instead, it deletes all records in the table.

The approach recommended here is to display a table listing all the titles in the favorites table. The table will contain special hyperlinks, namely HTML <a … > tags containing as the value of the href attribute the name of a handler script with a query string that specifies the id number of the associated title. The person at the client computer sees the screen in Figure 10.1.

Figure 10.1: Display of titles.

If he or she clicks The Princess Bride to indicate that it is to be deleted (which no one in my household would ever do), then the hyperlink taken would be something like the following:

    href='sdropfavoritefancy.asp?whichid=1'

The number would be 1 if this was the first record entered into the database, but it could be something else. You do not need to know this number. Instead, your ASP or PHP code will generate the code for the table, including the visible titles and descriptions, and also including the favorite_id values that get positioned after whichid as part of the href attribute. The part of the attribute following the question mark is called the query string. In this situation, there is exactly one name=value set. If there were more information to send to the script, you would use ampersands to separate the sets.

This is a case of something that looks like a form but is not an HTML form, even though we call it sdropfavoriteform for both the ASP and PHP case. However, you can think of sdropfavoritefancy.asp or sdropfavoritefancy.php as handler scripts. The coding in these scripts get the information from out of the query string data as they would get information from form data that is sent using the GET method.

A storyboard representing the deletion operation is shown in Figure 10.2.

Figure 10.2: Storyboard for dropping title application.

The PHP version would be the same, except with PHP as the extension of the filenames.

In prior storyboards, you saw a distinction between links representing form handling and links based on hyperlinks. The link from sdropfavoriteform to sdropfavoritefancy is the <a> tag with the query string. The link from sdropfavoritefancy to sdropfavoriteform is a plain hyperlink for the user who wants to delete another title.

Besides insertion and deletion, you might decide that it is critical to provide a facility for changing the information in the database. The design will be very similar to the deletion case in that you create a table with links that have query strings. However, for the update operation, you need one more step. Therefore, there will be three scripts. The strategy is the same for ASP and PHP. The file extensions are omitted in this description:

• supdate1: A script presenting all the titles, with links containing query strings.

• supdate2: A script with a form with text boxes for new title and description. The original values for the textboxes will be the current data.

• supdate3: A script that actually does the update operation on the database.

The problem of the troublesome single quote reared again. This time, the problem was putting a string containing a quotation mark to be the original value for a form input tag. To put this precisely, the HTML form input tags have attributes named VALUE. The value of the VALUE attribute, is the thing on the other side of the equal sign. For this situation, for the example giving a description of the show, The Sopranos, this was:

    one man's two families 

The HTML ignored what came after the apostrophe.

A solution is presented that works for the apostrophe (single quote) case for both ASP and PHP. However, PHP has a function called htmlspecialcharacters, which is more powerful. See the Exercises.

The storyboard diagram for the update example is shown in Figure 10.3.

Figure 10.3: Storyboard for update application.

The diagram is in a slightly different style: without file extensions and with the connection file described conceptually rather than by filename. This is done on purpose. Storyboards are for your benefit, so define your own conventions to make them work for you.

You will see in the code examples later in the chapter that the connection is closed at the end of each script and then re-opened in the next script. You might ask if this is not a waste of time. It is true that the closing and opening does take system resources. However, think about what is occurring for each script. The middleware script prepares an HTML file for a person to view. The person will take time reading the display and, perhaps, clicking to choose a record to update and, later, typing in the new values. This is an enormous amount of time for the system to keep a connection open. The connections could be limited in number. The person could walk away. The closing makes sense. However, this is a more complex question and is the topic for advanced database design. For example, it could be that you want to check that the data has not been changed since the previous read operation. Some ideas on this issue will be discussed in Chapter 16, “Scaling Up Your Application.”

To demonstrate the composition of joins involving more than one table, we will need to define a database with more than one table. The sample to be presented here is to add to the favorites database two more tables: a table containing warnings and a table for categories of warnings. This is intended to reflect the TV and movie practice of issuing a warning if a show has violence, sex, foul language, and/or adult themes. The categories table will contain two fields: an identifier number and a description. The warnings table will contain three fields: the identifier for the favorite that bears the warning, the identifier for the category of warning, and a reason for the warning.

The warnings table does not have a primary key. You could decide to establish that the combination of the favorite identifier and the category identifier uniquely define the warning record. This would be a concatenated primary key, which is allowed by both MySQL and Access. However, this would mean that you could not have multiple warnings for the same show in the same category. In any case, this example shows you that a table does not have to have a primary key.

Following the practice taken early, it is suggested that you use a different approach for PHP/MySQL and for ASP/Access. For PHP/MySQL, you will see scripts for creating the two additional tables. Since the categories table has a limited number of records, you can copy the code for inserting a specific favorite to write a script for inserting the four warning categories mentioned already. In contrast, for ASP/Access, you can download the database to your own computer. You do this in order not to lose any existing data. You then use Access as before to create two new tables. Use the Create in Design View option. You can then click on the newly created categories table and insert four records.

Use of Stand-Alone Access

The Access stand-alone system also has a feature for defining relationships. In the new database, there is a relationship between the warnings and the favorites tables, and the warnings and the categories table. A warning record contains as foreign keys, a favorite_id field and a cat_id field. Specifying this relationship “tells” the Access system to check that data being placed in the foreign key fields must be valid primary keys for the corresponding tables. The system does what are called referential integrity checks. As a side benefit, you can also get Access to draw the ER diagram for you. This will be demonstrated later.

Now we get to the design for inserting warning records in the system. This will follow the model for updates. Specifically, you will see in the next section (and should try to implement for yourself), the following three scripts:

• swarning1: A script that presents all the favorites in a table. The entries will be links with query strings holding the id information.

• swarning2: A script that presents a form. The form has a set of radio buttons with the category possibilities. It also has a text box in which to type in the reason for giving this warning. Lastly, the form has the favorite_id value as hidden data to be passed on to the script handler for this form.

• swarning3: A script to perform the insert operation.

Please note that this application, the update application, and the delete operation could all be done using the technique of one script being the form and the handler. The approach taken here follows the principle that several small tasks are easier than one complex task. However, this approach does mean that you need to deal with several scripts at a time.

More SQL

The last set of applications deal with presentation of information. Since the application now has warnings, we need to develop a way to show the warnings. Here are three distinct facilities you could provide:

• Display all warnings.

• Display all favorites and show the count of warnings.

• Allow the user to select a warning category and display all warnings (with title and description of favorite along with the reason given in the warning). The text implements this feature using two scripts.

The first two scripts are basically “constants.” They do not require any input from the user. They contain fixed SQL statements.

The challenge for the second task is to include the favorites that do not have any warnings indicated by a count of zero. The technique for doing this is to use what is called a LEFT JOIN in SQL. The term comes from the fact that you are joining two tables using some condition, but if there is not a match for something in the left table, SQL will build a row anyway, putting a null in the locations where there were no values. You will make use of an SQL aggregate function, COUNT, which will not add in anything for any null fields.

The third task does ask the user to select a category. It can be modeled after the first swarning2 scripts, which display a way to select a category, and the display scripts.

The next section shows details on these features using both ASP and PHP. Do feel free to try to write the scripts on your own.