Another part of the ever-ongoing project is to find a way to add a CMS function to the timetables.

Now we really should (and almost certainly will in the future) simply API data from our bookings and reservations system onto our site, so this solution is more of a stop-gap, but it does give me an excuse to fiddle with code, and anyone writing an excuse to help me refine it!

So, in essence we break down the list of flights/sailings into categories, for the ship it’s a little different hence; general, special, tidal and gig weekend sailings. So a database with a category field was created, my plan was (rightly or wrongly) to page through the recordset as many times as there were categories, the reason? So I can display them in sections on the page.

The CMS page is more or less the same as you see on the site http://www.islesofscilly-travel.co.uk/timetable_sc3.asp the main difference being that each row is a form, and each field is editable and has an update and delete button.

I’ve also added some (hopefully) intelligence in the page(s) that records will not display past their advertised date (much like the offers page) which keeps the whole thing nice and clean, and hopefully will mean that we can update as we go.

In order to get this happening I envisaged two aspects; 1) cycling through the recordset more than once 2) splitting the page down by category

To start me off I wanted a list of categories, this can be done by getting a “disctinct” set of data from a recordset

Select Distinct categoryname
From tablename

This yields a short list of distinct categories,next thing was to loop through them, but I also wanted to put a drop-down list on each row (in case we decided to change a category) so I added the list to an array, but in trying to cover off all of my bases I wanted the array to be dynamic;

So, I created a variable to count the number of records and used the following code;

number_of_records = 0
DO WHILE NOT recordset.EOF
number_of_records = number_of_records + 1
recordset.MoveNext
Loop

That gave me the magic number, then we reset the recordset and populated the array. The script moves to the first record, creates an array based on the number of records we’d previously counted (I had to use ReDim here, not 100% sure why!) then loop through the recordset adding the categories into the array.

Recordset.MoveFirst
ReDim list_of_categories(number_of_records)
Dim array_id
Do While Not Recordset.EOF
array_id = array_id + 1
list_of_categories(array_id)  = Recordset.Fields.Item(“categoryname”).Value
Recordset.MoveNext
Loop

Ok, so that’s populated our array. Next job is to cycle through the full recordset (remember the first recordset was only a DISTINCT list of categories) and acutally display the details per category. For this I had to use a second version of the recordset.

Recordset.MoveFirst
Do While Not Recordset.EOF
Which_Category = (Recordset.Fields.Item(“categoryname”).Value
<in here we have some html, table headings>
Do While Not Recordset2.EOF
If (Recordset2.Fields.Item(“categoryname”).Value) = Which_Category then
<display the data – as it matches the current category we are looking for>
Else
<do nothing>
End If
Recordset2.MoveNext
Loop

So, this moves through each category type (Recordset) and cycles through the whole, non-DISCTINCT, recordset to display all records in a given category, before moving on to the next. Once complete we have a list by category of all records.

Now, the clever bit (at least for me anyway!) is that I added in a form to each row, and, using Dreamweavers built-in application objects, added an update record script. At the end of each row there is the previously mentioned drop-down list that allows you to change the designation of each record if required. And by using the code that checks if a record is part of the category I also set the selected option correctly.

This gives us inline editing, bundled with my multi-purpose delete record script and we have a powerful CMS page that will deliver the required content.

Reading it back now I barely understand how it works! But that it does is enough!