In the previous section, you've seen how we can build a report page that can be used with any of our log summary tables. One thing we skipped over was how we might allow users to select subsets of records for a report. For example, if they want to know how many hits there were on a particular page, or between defined start and end dates, they would have to list all the pages on the site and look through the list for the ones they are interested in.
Solving this problem is a situation that arises regularly when you work with databases of all kinds. In this section of the chapter, we'll explore a specific solution that we built to use with our log tables. However, the techniques—and the query page itself—can easily be adapted to work with other sources of data.
The showlog.asp page we looked at earlier interfaces with another page, named getcriteria.asp. This page creates a WHERE clause, which can be added to the SQL statement that extracts the records from the summary tables in our log files database. You saw how certain queries will demand a criteria be present in the query string sent to the page:...
strCriteria = Request.Form("criteria") 'the criteria for the report
...
Case "hits" 'traffic volume sorted by number of hits
If Len(strCriteria) < 1 Then 'we need to collect a criteria string
Response.Redirect "getcriteria.asp?query=" & strQuery & "&site=" & strSite
End If
...The getcriteria.asp page accepts the query string sent to it in the URL (by the Redirect statement in the showlog.asp page), adds thecriteriaparameter to it, and reloads the showlog.asp page again. This time the test Len(strCriteria) < 1 will be false. The criteria will be added to the existing WHERE clause in strWhere, and the report will be displayed:
...
If Len(strCriteria) > 1 Then 'add the optional criteria to the WHERE clause
If Len(strWhere) Then
strWhere = strWhere & " AND " & strCriteria
Else
strWhere = " WHERE " & strCriteria
End If
End If
...If the user doesn’t provide any criteria within the getcriteria.asp page, it sends back a single space as the value of the parameter. This still passes the test in the showlog.asp page Case statement, but fails the test in the code above, so it doesn’t affect the SQL statement. The next screenshot shows what the getcriteria.asp page looks like after we've selected some conditions for our query:
The first step in the getcriteria.asp page is to collect the values sent in the query string from the showlog.asp page. We can then create two URLs that we'll use later for theRunQueryandCancelbuttons at the foot of the page. We also create a string containing details of the site(s) we're listing information for:
<%@ LANGUAGE=VBSCRIPT %>
<%
strSite = Request.QueryString("site")
strURL = "showlog.asp?query=" & Request("query") & "&site=" & strSite
strCancel = "trafficreports.asp"
Select Case strSite
Case "all"
strWhere = "( all sites )"
Case "wd"
strWhere = "194.73.51.228 ( Web Developer )"
Case "cd"
strWhere = "194.73.51.229 ( COM Developer )"
Case "wa"
strWhere = "194.73.51.230 ( World Of ATL )"
End Select
%>
...Notice that this page isn’t used toselectthe site, it only displays the selection originally made in the main traffic reports menu. The only reason we chose to do it this way is that some reports created by showlog.asp don’t use this criteria page, and so there would be no opportunity to select the site.
The page itself uses only standard HTML controls, meaning it will work with most browsers. We could have used the 'enhanced forms' and absolute positioning abilities of Internet Explorer 4+ to create a really attractive and interactive page, but we chose compatibility instead. However, by adding IE4-specific attributes to some of the controls, and some extra script code, we allow the page to use some of the extra features that IE4 provides without sacrificing compatibility.
For example, we can disable and 'gray-out' controls that are not available. In Navigator and pre-IE4 Microsoft browsers, the page still works fine. It's just that the controls don’t become disabled. Because JavaScript can attach arbitrary properties to objects, setting the DISABLED property of a control doesn’t break the code even if the browser doesn’t actually support this property.Here's the code that creates the first row of controls, where the user can select the start and end dates, and the file types to include:
...
<html>
<head><title>Criteria for your traffic query</title></head>
<body BGCOLOR="#FFFFFF">
Select the criteria for your query:<P>
Site IP is: <% = strWhere %><P>
<table cellpadding=0 border=0>
<form name="controls"> <!--form included for Navigator otherwise it won’t display-->
<tr> <!--controls. We won't submit form, so no ACTION required -->
<td align="right" nowrap>
<INPUT TYPE="CHECKBOX" NAME="chkPeriod" ONCLICK="clickPeriod()"></td>
<td nowrap>Only hits for this period:</td>
<td nowrap> </td>
<td align="right" nowrap>
<INPUT TYPE="CHECKBOX" NAME="chkFileType" ONCLICK="clickFileType()"></td>
<td nowrap>Include these types of pages only:</td>
</tr>
<tr>
<td></td>
<td align="right" valign="middle" nowrap>
From week:
<INPUT TYPE="TEXT" NAME="txtFromWeek" DISABLED
VALUE="<% = DatePart("ww", Now) - 1 %>" SIZE="2">
<INPUT TYPE="TEXT" NAME="txtFromYear" DISABLED
VALUE="<% = DatePart("yyyy", Now) %>" SIZE="4">
<BR>To week:
<INPUT TYPE="TEXT" NAME="txtToWeek" DISABLED
VALUE="<% = DatePart("ww", Now) - 1 %>" SIZE="2">
<INPUT TYPE="TEXT" NAME="txtToYear" DISABLED
VALUE="<% = DatePart("yyyy", Now) %>" SIZE="4">
</td>
<td></td>
<td></td>
<td align="left" valign="top" nowrap>
<INPUT TYPE="CHECKBOX" DISABLED NAME="chkContent" CHECKED>
Content files (asp, htm, html)<BR>
<INPUT TYPE="CHECKBOX" DISABLED NAME="chkZip"> Zip files (zip)<BR>
<INPUT TYPE="CHECKBOX" DISABLED NAME="chkGraphic"> Graphics (gif, jpg, jpeg, png)
</td>
</tr>
<tr>
<td colspan=5> </td> <!-- easy way to add a blank row-->
</tr>
...To allow our code to enable and disable the controls, we include ONCLICK attributes in the chkPeriod and chkFileType controls, setting them to the names of two JavaScript functions. In IE4+, the individual controls below these two checkboxes will be disabled when the page loads, and the code will enable them when the main checkbox is set. In Navigator, IE3, etc. the DISABLED attribute isn’t recognized, so the controls will not be disabled when the page loads.Here are the control definitions for the 'Includethesetypesoffilesonly' section again, followed by the JavaScript function (which actually appears at the end of the page) that changes the DISABLED setting. Similar routines are used for the other controls:
<INPUT TYPE="CHECKBOX" NAME="chkFileType" ONCLICK="clickFileType()"></td>
...
<INPUT TYPE="CHECKBOX" DISABLED NAME="chkContent" CHECKED> Content files...<BR>
<INPUT TYPE="CHECKBOX" DISABLED NAME="chkZip"> Zip files...<BR>
<INPUT TYPE="CHECKBOX" DISABLED NAME="chkGraphic"> Graphics...
...
function clickFileType() {
objCForm.chkContent.disabled = !(objCForm.chkFileType.checked);
objCForm.chkZip.disabled = !(objCForm.chkFileType.checked);
objCForm.chkGraphic.disabled = !(objCForm.chkFileType.checked);
}Notice also how we insert the current week and year into the date controls with ASP in the code above:
<INPUT TYPE="TEXT" NAME="txtFromWeek" DISABLED
VALUE="<% = DatePart("ww", Now) - 1 %>" SIZE="2">
<INPUT TYPE="TEXT" NAME="txtFromYear" DISABLED
VALUE="<% = DatePart("yyyy", Now) %>" SIZE="4">Here's the first row of controls again, enabled and with a new 'From week' selected:
The remainder of the HTML to create the getcriteria.asp page is shown below. This creates the section containing the controls where the user selects which files to include, by name or site section, the checkbox to specify that the SQL statements should be included in the results page, and the buttons to set it all going or cancel the query:...
<tr>
<td align="right" nowrap>
<INPUT TYPE="CHECKBOX" NAME=chkInclude ONCLICK="clickInclude()"></td>
<td align="left" nowrap>Include only files: </td>
<td colspan=3> </td>
</tr>
<tr>
<td></td>
<td align="left" nowrap>
<INPUT TYPE="RADIO" NAME="optSection" VALUE="0" CHECKED DISABLED>
In this part of the site:
</td>
<td align="left" colspan=3 nowrap>
<SELECT NAME="selSection" DISABLED>
<OPTION>( all files )
<% --add site-specific entries to drop-down list--
Select Case strSite 'from query string
Case "wd" 'site sections of Web-Developer site
%>
<OPTION>/dna
<OPTION>/books
<OPTION>/reference
... 'etc.
<OPTION>/webdev
<%
Case "cd" 'site sections of COMDeveloper site
... 'etc.
Case "wa" 'site sections of WorldOfATL site
... 'etc.
End Select
%>
</SELECT>
</td>
</tr>
<tr>
<td></td>
<td align="left" nowrap>
<INPUT TYPE="RADIO" NAME="optSection" VALUE="1" DISABLED> Where URL contains:
</td>
<td align="left" colspan=3 nowrap>
<INPUT TYPE="TEXT" NAME="txtURL" VALUE="/" DISABLED SIZE="30">
</td>
</tr>
</form> <!-- end of 'dummy' form-->
<tr>
<td colspan=5> </td>
</tr>
<!--form to reload the showlog.asp page and send the query string back-->
<!--the value for the ACTION attribute is set at the top of this page -->
<form name="QueryForm" action="<% = strURL %>" method="POST">
<tr>
<td align="right" nowrap><INPUT TYPE="CHECKBOX" NAME="showsql"></td>
<td align="left" nowrap>Show SQL Query</td>
<td align="right" nowrap colspan=3>
<INPUT TYPE="HIDDEN" NAME="criteria"> <!--return criteria to showlog.asp-->
<INPUT TYPE="BUTTON" VALUE="Run Query" ONCLICK="runQuery()">
<INPUT TYPE="BUTTON" VALUE="Cancel" ONCLICK="cancelQuery()">
</td>
</tr>
</form>
</table>
...Here's the lower section of the query page again. The list box is filled with different values by ASP code in the page, depending on which site is selected:
The getcriteria.asp page contains a substantial script section, most of which is used to create the criteria for the query from the values selected in the various controls on the page. The rest is used to change the disabled state of the controls, and cancel the query. We'll look at this section first.
The main set of controls are on a <FORM> in the page. We have no intention of submitting the values of the controls to another page, but we have to use a form because Navigator will not display them if we don’t. To make accessing the controls on this form easier in our code, we first create a reference to it in the variable objCForm in our script. Then come the three functions that change the disabled state of the main control groups for Internet Explorer 4+:
...
<SCRIPT LANGUAGE=JavaScript>
var objCForm = document.forms["controls"];
function cancelQuery() {
// runs when the Cancel button is clicked
window.location.href = "<% = strCancel %>"
}
function clickPeriod() {
// runs when the value of the chkPeriod checkbox is changed
objCForm.txtFromWeek.disabled = !(objCForm.chkPeriod.checked);
objCForm.txtFromYear.disabled = !(objCForm.chkPeriod.checked);
objCForm.txtToWeek.disabled = !(objCForm.chkPeriod.checked);
objCForm.txtToYear.disabled = !(objCForm.chkPeriod.checked)
}
function clickFileType() {
// runs when the value of the chkFileType checkbox is changed
objCForm.chkContent.disabled = !(objCForm.chkFileType.checked);
objCForm.chkZip.disabled = !(objCForm.chkFileType.checked);
objCForm.chkGraphic.disabled = !(objCForm.chkFileType.checked);
}
function clickInclude() {
// runs when the value of the chkInclude checkbox is changed
objCForm.optSection[0].disabled = !(objCForm.chkInclude.checked);
objCForm.optSection[1].disabled = !(objCForm.chkInclude.checked);
objCForm.selSection.disabled = !(objCForm.chkInclude.checked);
objCForm.txtURL.disabled = !(objCForm.chkInclude.checked)
}
...The fun part of the script comes next. This runs when the user clicks theRunQuerybutton. It builds up a SQL criteria string using the selections made in the controls on the form, places it into the hidden control named criteria, and submits the form:function runQuery() {strCriteria = " "; // if no criteria, we return a single spaceif (objCForm.chkPeriod.checked)// create SQL sub-clause to include only records between the two datesstrCriteria = "(DATEPART(week,TSumDate) BETWEEN " + objCForm.txtFromWeek.value+ " AND " + objCForm.txtToWeek.value + ") AND (DATEPART(year,TSumDate) BETWEEN "+ objCForm.txtFromYear.value + " AND " + objCForm.txtToYear.value + ")";if (objCForm.chkFileType.checked) {// create SQL sub-clause to include only these file typesstrFileType = "";if (objCForm.chkContent.checked) // content files (.asp .htm .html)strFileType = "CHARINDEX('.asp',TTarget) > 0 OR CHARINDEX('.htm',TTarget) > 0";if (objCForm.chkZip.checked) { // zip files (.zip)if (strFileType.length > 0) strFileType += " OR ";strFileType += "CHARINDEX('.zip',TTarget) > 0"};if (objCForm.chkGraphic.checked) { // graphics (.gif, .png, .jpg, .jpeg)if (strFileType.length > 0) strFileType += " OR ";strFileType += "CHARINDEX('.gif',TTarget) > 0 OR CHARINDEX('.png',TTarget) > 0 "+ "OR CHARINDEX('.jp',TTarget) > 0"};if (strFileType.length > 0) {if (strCriteria.length > 1) strCriteria += " AND ";strCriteria += "(" + strFileType + ")"}};if (objCForm.chkInclude.checked) {// create SQL sub-clause to include only file containing this textstrInclude = "";if (objCForm.optSection[0].checked) { // files in this section of the sitestrSection = objCForm.selSection.options[objCForm.selSection.selectedIndex].text;if (strSection.substr(0,1) == "/")strInclude = "(CHARINDEX('" + strSection + "',TTarget) > 0)"}else // files containing this text in URLstrInclude = "(CHARINDEX('" + objCForm.txtURL.value + "',TTarget) > 0)";if (strInclude.length > 0) {if (strCriteria.length > 1) strCriteria += " AND ";strCriteria += strInclude}};document.forms["QueryForm"].criteria.value = strCriteria; // put in hidden controldocument.forms["QueryForm"].submit() // and submit the form}</SCRIPT></body></html>
Now, when the user clicks theRunQuerybutton, our criteria is submitted to the showlog.asp page, where it is added to the existing SQL statement and used to create the report. Here's an example of a complex query created by this code:
In case you didn’t notice in the code (we hid it pretty well), we'll just point out that this page—like some other examples in the book—uses both the Form and QueryString collection to pass values between itself and another page. This is quite acceptable, and even useful in many situations. When using an <A> tag to load an ASP page, we can only directly add values to the QueryString collection. To get them into the Form collection requires quite a lot of extra work. However we can pass values back from a form into either the Form or QueryString collections by setting the METHOD attribute of the form to either GET (for the QueryString collection) or POST (for the Form collection). If there are a lot of values, using the QueryString collection is dangerous because the length of the query string is limited by the HTTP protocol (generally to around 1000 characters).Our page receives thesiteandqueryparameters from the showlog.asp page via the QueryString collection, and creates the URL for the return trip to showlog.asp using them:
strSite = Request.QueryString("site")
strURL = "showlog.asp?query=" & Request.QueryString("query") & "&site=" & strSiteThis URL, complete with query string, is then used in the opening <FORM> tag, but the criteria we create is placed in a control on the form itself by the script we've just seen—which also submits the form:<form name="QueryForm" action="<% = strURL %>" method="POST">
...
<INPUT TYPE="HIDDEN" NAME="criteria"> <!--return criteria to showlog.asp-->In the showlog.asp page (as we saw at the start of this section of the chapter), the values are then extracted from the two collections:strQuery = Request.QueryString("query") 'the name of the query we're going to run
strSite = Request.QueryString("site") 'the site(s) that we want to include
strCriteria = Request.Form("criteria") 'the criteria for the report