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

jAlbum 19.2 - Create custom photo galler...
With jAlbum, you can create gorgeous custom photo galleries for the Web without writing a line of code! Beginner-friendly, with pro results - Simply drag and drop photos into groups, choose a design... Read more
BlueStacks 4.140.13 - Run Android applic...
BlueStacks App Player lets you run your Android apps fast and fullscreen on your Mac. Feature comparison chart Version 4.140.13: Highlights/Bug Fixes: Feel free to use BlueStacks as your go to... Read more
Adobe Premiere Pro CC 2020 14.0.1 - Digi...
Premiere Pro CC 2020 is available as part of Adobe Creative Cloud for as little as $52.99/month. The price on display is a price for annual by-monthly plan for Adobe Premiere Pro only Adobe Premiere... Read more
VirtualBox 6.1.2 - x86 virtualization so...
VirtualBox is a family of powerful x86 virtualization products for enterprise as well as home use. Not only is VirtualBox an extremely feature rich, high performance product for enterprise customers... Read more
RoboForm 8.6.8 - Password manager; syncs...
RoboForm is a password manager that offers one-click login, mobile syncing, easy form filling, and reliable security. Password Manager. RoboForm remembers your passwords so you don't have to! Just... Read more
Postbox 7.0.11 - Powerful and flexible e...
Postbox is a new email application that helps you organize your work life and get stuff done. It has all the elegance and simplicity of Apple Mail, but with more power and flexibility to manage even... Read more
calibre 4.9.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
Notability 4.2 - Note-taking and annotat...
Notability is a powerful note-taker to annotate documents, sketch ideas, record lectures, take notes and more. It combines, typing, handwriting, audio recording, and photos so you can create notes... Read more
FoldersSynchronizer 5.0.1 - Synchronize...
FoldersSynchronizer is a popular and useful utility that synchronizes and backs-up files, folders, disks and boot disks. On each session you can apply special options like Timers, Multiple Folders,... Read more
Sketch 62 - Design app for UX/UI for iOS...
Sketch is an innovative and fresh look at vector drawing. Its intentionally minimalist design is based upon a drawing space of unlimited size and layers, free of palettes, panels, menus, windows, and... Read more

Latest Forum Discussions

See All

