User Recipe: Journal Archive

As you can imagine, an ongoing journal can build up a lot of entries very quickly. The archive feature of our recipe is designed to keep the journal manageable, showing entries from only the most current period initially, but making past periodswhether a day, a week, or a monthaccessible.

The Archive page closely resembles the Journal page, with one important difference: A drop-down list displays all the periods in which journal entries have been posted. For example, if a journal is set to archive monthly and entries were posted in July and September but not August, only July and September are listed. When the user chooses a particular period, those entries are retrieved and displayed on the same page in the view journal format used throughout the application.

From a coding perspective, the archive manipulation is by far the most complex and interesting aspect of the page. In addition to the two recordsets previously used, Journal and Entries, a third recordsetTimePeriodsexamines the full list of entries and separates them by their different periods. Achieving this functionality will require some custom variables to set the specific periods as well as a bit of client-side JavaScript to trigger the recordset update.

Step 1: Implement Journal Archive Design

As noted, this page closely resembles the Journal page. The layout calls for a top section providing details about the current journal and a bottom section displaying a repeated region filled with the applicable entries. The keyin fact, the onlyform element in this page is a list; JavaScript is used to submit the form, thus avoiding the unnecessary addition of a form button.

1.	Create a page for the View Journal recipe, either from a template or from the File > New dialog. In the Journal folder, locate the folder for your server model and open the archive page there.
2.	In the content area of your page, create a header for the page and an area to hold both the general journal information and the specific entries. From the Snippets panel, drag the Recipes > Journal > Wireframes > Journal Archive - Wireframe snippet into the Content editable region.
3.	Add labels and table cells to hold the journal information (name, owner, and description) as well as a form element for the time periods and, below that, a two-row, two-column table for the journal entries. Place your cursor in the first row below the words VIEW JOURNAL ARCHIVES and insert the Recipes > Journal > Forms > Journal Archive Info - Form snippet. Place your cursor in the bottom row of the wireframe and insert the Recipes > Journal > ContentTables > View Journal - Content Table snippet.
4.	Save the page [r6-15]. r6-15.

Step 2: Add Database Components: Part 1

Of the recordsets required for the Archive page, ASP and ColdFusion users will recognize the first two as being used in previous pages in this application. PHP coders will find their recordset setup to be a bit more elaborate, so as to better handle the archive date manipulation functionality.

In this step, we'll initially create the Journal and Entries recordsets. A bit later in the recipe, we'll create the TimePeriods recordset to work with the drop-down list as a filter for the Entries recordset.

For ASP and ColdFusion

1.

From the Bindings panel, choose Add (+) and select Recordset.

2.

In the dialog's simple view, enter an appropriate name for the recordset.

Enter Journal in the Name field.

3.

Choose a connection (data source) to use.

Select Recipes from the Connection list.

4.

Choose the table in the data source to work with.

Select Journals from the Table list.

5.

Leave the Columns option set to All.

6.

In the Filter area of the Recordset dialog, set the four Filter list elements like this:

JournalID	= (Equals)
URL Parameter	ID

7.

Leave the Sort option set to None and click OK to close the dialog.

Now, we'll insert the Entries recordset, from which we'll gather the journal entries for display at the bottom of the page.

1.

From the Bindings panel, choose Add (+) and select Recordset.

2.

In the dialog's simple view, enter an appropriate name for the recordset.

Enter Entries in the Name field.

3.

Choose a connection (data source) to use.

Select Recipes from the Connection list.

4.

Choose the table in the data source to work with.

Select JournalEntries from the Table list.

5.

Leave the Columns option set to All.

6.

In the Filter area of the Recordset dialog, set the four Filter list elements like this:

EnTRyJournal	= (Equals)
URL Parameter	ID

7.

Leave the Sort option set to None and click OK to close the dialog.

For PHP

If you're following our recipe, before you begin this step, copy the SQL code from the provided snippet. Right-click (Control-click) Recipes > Journal > SQL > Journal RS SQL - PHP and select Copy Snippet from the context menu.

1.	From the Bindings panel, choose Add (+) and select Recordset (Query) from the list.
2.	In the Recordset dialog, choose Advanced.
3.	Enter an appropriate name for the recordset. Enter Journal in the Name field.
4.	Select the data source. Choose Recipes from the Connections list.
5.	In the SQL area, enter the following code: Paste the copied code in the SQL area by pressing Ctrl-V (Command-V). [View full width] SELECT unix_timestamp(JournalDate) AS JournalTimeStamp, JournalID, JournalTitle, JournalOwner, JournalDescription, JournalArchive FROM journals WHERE JournalID = IDParam

