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

Cocktail 11.3.1 - General maintenance an...
Cocktail is a general purpose utility for macOS that lets you clean, repair and optimize your Mac. It is a powerful digital toolset that helps hundreds of thousands of Mac users around the world get... Read more
WebSnapperPro 2.0.5 - $20.00
WebSnapperPro lets you capture full web pages exactly as they appear in your browser, with a single mouse click, without the need to "stitch" or cut-and-paste. Save the page as an image file or as... Read more
VOX 3.2.1 - Music player that supports m...
VOX just sounds better! The beauty is in its simplicity, yet behind the minimal exterior lies a powerful music player with a ton of features and support for all audio formats you should ever need.... Read more
Evernote 7.0 - Create searchable notes a...
Evernote allows you to easily capture information in any environment using whatever device or platform you find most convenient, and makes this information accessible and searchable at anytime, from... Read more
Hopper Disassembler 4.3.16- - Binary dis...
Hopper Disassembler is a binary disassembler, decompiler, and debugger for 32- and 64-bit executables. It will let you disassemble any binary you want, and provide you all the information about its... Read more
VOX 3.2.1 - Music player that supports m...
VOX just sounds better! The beauty is in its simplicity, yet behind the minimal exterior lies a powerful music player with a ton of features and support for all audio formats you should ever need.... Read more
Evernote 7.0 - Create searchable notes a...
Evernote allows you to easily capture information in any environment using whatever device or platform you find most convenient, and makes this information accessible and searchable at anytime, from... Read more
Hopper Disassembler 4.3.16- - Binary dis...
Hopper Disassembler is a binary disassembler, decompiler, and debugger for 32- and 64-bit executables. It will let you disassemble any binary you want, and provide you all the information about its... Read more
Default Folder X 5.2.2 - Enhances Open a...
Default Folder X attaches a toolbar to the right side of the Open and Save dialogs in any OS X-native application. The toolbar gives you fast access to various folders and commands. You just click on... Read more
EtreCheck 4.0.1 - For troubleshooting yo...
EtreCheck is an app that displays the important details of your system configuration and allow you to copy that information to the Clipboard. It is meant to be used with Apple Support Communities to... Read more

Latest Forum Discussions

See All

Our top 5 characters from casual RPG Cre...
Creature Quest definitely lives up to its name with a host of collectible creatures based on fantasy tales and world mythologies. To celebrate Creature Quest’s first birthday, we’re going to lay out what we think are the five best characters in the... | Read more »
Around the Empire: What have you missed...
Did you know that Steel Media has a whole swathe of other sites dedicated to all aspects of mobile gaming? Sure you'll get the very best iPhone news, reviews, and opinions right here at 148Apps, but we don't want you missing out on a single piece... | Read more »
All the best games on sale for iPhone an...
Oh hi there, and welcome to our round-up of the best games that are currently on sale for iPhone and iPad. You thought I didn't see you there, did you, skulking behind the bushes? Trust me though, the bushes aren't where the best deals are. The... | Read more »
The Battle of Polytopia Guide - How to H...
A new update just released for The Battle of Polytopia (formerly Super Tribes), which introduces online multiplayer. For all the fans of Midjiwan’s lite take on Civilization, this is certainly welcome news, but playing online isn’t as easy and... | Read more »
Here are the very best mobile games to p...
It's Valentine's Day! Did you get loads of cards and chocolates and other tacky, simple expressions of human affection? Did you send out tat because you find it almost impossible to express emotion unless there's a section dedicated to it at your... | Read more »
Florence (Games)
Florence 1.0 Device: iOS Universal Category: Games Price: $2.99, Version: 1.0 (iTunes) Description: Florence is an interactive storybook from the award-winning lead designer of Monument Valley about the heart-racing highs and... | Read more »
Purrfect Date (Games)
Purrfect Date 1.0 Device: iOS Universal Category: Games Price: $4.99, Version: 1.0 (iTunes) Description: Cats are a lil’ like marmite. Either you absolutely head-over-heels love’ em, or… nahhh, who are we kidding?! Everyone... | Read more »
More monsters to collect and evolve in C...
A laid-back mix of RPG and TCG, Creature Quest is all about building your deck, evolving your creatures and winning in battle. It’s the creation of VC Mobile, set up by Might and Magic producer Jon Van Caneghem. There are elements of that classic... | Read more »
Check out this awesome hands-on with the...
Well, PlayerUnknown's Battlegrounds has come out on mobile. This isn't a clone, this isn't a riff on the battleroyale mechanics of the game, it's the official mobile port by Tencent. But there's a little bit of a hitch. [Read more] | Read more »
Hostage Negotiator (Entertainment)
Hostage Negotiator 1.1.0 Device: iOS Universal Category: Entertainment Price: $3.99, Version: 1.1.0 (iTunes) Description: Official app of the board game by AJ Porfirio and Van Ryder Games. In Hostage Negotiator, you play the part of... | Read more »

