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.

 
AAPL
$517.96
Apple Inc.
-3.72
MSFT
$39.75
Microsoft Corpora
+0.57
GOOG
$536.44
Google Inc.
+3.92

MacTech Search:
Community Search:

Software Updates via MacUpdate

Starcraft II: Wings of Liberty 1.1.1.180...
Download the patch by launching the Starcraft II game and downloading it through the Battle.net connection within the app. Starcraft II: Wings of Liberty is a strategy game played in real-time. You... Read more
Sibelius 7.5.0 - Music notation solution...
Sibelius is the world's best-selling music notation software for Mac. It is as intuitive to use as a pen, yet so powerful that it does most things in less than the blink of an eye. The demo includes... Read more
Typinator 5.9 - Speedy and reliable text...
Typinator turbo-charges your typing productivity. Type a little. Typinator does the rest. We've all faced projects that require repetitive typing tasks. With Typinator, you can store commonly used... Read more
MYStuff Pro 2.0.16 - Create inventories...
MYStuff Pro is the most flexible way to create detail-rich inventories for your home or small business. Add items to MYStuff by dragging and dropping existing information, uploading new images, or... Read more
TurboTax 2013.r17.002 - Manage your 2013...
TurboTax guides you through your tax return step by step, does all the calculations, and checks your return for errors and overlooked deductions. It lets you file your return electronically to get... Read more
TrailRunner 3.8.769 - Route planning for...
Note: While the software is classified as freeware, it is actually donationware. Please consider making a donation to help support development. TrailRunner is the perfect companion for runners,... Read more
Flavours 1.1.10 - Create and apply theme...
Flavours is a Mac application that allow users to create, apply and share beautifully designed themes. Classy Give your Mac a gorgeous new look by applying delicious themes! Easy Unleash your... Read more
Spotify 0.9.8.296. - Stream music, creat...
Spotify is a streaming music service that gives you on-demand access to millions of songs. Whether you like driving rock, silky R&B, or grandiose classical music, Spotify's massive catalogue... Read more
SlingPlayer Plugin 3.3.20.475 - Browser...
SlingPlayer is the screen interface software that works hand-in-hand with the hardware inside the Slingbox to make your TV viewing experience just like that at home. It features an array of... Read more
S.M.A.R.T. for USB and FireWire Drives 0...
S.M.A.R.T. for USB and FireWire Drives is a kernel driver for OS X external usb or firewire drives. It extends the standard driver behaviour by providing access to drive smart data. The interface to... Read more

Latest Forum Discussions

See All

148Apps Live on Twitch: Pivvot’s Looper...
On our latest Twitch stream, we’ll be playing a pair of minimalist arcade games, one that just got a big content update in Pivvot, and another that was inspired by it in 15 Coins. Whitaker Trebella, creator of Pivvot, will discuss the new modes... | Read more »
Word Cubes Review
Word Cubes Review By Jordan Minor on April 15th, 2014 Our Rating: :: SQUARESVILLEUniversal App - Designed for iPhone and iPad Word Cubes is fine, but it is barely any different from any other word game.   | Read more »
PAX East 2014 – Desert Fox: The Battle o...
PAX East 2014 – Desert Fox: The Battle of El Alamein is Coming to iOS Soon Posted by Rob Rich on April 15th, 2014 [ permalink ] Shenandoah Studio has become one of the go-to developers for war games on iOS, with | Read more »
Tank of Tanks Review
Tank of Tanks Review By Carter Dotson on April 15th, 2014 Our Rating: :: TANKS A LOT!iPad Only App - Designed for the iPad This multiplayer game played on a single iPad is simple, chaotic fun.   | Read more »
PAX East 2014 – Dungeon of the Endless J...
PAX East 2014 – Dungeon of the Endless Just Might Have a Shot at an iPad Release Posted by Rob Rich on April 15th, 2014 [ permalink ] I think it’s fair to say that | Read more »
SideSwype Review
SideSwype Review By Carter Dotson on April 15th, 2014 Our Rating: :: ON YOUR SIDEUniversal App - Designed for iPhone and iPad SideSwype is a puzzler that takes inspiration from Threes, but becomes its own incredibly fun game.   | Read more »
PAX East 2014 – Bigfoot Hunter Invites P...
PAX East 2014 – Bigfoot Hunter Invites Players on a Wild and Wooly Photo Safari Posted by Rob Rich on April 15th, 2014 [ permalink ] Yeti. Sasquatch. Wendigo. | Read more »
Dungeon Quest Review
Dungeon Quest Review By Cata Modorcea on April 15th, 2014 Our Rating: :: NO STORY, BUT GOOD FUNUniversal App - Designed for iPhone and iPad Dungeon Quest does a lot of things right, but ultimately forgets about one of the core... | Read more »
Tempo AI and Speek Join Forces to “Kill...
Tempo AI and Speek Join Forces to “Kill the Conference Call PIN” Posted by Rob Rich on April 15th, 2014 [ permalink ] Today Tempo AI, makers of Tempo Smart Calendar, and | Read more »
Fighting Fantasy: Starship Traveller Rev...
Fighting Fantasy: Starship Traveller Review By Jennifer Allen on April 15th, 2014 Our Rating: :: A SIGNIFICANT VOYAGEUniversal App - Designed for iPhone and iPad Continuing the release of Fighting Fantasy titles, Starship Traveller... | Read more »