The unix_timestamp() function is used to turn the JournalDate field from the MySQL datetime format so that it can be easily manipulated into different formats using the PHP date() command.

Now we'll add a variable, one for each of the session variables.

1.

In the Variables section, choose Add (+) and enter the following details in the Add Parameter dialog:

Name:	IDParam
Default Value:	1
Run-time Value:	$_GET['ID']

2.

Click OK to close the dialog and insert the recordset.

Let's follow much the same process to create the Entries recordset:

In preparation, you'll need to copy the SQL code from the provided snippet. Right-click (Control-click) Recipes > Journal > SQL > Entries RS SQL - PHP and select Copy Snippet from the context menu.

1.	From the Bindings panel, choose Add (+) and select Recordset (Query) from the list.
2.	In the Recordset dialog, choose Advanced.
3.	Enter an appropriate name for the recordset. Enter Entries in the Name field.
4.	Select the data source. Choose Recipes from the Connections list.
5.	In the SQL area, enter the following code: Paste the copied code in the SQL area by pressing Ctrl-V (Command-V). SELECT EntryID, EntryJournal, EntryBy, EntryTitle, EntryText, unix_timestamp(EntryDate) AS EntryDateTimeStamp FROM JournalEntries WHERE EntryJournal = IDParam ArchiveWhere

The ArchiveWhere variable will be coded later in the recipe, but we still need to set it up as well as IDParam.

1.

In the Variables section, choose Add (+) and enter the following details in the Add Parameter dialog:

Name:	IDParam
Default Value:	1
Run-time Value:	$_GET['ID']

2.

Click OK to close the Add Parameter dialog, and then choose Add (+) and enter the following details for the second variable:

Name:	ArchiveWhere
Default Value:	;
Run-time Value:	$ArchiveWhere

The semicolon used as the default value for the ArchiveWhere variable ensures that the PHP code will end properly regardless of the variable value.

3.

Click OK to close the dialog and insert the recordset.

4.

Save your page.

Step 3: Add Database Components: Part 2

The final recordset for the Archive page requires a bit more finesse than the others used here and includes a technique not seen in any of the other applications in this book. The TimePeriods recordset contains a set of periods drawn from a given journal that contain entries. What makes this recordset special is that the period itself (day, week, or month) varies according to the archive period specified for the journal. Thus, one of the actual fields returned by the SQL statement is set up as a variable. Here's the ASP version of the SQL:

SELECT DISTINCT (PeriodField) AS Period, ArchPeriod AS IncludeDate FROM JournalArchiveDates WHERE EntryJournal = IDParam ORDER BY ArchPeriod DESC

The parentheses in (PeriodField) designate it as a variable. Later in the recipe, we'll include some custom code for setting the variable properly. Now, we can start by inserting the recordset.

For ASP

In preparation, you'll need to copy the SQL code from the provided snippet. Place your cursor in any text on the page and then, from the Snippets panel, right-click (Control-click) the Recipes > Journal > SQL > TimePeriods RS - ASP snippet and select Copy Snippet from the context menu.

1.	From the Bindings panel, choose Add (+) and select Recordset (Query) from the list.
2.	In the advanced view of the Recordset dialog, enter an appropriate name for the recordset. Enter TimePeriods in the Name field.
3.	Select the data source. Choose Recipes from the Connections list.
4.	In the SQL area, enter the following code: Paste the copied code in the SQL area by pressing Ctrl-V (Command-V). SELECT DISTINCT (PeriodField) AS Period, ArchPeriod AS IncludeDate FROM JournalArchiveDates WHERE EntryJournal = IDParam ORDER BY ArchPeriod DESC

We'll need to declare three variables seen in the SQL: IDParam, PeriodField, and ArchPeriod.

