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

Apple macOS Sierra 10.12.3 - The latest...
With Apple macOS Sierra, Siri makes its debut on Mac, with new features designed just for the desktop. Your Mac works with iCloud and your Apple devices in smart new ways, and intelligent... Read more
Lyn 1.8.5 - 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
iClock Pro 3.4.7 - Customize your menuba...
iClock Pro is a menu bar replacement clock for Apple's default clock. iClock Pro is an update, total rewrite and improvement to the popular iClock. Have the day, date and time in different fonts and... Read more
Opera 42.0.2393.137 - High-performance W...
Opera is a fast and secure browser trusted by millions of users. With the intuitive interface, Speed Dial and visual bookmarks for organizing favorite sites, news feature with fresh, relevant content... Read more
Apple Security Update 2016-003 Supplemen...
Apple Security Update is recommended for all users and improves the security of OS X. For detailed information about the security content of this update, please visit: http://support.apple.com/kb/... Read more
Apple iOS 10.2.1 - The latest version of...
iOS 10 is the biggest release of iOS ever. A massive update to Messages brings the power of the App Store to your conversations and makes messaging more personal than ever. Find your route with... Read more
BetterTouchTool 1.992 - Customize Multi-...
BetterTouchTool adds many new, fully customizable gestures to the Magic Mouse, Multi-Touch MacBook trackpad, and Magic Trackpad. These gestures are customizable: Magic Mouse: Pinch in / out (zoom... Read more
Viber 6.5.5 - Send messages and make cal...
Viber lets you send free messages and make free calls to other Viber users, on any device and network, in any country! Viber syncs your contacts, messages and call history with your mobile device, so... Read more
Lyn 1.8.5 - 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
Apple iOS 10.2.1 - The latest version of...
iOS 10 is the biggest release of iOS ever. A massive update to Messages brings the power of the App Store to your conversations and makes messaging more personal than ever. Find your route with... Read more

Clash Royale gets some serious balance u...
| Read more »
Ironhide Game Studio prepares for a busy...
Kingdom Rush breathed fresh life into the tired tower defense genre way back in 2012. The game was a robust challenge that somehow managed to lift you up, rather than leaving you feeling crushed and hopeless. The rich array of unit types and... | Read more »
Collect pets and sling arrows in Arcane...
Mobile gaming is a crowded market, but regular updates are a good way to keep us attention-short players keen. The brand new content in Arcane Online is a prime example. Published by Japanese developer Gala, Arcane Online is a fantasy MMO that... | Read more »
Super Mario Run dashes onto Android in M...
Super Mario Run was one of the biggest mobile launches in 2016 before it was met with a lukewarm response by many. While the game itself plays a treat, it's pretty hard to swallow the steep price for the full game. With that said, Android users... | Read more »
WarFriends Beginner's Guide: How to...
Chillingo's new game, WarFriends, is finally available world wide, and so far it's a refreshing change from common mobile game trends. The game's a mix of tower defense, third person shooter, and collectible card game. There's a lot to unpack here... | Read more »
Super Gridland (Entertainment)
Super Gridland 1.0 Device: iOS Universal Category: Entertainment Price: $1.99, Version: 1.0 (iTunes) Description: Match. Build. Survive. "exquisitely tuned" - Rock Paper Shotgun No in-app purches, and no ads! | Read more »
Red's Kingdom (Games)
Red's Kingdom 1.0 Device: iOS Universal Category: Games Price: $4.99, Version: 1.0 (iTunes) Description: Mad King Mac has kidnapped your father and stolen your golden nut! Solve puzzles and battle goons as you explore and battle your... | Read more »
Turbo League Guide: How to tame the cont...
| Read more »
Fire Emblem: Heroes coming to Google Pla...
Nintendo gave us our first look at Fire Emblem: Heroes, the upcoming mobile Fire Emblem game the company hinted at last year. Revealed at the Fire Emblem Direct event held today, the game will condense the series' tactical RPG combat into bite-... | Read more »
ReSlice (Music)
ReSlice 1.0 Device: iOS Universal Category: Music Price: $9.99, Version: 1.0 (iTunes) Description: Audio Slice Machine Slice your audio samples with ReSlice and create flexible musical atoms which can be triggered by MIDI notes or... | Read more »

Price Scanner via MacPrices.net

Deal alert! 13-inch 2.0GHz MacBook Pros for $...
B&H Photo has the new 2016 13″ 2.0GHz non-Touch Bar MacBook Pros in stock today and on sale for $225 off MSRP. Shipping is free, and B&H charges NY sales tax only: - 13″ 2.0GHz MacBook Pro... Read more
Free LibreOffice Portable 5.2.4 Complete Offi...
PortableApps.com and The Document Foundation have announce the release of LibreOffice Portable 5.2.4. LibreOffice Portable is an Open Source full-featured office suite — including a word processor,... Read more
Apple Planning Three New Tablets For 2017 – D...
Digitimes’ Rebecca Kuo and Joseph Tsai say that unnamed insider sources report Apple having three new tablets in the pipeline for 2017 release: a 9.7-inch model with a friendly price range, a new mid... Read more
Roundup of 15-inch Touch Bar MacBook Pro sale...
B&H Photo has the new 2016 15″ Apple Touch Bar MacBook Pros in stock today and on sale for up to $150 off MSRP. Shipping is free, and B&H charges NY sales tax only: - 15″ 2.7GHz Touch Bar... Read more
Apple refurbished iPad Pros available for up...
Apple has Certified Refurbished 9″ and 12″ Apple iPad Pros available for up to $160 off the cost of new iPads. An Apple one-year warranty is included with each model, and shipping is free: - 32GB 9″... Read more
16GB iPad Air 2, Apple refurbished, available...
Apple has Certified Refurbished 16GB iPad Air 2s available for $319 including free shipping. A standard Apple one-year is included. Their price is $60 off original MSRP for this model. Read more
Apple iMacs on sale for up to $120 off MSRP
B&H Photo has 21″ and 27″ Apple iMacs on sale for up to $120 off MSRP, each including free shipping plus NY sales tax only: - 27″ 3.3GHz iMac 5K: $2199 $100 off MSRP - 27″ 3.2GHz/1TB Fusion iMac... 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
Save up to $350 with Apple Certified Refurbis...
Apple has Certified Refurbished 2015 21″ & 27″ iMacs available for up to $350 off MSRP. Apple’s one-year warranty is standard, and shipping is free. The following models are available: - 21″ 3.... Read more
2015 12-inch Retina MacBooks, Apple refurbish...
Apple has Certified Refurbished 2015 12″ Retina MacBooks available for up to $410 off original MSRP. Apple will include a standard one-year warranty with each MacBook, and shipping is free. The... Read more

Jobs Board

*Apple* & PC Desktop Support Technician...
Apple & PC Desktop Support Technician job in Manhattan, NY Introduction: We have immediate job openings for several Desktop Support Technicians with one of our most Read more
*Apple* & PC Desktop Support Technician...
Apple & PC Desktop Support Technician job in Stamford, CT We have immediate job openings for several Desktop Support Technicians with one of our most well-known 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* Site Security Manager - Apple (Unite...
# Apple Site Security Manager Job Number: 54692472 Culver City, California, United States Posted: Jan. 19, 2017 Weekly Hours: 40.00 **Job Summary** The Apple Read more
*Apple* macOS Systems Integration Administra...
…most exceptional support available in the industry. SCI is seeking an Junior Apple macOS systems integration administrator that will be responsible for providing Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.