ht4

It goes without saying that anybody who has ever done open source development has heard of phpMyAdmin – a tool that lets you manipulate MySQL databases through a web interface.

What may be less known is that phpMyAdmin comes with a variety of features that aren’t enabled by default.

Enabling the features

To enable these features, you will first need to create a database called “phpmyadmin”.

Once that’s done, execute the SQL file “create_tables.sql” found in “/scripts”.

Afterwards, open up the phpMyAdmin configs file: “config.inc.php” and uncomment the following lines:

$cfg['Servers'][$i]['pmadb'] = ‘phpmyadmin’;

$cfg['Servers'][$i]['bookmarktable'] = ‘pma_bookmark’;

$cfg['Servers'][$i]['relation'] = ‘pma_relation’;

$cfg['Servers'][$i]['table_info'] = ‘pma_table_info’;

$cfg['Servers'][$i]['table_coords'] = ‘pma_table_coords’;

$cfg['Servers'][$i]['pdf_pages'] = ‘pma_pdf_pages’;

$cfg['Servers'][$i]['column_info'] = ‘pma_column_info’;

$cfg['Servers'][$i]['history'] = ‘pma_history’;

$cfg['Servers'][$i]['designer_coords'] = ‘pma_designer_coords’;

$cfg['Servers'][$i]['pmadb'] = ‘phpmyadmin’;

You should now see a couple of new buttons in the horizontal menu after selecting a database:

Designer Mode

The designer mode allows you to visualize your database structure and create table relations on the fly.

You may not be able to create the structure of a database with this tool, but it does a good job at creating a diagram out of an existing structure and allowing you to trace relations. With only two clicks you can create a foreign key relationship between two tables.

The “Save position” button is great for saving your diagram for later use.

The designer mode also allows you to print and export the diagram. This feature can be really useful when there is more than one person on the project or when you need a paper copy of the diagram.

Query History

Another great and underestimated feature is the query history.

I can’t help but think of the numerous times I ran queries in phpMyAdmin and had to re-run them again at a later moment.

The query history tool allows you to view a list of all SELECT queries you executed.

Unfortunately this SQL history is only available for the current session. The next time you login or if you are booted out of phpMyAdmin, you will lose the content of the query history.

To access the query history, simply click the “Edit” link after executing a query.

Clicking the “SQL History” tab will bring you to the query history window:

Query Bookmarking

The query bookmarking feature complements the query history feature. In essence, it allows you to save a query for later use.

You can bookmark a query in the previous dialog window as well as the “SQL” tab in the database view.

After bookmarking a query, you can execute it from the SQL tab.

For example, suppose we have this table:

CREATE TABLE `user` (
`id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`active` TINYINT( 1 ) UNSIGNED NOT NULL ,
`username` VARCHAR( 32 ) NOT NULL
) ENGINE = INNODB ;

We can bookmark the following query so that we can have easy access to active users:

SELECT * FROM user WHERE active = 1

A neat feature included with the bookmarking tool is the variable option. This option lets you dynamically add a variable or a statement to the query when executing the bookmark.

For example, we can bookmark the following query:

SELECT * FROM user WHERE active = 1 /* AND username = ‘[VARIABLE]‘ */

When we execute the bookmark with no variable, the second part of the query will simply be sent as a comment.

Suppose I execute the bookmark with the variable value ‘chris’, the query will become:

SELECT * FROM user WHERE active = 1 AND username = ‘chris’

Many other features are available after enabling them. It’s really a matter of browsing through phpMyAdmin to find them. That being said I hope this will help people increase their productivity when using phpMyAdmin.

- Christian Joudrey