Low End Mac's Online Tech Journal

Creating Your My SQL Database

Website Automation with PHP and MySQL, Part 2

Dan Knight - 2002.04.24

What is PHP? I can't summarize it any better than Try a New Language: PHP:

PHP is an open-source, server-side, cross-platform scripting language that is used to generate dynamic Web pages or to build browser-based applications. The PHP script can be embedded in the HTML page to generate HTML code that is inserted in the Web page at that point, or the PHP script can generate the entire HTML output. One of PHP's strengths is easy database integration.

That database integration usually comes from a MySQL database, another open-source standard. For our purposes, creating a database will be the first thing we do - before we even begin programming in PHP. (And it's all free.)

Creating the Mac of the Day Database

The first piece of Low End Mac we be automated was the Mac of the Day. I have a list of nearly 100 Apple models and clones, along with a page link and a brief note on the model's significance. Here's what the Mac Plus entry looks like in HTML:

<A HREF="/compact/macintosh-plus.html">Mac Plus</A> (1/86-10/90). First Mac with SCSI, memory expansion. Longest model life - over 4 years.

We could create a database containing that entire text string or create one with separate fields for the link, the model name, and the description. Neither system is inherently better than the other, but using three fields will create a system where editing the description could be done with no risk of messing up the link or the model name. It will also have a second benefit, which I'll mention later.

MySQL has three types of string or text records. (Other record types are numeric and time-related.) The CHAR type uses fixed length strings, while VARCHAR allows variable string lengths. Although that's a more efficient use of space, VARCHAR adds overhead and reduces performance. Both of these field types are limited to 255 characters, which will be plenty for our needs.

The third string record is BLOB or TEXT fields, which can be as large as 65,535 characters. We might use such fields when we work with mailing lists and articles, but we don't need such large fields right now.

The longest model name is under 30 characters, and the longest link is just over 20 characters, so we'll give ourselves a bit of room and give the model name field a maximum length of 32 and the link a maximum size of 24 characters.

A few of the descriptions are around 100 characters, so we'll set the maximum size for that text field to 128 characters.

To create the database, you'll need to log into the MySQL server you'll be using. Your ISP or system administrator should be able to provide all the information you need to do that. If you're using Telnet, type the following at the prompt to create your database:

mysql> create database macs;

This creates an empty database named "macs" - and it's just waiting for us to create a table for our data. We'll be creating the following fields:

mysql> CREATE TABLE mod (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> link CHAR(24),
-> model CHAR(32),
-> text CHAR(128),
-> );

The first line creates a table called "mod" (Mac of the Day") within our database. The first field gives each record a unique and automatically created ID number with a maximum value way up there in the billions. PRIMARY KEY declares id as the primary field in this table, meaning it is the one MySQL will index and access most readily.

NOTE: I didn't do it this way. My son Brian <http://brkn.net/> told me about a program called PHPMyAdmin that's much easier than using Telnet.

All that remains is to move the data from my current HTML file into MySQL.

Filling the Mac of the Day Database

Call me lazy, but I simply don't want to spend a lot of time rekeying this information or doing the cut-and-paste thing.

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 12 in LEM history: 99: $4,320 for a 'free' iMac - 01: 10 Commandments of Macintosh - High-end word processing for free - 02: 8 OS X gotchas - Working less with my Mac than with my PC - Microsoft Office v. X - 03: New Zealand's Mac mag - 04: Nothing else is a Mac - 07: On Mighty Toaster Wings - Jobs stirs up DRM hornet's nest - OS X 10.2 best for Classic? - 08: Too much software choice is not a blessing - A month with Windows
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