Low End Mac Reader Specials

TypeStyler For Mac OS X is Now Shipping! Download The Free Fully Functional 60 Day Tryout at www.typestyler.com

Poker Mac Don't install Parallels to play poker online! Poker Mac will show you how to download and install a native Mac poker application such as Full Tilt Poker Mac.

Compare products like desktop computers, apple laptops, apple macs, and LCD Monitors side by side! All the information and reviews to make the best purchasing decision for new mobile phones, sat nav systems, or MP3 players. The Ciao online shopping community makes searching products easy for you.

Low End Mac's Online Tech Journal

Managing External Links

Website Automation with PHP and MySQL, Part 9

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:

  1. Category
  2. Linked text
  3. URL
  4. Author, when known
  5. Column name, not always present
  6. Website
  7. Date of publication
  8. 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. LEM

Recent Online Tech Journal Columns

Links for the Day

Recent Content on Low End Mac

Recent Deals

About LEM | Support | Usage | Privacy | Contacts

Entire Low End Mac website copyright ©1997-2010 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, iPhone, PowerBook, MacBook, MagSafe, 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.

Custom Search

Amazon.com

Navigation

Used Mac Dealers
Apple History
Video Cards
Email Lists

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
B&H
MacMall
TechRestore
ExperCom
Crucial Memory
batteries.com

Low End Mac's Amazon.com store

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.