Dan Knight
- 2002.07.03
We've finally finished automating link updates on the home page,
the WebTV home page, the mobile edition home page (a
subscriber-only feature), and most editorial content. It's been
quite a project.
Creating the "local links" database was pretty simple, although
we did have a few different fields compared to the external links
database. Because most of our new content is available in regular,
WebTV, and mobile versions (the latter especially formatted to work
well on Palms), we need to track three different URLs. Here's the
naming convention we use:
- General: http://lowendmac.com/column/02/0703.html
- Mobile: http://lowendmac.com/column/02/0703.htm
- WebTV: http://lowendmac.com/column/02/0703w.htm
Every link on the site begins with "http://lowendmac.com", so we
don't have to include that part in our database. And rather than
create three longer records with the balance of the URL, we use
four: one to hold the "/column/02/" part and three to hold the
general, mobile, and WebTV final part of the URL.
Entries are dated by their publication date and also time
stamped with a Unix time code when entered into the database, just
like we did with external links. In fact, except for having to
track three types of pages, the local links database is very
similar to the external links database.
Displaying the Data
Where things become very different is when we display the data.
For external links, sorting by time stamp is fine, but we run into
some issues - and some opportunities.
First, I want to include a link to "Today in LEM History." This
could come first, but I prefer to focus on the new content and want
the history link to show up after today's new articles. That means
somehow separating today's links from yesterday's.
And that brings up another issue - days with no updates. I don't
usually add content over the weekend or on holidays. So we can't
simply search for today's date; we have to search for the most
recent date and display that first.
Brian, who has been helping me a lot with this, was on a church
sponsored mission trip in Washington, D.C., last week, so Stephen,
our 14-year-old PHP wizard and the publisher of PetCube.com (which gets more
traffic every day than Low End Mac!), helped me out.
Here's the snippet of code that finds that most recent date:
$latestdate = mysql_fetch_array(mysql_query("SELECT * FROM
links ORDER BY timestamp DESC LIMIT 1"));
And here's the code that displays local links for that day:
- $get_links = mysql_query("SELECT * FROM links WHERE pubdate
= '$latestdate[pubdate]' ORDER BY timestamp DESC");
- while ($array = mysql_fetch_array($get_links))
- {
- echo "<li>";
- if ($array[flag]<>"")
- {echo"<img src=/art/$array[flag].gif width=17 height=13
align=middle> ";}
- echo "<a
href=\"$array[path]$array[html]\">$array[linktext]</a>,
";
- if ($array[author]<>"")
- {echo"$array[author], ";}
- if ($array[columnname]<>"")
- {echo"$array[columnname], ";}
- echo "$array[pubdate].
- $array[description]</li>";
- }
This creates a bullet list item for local content just like we
did last week with external links.
Next we add our history link with the following code:
- $thisdate = date(md);
- $thisrecord = date(nd);
- $today = date("F j");
-
- $get_links = mysql_fetch_array(mysql_query("SELECT * FROM
lemhistory WHERE datefield = '$thisrecord'"));
-
if ($get_links == "") {die ("no
record");}echo "<li> <a
href=\"/arc/$thisdate.html\">$today in LEM
history:</a>
$get_links[stories]</li>";
|
We discovered the above code didn't quite work on July 8, when
we had no archive link. Here's how the revised code:
- if ($get_links <> "")
- {echo "<li> <a
href=\"/arc/$thisdate.html\">$today in LEM history:</a>
$get_links[stories]</li>";}
The difference is that instead of telling this subroutine to die
if there is no record, we now tell it to display a record if there
is one. Problem solved.
|
Our archive filenames are of the format MMDD, but our MySQL
database strips off the leading zero in our integer field, which is
why we need both $thisdate and $thisrecord.
$today displays the date using the full name of the month
and the one or two digit day. If there is no record for a specific
date, which happens maybe twice a month, no LEM archive link is
displayed.
After this, we need to display the previous day's links, and
here's where we have a unique opportunity to change the sort order
from timestamp to something else.
As you may recall, we had to call a second PHP script to create
a counter for external links. With local links, I'm less concerned
with how many times a link is followed from our home page and more
interested in how many times the article itself is read. So instead
of calling a second PHP script that increments the counter and then
leads to the article in question, I've put a small PHP script on
the article's page that increments the counter every time the
article is read - even if it's being linked from MacSurfer or
another outside source.
Based on the assumption that the article that most people read
one day is the one from that date that will generate the most
interest the next day, instead of sorting older links by timestamp,
we sort them by popularity.
- $previous_date = mysql_fetch_array(mysql_query("SELECT *
FROM links WHERE pubdate != '$latestdate[pubdate]' ORDER BY
timestamp DESC LIMIT 1"));
-
- $previous_links = mysql_query("SELECT * FROM links WHERE
pubdate = '$previous_date[pubdate]' ORDER BY clicks
DESC");
-
- while ($previous_array =
mysql_fetch_array($previous_links))
- {
- <display code omitted>
- }
The first line finds the second most recent date by only
checking publication dates not equal to (!=) the latest
date used above. And in the next section, which I'm not posting, we
do the same thing for one day earlier by checking dates not equal
to the $latestdate or $previous_date.
And this code puts in a lot of work. The same script is called
up on the main Low End Mac home page; our iMac, Power Mac, and
'Book indexes; and at the bottom of a lot of our editorial content.
(Eventually all of it, but it's going to take some time to update
and upload all those pages.)
I've slightly modified this code for our mobile edition and
WebTV home pages. Best of all, I don't have to manually update all
of these pages 2-3 times daily - the computer is doing it for me
and making my life simpler.
