Low End Mac's Online Tech Journal

Managing Local Links with PHP and MySQL

Website Automation with PHP and MySQL, Part 12

Low End Mac Reader Specials

Memory To Go Special: MacPro 8 Core Memory 4GB kit $192 / 2GB kit $109. MacBook Pro / MacMini / iMac Intel Core2 DUO 2GB $44 1GB $23--Free shipping available.

Download Typestyler, still the Ultimate Styling Tool for Internet, Print and Video Graphics. Works great in Classic with a Native OS X Version on the way. Free Tryout: www.typestyler.com

LA Computer Company: LA Computer Company: Specials on AppleCare, Apple Displays, MacBooks, iMac's, MacBook Pros, Laptop and iPod accessories and more. Apple A/C Adapters for laptops starting at $25.00 Call 1-800-941-7654 or Click Here.

Other World Computing: Go Longer Between Charges with a NewerTech NuPower Batteries! Not just another battery, NuPower available for PowerBook and iBook models, is built in the USA to offer the longest Run Time + the longest useful LifeTime too! In Stock at OWC from $99.95

Mac users can finally play Party Poker for Mac. Not only that, they can also learn how to play PokerStars for Mac.

Laptop Hardware Provided by TechRestore - Overnight Mac & iPod Repairs.

Compare products like desktop computers, laptops, and LCD TVs side by side! All the information and reviews to make the best purchasing decision for a new cell phone GPS products or MP3 players. The Ciao network makes searching products easy for you.

NEW MacPro Memory 800Mhz With Apple Spec Heat Sink 2GB Kit $104 / 4GB Kit $184 / 8GB Kit $362 Click to Maximize your Macs...

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

Recent Online Tech Journal Columns

Recent Content on Low End Mac

Go to the Online Tech Journal index.

About LEM | Support | Usage | Privacy | Contacts

Entire Low End Mac website copyright ©1997-2008 by Cobweb Publishing, Inc., unless otherwise noted. All rights reserved. Advice presented in good faith, but what works for one may not work for all. Please report errors to .
  LINKS: We allow and encourage links to any public page as long as the linked page does not appear within a frame that prevents bookmarking it.
  Access our RSS news feed at http://lowendmac.com/feed.xml.
  Email may be published at our discretion; email addresses will not be published without permission, and we will encrypt them in hopes of avoiding spammers. If you prefer your message not be published, mark it "not for publication." Letters may be edited for length, context, and to match house style.
  PRIVACY: We don't collect personal information unless you explicitly provide it. For more details, see our Terms of Use.
  Low End Mac is an independent publication and has not been authorized, sponsored, or otherwise approved by Apple Inc. Apple, the Apple logo, Macintosh, iBook, iMac, eMac, iPod, PowerBook, MacBook, Mac Pro, Apple TV, and AirPort are registered trademarks of Apple Inc. Additional company and product names may be trademarks or registered trademarks and are hereby acknowledged.


Have a question?
Ask an expert!

Low End Living

Amazon.com

Navigation

Used Mac Dealers
Apple History
Best Used Macs
Video Cards
Email Lists
InfoMac's Low
End Mac Forum

Favorite Sites

MacSurfer
MacMinute
MacInTouch
MyAppleMenu
InfoMac
Macs Only!
The Mac Observer
Accelerate Your Mac
RetroMacCast
PB Central
MacWindows
The Vintage Mac
   Museum
DealMac
DealsOnTheWeb
Mac2Sell
ramseeker
Mac Driver Museum
JAG's House
System 6 Heaven
System 7 Today
the pickle's Low-End
   Mac FAQ
Abandonware
   Petition
Mac vs. PC Info

Affiliates

The Apple Store
Mac Connection
MacMall
TechRestore
MacResQ
ExperCom
Crucial Memory
batteries.com

Advertise

All of our advertising is handled by BackBeat Media. For price quotes and advertising information, please contact at BackBeat Media (646-546-5194). This number is for advertising only.

Open Link

Problems viewing this page with Internet Explorer 5.5 or 6? It works fine in other browsers, including IE 7. We recommend Firefox for those using Windows, as it is standards based and more secure than IE 6 (and earlier). More LEM visitors use Firefox than any other browser.