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 http://cocoamysql.sf.net/. 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 "127.0.0.1" (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">
  <row>
        <id>9</id>
        <title>Spidering Hacks</title>
        <publication>2003-11-01</publication>
  </row>

  <row>
        <id>10</id>
        <title>Mac OS X Hacks</title>
        <publication>2003-04-01</publication>
  </row>

  <row>
        <id>13</id>
        <title>Object Oriented Perl</title>
        <publication>2000-00-00</publication>
  </row>

  <row>
        <id>14</id>
        <title>MySQL</title>
        <publication>1999-01-01</publication>
  </row>

  <row>
        <id>15</id>
        <title>PHP and MySQL Web Development</title>
        <publication>2003-00-00</publication>
  </row>
</resultset>

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 (http://phpmyadmin.sf.net/) 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 config.inc.php 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 http://127.0.0.1/phpMyAdmin 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

editor-in-chief@mactech.com.


Kevin Hemenway, coauthor of Mac OS X Hacks and Spidering Hacks, is better known as Morbus Iff, the creator of disobey.com, 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 Gamegrene.com, the ever ignorable Nonsense Network, etc.), he's currently a more-than-eighth level RDM on the Phoenix server. Contact him at morbus@disobey.com.

 

Community Search:
MacTech Search:

Software Updates via MacUpdate

