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.
- 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.