More advanced features in phpMyAdmin

This is part two of the article “Database management with phpMyAdmin” a review of the book “Mastering phpMyAdmin” and the open source software phpMyAdmin.

The PMA relational system

In MySQL there is no native function to create relations between several database tables. Using the InnoDB database type makes it possible to link with foreign keys and in the latest versions there are stored procedures available to create or handle relations (and other intelligence) inside the database. PMA has great features to create relations while working on data within the system, Yes the limit is for transactions within PMA, if you need relations within your own application you need to create them by yourself, all relational data created by PMA is stored as PMA meta data. The relational system is not enabled by default, setup scripts (table creation statements) are located in the scripts sub-directory. Execute them within PMA and change your configuration file to fit your database structure:

$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';

After the configurations are done, you get new features in your PMA installation. Note there is also a better support for relations using the foreign key feature while using the InnoDB database type.

SQL validator

I used PMA with the MySQL errors for debugging in the past, that was for me some easy way to test SQL statement and find SQL errors very fast. In PMA its also possible to use the “Mimer SQL validator“, using this validator your SQL statement is test again the Core SQL-99 rules and gives a report. Note that you need your server needs support for XML, PCRE and PEAR.

Multi table query generator

I tried this generator a few years ago and never used it really. Using the book it’s very easy to understand and maybe you like to play with that feature for a while. I don’t advice this kind of query generators because of risk of loosing your knowledge in SQL programming. A beginner could use that the generator to learn basic JOIN statements.

Bookmark successful queries

You know the situation that you have to execute a SQL statement once a in period and you save that code in your own file of often used SQL statements. PMA has some nice feature to store these queries in a kind of bookmark file. Note that you need to install the relational feature to store the bookmarks (meta information). The following extras are available with the bookmark feature:

• Storing bookmarks before sending a query
• multi query bookmarks
• bookmark manipulation using variables
• default query for each table

Creating PDF files showing relational schemas

After building relations in MySQL it’s very useful to print these linked database table structure on paper. PMA has a compete editor interface to show the table structure using your own positions.

MIME-Based transformations

for those people using PMA to maintain the full database (like a CMS) is this feature: MIME-Based Transformations. With this feature its possible to create some “Intelligence” for table columns to transform BLOB data into images, text into clickable URLs, date formatting… Note that the GD library is required to show Images with this feature.

Character Sets and Collations

Since MySQL version 4.1.x and later it’s possible to store data with collations in a database table. This way data with special characters like “ß” or “ë” are comparable with “normal” characters. PMA has full support for collations since version 2.6.

If you use data which includes special characters you should use the right “effective” character set otherwise the data is not displayed the right way.

There are many more feature in PMA like described here. If you read the book “Mastering phpMyAdmin for Effective MySQL Management” you get a better idea on all of them.

Published in: PHP Scripts