Database Queries for Mere Mortals

Nearly every Internet hosting company—certainly all of the ones I'd recommend considering—offers a MySQL database option. My old site hosting company, Pair, did, as does the one I changed to, a non-commercial service run by friends. MySQL, the "World's Most Popular Open Source Database," is one of the most prevalent ways to host data for smaller back-end web databases. MySQL is guru-friendly, and standard enough that even not-so-computer-savvy companies are using it (or more likely, having it used) for their online stores, data lookup sources, and the like. MySQL is often coupled by PHP to the front-end logic of a website; PHP is somewhat less scary to learn. Lately, BYTE.com Contributing Editor Ernest Lilley has been doing quite a bit of PHP programming on his www.sfrevu.com site, to populate such things as a book's ISBN, publishing date, price, etc., extracting the data directly from Amazon. This electronic data sharing is a relatively new and very interesting development.

The good news is that, even without formal programming, just about anyone can extract information from a MySQL database, into (say) Excel, Access or FileMaker, where more familiar tools may make it easier to torture data into shape. My son Alex has been doing some of this lately, and here's his report.

MyODBC, or more formally, "MySQL Connector/ODBC," is freely downloadable for many platforms, including Windows (see http:// dev.mysql.com/downloads/connector/odbc/3.51.html). (ODBC, short for Open DataBase Connectivity, is a standard way for apps to query databases.) After installing MyODBC, there will be a new data source available; in Windows, they're listed in Start | Programs | Administrative Tools | Data sources. You'll still need to ask your web programmer for the database IP address and name, plus the username and password, but once set up you can pull most of your own reports without bothering him. Make sure that you use a read-only password for this access, or you'll risk messing with the "live" data, which you probably didn't want.

Once the connector is in place, Access will let you link to external data (File menu | Get External data); if you know Access then this will be relatively straightforward. In Excel, click on the cell (or more likely the first cell of the column) into which you'll import data, Data menu, Import External Data, New Database Query, then the Choose Data Source dialog box, where the MyODBC link you created should appear. More on that in a moment.

The whole subject of grabbing data from a web back-end, let alone from an SQL database, seemed a bit scary at first prospect. After an hour of meddling, the tools became reasonably straightforward, at least for basic queries. I dove into the topic because of one work project, but the exact same need came up two weeks later for another client. I can hardly say I'm an expert but I feel confident anyone with intermediate-level Excel experience can pull simple reports.

A tip: If you're using Microsoft Office, maintain and review your external data links with Microsoft Query. Set up the basic query (even if it's not quite correct) using the Data Source wizard, then open Query and edit it. I discovered this a bit late in the process; I needed to edit out test data from my analysis, but the Data Source wizard doesn't offer a way to select data unless the field used for selection is being imported. So, I ended up importing the record number (which I didn't need) plus the relevant data, then creating an Excel formula to select only the rows I did need. Oops. Result: A query in two parts, harder to edit later, and incidentally slower to complete. I updated an even dozen queries for consistency.

Once your queries are in place, you'll probably turn them into charts and pretty pictures for the boss. With a MyODBC link, the queries themselves are "live," and only require refreshing (click on a linked area to be refreshed, then Data menu | Refresh Data) to have the latest values. If there's even a tiny chance that your one-shot queries will be re-used, I recommend documenting the queries right in the Excel spreadsheet where they're performed, good practice for any form of scripting or programming. I made a worksheet just for performing the queries, then linked that to another, prettier worksheet with the summary results and the charts derived from them. The complexity is there when needed, but hidden otherwise.

Back to charts. Just as the Excel ODBC links are live, so are any Excel charts linked to those values. However, if you copy a chart from Excel to, say, Word, that copy won't be live; you'll have to copy the chart every time it gets updated. I have no faith in Office's live Object Linking and Embedding (OLE); every time I've tried it, Word has become unusably slow and the links break when I edit the Word document. Perhaps a proponent of StarOffice or the like has had better results. I work with a 600 MHz laptop, and faster machines might get better results from OLE, although we know no one who reports much success with it.

Also, Excel's charting function, even in Office 2003, is barely above rudimentary. Hammering out a professional pie chart with legible, smartly placed legends is a good precursor to impromptu testing for your computer's glide ratio. If you become the office's designated stuckee for creating regular, ongoing reports pulled from a live data source, get the boss to spring for Crystal Reports (now from Business Objects or the like. Otherwise, you'll end up performing a lot of manual work to adjust the look of the chart every time the data change radically.

Copyright © 2004 CMP Media LLC

Questions or problems regarding this web site should be directed to abeckman@outdoorssite.com.

Copyright © 2008 Art Beckman. All rights reserved.

Last Modified: March 9, 2008