1.	In the Variable area, choose Add (+) to declare the first variable.
2.	Enter the name for the variable. In the Name column, enter IDParam.
3.	Enter a default value for the variable. In the Default Value column, enter 1.
4.	Enter a run-time value for the variable. In the Run-time Value column, enter Journal.Fields("JournalID").value.
5.	In the Variable area, choose Add (+) again to declare the second variable.
6.	Enter the name for the variable. In the Name column, enter PeriodField.
7.	Enter a default value for the variable. In the Default Value column, enter Month.
8.	Enter a run-time value for the variable. In the Run-time Value column, enter PeriodColumn.
9.	In the Variable area, choose Add (+) one more time to declare the last variable.
10.	Enter the name for the variable. In the Name column, enter ArchPeriod.
11.	Enter a default value for the variable. In the Default Value column, enter Date3.
12.	Enter a run-time value for the variable. In the Run-time Value column, enter ArchPeriod.
13.	Click OK to close the dialog, and then save your page.

For ColdFusion

Unfortunately, the ColdFusion implementation in Dreamweaver isn't properly set up to handle SQL queries with dynamic variables. If you attempt to enter the needed SQL in the recordset dialog, an error is displayed. To work around this issue, we'll drop in our recordset code and <cfparam> tags by hand.

Note

For the variables to work properly, you'll need to hold off on testing the page until it is complete.

The TimePeriods recordset must be placed after the Journal recordset and before the Entries one for all the pieces to work together properly.