The Alliance update to Out There: Omega...
Out There is an old go-to recommendation for a lot of mobile stalwarts, but I could never really get into it. This sci-fi survival game that blended elements of interactive fiction and roguelike mechanics just felt a little off-balance and a little... | Read more »
Animal Fury Destination is an action-adv...
Animal Fury Destination is an action-adventure game from independent, Colombian developer Ignicion Games. It's a 3D action game where you'll play as various different characters as you embark on a quest to stop an evil crow sorcerer. [Read more] | Read more »
Shadowgun War Games Closed Beta Impressi...
Shadowgun: War Games is an upcoming free-to-play multiplayer shooter that’s essentially just an Overwatch knock-off. There are hero characters with special abilities, and you compete in 5-v-5 game modes where the goal is to use superior team... | Read more »
Slingsters is a physics-based puzzler fo...
Slingsters is a physics-based puzzle game where the aim is to collect various different monsters by flinging them from one side of a level to the other and into a box. It's also the first game from Nappy Cat and is available now for iOS and... | Read more »
Spiritwish's latest update sees the...
A sizeable update has hit Nexon's MMORPG Spiritwish today. It brings a new game mode, characters and there will also be a special event to celebrate the update with a firework display. [Read more] | Read more »
Maze Machina, a turn-based puzzler from...
The latest game from Arnold Rauers also known as Tiny Touch Tales is now available. You may be familiar with one of his many excellent titles such as Card Crawl, Enyo and Card Thief. His latest endeavour is called Maze Machina and you can grab it... | Read more »
Mario Kart Tour's Ice Tour races to...
Can you believe Mario Kart Tour is already on its 9th tour? The game only launched back in September, and since then it's become increasingly tricky to keep on top of the amount of new content Nintendo is pumping out. [Read more] | Read more »
Apple Arcade: Ranked - Top 50 [Updated 1...
In case you missed it, I am on a quest to rank every Apple Arcade game there is. [Read more] | Read more »
Marvel Future Fight's latest update...
Marvel Future Fight's latest update has added an all-new team of heroes to recruit and do battle with. The 'Warriors of the Sky' include Blue Dragon, War Tiger, Sun Bird, and Shadow Shell. As is the norm, each character comes with their own unique... | Read more »
Klee: Spacetime Cleaners is a fast-paced...
Klee: Spacetime Cleaners is a fast-paced auto-shooter that sports a cute retro aesthetic thathad racked up an impressive 100,000 pre-registers prior to its release. It's available now for both iOS and Android. [Read more] | Read more »

Price Scanner via MacPrices.net

Just in! Apple iMacs on sale for $100-$150 of...
B&H Photo has new 2019 21″ and 27″ 5K iMacs on stock today and on sale for up to $150 off Apple’s MSRP, with prices starting at only $999. These are the same iMacs sold by Apple in their retail... Read more
Save $100 on the 13″ 1.4GHz MacBook Pro at th...
Apple resellers have 13″ 1.4GHz MacBook Pros on sale today for $100 off Apple’s MSRP, and some are including free overnight delivery: (1) Amazon has new 2019 13″ 1.4GHz MacBook Pros on sale for $100... Read more
AT&T offers free 64GB Apple iPhone XS wit...
Open a new line of service with AT&T, and they will include a free 64GB iPhone XS. Credit for the phone is applied monthly over a 30 month lease. The fine print: “Limited Time Requires new line... Read more
New Verizon deal: Apple iPhone XR for $300 of...
Switch to Verizon and sign up with one of their Unlimited plans, and Verizon will take $300 off the price of an Apple iPhone XR (regularly $749), plus get a free $200 prepaid Mastercard. This is an... Read more
Amazon’s popular AirPods sale is back with mo...
Amazon has new 2019 Apple AirPods on sale today ranging up to $40 off MSRP, starting at $129, as part of their popular Apple AirPods sale. Shipping is free: – AirPods Pro: $234.98 $15 off MSRP –... Read more
Apple’s top of the line 10.5″ 256GB WiFi + Ce...
B&H Photo has the top of the line 10.5″ 256GB WiFi + Cellular iPad Air on sale for $599 shipped. That’s $180 off Apple’s MSRP for this model and the cheapest price available. Overnight shipping... Read more
Apple’s refurbished iPad Pros are the cheapes...
Apple has Certified Refurbished 11″ iPad Pros available on their online store for up to $220 off the cost of new models. Prices start at $679. Each iPad comes with a standard Apple one-year warranty... Read more
Just in: Take $100 off the price of the 3.0GH...
Apple resellers are offering new 2018 6-Core Mac minis for $100 off Apple’s MSRP today, only $999. B&H Photo has 6-Core Mac minis on sale for $100 off Apple’s standard MSRP. Overnight shipping is... Read more
Apple has 4-core and 6-core 2018 Mac minis av...
Apple has Certified Refurbished 2018 Mac minis available on their online store for $120-$170 off the cost of new models. Each mini comes with a new outer case plus a standard Apple one-year warranty... Read more
Amazon offers $200 discount on 13″ MacBook Ai...
Amazon has new 2019 13″ MacBook Airs with 256GB SSDs on sale for $200 off Apple’s MSRP, now only $1099, each including free shipping. Be sure to select Amazon as the seller during checkout, rather... Read more

Jobs Board

Tableau Data Visualization Engineer - *Apple...
Job Summary Apple is looking for a seasoned Tableau Data Visualization Engineer to join the team on an initial 9 month contract at our Global HQ in Cupertino. The Read more
*Apple* Mobility Pro - Best Buy (United Stat...
**744429BR** **Job Title:** Apple Mobility Pro **Job Category:** Store Associates **Store NUmber or Department:** 000574-Garner-Store **Job Description:** At Best Read more
Geek Squad *Apple* Consultation Professiona...
**757963BR** **Job Title:** Geek Squad Apple Consultation Professional **Job Category:** Store Associates **Store NUmber or Department:** 000433-Henrietta-Store Read more
*Apple* Computing Professional - Best Buy (U...
**754611BR** **Job Title:** Apple Computing Professional **Job Category:** Store Associates **Store NUmber or Department:** 000142-Milpitas-Store **Job Read more
Best Buy *Apple* Computing Master - Best Bu...
**745058BR** **Job Title:** Best Buy Apple Computing Master **Job Category:** Store Associates **Store NUmber or Department:** 001080-Lake Charles-Store **Job Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.