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

ScreenFlow 8.2.5 - Create screen recordi...
ScreenFlow is powerful, easy-to-use screencasting software for the Mac. With ScreenFlow you can record the contents of your entire monitor while also capturing your video camera, microphone and your... Read more
MegaSeg 6.1.1 - Professional DJ and radi...
MegaSeg is a complete solution for pro audio/video DJ mixing, radio automation, and music scheduling with rock-solid performance and an easy-to-use design. Mix with visual waveforms and Magic... Read more
Beamer 3.4 - Stream any movie file from...
Beamer streams to your Apple TV or Chromecast. Plays any movie file - Just like the popular desktop movie players, Beamer accepts all common formats, codecs and resolutions. AVI, MKV, MOV, MP4, WMV... Read more
FotoMagico 5.6.12 - Powerful slideshow c...
FotoMagico lets you create professional slideshows from your photos and music with just a few, simple mouse clicks. It sports a very clean and intuitive yet powerful user interface. High image... Read more
OmniGraffle Pro 7.12.1 - Create diagrams...
OmniGraffle Pro helps you draw beautiful diagrams, family trees, flow charts, org charts, layouts, and (mathematically speaking) any other directed or non-directed graphs. We've had people use... Read more
beaTunes 5.2.1 - Organize your music col...
beaTunes is a full-featured music player and organizational tool for music collections. How well organized is your music library? Are your artists always spelled the same way? Any R.E.M. vs REM?... Read more
HandBrake 1.3.0 - Versatile video encode...
HandBrake is a tool for converting video from nearly any format to a selection of modern, widely supported codecs. Features Supported Sources VIDEO_TS folder, DVD image or real DVD (unencrypted... Read more
Macs Fan Control 1.5.1.6 - Monitor and c...
Macs Fan Control allows you to monitor and control almost any aspect of your computer's fans, with support for controlling fan speed, temperature sensors pane, menu-bar icon, and autostart with... Read more
TunnelBear 3.9.3 - Subscription-based pr...
TunnelBear is a subscription-based virtual private network (VPN) service and companion app, enabling you to browse the internet privately and securely. Features Browse privately - Secure your data... Read more
calibre 4.3.0 - Complete e-book library...
Calibre is a complete e-book library manager. Organize your collection, convert your books to multiple formats, and sync with all of your devices. Let Calibre be your multi-tasking digital librarian... Read more

Latest Forum Discussions

See All

The House of Da Vinci 2 gets a new gamep...
The House of Da Vinci launched all the way back in 2017. Now, developer Blue Brain Games is gearing up to deliver a second dose of The Room-inspired puzzling. Some fresh details have now emerged, alongside the game's first official trailer. [Read... | Read more »
Shoot 'em up action awaits in Battl...
BattleBrew Productions has just introduced another entry into its award winning, barrelpunk inspired, BattleSky Brigade series. Whilst its previous title BattleSky Brigade TapTap provided fans with idle town building gameplay, this time the... | Read more »
Arcade classic R-Type Dimensions EX blas...
If you're a long time fan of shmups and have been looking for something to play lately, Tozai Games may have just released an ideal game for you on iOS. R-Type Dimensions EX brings the first R-Type and its sequel to iOS devices. [Read more] | Read more »
Intense VR first-person shooter Colonicl...
Our latest VR obsession is Colonicle, an intense VR FPS, recently released on Oculus and Google Play, courtesy of From Fake Eyes and Goboogie Games. It's a pulse-pounding multiplayer shooter which should appeal to genre fanatics and newcomers alike... | Read more »
PUBG Mobile's incoming update bring...
PUGB Mobile's newest Royale Pass season they're calling Fury of the Wasteland arrives tomorrow and with it comes a fair chunk of new content to the game. We'll be seeing a new map, weapon and even a companion system. [Read more] | Read more »
PSA: Download Bastion for free, but wait...
There hasn’t been much news from Supergiant Games on mobile lately regarding new games, but there’s something going on with their first game. Bastion released on the App Store in 2012, and back then it was published by Warner Bros. This Warner... | Read more »
Apple Arcade: Ranked - 51+ [Updated 11.5...
This is Part 2 of our Apple Arcade Ranking list. To see part 1, go here. 51. Patterned [Read more] | Read more »
NABOKI is a blissful puzzler from acclai...
Acclaimed developer Rainbow Train's latest game, NABOKI, is set to launch for iOS, Android, and Steam on November 13th. It's a blissful puzzler all about taking levels apart in interesting, inventive ways. [Read more] | Read more »
A Case of Distrust is a narrative-driven...
A Case of Distrust a narrative-focused mystery game that's set in the roaring 20s. In it, you play as a detective with one of the most private eye sounding names ever – Phyllis Cadence Malone. You'll follow her journey in San Francisco as she... | Read more »
Brown Dust’s October update offers playe...
October is turning out to be a productive month for the Neowiz team, and a fantastic month to be a Brown Dust player. First, there was a crossover event with the popular manga That Time I Got Reincarnated as a Slime. Then, there was the addition of... | Read more »

Price Scanner via MacPrices.net

Score a 37% discount on Apple Smart Keyboards...
Amazon has Apple Smart Keyboards for current-generation 10″ iPad Airs and previous-generation 10″ iPad Pros on sale today for $99.99 shipped. That’s a 37% discount over Apple’s regular MSRP of $159... Read more
Apple has refurbished 2019 13″ 1.4GHz MacBook...
Apple has a full line of Certified Refurbished 2019 13″ 1.4GHz 4-Core Touch Bar MacBook Pros available starting at $1099 and up to $230 off MSRP. Apple’s one-year warranty is included, shipping is... Read more
2019 13″ 1.4GHz 4-Core MacBook Pros on sale f...
Amazon has new 2019 13″ 1.4GHz 4-Core Touch Bar MacBook Pros on sale for $150-$200 off Apple’s MSRP. These are the same MacBook Pros sold by Apple in its retail and online stores: – 2019 13″ 1.4GHz/... Read more
11″ 64GB Gray WiFi iPad Pro on sale for $674,...
Amazon has the 11″ 64GB Gray WiFi iPad Pro on sale today for $674 shipped. Their price is $125 off MSRP for this iPad, and it’s the lowest price available for the 64GB model from any Apple reseller. Read more
2019 15″ MacBook Pros available for up to $42...
Apple has a full line of 2019 15″ 6-Core and 8-Core Touch Bar MacBook Pros, Certified Refurbished, available for up to $420 off the cost of new models. Each model features a new outer case, shipping... Read more
2019 15″ MacBook Pros on sale this week for $...
Apple resellers B&H Photo and Amazon are offering the new 2019 15″ MacBook Pros for up to $300 off Apple’s MSRP including free shipping. These are the same MacBook Pros sold by Apple in its... Read more
Sunday Sale: AirPods with Wireless Charging C...
B&H Photo has Apple AirPods with Wireless Charging Case on sale for $159.99 through 11:59pm ET on November 11th. Their price is $40 off Apple’s MSRP, and it’s the lowest price available for these... Read more
Details of Sams Club November 9th one day App...
Through midnight Saturday night (November 9th), Sams Club online has several Apple products on sale as part of their One Day sales event. Choose free shipping or free local store pickup (if available... Read more
Sprint is offering the 64GB Apple iPhone 11 f...
Sprint has the new 64GB iPhone 11 available for $15 per month for new lines. That’s about 50% off their standard monthly lease of $29.17. Over is valid until November 24, 2019. The fine print: “Lease... Read more
New Sprint November iPhone deal: Lease one iP...
Switch to Sprint and purchase an Apple iPhone 11, 11 Pro, or 11 Pro Max, and get a second 64GB iPhone 11 for free. Requires 2 new lines or 1 upgrade-eligible line and 1 new line. Offer is valid from... Read more

Jobs Board

*Apple* Mobility Pro - Best Buy (United Stat...
**746087BR** **Job Title:** Apple Mobility Pro **Job Category:** Store Associates **Store NUmber or Department:** 000319-Harlem & Irving-Store **Job Description:** Read more
Best Buy *Apple* Computing Master - Best Bu...
**743392BR** **Job Title:** Best Buy Apple Computing Master **Job Category:** Store Associates **Store NUmber or Department:** 001171-Southglenn-Store **Job Read more
Best Buy *Apple* Computing Master - Best Bu...
**746015BR** **Job Title:** Best Buy Apple Computing Master **Job Category:** Sales **Store NUmber or Department:** 000372-Federal Way-Store **Job Description:** Read more
*Apple* Mobility Pro - Best Buy (United Stat...
**744658BR** **Job Title:** Apple Mobility Pro **Job Category:** Store Associates **Store NUmber or Department:** 000586-South Hills-Store **Job Description:** At Read more
Best Buy *Apple* Computing Master - Best Bu...
**741552BR** **Job Title:** Best Buy Apple Computing Master **Job Category:** Sales **Store NUmber or Department:** 000277-Metcalf-Store **Job Description:** **What Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.