Adium 1.5.10.3 - Popular instant messagi...
Adium is a fast and free instant messaging client which supports AIM, ICQ, Jabber, MSN, Yahoo!, Google Talk, Yahoo! Japan, Bonjour, Gadu-Gadu, Novell Groupwise, SIP/SIMPLE (Text), and Lotus Sametime... Read more
CleanMyMac 3.8.1 - $39.95
CleanMyMac makes space for the things you love. Sporting a range of ingenious new features, CleanMyMac lets you safely and intelligently scan and clean your entire system, delete large, unused files... Read more
BusyCal 3.1.7 - Powerful calendar app wi...
BusyCal is an award-winning desktop calendar that combines personal productivity features for individuals with powerful calendar sharing capabilities for families and workgroups. Its unique features... Read more
Lyn 1.8.9 - Lightweight image browser an...
Lyn is a fast, lightweight image browser and viewer designed for photographers, graphic artists, and Web designers. Featuring an extremely versatile and aesthetically pleasing interface, it delivers... Read more
Tweetbot 2.5 - Popular Twitter client.
Tweetbot is a full-featured OS X Twitter client with a lot of personality. Whether it's the meticulously-crafted interface, sounds and animation, or features like multiple timelines and column views... Read more
Monolingual 1.7.8 - Remove unwanted OS X...
Monolingual is a program for removing unnecesary language resources from OS X, in order to reclaim several hundred megabytes of disk space. If you use your computer in only one (human) language, you... Read more
Dash 4.0.3 - Instant search and offline...
Dash is an API documentation browser and code snippet manager. Dash helps you store snippets of code, as well as instantly search and browse documentation for almost any API you might use (for a full... Read more
Posterino 3.3.6 - Create posters, collag...
Posterino offers enhanced customization and flexibility including a variety of new, stylish templates featuring grids of identical or odd-sized image boxes. You can customize the size and shape of... Read more
Apple Numbers 4.1.1 - Apple's sprea...
With Apple Numbers, sophisticated spreadsheets are just the start. The whole sheet is your canvas. Just add dramatic interactive charts, tables, and images that paint a revealing picture of your data... Read more
Apple Pages 6.1.1 - Apple's word pr...
Apple Pages is a powerful word processor that gives you everything you need to create documents that look beautiful. And read beautifully. It lets you work seamlessly between Mac and iOS devices, and... Read more

Latest Forum Discussions

See All

Get Ike in the new Fire Emblem: Heroes u...
One of the most popular Fire Emblem characters is finally available in a new update to Nintendo'sFire Emblem: Heroes. [Read more] | Read more »
Die With Glory in a new viking adventure...
If you're a fan of classic adventure games you'll do well to pick upDie With Glory, the gorgeous new title from Cloud Castle inc. Die With Glory updatesthe gameplay of the same kind of adventure classics such asMonkey Island for modern, mobile... | Read more »
Get up to speed with everything you need...
In case you haven’t heard, MU Origin just got a colossal new update with new all-server events, battle modes, and systems making their way to the land of MU. Here’s a handy guide to everything you need to know about the latest content. [Read... | Read more »
Minimalist puzzle game, Cuts, free on iO...
If you're looking for a gorgeous puzzle experience on iOS devices, developer Gamebra.in's aesthetically interesting puzzler, Cuts, is discounted to free on the iOS App Store right now. [Read more] | Read more »
Anime tactical RPG, War of Crown, comes...
If you're looking for another tactical RPG fix to go alongside your Fire Emblem Heroes campaigns check out Gamevil's newest, anime-inspired tactics RPG, War of Crown, which comes out tomorrow. [Read more] | Read more »
Fantasy MMORPG MU Origin adds new modes,...
MU Origin, Webzen’s highly popular fantasy MMORPG is getting ready to shake things up for the second time this year, as a new update makes its way to the Google Play and App Store from today. Introducing new systems, modes, and events, the land of... | Read more »
Blizzard is looking to hire a mobile dev...
A new thread on the popular video game rumor forum, NeoGAF, uncovered an interesting job listing over at Blizzard Entertainment. It appears the studio behindStarCraft, World of WarCraft, Hearthstone,andOverwatch is looking to bring on a new hire... | Read more »
Legend of Zelda meets Cooking Mama in ne...
Dungeon Chef is what happens when you mix the RPG elements (and style) of a Legend of Zelda game, with cooking elements. Although, now that The Legend of Zelda: Breath of the Wild also has cookingelements, so maybe the gameplay is not so novel.... | Read more »
ChordFlow (Music)
ChordFlow 1.0.0 Device: iOS Universal Category: Music Price: $6.99, Version: 1.0.0 (iTunes) Description: ChordFlow is a chord sequencer with a unique 4-track polyphonic arpeggiator, extensive chord library, MIDI out and Ableton Link... | Read more »
The Walking Dead: A New Frontier is out...
The newest season of Telltale Games'The Walking Dead is well underway. After the release of the third episode, "Above the Law" about a month ago, episode four, "Thicker Than Water" is hot and ready for more zombies and gut-wrenching emotional... | Read more »

Price Scanner via MacPrices.net

Digital Paper Tablet Offers Distraction Free...
I typically spend 8-10 hours a day gazing at the screens in my laptops and iPad, as tools of my livelihood, I don’t as a rule use electronic devices for pleasure reading. I subscribe to a daily... Read more
“Today at Apple” Bringing New Educational Ses...
Apple has announced plans to launch dozens of new educational sessions next month in all 495 Apple Stores ranging in topics from photo and video to music, coding, art and design, and more. The hands-... Read more
Smart Finance Free Comprehensive Personal Fin...
Moscow-based indie developer, Alexander Survillo has announced the release and immediate availability of Smart Finance: Personal Finance, Budget & Money 1.1.4, an update to his comprehensive... Read more
12-inch 1.1GHz Retina MacBooks on sale for $1...
B&H has 12″ 1.1GHz Retina MacBooks on sale for $100 off MSRP. Shipping is free, and B&H charges NY & NJ sales tax only: - 12″ 1.1GHz Space Gray Retina MacBook: $1199.99 $100 off MSRP - 12... Read more
13-inch 2.7GHz Retina MacBook Pro on sale for...
B&H Photo has the 13″ 2.7GHz Retina MacBook Pro on sale for $130 off MSRP. Shipping is free, and B&H charges NY & NJ tax only: - 13″ 2.7GHz/128GB Retina MacBook Pro (MF839LL/A): $1169 $... Read more
15-inch 2.2GHz Retina MacBook Pros available...
B&H Photo has the 15″ 2.2GHz Retina MacBook Pro available for $200 off MSRP including free shipping plus NY & NJ sales tax only: - 15″ 2.2GHz Retina MacBook Pro (MJLQ2LL/A): $1799.99 $200 off... Read more
13-inch Touch Bar MacBook Pros on sale for up...
B&H Photo has the 2016 Apple 13″ Touch Bar MacBook Pros in stock today for up to $150 off MSRP. Shipping is free, and B&H charges NY & NJ sales tax only: - 13″ 2.9GHz/512GB Touch Bar... Read more
Apple refurbished Apple TVs available for up...
Apple has Certified Refurbished 32GB and 64GB Apple TVs available for up to $30 off the cost of new models. Apple’s standard one-year warranty is included with each model, and shipping is free: -... Read more
12-inch 1.2GHz Retina MacBooks on sale for up...
B&H has 12″ 1.2GHz Retina MacBooks on sale for up to $160 off MSRP. Shipping is free, and B&H charges NY sales tax only: - 12″ 1.2GHz Space Gray Retina MacBook: $1439.99 $160 off MSRP - 12″ 1... Read more
HyperX Ships Pulsefire FPS Gaming Mouse, Winn...
Your reporter is a longtime fan of gaming mice for general purpose coomnputing use, finding them typically superior in comfort and performance. HyperX, a division of Kingston Technology Company, Inc... Read more

Jobs Board

Product Manager, *Apple* Platforms - Viacom...
…Product Manager to drive the execution of its iOS and AppleTV experiences. The Apple Platform Product Manager will be a leader in our Agile/Scrum environment and Read more
Geek Squad *Apple* Master Consultation Agen...
**500662BR** **Job Title:** Geek Squad Apple Master Consultation Agent **Location Number:** 000286-Canton-Store **Job Description:** **What does a Geek Squad Read more
*Apple* Mobile Master - Best Buy (United Sta...
**500710BR** **Job Title:** Apple Mobile Master **Location Number:** 000279-North Olmsted-Store **Job Description:** **What does a Best Buy Apple Mobile Master Read more
*Apple* Engineering Specialist - CSRA (Unite...
Apple Engineering Specialist All times are in Eastern Daylight Time Requisition ID Job Locations US DC Washington DC Posted Date Category Engineering Sciences Read more
*Apple* Mac Computer Technician - GeekHampto...
…complex computer issues over the phone and in person? GeekHampton, Long Island's Apple Premium Service Provider, is looking for you! Come work with our crew Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.