The Editorial Database

Our database for the editorial content of the site is broken up into several fields within one SQL table. It is a very simple table containing all of the basic information for the stories.

We originally had many more fields, but as the years passed and we tightened up the database, some fields became unneccessary. The current fields are ID, Headline, Authors, Pic1_path, Pic2_path, Story, Keywords, PostDate, PubDate, Issue, and Level.

The short descriptions of these fields are

  • ID is just an automatically generated ID number.
  • Headline is a text field containing the headline.
  • Authors is the Author of the story if any is to be used.
  • The Pic_path fields contain the file path to the images used in the article. When the story is uploaded any pictures in the form are processed using ASPUpload and placed in a particular directory and then the path is noted here in the database.
  • Story is a Long text field containing the body of the article. When this is processed in the form, a javascript is run to take every line break and convert it to </p><p>. Then when the story is called to the page, we just need to hard code into the page the very first and last </p> tags.
  • The keywords field contains, obviously, keywords related to the article. This is needed by the search page on the site. A SQL ‘like’ clause is used against this field to return any appropriate results.
  • The postdate and pubdate are self explanitory and are used to make sure the stories appear in the appropriate order. By post dating stories in the PubDate field, we can also load up stories days in advance for later publishing.
  • Issue is used because we have 4 different regional papers. The issue field is used to denote NE, SE, SW, or MW and is used on the search page when someone wants to search on a specific region.
  • The level field is to specify wether the story should appear in the main story section of the sidebar story section.

Calling this information to the front page at this point is relatively easy. After making a connection to the database and creating a recordset, we use a SQL select statement along the lines of

SELECT TOP 5 editorials.* FROM editorials WHERE (initialLevel = ‘Head’) and edPubDate<='” & date() ORDER BY edPubDate DESC, edPostDate DESC.

Now this isnt the Exact code, but you get the idea.

This returns the last 5 stories from today’s date and puts them in date order from newest to oldest. Then we just set the headline as an h1 tag, the author as an h2 and the story as a p tag. There is also a javascript funtion that I will put up later that only returns the first 300 characters of the body copy, while at the same time making sure it ends at the end of a sentence.