Dan Knight
- 2002.06.24
I've been thinking long and hard about automating different
parts of the site and making the manual three-times-a-day updates a
thing of the past. The last three pieces are handling new articles
online, tracking new content, and working with links to content on
external sites.
Last week I decided it was time to sit down and create a MySQL
database for those outside links. Once the system is in place, new
links will be available on the Low End Mac
home page as well as the iMac
Channel, iBook & PowerBook
Page, Power Macintosh page,
and WebTV index immediately. We'll still
be manually adding content to some other pages, but our frontline
link pages can have fresh outside links several times during the
day.
Crucial Fields
There's a lot involved here, starting with the fields needed to
create and track these articles:
- Category
- Linked text
- URL
- Author, when known
- Column name, not always present
- Website
- Date of publication
- Description
I didn't have to break things down that thoroughly, but this
will eventually let me search the database by author, website, or
column name to see if some sites or writers tend to receive more
clicks - but that's a topic for a future column.
After wading through two months of link archives, we came up
with about 20 categories ranging from News and Opinion to AAPL and
Oops. We also came up with fairly extensive names of authors and
websites. These lists are something we'll deal with later.
I also needed one more category, Flag, to display a U.S.,
Canadian, U.K., or Australian flag when an article has a specific
national focus. That's eight fields.
Helpful Fields
Because publication date isn't always the same as the date we
add a link - sometimes we link to a week old article, and other
times the article has a publication date several days in the future
- we added a Unix timestamp field. With an ID field to number our
database entries, that brings us to 10 fields.
We've been adding articles to the database for a few days and
went live on Saturday. Sometime today we'll remove the last few
manually added links from the home page, and you'll probably never
even notice the change, except that new links in the "Around the
Web" section are being added more frequently.
Implementation
The only glitch we ran into when creating the MySQL database
comes from the fact that MySQL timestamps are not the same as Unix
timestamps. In fact, they're not even close. A MySQL timestamp for
today would begin 20020624..., while a Unix timestamp is the number
of seconds since 1970.01.01.
We have future plans that make the MySQL timestamp problematic,
so we changed that field to an integer. More on that when we're
ready to make some changes to our display system.
Follow Up
A month ago I promised to look at the PHP code that displays our
deal of the day. Sorry it took so long, but here it is:
- $dod = date(Ymd);
- $query = mysql_fetch_array(mysql_query("select * from deals
where date = '$dod'"));
-
- echo "<li>Deal of the Day: <a
href=\"$query[URL]\">$query[text]</a>$query[extra]</li>";
As always, a million thanks to my son Brian <http://brkn.net/> for his help
with the PHP and MySQL.
And next time we'll look at some of the coding necessary to
display data when some fields may be empty.
