TweetFollow Us on Twitter

Database Modification with a GUI

Volume Number: 20 (2004)
Issue Number: 4
Column Tag: Programming

Untangling the Web

by Kevin Hemenway, Windusrtian Tarutaru

Database Modification with a GUI

Modifying our MySQL database further with GUI-based editors.

For the past three or four issues, we've been exploring the MySQL database server: how to create databases, database users and their permissions, as well as how to access data from the mysql command line shell, and the Perl and PHP programming languages. In our journeys, we've also seen numerous examples of how to insert, modify, select, and delete data using the Structured Query Language (SQL).

Now that we've got some initial grounding in the manual way of doing things, we can take a brief tour of how to accomplish things visually instead. The two visual tools we present in this article, CocoaMySQL and phpMyAdmin, are roughly equivalent to the included mysql shell: you'll still need to know some SQL to get the most use out of them.

CocoaMySQL. Free, Open sourced, and OS X Only

The first utility we'll look at is CocoaMySQL from It's own description is succinct enough: CocoaMySQL is an application used to manage MySQL databases (locally or over the internet). It lets you add and remove databases and tables, change fields and indexes, view and filter the content of tables, add, edit and remove rows, perform custom queries and dump tables or entire databases. When you open CocoaMySQL, first time or not, you'll always be prompted for your database settings (Figure 1).

Figure 1: Configuring a database connection in CocoaMySQL.

For our Host, we'll use "" (or "connect to the MySQL server on this machine"), and you can leave Socket blank. User and Password could be one of two things: the root user created when we first installed MySQL, or the specific username and password of the database we've been fiddling with. If you choose to access your MySQL server as the root user, you can leave the Database field blank, as you'll be able to choose from a master list of databases (via the dropdown menu on the left side of Figure 1). You can also ignore the Database entry entirely: a lesser-privileged MySQL user would be given only a list of databases they have permission to (for example, leaving User and Password blank would give you access to the MySQL test database created during installation). If you'd like to connect to a specifically named database, have a blast doing so. You can save your settings by choosing the "Favorites" dropdown and then "Save to favorites..."

Figure 2 shows us connected as the davemarksman user to the mactech database. The list of database tables is shown on the left hand side, and the currently selected person table is described in the right hand pane. Depending on the level of access your MySQL user has, you'd be able to add, modify, or delete rows, as well as indexes, for this particular table.

Figure 2: The person table of our mactech database.

Particularly handy are two of our toolbar buttons. "Optimize Table" will do as it suggests: some housekeeping to keep tables that have a regular (and healthy) flow of row updates. The MySQL documentation suggests you'd only need to do this once a week or month for the heaviest of database tables, but if you're in CocoaMySQL on a regular basis, it's hard not to flick a click in its general direction. Our neighbor, "Create Table Syntax", simply spits out the raw SQL used to create the currently selected table (Figure 3). You may not find yourself using this button a lot, but it's far quicker than issuing the SQL manually.

Figure 3: The results of a "Create Table Syntax" click.

You'll also notice another handy feature of CocoaMySQL: the console, enabled with the "Show Console" toolbar button (see Figure 3). It keeps a running log of every SQL statement you've issued during your mousing with the GUI. This becomes very useful and instructional when you're still a SQL neophyte.

Let's choose the "books" table from our "Tables" menu, and click on the "Content" tab of our right-hand pane. You'll see a list of all the data within that table (Figure 4). You can rearrange the width of the columns as you see fit, but be forewarned that these visual-only preferences will be lost when you quit CocoaMySQL. You also have the ability to more narrowly determine what content you'd like to see with the forms above the data field: choose the table field to search through, the type of evaluation (context-sensitive, it'll change depending on whether the field is an integer or string), and the desired match.

Figure 4: Displaying the contents of our book table.

Unfortunately, you can't create AND/OR queries here; instead, you'd use the "Custom Query" tab, as shown in Figure 5. Previously entered custom queries are selectable from a dropdown, can be saved as "Favorites", and SQL errors are displayed on screen. It'd be nice if a future version of CocoaMySQL could allow multiple conditions under the "Content" tab, but for those that know SQL already, we'll get by with the "Custom Query" tab instead.

Figure 5: Custom queries within CocoaMySQL.

CocoaMySQL can also export entire databases in one of three formats: raw SQL (for when you want to dump the entire database, and then import it elsewhere), and comma-separated or XML for entire tables, groups of tables, or a custom query. The XML export, buzzwordy enough to satisfy feature gluts, is a wrapper for the same feature through the mysql shell:

~ > mysql -X -e "select * from books" mactech
<?xml version="1.0"?>

