I upgraded our working version of PhpMyAdmin today. Our previous version was 2.6.0-pl2, released on Oct. 12, 2004. Our new version is 2.6.4-rc1, released on Aug. 28, 2005 as a Release Candidate. The new version is installed at the same url: http://oceaninformatics.ucsd.edu/phpmyadmin. I removed (archived outside the web root) the old version since we should no longer use it. You need a mysql login/pass to login. Talk to Jerry if you don’t have one.
(Note: I changed the authentication scheme from http-based to cookie-based… don’t worry if you don’t know what this means).

There are many major UI improvements which should be useful for db administration work. In addition, I discovered a number of cool features that ship with PhpMyAdmin, but are not available by default; they require additional configuration steps.

These features include:
1. Foreign key enforcement and visualization through query/edit/insert interface.
2. Bookmarking reusable queries
3. Maintaining history of recent queries
4. Creating database dictionaries and schema diagrams as PDFs

Foreign Key Enforcement
Though MySQL now includes support for foreign keys/relations, it is difficult to model these relations in an application interface or on the mysql command line. For example, if you want to insert a new record that has a foreign key, you need to look in the referenced table to make sure you use a valid value for the foreign key. However, using PhpMyAdmin, you can choose a value from a drop-down list maintaining foreign key integrity. This feature is extremely useful for relational database work because it removes the burden from the administrator of looking up foreign keys.

Bookmarking Reusable Queries
PhpMyAdmin lets you bookmark common queries, so you don’t have to type them out each time. In addition, you can pass in a variable to a bookmarked query, which is useful for singling out specific records with the WHERE clause.

History of Recent Queries
PhpMyAdmin tracks the history of recent queries, making it easy to go back and reuse a query, even if you didn’t bookmark it.

Database Dictionaries and Schema Diagrams
You can export a Database Dictionary as a PDF. The dicitionary contains the structure, comments, etc. for each table in the database. Furthermore, PhpMyAdmin creates schema diagrams… and they look snazzier than DBDesigner4!

View the differences here.

I think I may start using PhpMyAdmin to model my schema diagrams, an ironic choice considering I spent the last couple days working with DBDesigner4 and related X11 forwarding issues. However, it is best to keep both apps in consideration for making schema diagrams, as each application has its own strengths. PhpMyAdmin produces clean and colorful diagrams, whereas DBDesigner4 gives you better visual feedback when laying out tables.

These additional features should be highly beneficial to our database workflow and productivity. I decided to upgrade our version after reading these articles.

I didn’t really go into specifics here, such as exactly how to bookmark a query or create a schema diagram. I can save these topics for later posts, or answer them in the comments section.