Price Scanner via MacPrices.net

Save $50 on Mac mini Server
B&H Photo has the 2012 Mac mini Server on sale for $949 including free shipping plus NY sales tax only. Their price is $50 off MSRP. Read more
PhatWare’s “Ultimate Writing App For iOS” Ren...
PhatWare Corp. has announced it has renamed its new WritePro word processing app for iPhone and iPad: WritePad Pro. The decision to change the app’s name to leverages the strong brand awareness and... Read more
Full Resolution Photo Editor Tint Mint 1.0 Re...
California based independent developer, Jeffrey Sun, creator of the iOS app Modern Editor, has released Tint Mint, a new photography app for editing enthusiasts. The app costs a dollar, and it packs... Read more
16GB iPad mini (Apple refurbished) available...
The Apple Store has refurbished 1st generation 16GB iPad minis available for $249 including free shipping. Both black and white models are available. Read more
Save $120 on the 27-inch 3.2GHz Haswell iMac
B&H Photo has the 27″ 3.2GHz iMac on sale for $1679.99 including free shipping plus NY sales tax only. Their price is about $120 off MSRP. Read more
Using a Mac Doesn’t Eliminate The Heartbleed...
Low End Mac’s Dan Knight notes that any time you visit a website with an https: prefix or see that secure lock icon on your browser, some type of security software is busy trying to protect your data... Read more
AirPrint Basics Tutorial Posted
A new Apple Knowledge Base article helps get you started using AirPrint, the Apple protocol that enables instant printing from iPad, iPhone, iPod touch, and Mac without the need to install drivers or... Read more
Speed Tips For Running LibreOffice On The Mac
LibreOffice is my favorite of several free, open-source application suites, and the one I have configured on my Mac as my default app for Word documents that one frequently has to deal with. It also... Read more
Snag a 15-inch Retina MacBook Pro for $115 of...
B&H Photo has 2013 15″ Retina MacBook Pros on sale for up to $115 off MSRP for a limited time. Shipping is free, and B&H charges NY sales tax only: - 15″ 2.3GHz Retina MacBook Pro: $2489.99... Read more
MacBook Airs on sale for $50 to $100 off MSRP
Several resellers are offering $50-$100 discounts on 11″ and 13″ MacBook Airs today, including Amazon, Best Buy, B&H, and others. See the breakdown of deals on our MacBook Air Price Tracker,... Read more

Jobs Board

Position Opening at *Apple* - Apple (United...
**Job Summary** Every day, business customers come to the Apple Store to discover what powerful, easy-to-use Apple products can do for them. As a Business Leader, Read more
Position Opening at *Apple* - Apple (United...
…challenges of developing individuals, building teams, and affecting growth across Apple Stores. You demonstrate successful leadership ability - focusing on excellence Read more
Position Opening at *Apple* - Apple (United...
…Summary** As a Specialist, you help create the energy and excitement around Apple products, providing the right solutions and getting products into customers' hands. You Read more
Position Opening at *Apple* - Apple (United...
**Job Summary** The Apple Store is a retail environment like no other - uniquely focused on delivering amazing customer experiences. As an Expert, you introduce people Read more
Position Opening at *Apple* - Apple (United...
**Job Summary** Being a Business Manager at an Apple Store means you're the catalyst for businesses to discover and leverage the power, ease, and flexibility of Apple Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.