<resultset statement="select * from books">
        <title>Spidering Hacks</title>

        <title>Mac OS X Hacks</title>

        <title>Object Oriented Perl</title>


        <title>PHP and MySQL Web Development</title>

phpMyAdmin: Free, Open sourced, and Platform Agnostic

If there's one major drawback of CocoaMySQL, it's that you have to be using Mac OS X. When you desperately need a quick and dirty piece of data, you'll probably be using something downright distasteful. phpMyAdmin ( is a web-based solution that can be accessed from everywhere you want to be, and on whatever OS you happened to be saddled with. It's also one of those applications that can cause some quizzical misgivings until you become familiar with it.

To install phpMyAdmin, download and extract the latest version (2.5.6 as of this writing) into /Library/WebServer/Documents/phpMyAdmin/. Open in your favorite editor and modify the $cfg['Servers'][$i]['password'] to contain your MySQL root password. Also take a look at the surrounding configuration values and set them as appropriate: the host and PmaAbsoluteUri are especially relevant (for our purposes here, simply inserting the root password will work well enough). Finally, load in your browser to display Figure 6.

Figure 6: The start page of our phpMyAdmin installation.

phpMyAdmin provides an insane amount of features in regards to database manipulation and analysis: you'll do yourself some good to familiarize yourself with the available documentation. For now, choose the mactech database from the left-handed dropdown menu, and you'll be shown something similar to Figure 7.

Figure 7: Icons, options, and inputs aplenty

As previously alluded, it is very easy to be overwhelmed by the sheer enormity of options available. Naturally, things will start falling into place if you use phpMyAdmin often enough, but until then, all the icons have tooltips, all the errors have clickable explanations, and most of the worrisome options have "are you sure?" Javascript confirmations. Figure 8, accessible by clicking the "Properties" icon of a table, is similar to the table display of CocoaMySQL's Figure 2, and Figure 9 (the "Browse" tab) is equivalent to the listing of CocoaMySQL's Figure 4.

Figure 8: The person table of our mactech database.

Figure 9: Displaying the contents of our book table.

Homework Malignments

And thus completes our dissertation on basic database access with MySQL, the shell, PHP, Perl, CocoaMySQL, phpMyAdmin, and OS X. Helpful? Useless? Lacking? Confusing? Want more? Have questions? Next month, we'll be starting a new breed of Untangling the Web articles, but until then...

Email your suggestions, thoughts and comments to

Kevin Hemenway, coauthor of Mac OS X Hacks and Spidering Hacks, is better known as Morbus Iff, the creator of, which bills itself as "content for the discontented." Publisher and developer of more home cooking than you could ever imagine (like the popular open-sourced aggregator AmphetaDesk, the best-kept gaming secret, the ever ignorable Nonsense Network, etc.), he's currently a more-than-eighth level RDM on the Phoenix server. Contact him at


Community Search:
MacTech Search:

Software Updates via MacUpdate

Between 2 Taps - Tap for Tap interview M...
Hello, and welcome back to Between 2 Taps, Tap for Tap’s Indie Dev interview series. [Read more] | Read more »
Facility 47 (Games)
Facility 47 1.0.1 Device: iOS Universal Category: Games Price: $3.99, Version: 1.0.1 (iTunes) Description: You wake up alone and freezing in an icy cell. You try the cell door but it’s locked, it seems that you are stuck with no... | Read more »
The best Photoshop alternative on iPad
Instagram and Lightroom are great and all, but sometimes people need to get extra creative with their image editing.Like, Photoshop creative. If you're one of these people, take a look at our pick for the best mobile Photoshop experience on iPad... | Read more »
The Walking Dead: No Man’s Land guide -...
A new update for The Walking Dead: No Man’s Land was released last week, making it the perfect time for you to head back to your base and take out some walkers. Here’s the lowdown on what’s new to the game, and how to take advantage. [Read more] | Read more »
Goat Rider guide - Tips and tricks to st...
We've all been there. One second, we're riding high on a crazed goat, and the next, we've been tossed off it like someone who's no good at goat ridin'. [Read more] | Read more »
Real Boxing 2 CREED: How to become a gre...
Just in time for Rocky fans who can’t wait to see CREED, the latest movie, we have the official tie-in game,Real Boxing 2 CREED. It builds on the success of its predecessor and there’s lots to take in so we at 148apps thought we’d run you through... | Read more »
CoinOp Heroes 2 guide - How to build an...
CoinOp Heroes 2 justlaunched and, like all clickers, it's dangerously addictive stuff. You have to furiously tap your screen to defeat wave after wave of foes and earn an insane amount of cash to spend on character upgrades and an army of minions... | Read more »
Dr. Panda Firefighters (Education)
Dr. Panda Firefighters 1.0.1 Device: iOS Universal Category: Education Price: $2.99, Version: 1.0.1 (iTunes) Description: FIGHT FIRES AND SAVE THE DAY!Work together with Dr. Panda and his firefighting team to rescue his trapped... | Read more »
Puddle + (Games)
Puddle + 1.0 Device: iOS iPhone Category: Games Price: $2.99, Version: 1.0 (iTunes) Description: Puddle is back in a new "+" edition featuring enhanced graphics, new videos and Apple TV support ! No IAP and No Ads. Dive into Puddle... | Read more »
Football Manager Mobile 2016 (Games)
Football Manager Mobile 2016 7.0.0 Device: iOS Universal Category: Games Price: $8.99, Version: 7.0.0 (iTunes) Description: Football Manager Mobile 2016 is designed to be played on the move and is the quickest way to manage your... | Read more »