Price Scanner via MacPrices.net

13″ 3.1GHz/256GB Silver Touch Bar MacBook Pro...
Amazon has the Silver 13″ 3.1GHz/256GB Touch Bar MacBook Pro (MPXX2LL/A) on sale for $1649.99 including free shipping. Their price is $150 off MSRP, and it’s the lowest price available for a new 13″... Read more
Saturday Sale: Amazon offers 13″ 1.8GHz/256GB...
Amazon has the 13″ 1.8GHz/256B Apple MacBook Air on sale today for $250 off MSRP including free shipping: – 13″ 1.8GHz/256GB MacBook Air (MQD42LL/A): $949.99, $250 off MSRP Their price is the lowest... Read more
Roundup of Apple Certified Refurbished 12″ Ma...
Apple has Certified Refurbished 2017 12″ Retina MacBooks available for $200-$240 off the cost of new models. Apple will include a standard one-year warranty with each MacBook, and shipping is free.... Read more
Apple offers Certified Refurbished 10″ and 12...
Apple is now offering Certified Refurbished 2017 10″ and 12″ iPad Pros for $100-$190 off MSRP, depending on the model. An Apple one-year warranty is included with each model, and shipping is free: –... Read more
Apple Canada offers Certified Refurbished Mac...
 Canadian shoppers can save up to $560 on the purchase of a 2017 current-generation MacBook Pro, MacBook, or MacBook Air with Certified Refurbished models at Apple Canada. Apple’s refurbished prices... Read more
Sale! 13″ MacBook Airs for up to $180 off MSR...
B&H Photo has 13″ MacBook Airs on sale for $50-$120 off MSRP. Shipping is free, and B&H charges sales tax for NY & NJ residents only: – 13″ 1.8GHz/128GB MacBook Air (MQD32LL/A): $899, $... Read more
Sale! New 8-core iMac Pro for $4799, $200 off...
Adorama has the 8-core iMac Pro on sale for $4799 including free shipping plus NY & NJ sales tax only. Their price is $200 off MSRP, and it’s the currently lowest price available for an iMac Pro. Read more
Sale! Walmart lowers prices even more on 9″ i...
Walmart has lowered their sale price on 9.7″ Apple iPads to $80 off MSRP for a limited time. Sale prices are for online orders only, in-store prices may vary: – 9″ 32GB iPad: $249.99 $80 off – 9″... Read more
Roundup of 13″ MacBook Pro sales, models avai...
B&H Photo has 13″ MacBook Pros on sale for up to $200 off MSRP. Shipping is free, and B&H charges sales tax for NY & NJ residents only. Their prices are the lowest available for these... Read more
Roundup of 15″ MacBook Pros sale, models up t...
B&H Photo has 15″ MacBook Pros on sale for up to $200 off MSRP. Shipping is free, and B&H charges sales tax for NY & NJ residents only: – 15″ 2.8GHz Touch Bar MacBook Pro Space Gray (... 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
*Apple* Retail - Multiple Positions - Apple,...
Job Description:SalesSpecialist - Retail Customer Service and SalesTransform Apple Store visitors into loyal Apple customers. When customers enter the store, 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: 113501424 Norman, Oklahoma, United States Posted: 15-Feb-2018 Weekly Hours: 40.00 **Job Summary** Are you passionate about Read more
Senior Program Manager - *Apple* Music - Ap...
# Senior Program Manager - Apple Music Job Number: 113393020 Seattle, Washington, United States Posted: 02-Feb-2018 Weekly Hours: 40.00 **Job Summary** Apple is Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.