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

OWC: Get the Right Memory for Your Mac Top Quality, Competitive Price, Lifetime Backed Free Expert Support + Installation Videos too! MacBook & mini 8GB, iMac 16GB, Mac Pro up to 32GB. Click here

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.

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

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

Importing Data into Your MySQL Database

Website Automation with PHP and MySQL, Part 3

Dan Knight - 2002.04.25

Last time we created our database with four fields: id, link, model, and text. Now we need to fill the database.

Again, there are two ways to do things. The hard way is to use Telnet and manually enter all of your data. The easy way, assuming you have the data in a file, is to import your information.

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. Three fields time nearly 100 Macs would take a long time.

Besides, I already had everything in HTML format. Here's the line for the Mac Plus as an example:

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

I wanted to break this down to three fields: link, model, and text, as follows:

  • link: compact/plus.shtml
  • model: Mac Plus
  • text: (1/86-10/90). First Mac with SCSI, memory expansion. Longest model life - over 4 years.

I'd add the id field manually, since it didn't exist in my HTML file.

The first step was to take the raw HTML from my file and copy it into TextSoap, a powerful program for cleaning up text with manual line breaks, extra spaces, and other problems. Running the scrub command reconnected the HTML lines that Home Page had nicely broken into pieces.

Then we copied that code to BBEdit Lite, where our next step was to eliminate the extra space between lines of code. Search for \r\r (two returns) and replace with \r. Then save the file to the desktop.

As Brian < http://brkn.net/> explained to me, before you go any further you need to "escape" things like quote marks. So we did a global search for the quote mark and replaced it with \" - the backslash is used to indicate that the next character is literal instead of punctuation. We did the same with parentheses.

Our Mac Plus description now looked like this:

<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 also stripped out the unnecessary HTML code such as <A HREF="/ and "> and </A> . Because MySQL likes to work with comma delimited files, we replaced "> with ", " and also replaced </A> with ", ". Now it looked like this:

compact/plus.shtml", "Mac Plus", "\(1/86-10/90\). First Mac with SCSI, memory expansion. Longest model life - over 4 years.

That had us most of the way there, but we still needed to add quote marks at the start and end of each line, so we had BBEdit search for \r and replace it with "\r". Three fields down; one to go.

"compact/plus.shtml", "Mac Plus", "\(1/86-10/90\). First Mac with SCSI, memory expansion. Longest model life - over 4 years."

Next we manually numbered every line in our database. The Mac Plus is the 27th in our list, so the entry now looked like this:

"27", "compact/plus.shtml", "Mac Plus", "\(1/86-10/90\). First Mac with SCSI, memory expansion. Longest model life - over 4 years."

Brian and I had created 89 records that were just about ready to be imported into the MySQL database we'd created. Just a couple more steps. We needed to preface each line with INSERT INTO mod (id,link,model,text) VALUES( and append ); to the end of the line. Again, we did a search for the return (\r in BBEdit) and replaced it with );\rINSERT INTO mod (id,link,model,text) VALUES( - resulting in the following

INSERT INTO mod (id,link,model,text) VALUES("27", "compact/plus.shtml", "Mac Plus", "\(1/86-10/90\). First Mac with SCSI, memory expansion. Longest model life - over 4 years.");

It probably takes longer to explain the steps than it took to do them. Then it was time to import the whole text file into MySQL using PHPMyAdmin. It failed the first two or three times because I'd accidentally given two lines the same number.

Once the import was successful, we were done. The database had been created. Now all we needed to do was use the data to create Mac of the Day entries. 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-2009 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

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.