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

Garmin Express - Manage your Gar...
Garmin Express is your essential tool for managing your Garmin devices. Update maps, golf courses and device software. You can even register your device. Update maps Update software Register your... Read more
Duet - Use your iPad as an exter...
Duet is the first app that allows you to use your iDevice as an extra display for your Mac using the Lightning or 30-pin cable. Note: This app requires a $14.99 iOS companion app. Version Read more
Apple iTunes 12.5.2 - Play Apple Music a...
Apple iTunes lets you organize and stream Apple Music, download and watch video and listen to Podcasts. It can automatically download new music, app, and book purchases across all your devices and... Read more
Pinegrow Web Designer 2.95 - Mockup and...
Pinegrow Web Designer is desktop app that lets you mockup and design webpages faster with multi-page editing, CSS and LESS styling, and smart components for Bootstrap, Foundation, Angular JS, and... Read more
jAlbum Pro 13.6 - Organize your digital...
jAlbum Pro has all the features you love in jAlbum, but comes with a commercial license. You can create gorgeous custom photo galleries for the Web without writing a line of code! Beginner-friendly... Read more
jAlbum 13.6 - Create custom photo galler...
With jAlbum, you can create gorgeous custom photo galleries for the Web without writing a line of code! Beginner-friendly, with pro results - Simply drag and drop photos into groups, choose a design... Read more
TextMate 2.0-beta.12.26 - Code/markup ed...
TextMate is a versatile plain text editor with a unique and innovative feature set which caused it to win an Apple Design Award for Best Mac OS X Developer Tool in August 2006. A rapidly growing... Read more
VMware Fusion 8.5.1 - Run Windows apps a...
VMware Fusion 8 and Fusion 8 Pro--the latest versions of its virtualization software for running Windows on a Mac without rebooting--include full support for Windows 10, OS X El Capitan, and the... Read more
Apple Final Cut Pro X 10.3 - Professiona...
Apple Final Cut Pro X is a professional video editing solution.Completely redesigned from the ground up, Final Cut Pro adds extraordinary speed, quality, and flexibility to every part of the post-... Read more
Civilization VI 1.0.0 - Next iteration o...
Sid Meier’s Civilization VI is the next entry in the popular Civilization franchise. Originally created by legendary game designer Sid Meier, Civilization is a strategy game in which you attempt to... Read more

Latest Forum Discussions

See All

Plants vs. Zombies Heroes guide: How to...
Plants vs. Zombies Heroes might look cute and cuddly, but it's actually a very complex deck builder that requires a lot of time to master. While there's a bit of a learning curve, it's easy for people of any skill level to have fun with the game. | Read more »
Roofbot: Puzzler On The Roof (Games)
Roofbot: Puzzler On The Roof 1.0.2 Device: iOS Universal Category: Games Price: $2.99, Version: 1.0.2 (iTunes) Description: Guide Roofie through gorgeous, meditative rooftops and try to get the right color energy balls into the... | Read more »
The 4 best food delivery apps
As the temperatures continue to drop, so does the motivation to venture outside. Sometimes you still want to eat a nice meal from that sushi place down the road though. Thankfully in these trying times, there are a number of fine food delivery... | Read more »
Toca Life: Farm (Education)
Toca Life: Farm 1.0 Device: iOS Universal Category: Education Price: $2.99, Version: 1.0 (iTunes) Description: Work and play the farmer's way! Milk your cow, gather eggs from your hens and raise your crops. Have a picnic, play the... | Read more »
The Lost Shield (Games)
The Lost Shield 1.0.0 Device: iOS Universal Category: Games Price: $1.99, Version: 1.0.0 (iTunes) Description: The Lost shield is a brick break/adventure game. You play as a hero who must return a powerful but dangerous magic shield... | Read more »
The Forgotten Room (Games)
The Forgotten Room 1.0.1 Device: iOS Universal Category: Games Price: $1.99, Version: 1.0.1 (iTunes) Description: Play as paranormal investigator John “Buster of Ghosts” Murr as he explores yet another mysteriously creepy house. This... | Read more »
5 Halloween mobile games for wimps
If you're anything like me, horror games are a great way to have nightly nightmares for the next decade or three. They're off limits, but perhaps you want to get in on the Halloween celebrations in some way. Fortunately not all Halloween themed... | Read more »
The 5 scariest mobile games
It's the most wonderful time of the year for people who enjoy scaring themselves silly with haunted houses, movies, video games, and what have you. Mobile might not be the first platform you'd turn to for quality scares, but rest assured there are... | Read more »
Lifeline: Flatline (Games)
Lifeline: Flatline 1.0.0 Device: iOS Universal Category: Games Price: $2.99, Version: 1.0.0 (iTunes) Description: The Lifeline series takes a terrifying turn in this interactive horror experience. Every decision you make could help... | Read more »
Game of Dice is now available on Faceboo...
After celebrating its anniversary in style with a brand new update, there’s even more excitement in store for Game of Dice has after just being launched on Facebook Gameroom. A relatively new platform, Facebook Gameroom has been designed for PC... | Read more »