1.	From the Server Behaviors panel, select the Entries recordset.
2.	Switch to Code view and move the cursor before the selected recordset.
3.	Insert the following code: From the Snippets panel, insert the Recipes > Journal > CustomCode_CF > TimePeriods RS snippet. [View full width] <cfquery name="TimePeriods" datasource="Recipes"> SELECT DISTINCT (#PeriodColumn#) AS Period, #ArchPeriod# AS IncludeDate FROM JournalArchiveDates WHERE EntryJournal = #IDparam# ORDER BY #ArchPeriod# DESC </cfquery>
4.	Place the cursor near the top of the page after the other <cfparam> statements and insert the following code: From the Snippets panel, insert the Recipes > Journal > CustomCode_CF > TimePeriods RS - SQL Parameters snippet. <cfparam name="PeriodColumn" default="Day"> <cfparam name="ArchPeriod" default="Date1"> <cfparam name="IDparam" default="#URL.ID#">
5.	Save your page.

For PHP

If you're following our recipe, copy the SQL code from the provided snippet before you begin this step. Place your cursor in any text on the page, and then, from the Snippets panel, right-click (Control-click) Recipes > Journal > SQL > TimePeriods RS - PHP and select Copy Snippet from the context menu.

1.	From the Bindings panel, choose Add (+) and select Recordset (Query) from the list.
2.	In the Recordset dialog, choose Advanced.
3.	Enter an appropriate name for the recordset. Enter TimePeriods in the Name field.
4.	Select the data source. Choose Recipes from the Connections list.
5.	In the SQL area, enter the following code: Paste the copied code in the SQL area by pressing Ctrl-V (Command-V). [View full width] SELECT EntryID, EntryJournal, EntryBy, EntryTitle, EntryText, unix_timestamp(EntryDate) AS EntryDateTimeStamp FROM JournalEntries WHERE EntryJournal = IDParam ORDER BY unix_timestamp(EntryDate) ASC

Again, the unix_timestamp() function is used to turn the JournalDate field from MySQL datetime format so that it can be easily manipulated into different formats using the PHP date() command.

Now we'll add a variable for the session variables.

1.

In the Variables section, choose Add (+) and enter the following details in the Add Parameter dialog:

Name:	IDParam
Default Value:	1
Run-time Value:	$_GET['ID']

2.

Click OK to close the dialog and insert the recordset.

3.

Save your page when you're ready.

Step 4: Data Binding Process

Now that our recordsets are defined, we can integrate the data into the page.

1.

From the Bindings panel, expand the Journal recordset.

2.

Place the data source fields onto the page in their respective areas:

	Drag JournalTitle into the cell next to the Journal Name label.
	Drag JournalOwner into the cell next to the Journal Owner label.
	Drag JournalDescription into the cell next to the Journal Description label.

Select the term [date] in the placeholder text at the top of the content area and drag JournalDate from the Bindings panel onto the selection.

Delete the selection and insert the Recipes > Journal > CustomCode_PHP > Journal Archive - Insert Journal Date snippet, which contains the following code:

[View full width]
<?php echo date("m/d/Y" ,$row_Journal['JournalTimeStamp']); ?>

3.

Save the page.

Now, let's add the data fields from the Entries recordset.

1.

From the Bindings panel, expand the Entries recordset.

2.

Place the data source fields onto the page in their respective areas:

	Select the term [EntryTitle] in the placeholder text at the top of the content area and drag EntryTitle from the Bindings panel onto the selection.
	Select the term [EntryBy] in the placeholder text at the top of the content area and drag EntryBy from the Bindings panel onto the selection.
	Select the term [EntryText] in the placeholder text at the top of the content area and drag EntryText from the Bindings panel onto the selection.

Drag EntryDate in the cell below the Date/Time label.

Insert the Recipes > Journal > CustomCode_PHP > Journal Archive - Insert Entry Date snippet, which contains the following code:

[View full width]
<?php echo date("m/d/Y h:i A" ,$row_Entries['EntryDateTimeStamp']); ?>

3.

Save the page [r6-16].

r6-16.

As we've done in other pages in this application, we'll now create a link for editing a listed entry.

1.	Select the text or image you want to serve as the link. Choose the text [edit] below the Journal Description label.
2.	Click the folder icon next to the Link field in the Property inspector. The Select File dialog opens.
3.	Choose Parameters at the bottom of the dialog.
4.	In the Name column of the Parameters dialog, enter the variable name. Enter ID in the Name column.
5.	In the Value column, enter the dynamic value of the current survey's ID. Click the lightning bolt icon next to the Value column and, from the Dynamic Data dialog, choose JournalID from the Journal recordset. When you're done, click OK to close the Dynamic Data dialog.
6.	Choose Add (+) to insert another parameter.
7.	Enter a name for the entry ID variable in the Name column. Enter EditID in the Name column.
8.	Insert the following code in the Value column: <%=Request("ID")%> <%=Request("ID")%> #URL.ID# <?php echo $_GET['ID']; ?>
9.	In the Select File dialog, select the file that will be used to edit the survey information. Choose journal_editor in the Journal folder for your server model.
10.	When you're done, click OK to insert the link.
11.	Save your page before continuing.

Step 5: Bind Data to List

The TimePeriod list element is quite different from your standard drop-down menu. While other select lists may be dynamically populated from a recordset, both the label and the value for the TimePeriod list element are dynamically generated. More importantly, the value chosen from this list is automatically submitted (thanks to a bit of JavaScript added later to this page) to refilter the recordsets, thus repopulating the page with new data.

1.	Place your cursor in the cell adjacent to the Time Period label.
2.	Insert the custom code to create the TimePeriods select list. From the Snippets panel, open the Recipes > Journal > CustomCode folder for your server model and insert the Journal Archive - Time Period Menu snippet on the page. [View full width] <select name="TimePeriod"> <option value="" <%If (Not isNull(Request ("TimePeriod"))) Then If ("" = CStr(Request ("TimePeriod"))) Then Response.Write ("selected=""selected""") : Response.Write("") %>>Current Period</option> <% While (NOT TimePeriods.EOF) %> <option value="<%=(TimePeriods.Fields.Item ("IncludeDate").Value)%>" <%If (cStr(Request ("TimePeriod")) <> "") Then If (CStr(TimePeriods .Fields.Item("IncludeDate").Value) = CStr(cDate (Request("TimePeriod")))) Then Response.Write ("selected= ""selected""") : Response.Write("")%> > <%=(TimePeriods.Fields.Item("Period").Value)%>< /option> <% TimePeriods.MoveNext() Wend If (TimePeriods.CursorType > 0) Then TimePeriods.MoveFirst Else TimePeriods.Requery End If %> </select> [View full width] <select name="TimePeriod"> <option value="undefined" <%=(("" == (String (Request("TimePeriod"))))?"selected=\"selected\"" :"")%>> CurrentPeriod</option> <% while (!TimePeriods.EOF) { %> <option value="<%=(TimePeriods.Fields.Item ("IncludeDate").Value)%>" <%=((String(TimePeriods .Fields.Item("IncludeDate").Value) == new Date (Request("TimePeriod")))?"selected=\"selected\"" :"")%> > <%=(TimePeriods.Fields.Item("Period") .Value)%></option> <% TimePeriods.MoveNext(); } if (TimePeriods.CursorType > 0) { if (!TimePeriods.BOF) TimePeriods.MoveFirst(); } else { TimePeriods.Requery(); } %> </select> [View full width] <select name="TimePeriod"> <option value="" <cfif (isDefined("Form .TimePeriod") AND "" EQ Form .TimePeriod)>selected="selected"</cfif]Current Period</option> <cfoutput query="TimePeriods"> <option value="#Left(TimePeriods.IncludeDate,Find (" ",TimePeriods.IncludeDate))#" <cfif (isDefined ("Form.TimePeriod") AND TimePeriods.IncludeDate EQ Form.TimePeriod)>selected="selected" < /cfif]#TimePeriods.Period#</option> </cfoutput> </select> [View full width] <select name="TimePeriod"> <option value="">Current Period</option> <?php foreach($dataArray as $temp){ $tempArray = explode("*",$temp); if($tempArray[1] == $_POST['TimePeriod']){ echo "<option value='" . $tempArray[1] . "' selected>" .$tempArray[0] . "</option>"; } else { echo "<option value='" . $tempArray[1] . "'>" .$tempArray[0] . "</option>"; } } ?> </select>
3.	Save your page.

