August 21, 2008

phpMinAdmin is a powerful minimalist MySQL editor

Author: Mayank Sharma

If you've ever worked with and manipulated MySQL databases, chances are you've used phpMyAdmin to manage your databases from a Web interface. But phpMyAdmin can be a little complex; if you want a lightweight alternative, try phpMinAdmin. It's easier to install than its more robust cousin and has an easy-to-navigate graphical user interface for most important MySQL functions.

Many Web hosting solutions bundle phpMyAdmin to let users create databases for their blogging software or content management systems. If yours doesn't, you'll find installing phpMinAdmin far easier than installing phpMyAdmin. phpMinAdmin is a single PHP script that requires no configuration. You just need to upload it to your Web server's root directory and log in with your database connection settings. This also makes it an ideal tool for people working on local database projects who need a MySQL GUI but don't have the time to install phpMyAdmin.

Once you've uploaded the script and entered your database connection details (host, database admin's username, and password), you'll be dropped to phpMinAdmin's simple main page. I was initially surprised by its minimalism, especially when compared to phpMyAdmin's landing page. Looking closely though, you can do quite a bit from phpMinAdmin's main page -- you can create a new database, edit database user privileges, and see a list of ongoing database activities, all from links on the right-hand pane, and from select a database for querying or editing, and back up all databases on the host from choices on the left.

The best part about phpMinAdmin is that it maintains minimalism throughout its interface without compromising on functionality. You get additional options and functions as you dive deeper into tasks such as creating a database or adding tables.

When you select a database from the drop-down list on the main interface, the view is updated to show you a list of all the tables in the database in the left pane. In the right pane you get advanced controls to alter the database, prepare simple database schemas, create and store a database view, and create procedures and functions.

If you are using phpMinAdmin to manage your blog or Web software, your interactions with a database will be limited to creating or removing one. If you are updating your Web software or moving to another one, you'll also want to back up and restore your database. With phpMinAdmin you can do these tasks without much effort.

The "Create new database" link on the main page takes you to a simple form that asks you to enter a name of the database you want to create and select a collation scheme for it from a drop-down list. Collation is the character set used to store data in the text field and is part of MySQL's internationalization efforts. If you don't select any collation, phpMinAdmin uses MySQL's default collation, latin1_swedish_ci, which works fine for storing text in English.

If you create a database for a Web application, such as WordPress, the application's installer will automatically create tables. But if you are working on a database project of your own, you can create tables with phpMinAdmin, using the "Create new table" link after selecting the database from the main interface. As in any relational database, a MySQL table consists of columns, all of which have unique names and are of a particular type and length. To make your task easier, phpMinAdmin lists all MySQL column types and their respective options in a drop-down list. You can also easily define primary keys, foreign keys, and triggers.

More importantly, modifying the structure of tables, keys, and triggers is as easy as creating them. In fact you can also alter the name of the database and its collation using the "Alter database" link after selecting the database from the pull-down list on the main interface. You also get a "Drop" button within the alter database section to delete the database and all its tables.

Another common database task is to sort the data in the tables. Instead of using WordPress's built-in comment removal system, I find it easier to hunt for spam comments on my WordPress site (or for that matter any database-driven blogging software) by querying the database directly and then deleting the selected ones. phpMinAdmin is perfectly suited for this as well. To hunt for spam in the comments table in my WordPress database, I click on the "select" link next to the wp_comments table. In the interface that opens, phpMinAdmin helps me write a SELECT SQL query by arranging all the relevant attributes, keywords, and clauses in drop-down lists.

Once your query returns a group of records, you can edit them individually using the "edit" link, or select them all, or particular ones. You can then delete the selected ones or use the "Truncate table" button to empty the table.

Since you can't undeleted a dropped table, it's a good idea to keep a backup of any tables you plan to delete. In fact, in general, backups are a guarantee against those situations when the database is corrupted due to reasons beyond your control, such as disk errors. Currently with phpMinAdmin you can import a single table, a whole database, or all databases on the host as SQL queries. This means when you use the "Dump" link after selecting a database from the pull-down menu in the main interface, phpMinAdmin will display a long list of SQL queries you can use to restore the database. Tools like phpMyAdmin can also read these queries and restore the database. You can also restore by directly feeding the SQL statements to MySQL.

Similarly, to import a database with phpMinAdmin, you'll have to either paste a SQL query or upload a file with the list of SQL queries. You'll find the text box to paste the SQL query under the "SQL command" link on the main interface.

Jakub Vrána, phpMinAdmin's developer, is working to improve the tool's import and export options. He says he's working on a new version of phpMinAdmin, which he hopes will be released in early September, that will allow users to choose the tables and the type of data they want to export, and the format of their choice. He's also working on a feature that will sync databases on two machines easily. But Vrána isn't considering working on a PostgreSQL version of phpMinAdmin.

phpMinAdmin is a nice little app that can be employed in a broad range of database projects. You may be able to do more with phpMyAdmin, but phpMinAdmin can do many of the most common MySQL database tasks, which makes it suitable for most kinds of database projects. Thanks to its no-nonsense install and easy-to-use interface, it's also a good tool for anyone new to databases, for tasks that involve limited database interactions like maintaining a blog or other CMS.


  • System Administration
  • Databases
Click Here!