Price Scanner via

Adorama Black Friday deals: Up to $400 off Ma...
Adorama has released their Black Friday deals for 2015. Save up to $400 on MacBook Pros, $200 on MacBooks and MacBook Airs, and $270 on iMacs. Use code RYBFDEAL during checkout to see these prices.... Read more
B&H Photo Deals: $200 off 12-inch 1.2GHz...
In addition to the B&H Photo Black Friday week sales we posted yesterday, B&H has lowered their price on two products to $200 off MSRP: - 12″ 1.2GHz Gray Retina MacBook: $1399 save $200 - 13... Read more
Best Buy Early Access: Today only, Up to $125...
Best Buy has iPad Air 2s on sale for up to $125 off MSRP and Apple Watch models on sale on their online store for up to $100 off MSRP with special codes through midnight CT tonight. Choose free... Read more
UPPERCASE DESIGNS Premium Ultra Thin Keyboard...
UPPERCASE Designs today announced its new Premium Ultra Thin Keyboard Protector and its Palm Rest Protector Set for the 12-inch MacBook. The accessories provide durable protection for the 12-inch... Read more
Al Jazeera Launches New iOS And Android Mobil...
Doha, Qatar based Al Jazeera has launched new mobile and tablet apps on the iOS and Android systems bringing the latest Al Jazeera news and programmes live together with on-demand personalisation.... Read more
B&H Photo Holiday Sale: Up to $250 off Ma...
B&H Photo has all new Macs on sale for up to $500 off MSRP as part of their Holiday sale including free shipping plus NY sales tax only: - 15″ 2.2GHz Retina MacBook Pro: $1799 $200 off - 15″ 2.... Read more
Free Aura ‘Ultimate’ Mac App For Gmail Update...
Miami, Florida based Crosscoded has announced Aura 1.2.0, an update to the Mac app for Gmail. Aura mixes the power of a native client with the flexibility of the Gmail web app with support for up to... Read more
Apple Will Edge Closer to Samsung in Smartpho...
Total smartphone shipments for 2015 are projected to decline by 9.7% to 1.286 billion units, according to the latest report from global market research firm TrendForce. Though Chinese vendors have... Read more
Sidefari – Split Screen Multitasking In Safar...
Francisco Cantu’s Sidefari is a simple web browser designed to act as a companion to Safari on the iPad. With multitasking in iOS 9, Sidefari uses the new Safari View Controller to show an extra... Read more
12-inch MacBooks in stock for up to $120 off,...
Adorama has 12″ Retina MacBooks in stock for up to $120 off MSRP including free shipping plus NY & NJ sales tax only. For a limited time, Adorama will include a free Apple USB-C to USB Adapter,... Read more

Jobs Board

*Apple* Retail - Multiple Positions (US) - A...
Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, you're also the Read more
Merchant Operations Manager: *Apple* Pay -...
Changing the world is all in a day's work at Apple . If you love innovation, here's your chance to make a career of it. You'll work hard. But the job comes with more than Read more
*Apple* Pay QA Manager - Apple Inc. (United...
Changing the world is all in a day's work at Apple . If you love innovation, here's your chance to make a career of it. You'll work hard. But the job comes with more than Read more
Sr Software Engineer *Apple* Pay - Apple In...
Changing the world is all in a day's work at Apple . If you love innovation, here's your chance to make a career of it. You'll work hard. But the job comes with more than Read more
Hardware Systems Architect - *Apple* Watch...
# Hardware Systems Architect - Apple Watch Job Number: 38449977 Santa Clara Valley, Califo ia, United States Posted: Apr. 16, 2015 Weekly Hours: **Job Summary** The Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.