As a reminder, you won't see any special behavior with this list until the page is completed and the JavaScript function is added to activate it.

Step 6: Add Repeat Region

The Repeat Region server behavior is used to show all the entries in the selected period of the current journal.

1.	Select any of the dynamic data fields in the entries content area.
2.	From the Tag Selector, choose the table row tag. Select the <tr> tag from the Tag Selector.
3.	From the Server Behaviors panel, choose Add (+) and select Repeat Region.
4.	In the Repeat Region dialog, choose the recordset. Choose Entries from the Recordset list.
5.	Set the Show option to display the number of records you want. Choose Show All Records.
6.	Click OK when you're done and save your page.

Step 7: Move TimePeriods Recordset

The TimePeriods recordset relies on values derived from the Journal recordset and, in turn, passes on other values to the Entries recordset. To fully carry out its functions, the TimePeriods recordset needs to be moved from its current location and placed after the Journal recordset and before the Entries recordset.

1.	From the Server Behaviors panel, select the TimePeriods recordset.
2.	In Code view, extend the selection to include the three associated parameters and cut it. The selected code should include: [View full width] <% Dim TimePeriods__IDParam TimePeriods__IDParam = "10" If (Journal.Fields("JournalID").value <> "") Then TimePeriods__IDParam = Journal.Fields("JournalID") .value End If %> <% Dim TimePeriods__PeriodField TimePeriods__PeriodField = "Month" If (PeriodColumn <> "") Then TimePeriods__PeriodField = PeriodColumn End If %> <% Dim TimePeriods__ArchPeriod TimePeriods__ArchPeriod = "Date3" If (ArchPeriod <> "") Then TimePeriods__ArchPeriod = ArchPeriod End If %> <% Dim TimePeriods Dim TimePeriods_numRows Set TimePeriods = Server.CreateObject("ADODB .Recordset") TimePeriods.ActiveConnection = MM_Recipes_VB_STRING TimePeriods.Source = "SELECT DISTINCT (" + Replace (TimePeriods__PeriodField, "'", "''") + ") AS Period, " + Replace(TimePeriods__ArchPeriod, "'", "''") + " AS IncludeDate FROM JournalArchiveDates WHERE EntryJournal = " + Replace (TimePeriods__IDParam, "'", "''") + " ORDER BY " + Replace(TimePeriods__ArchPeriod, "'", "''") + " DESC" TimePeriods.CursorType = 0 TimePeriods.CursorLocation = 2 TimePeriods.LockType = 1 TimePeriods.Open() TimePeriods_numRows = 0 %> [View full width] <% var TimePeriods__IDParam = "10"; if (String(Journal.Fields("JournalID").value) != "undefined" && String(Journal.Fields("JournalID") .value) != "") { TimePeriods__IDParam = String(Journal.Fields ("JournalID").value); } %> <% var TimePeriods__PeriodField = "Month"; if (String(PeriodColumn) != "undefined" && String (PeriodColumn) != "") { TimePeriods__PeriodField = String(PeriodColumn); } %> <% var TimePeriods__ArchPeriod = "Date3"; if (String(ArchPeriod) != "undefined" && String (ArchPeriod) != "") { TimePeriods__ArchPeriod = String(ArchPeriod); } %> <% var TimePeriods = Server.CreateObject("ADODB .Recordset"); TimePeriods.ActiveConnection = MM_Recipes_STRING; TimePeriods.Source = "SELECT DISTINCT ("+ TimePeriods__PeriodField.replace(/'/g, "''") + ")AS Period, "+ TimePeriods__ArchPeriod.replace(/' /g, "''") + " AS IncludeDate FROM JournalArchiveDates WHERE EntryJournal = "+ TimePeriods__IDParam.replace(/'/g, "''") + " ORDER BY "+ TimePeriods__ArchPeriod.replace(/'/g, "''") + " DESC"; TimePeriods.CursorType = 0; TimePeriods.CursorLocation = 2; TimePeriods.LockType = 1; TimePeriods.Open(); var TimePeriods_numRows = 0; %> [View full width] <cfquery name="TimePeriods" datasource="Recipes"> SELECT DISTINCT (#PeriodColumn#) AS Period, #ArchPeriod# AS IncludeDate FROM JournalArchiveDates WHERE EntryJournal = #IDparam# ORDER BY #ArchPeriod# DESC </cfquery> [View full width] $IDParam_Archive = "1"; if (isset($_GET['ID'])) { $IDParam_Archive = (get_magic_quotes_gpc()) ? $_GET['ID'] : addslashes($_GET['ID']); } mysql_select_db($database_Recipes, $Recipes); $query_Archive = sprintf("SELECT EntryID, EntryJournal, EntryBy, EntryTitle, EntryText, unix_timestamp(EntryDate) AS EntryDateTimeStamp FROM JournalEntries WHERE EntryJournal = %s ORDER BY unix_timestamp(EntryDate) ASC ", $IDParam_Archive); $Archive = mysql_query($query_Archive, $Recipes) or die(mysql_error()); $row_Archive = mysql_fetch_assoc($Archive); $totalRows_Archive = mysql_num_rows($Archive);
3.	Place the cursor below the Journal recordset and press Ctrl-V (Command-V) to paste in the cut code.
4.	Be sure to save your page before continuing.

Step 8: Establish Variables

With the TimePeriods recordset in the proper location, we can now place the necessary custom code. ASP and ColdFusion need only declare and define two variables (ArchPeriod and PeriodColumn). PHP, on the other hand, has a bit more to do to enable the date manipulation required.

1.	In Code view, place the cursor above the just-moved TimePeriods recordset (and below the Journal recordset) and insert the following code: From the Snippets panel, open the Recipes > Journal > Custom Code folder for your server model and insert the TimePeriod RS - Variable Declarations snippet. [View full width] <% Dim ArchPeriod, PeriodColumn ArchPeriod = "Date" & Journal.Fields ("JournalArchive").value PeriodColumn = "Month" if (Journal.Fields("JournalArchive").value=1) then PeriodColumn = "Day" if (Journal.Fields("JournalArchive").value=2) then PeriodColumn = "Week" %> [View full width] <% var ArchPeriod = "Date"+Journal.Fields ("JournalArchive").value; var PeriodColumn = "Month"; if (Journal.Fields("JournalArchive").value==1) PeriodColumn = "Day"; if (Journal.Fields("JournalArchive").value==2) PeriodColumn = "Week"; %> <cfset ArchPeriod = "Date" & Journal.JournalArchive> <cfset PeriodColumn = "Month"> <cfif (Journal.JournalArchive EQ "1")> <cfset PeriodColumn = "Day"> </cfif> <cfif (Journal.JournalArchive EQ "2")> <cfset PeriodColumn = "Week"> </cfif>
2.	PHP users should place their cursor between the TimePeriods and Entries recordsets and insert the following code: From the Snippets panel, insert the Recipes > Journal > Custom Code_PHP > TimePeriod Code snippet: [View full width] $dataArray = ""; for($i=0;$i<$totalRows_TimePeriods - 1;$i++){ $date = $row_TimePeriods['EntryDateTimeStamp']; $day = date("d",$date); $month = date("m",$date); $year = date("Y",$date); if($row_Journal['JournalArchive'] == 1){ $label = date("D M d Y",$date); $startDate = mktime(0,0,0,$month,$day,$year); $endDate = mktime(23,59,59,$month,$day,$year); $value = "1/" . $startDate . "/" . $endDate; $ArchiveWhere = " AND EntryDate >= CURDATE()"; } if($row_Journal['JournalArchive'] == 2){ $startDay = date("w",$date); $startWeek = mktime(0,0,0,$month,$day - $startDay,$year); $endWeek = mktime(0,0,0,$month,$day - $startDay + 7,$year); $label = date("d/m",$startWeek) . "-" . date("d/m",$endWeek) . " " . date("Y",$endWeek); $value = "2/" . $startWeek . "/" . $endWeek; $ArchiveWhere = " AND EntryDate >= DATE_SUB(CURDATE(),INTERVAL 7 DAY)"; } if($row_Journal['JournalArchive'] == 3){ $daysInMonth = date("t",$date); $startMonth = mktime(0,0,0,$month,1,$year); $endMonth = mktime(0,0,0,$month,$daysInMonth,$year); $label = date("F Y", $startMonth); $value = "3/" . $startMonth . "/" . $endMonth; $ArchiveWhere = " AND EntryDate >= DATE_SUB(CURDATE(),INTERVAL 30 DAY)"; } $temp = $label . "*" . $value; if($i == 0){ $dataArray[] = $temp; } else { if(!in_array($temp,$dataArray)){ $dataArray[] = $temp; } } $row_TimePeriods = mysql_fetch_assoc($TimePeriods); } if(isset($_POST['TimePeriod']) && $_POST['TimePeriod'] != ""){ $tempArray = explode("/",$_POST['TimePeriod']); $ArchiveWhere = " AND unix_timestamp(EntryDate) < " . $tempArray[2] . " AND unix_timestamp(EntryDate) > " . $tempArray[1]; }
3.	Save your page.

PHP users can skip the following step and proceed to the final one, step 10, page 435.

Step 9: Add Custom SQL Clause

For ASP and ColdFusion users, it's time to insert the custom WHERE clause, which adds an extra filter to the Entries recordset and effectively displays only the entries of the selected archive period. The necessary custom code is applied in two steps: First, a separate code block is added to put the proper values into the ArchiveWhere variable. Then, a code line is inserted into the Entries recordset to integrate the ArchiveWhere filter.

1.

In Code view, ASP users should place the cursor before the SQL parameters associated with the Entries recordset, while ColdFusion users should put the cursor before the <cfquery> tag for the TimePeriods recordset. Then, insert the following code: Note This step is not necessary for PHP developers, because the functionality here was integrated into the custom code added in the last step. From the Snippets panel, open the Recipes > Journal > Custom Code folder for your server model and insert the Entries RS - ArchiveWhere snippet. [View full width] <% Dim ArchiveWhere ArchiveWhere = " AND EntryDate >= Date()" if (Journal.Fields("JournalArchive").value = 2) then ArchiveWhere = " AND EntryDate >= Date()-7 AND DatePart('ww',EntryDate) = DatePart('ww',Date())" if (Journal.Fields("JournalArchive").value = 3) then ArchiveWhere = " AND EntryDate >= Date()-31 AND DatePart('m',EntryDate) = DatePart('m',Date())" %> <% if (cStr(Request("TimePeriod"))<>"") then ArchiveWhere = " AND EntryDate >= #" & cStr (Request("TimePeriod")) & "# AND EntryDate < #" & cStr(Request("TimePeriod")) & "# + 1" if (Journal.Fields("JournalArchive").value = 2) then ArchiveWhere = " AND EntryDate >= #"+cStr (Request("TimePeriod")) & "# AND EntryDate < #" & cStr(Request("TimePeriod")) & "# + 7" if (Journal.Fields("JournalArchive").value = 3) then ArchiveWhere = " AND EntryDate >= #"+cStr (Request("TimePeriod")) & "# AND EntryDate < #" & cStr(Request("TimePeriod")) & "# + 31 AND DatePart ('m',EntryDate) = DatePart('m',#"+String(Request ("TimePeriod"))+"#)" end if %> [View full width] <% var ArchiveWhere = " AND EntryDate >= Date()"; if (Journal.Fields("JournalArchive").value == 2) ArchiveWhere = " AND EntryDate >= Date()-7 AND DatePart('ww',EntryDate) = DatePart('ww',Date())"; if (Journal.Fields("JournalArchive").value == 3) ArchiveWhere = " AND EntryDate >= Date()-31 AND DatePart('m',EntryDate) = DatePart('m',Date())"; %> <% if (String(Request("TimePeriod"))!="undefined") { var ArchiveWhere = " AND EntryDate >= #"+String (Request("TimePeriod"))+"# AND EntryDate < #"+String(Request("TimePeriod"))+"# + 1"; if (Journal.Fields("JournalArchive").value == 2) ArchiveWhere = " AND EntryDate >= #"+String (Request("TimePeriod"))+"# AND EntryDate < #"+String(Request("TimePeriod"))+"# + 7"; if (Journal.Fields("JournalArchive").value == 3) ArchiveWhere = " AND EntryDate >= #"+String (Request("TimePeriod"))+"# AND EntryDate < #"+String(Request("TimePeriod"))+"# + 31 AND DatePart('m',EntryDate) = DatePart('m',#"+String (Request("TimePeriod"))+"#)"; } %> [View full width] <cfset ArchiveWhere = " AND EntryDate >= Date()"> <cfif (Journal.JournalArchive EQ 2)> <cfset ArchiveWhere = " AND EntryDate >= Date()-7 AND DatePart('ww',EntryDate) = DatePart('ww',Date())"> <cfset PeriodColumn = "Week"> <cfset ArchPeriod = "Date2"> </cfif> <cfif (Journal.JournalArchive EQ 3)> <cfset ArchiveWhere = " AND EntryDate >= Date ()-31 AND DatePart('m',EntryDate) = DatePart('m' ,Date())"> <cfset PeriodColumn = "Month"> <cfset ArchPeriod = "Date3"> </cfif> <cfif isDefined("Form.TimePeriod") AND Form .TimePeriod NEQ ""> <cfset ArchiveWhere = " AND EntryDate >= ##" & Form.TimePeriod & "## AND EntryDate < ##" & Form .TimePeriod & "## + 1"> <cfif (Journal.JournalArchive EQ 2)> <cfset ArchiveWhere = " AND EntryDate >= ##" & Form.TimePeriod & "## AND EntryDate < cDate('" & Form.TimePeriod & "') + 7"> </cfif> <cfif (Journal.JournalArchive EQ 3)> <cfset ArchiveWhere = " AND EntryDate >= ##" & Form.TimePeriod & "## AND EntryDate < cDate('" & Form.TimePeriod & "') + 31 AND DatePart('m' ,EntryDate) = DatePart('m',##"+Form.TimePeriod+"##)"> </cfif> </cfif>

2.

Save your page.

The second half of this code adjustment is to append the ArchiveWhere variable, just programmed, to the proper place in the Entries recordset.

1.

Locate the Entries recordset in Code view, and place the cursor in the appropriate place to add the following code.

From the Recipes > Journal > CustomCode folder for your server model, insert the Entries RS - Append ArchiveWhere snippet.

	Place the cursor on a new line after the SQL declaration and add:
	[View full width] Entries.Source = Entries.Source & ArchiveWhere & " ORDER BY EntryID DESC".

	Place the cursor on a new line after the SQL declaration and add:
	[View full width] Entries.Source += ArchiveWhere + " ORDER BY EntryID DESC".

	Place the cursor at the end of the SQL declaration, add a space, and add:
	[View full width] #PreserveSingleQuotes(ArchiveWhere)# Order By EntryID DESC.

The function used for the ColdFusion code is a special built-in one that formats the string properly for SQL.

2.

Save your page when you're done.

Step 10: Activate List with JavaScript

The final step on the Archive page is to activate the Time Period drop-down list. With a bit of JavaScript and a standard Dreamweaver behavior, we'll be able to submit the form containing the list whenever the user makes a different selection from the list. After the form is submitted, a new Entries recordset is created, and the journal entries for the requested archive period are displayed.

1.	In Design view, select the TimePeriod list form element.
2.	From the Behaviors panel, choose Add (+) and select Call JavaScript from the list.
3.	In the Call JavaScript dialog field, enter code to submit the current form: In the JavaScript field, enter document.ChangeTimePeriod.submit()
4.	Click OK to apply the behavior and save your page.

For PHP

If there are no records in the Entries recordset, PHP displays a blank row. Dreamweaver's Show If Record Is Note Empty server behavior is perfect for working around this undesired behavior.

1.	Select any of the dynamic text elements in the cell under the Message Post, like Entries.EntryTitle.
2.	From the Tag Selector, choose the <tr> tag to select the surrounding row.
3.	From the Server Behaviors panel, choose Add (+) and choose Show Region > Show If Recordset Is Not Empty.
4.	In the Show If Recordset Is Not Empty dialog box, select Entries from the Recordset list; click OK when you're done.
5.	Save your page.

This page and the entire application are now complete and ready for testing and deployment. When browsing the Archive page, select the TimePeriod list to view different archives. Use the Journal navigation to visit the Journal Manager, and then select View under the Archive column to inspect the archives of a different journal [r6-17].

r6-17.