Price Scanner via

WaterField Unveils Syde MacBook Pro Touch Bar...
The new WaterField Designs Syde Case for Apple’s new MacBook Pro Touch Bar combines the top-notch laptop protection of a sleeve with the functionality and handsome look of an over-the-shoulder or... Read more
Apple Unveils Redesigned MacBook Pro With Tou...
October 27, 2016 – Apple today introduced the thinnest and lightest MacBook Pro yet, along with a new interface innovation that replaces the traditional row of function keys with a Retina-quality... Read more
Apple Unveils New TV App for Apple TV, iPhone...
October 27, 2016 – Apple today introduced a new TV app, offering a unified experience for discovering and accessing TV shows and movies from multiple apps on Apple TV, iPhone and iPad. The TV app... Read more
Price drops on select refurbished 2015 13″ Re...
Apple dropped prices on select Certified Refurbished 2015 13″ Retina MacBook Pros by as much as $90. An Apple one-year warranty is included with each model, and shipping is free: - 13″ 2.7GHz/256GB... Read more
Apple reveals new next-generation 15″ and 13″...
Apple today revealed their next-generation 15″ and 13″ MacBook Pros. The new models are thinner and lighter than before with a new aluminum design featuring an enhanced keyboard with retina, multi-... Read more
Worldwide Smartphone Shipments Up 1.0% Year o...
According to preliminary results from the International Data Corporation (IDC) Worldwide Quarterly Mobile Phone Tracker, vendors shipped a total of 362.9 million smartphones worldwide in the third... Read more
TuneBand Arm Band For iPhone 7 and 7 Plus Rel...
Grantwood Technology has added the TuneBand for iPhone 7 and 7 Plus to its smartphone armband series. The TuneBand provides a lightweight and comfortable way to wear the iPhone while running,... Read more
1.4GHz Mac mini on sale for $449, save $50
Adorama has the 1.4GHz Mac mini on sale for $50 off MSRP including free shipping plus NY & NJ sales tax only: - 1.4GHz Mac mini (Apple sku# MGEM2LL/A): $449 $50 off MSRP To purchase a mini at... Read more
21-inch 1.6GHz iMac on sale for $999, save $1...
B&H has the 21″ 1.6GHz Apple iMac on sale for $999 including free shipping plus NY sales tax only. Their price is $100 off MSRP. Read more
Macs’ Superior Enterprise Deployment Cost Eco...
IBM’s debunking of conventional wisdom and popular mythology about the relative cost of using Apple Mac computers as opposed to PCs running Microsoft Windows at the sixth annual Jamf Nation User... Read more

Jobs Board

*Apple* Retail - Multiple Positions - Apple,...
Job Description: Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
Software Engineering Intern: UI Applications...
Job Summary Apple is currently seeking enthusiastic interns who can work full-time for a minimum of 12-weeks between Fall 2015 and Summer 2016. Our software Read more
Security Data Analyst - *Apple* Information...
…data sources need to be collected to allow Information Security to better protect Apple employees and customers from a wide range of threats.Act as the subject Read more
*Apple* Retail - Multiple Positions - Apple,...
Job Description: Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
*Apple* Solutions Consultant - Apple (United...
# Apple Solutions Consultant Job Number: 52812872 Houston, Texas, United States Posted: Oct. 18, 2016 Weekly Hours: 40.00 **Job Summary** As an Apple Solutions Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.