Low End Mac's Online Tech Journal

The Deal of the Day Database

Website Automation with PHP and MySQL, Part 7

Dan Knight - 2002.05.13

The Deal of the Day database will need several fields:

ID
URL
description
short description
extra
date

The ID will be a unique integer that automatically increments itself. The next four fields will contain text and be of type char, which allows them to be up to 256 bytes long. The date field is the date that the deal should be displayed and will be of type date.

Dates in MySQL

There are many ways to enter dates. Here in the States, most people would write today's date at 5/13/02. In most other English-speaking regions, it would be written 12/5/02. Since the Web is international, that gets confusing in a real hurry.

To address this, Low End Mac began by dating articles like this: 13 May 2002. No confusion, but a mess if you want to sort dates. We later settled on the format YYYY.MM.DD, since it simplifies sorting. It's also an international standard.

While Unix uses a timestamp, that number needs to be converted to years, months, days, hours, minutes, and seconds so we humans can make sense of it. MySQL and PHP also support the YYYY-MM-DD format for dates. Since we're not worried about date stamps and want to make it easy to schedule our Deal of the Day for a specific date, that's the format we'll use.

Creating Six Fields

We've got six fields. After analyzing field lengths from our specials of the day over the past months, we came up with the following:

ID, just a simply integer that automatically increments
URL, the URL for the day's special, up to 128 characters long
text, our long description of the day's special, up to 64 characters
short, a shorter description for the navigation bar, up to 32 characterss
extra, used for tracking code required by some affiliate programs, up to 128 characters
date, the date the special is scheduled to appear, this is also the index field for our database

That gives us a template to pour our data into - except that in this case there isn't a text file or database to import. We'll be adding our Deal of the Day one at a time, so the next project is designing a Web-based front end for editing our database and adding new records.

Join us on Facebook. Follow us on Twitter.

Recent Online Tech Journal Columns

Optimized Software Builds Bring Out the Best in Your Mac, 2009.06.30. Applications compiled for your Mac's CPU can load more quickly and run faster than ones compiled for universal use.
Low End Mac's Safe Sleep FAQ, 2009.06.15. What is Safe Sleep mode? Which Macs support it? How can you enable or disable it? And more.
The Original Macintosh, 2009.01.12. An in-depth look at the original Macintosh and how it shaped future Macs.
More in the Online Tech Journal index.

Links for the Day

Mac of the Day: Macintosh Portable, introduced 1989.09.20. The nearly 16 lb. behemoth was innovative but not a smashing success.
February 13 in LEM history: 01: Layoffs may hurt Mac market - 02: Unix for the Mac - Rage against the Macintosh - 03: Options to move data from PCs to Macs - 04: Low cost RAM for older 'Books - 06: Apple, IBM, and Intel - 07: Picking the right cheap computer, new or used - 08: I needed to find an older Mac
Support Low End Mac

Recent Deals

About LEM Support Usage Privacy Contact

Low End Mac is an independent publication and has not been authorized, sponsored, or otherwise approved by Apple Inc. Entire Low End Mac website copyright ©1997-2012 by Cobweb Publishing, Inc. unless otherwise noted. All rights reserved. Apple, the Apple logo, Macintosh, iPad iPhone, iMac, iPod, 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.
Advice is 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.
Email may be published at our discretion unless marked "not for publication"; email addresses will not be published without permission, and we will encrypt them in hopes of avoiding spammers. 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.

Custom Search

Follow Low End Mac on Twitter
Join Low End Mac on Facebook

Low End Mac Reader Specials

TypeStyler 11 is now in the Mac App Store!! -- Special Introductory Price of $59.95!! -- To Buy From The Mac App Store Click Here Now!! Or buy direct from Strider Software.

Don't install Parallels to play poker online! Poker Mac will show you how to download and install a native Mac poker and Mac Casino applications in minutes.

Affiliates

Amazon.com
The Apple Store
The iTunes Store
PC Connection Express
GainSaver
Parallels Desktop for Mac
eBay