Managing Local Links with PHP and MySQL

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>";

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.