Back For More
In the first segment of this two-part tutorial, I gave you a quick overview of some of the interesting new features available in phpMyAdmin. Starting with the basics of the installation process and proceeding to the intricacies of securing your instance of phpMyAdmin from prying eyes,changing the application’s appearance and using its built-in reporting features to obtain information on who is using your MySQL RDBMS for what, I hope you’re slowly realizing the utility of this wonderful open-source tool. In fact, I’m going to stick my head out and state that in my humble opinion, phpMyAdmin is to a MySQL developer what TOAD is to an Oracle developer.
You might disagree with this - after all, TOAD allows developers to model a database schema, perform step-by-step SQL debugging and a whole lot more. In fact, that’s the reason I came back for part two - to convince skeptics such as yourself that phpMyAdmin is indeed a worthy competitor, having added many useful features over the years.
Keep reading to find out more.
The Ground Work
Before you get your hands dirty with the new phpMyAdmin enhancements, you need to jump through a couple of hoops to enable them. The basic concept here is simple: phpMyAdmin maintains a special database of its own, which it uses to store information related to these new features (in much the same manner as MySQL itself creates a special “mysql” database to store information about MySQL user privileges). Thus, in order to enable these special features, it is necessary to initialize this database and create a user with rights to manipulate it.
Setting up the special phpMyAdmin database is pretty simple - pop open a MySQL command prompt, and enter the following SQL (modified from the phpMyAdmin-supplied SQL dump file “scripts/create_tables.sql”):
DROP DATABASE IF EXISTS `phpmyadmin`;
CREATE DATABASE IF NOT EXISTS `phpmyadmin`;
USE phpmyadmin;
GRANT SELECT, INSERT, DELETE, UPDATE ON `phpmyadmin`.* TO 'admin'@localhost;
DROP TABLE IF EXISTS `PMA_bookmark`;
CREATE TABLE `PMA_bookmark` (
`id` int(11) DEFAULT '0' NOT NULL AUTO_INCREMENT,
`dbase` VARCHAR(255) NOT NULL,
`user` VARCHAR(255) NOT NULL,
`label` VARCHAR(255) NOT NULL,
`query` TEXT NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM COMMENT='Bookmarks';
DROP TABLE IF EXISTS `PMA_relation`;
CREATE TABLE `PMA_relation` (
`master_db` VARCHAR(64) NOT NULL DEFAULT '',
`master_table` VARCHAR(64) NOT NULL DEFAULT '',
`master_field` VARCHAR(64) NOT NULL DEFAULT '',
`foreign_db` VARCHAR(64) NOT NULL DEFAULT '',
`foreign_table` VARCHAR(64) NOT NULL DEFAULT '',
`foreign_field` VARCHAR(64) NOT NULL DEFAULT '',
PRIMARY KEY (`master_db`, `master_table`,`master_field`),
KEY `foreign_field` (`foreign_db`, `foreign_table`)
) TYPE=MyISAM COMMENT='Relation table';
DROP TABLE IF EXISTS `PMA_table_info`;
CREATE TABLE `PMA_table_info` (
`db_name` VARCHAR(64) NOT NULL DEFAULT '',
`table_name` VARCHAR(64) NOT NULL DEFAULT '',
`display_field` VARCHAR(64) NOT NULL DEFAULT '',
PRIMARY KEY (`db_name`, `table_name`)
) TYPE=MyISAM COMMENT='Table information for phpMyAdmin';
DROP TABLE IF EXISTS `PMA_table_coords`;
CREATE TABLE `PMA_table_coords` (
`db_name` VARCHAR(64) NOT NULL DEFAULT '',
`table_name` VARCHAR(64) NOT NULL DEFAULT '',
`pdf_page_number` INT NOT NULL DEFAULT '0',
`x` float unsigned NOT NULL DEFAULT '0',
`y` float unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`db_name`, `table_name`, `pdf_page_number`)
) TYPE=MyISAM COMMENT='Table coordinates for phpMyAdmin PDF output';
DROP TABLE IF EXISTS `PMA_pdf_pages`;
CREATE TABLE `PMA_pdf_pages` (
`db_name` VARCHAR(64) NOT NULL DEFAULT '',
`page_nr` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`page_descr` VARCHAR(50) NOT NULL DEFAULT '',
PRIMARY KEY (`page_nr`),
KEY (`db_name`)
) TYPE=MyISAM COMMENT='PDF Relationpages for PMA';
DROP TABLE IF EXISTS `PMA_column_info`;
CREATE TABLE `PMA_column_info` (
`id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`db_name` VARCHAR(64) NOT NULL DEFAULT '',
`table_name` VARCHAR(64) NOT NULL DEFAULT '',
`column_name` VARCHAR(64) NOT NULL DEFAULT '',
`comment` VARCHAR(255) NOT NULL DEFAULT '',
`mimetype` VARCHAR(255) NOT NULL DEFAULT '',
`transformation` VARCHAR(255) NOT NULL DEFAULT '',
`transformation_options` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `db_name` (`db_name`, `table_name`, `column_name`)
) TYPE=MyISAM COMMENT='Column Information for phpMyAdmin';
DROP TABLE IF EXISTS `PMA_history`;
CREATE TABLE `PMA_history` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`username` VARCHAR(64) NOT NULL,
`db` VARCHAR(64) NOT NULL,
`table` VARCHAR(64) NOT NULL,
`timevalue` TIMESTAMP NOT NULL,
`sqlquery` TEXT NOT NULL,
PRIMARY KEY (`id`),
KEY `username` (`username`, `db`, `table`, `timevalue`)
) TYPE=MyISAM COMMENT='SQL history';
Most of this is pretty straightforward - create a database for phpMyAdmin to use, and give the special “admin” user (I created it in the first part of this article, remember?) SELECT, INSERT, DELETE and UPDATE rights to that database. Then create a bunch of tables for use by the application, including tables for bookmarks, history and entity relationships. As noted previously, the code to create these tables is supplied with the phpMyAdmin distribution, in a file named “create_tables.sql” in the “scripts/” directory
Once the database has been created, the next step is to tell phpMyAdmin about it. As usual, this is achieved by setting the appropriate parameters in the ubiquitous “config.inc.php” file. Update the file so it looks something like this:
// snip
$cfg['Servers'][$i]['controluser'] = 'admin'; // MySQL control user settings
$cfg['Servers'][$i]['controlpass'] = 'j823kfg2ld'; // access to the grant tables
// snip
$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';
// snip
The “controluser” and “controlpass” parameters tell phpMyAdmin which user has privileges to the “phpmyadmin” database created in the previous step - in this example, the user is named “admin”. The “pmadb” parameter tells phpMyAdmin the name of the database itself, while the remaining parameters tell it which tables to use for specific features. You can turn off support for any feature by setting the respective parameter to a blank value.
Windows users, be warned: MySQL automatically lowercases table names on Windows, so make sure that the names you’re entering into the configuration file match the actual table names.
Total Recall
Before I begin, a quick introduction to the tables I’ll be using throughout this tutorial seems to be in order. In order to explain the new features of phpMyAdmin, I’ll be using a fictitious company’s accounting database, which consists of the following four tables:
- The “services” table: The fictitious system under discussion consists of a service company’s accounting database. This company offers customers a number of outsourced services, each of which is associated with a fee and has a unique service ID. This information is stored in a “services” table, which looks like this:
CREATE TABLE `services` (
`sid` tinyint(4) NOT NULL default '0',
`sname` varchar(255) NOT NULL default '',
`sfee` float(6,2) NOT NULL default '0.00',
PRIMARY KEY (`sid`)
) TYPE=MyISAM;
INSERT INTO `services` VALUES (1, 'Accounting', '1500.00');
INSERT INTO `services` VALUES (2, 'Recruitment', '500.00');
INSERT INTO `services` VALUES (3, 'Data Management', '300.00');
INSERT INTO `services` VALUES (4, 'Administration', '500.00');
INSERT INTO `services` VALUES (5, 'Customer Support', '2500.00');
INSERT INTO `services` VALUES (6, 'Security', '600.00');
- The “clients” table: The company also has a list of its current clients stored in a separate “clients” table. Each client is identified with a unique customer ID.
CREATE TABLE `clients` (
`cid` tinyint(4) NOT NULL default '0',
`cname` varchar(255) NOT NULL default '',
PRIMARY KEY (`cid`)
) TYPE=MyISAM;
INSERT INTO `clients` VALUES (101, 'JV Real Estate');
INSERT INTO `clients` VALUES (102, 'ABC Talent Agency');
INSERT INTO `clients` VALUES (103, 'DMW Trading');
INSERT INTO `clients` VALUES (104, 'Rabbit Foods Inc');
INSERT INTO `clients` VALUES (110, 'Sharp Eyes Detective Agency');
- The “branches” table: Each customer may have one or more branch offices. The “branches” table lists the branch offices per customer, together with each branch’s location. Each branch has a description, a unique branch ID, and a foreign key reference to the customer ID.
CREATE TABLE `branches` (
`bid` int(8) NOT NULL default '0',
`cid` tinyint(4) NOT NULL default '0',
`bdesc` varchar(255) NOT NULL default '',
`bloc` char(3) NOT NULL default ''
) TYPE=MyISAM;
INSERT INTO `branches` VALUES (1011, 101, 'Corporate HQ', 'CA');
INSERT INTO `branches` VALUES (1012, 101, 'Accounting Department', 'NY');
INSERT INTO `branches` VALUES (1013, 101, 'Customer Grievances Department', 'KA');
INSERT INTO `branches` VALUES (1041, 104, 'Branch Office (East)', 'MA');
INSERT INTO `branches` VALUES (1042, 104, 'Branch Office (West)', 'CA');
INSERT INTO `branches` VALUES (1101, 110, 'Head Office', 'CA');
INSERT INTO `branches` VALUES (1031, 103, 'N Region HO', 'ME');
INSERT INTO `branches` VALUES (1032, 103, 'NE Region HO', 'CT');
INSERT INTO `branches` VALUES (1033, 103, 'NW Region HO', 'NY');
- The “branches_services” table: Services supplied to each branch office are listed in this table, which contains pairs of branch IDs and service IDs (foreign keys into the “branches” and “services” table respectively).
CREATE TABLE `branches_services` (
`bid` int(8) NOT NULL default '0',
`sid` tinyint(4) NOT NULL default '0'
) TYPE=MyISAM;
INSERT INTO `branches_services` VALUES (1011, 1);
INSERT INTO `branches_services` VALUES (1011, 2);
INSERT INTO `branches_services` VALUES (1011, 3);
INSERT INTO `branches_services` VALUES (1011, 6);
INSERT INTO `branches_services` VALUES (1012, 1);
INSERT INTO `branches_services` VALUES (1013, 5);
INSERT INTO `branches_services` VALUES (1041, 1);
INSERT INTO `branches_services` VALUES (1041, 4);
INSERT INTO `branches_services` VALUES (1042, 1);
INSERT INTO `branches_services` VALUES (1042, 6);
INSERT INTO `branches_services` VALUES (1101, 1);
INSERT INTO `branches_services` VALUES (1031, 2);
INSERT INTO `branches_services` VALUES (1031, 3);
INSERT INTO `branches_services` VALUES (1031, 4);
INSERT INTO `branches_services` VALUES (1032, 3);
INSERT INTO `branches_services` VALUES (1033, 4);
Now, let’s see what we can do with this.
Tangled Relationships
A major cause for complaint amongst developers working with earlier versions of MySQL was the lack of support for foreign keys. This lack of support meant that developers needed to hard-wire additional safeguards into their code to maintain data integrity between tables. Newer versions of MySQL do include support for foreign keys, but this support is still fairly new and fails to address users still working with older versions of the software.
While phpMyAdmin cannot do much to solve this problem, the developers behind the application have tried to make things a little simpler by allowing developers to define foreign key relationships between tables at the phpMyAdmin level, if not the MySQL level. Doing this makes it possible to enforce the integrity constraints between tables when entering records so long as phpMyAdmin is being used for data entry.
The best way to understand this is with a simple example. Navigate to the “Structure” option of the “branches” table created previously, and scroll down the page to location the “Relation view” hyperlink. Selecting this link will take you to the section that allows you to define relationships between tables, associate comments with columns (useful when creating a database dictionary, explained later in this tutorial) and specify the column to use in foreign key references. Here’s what it looks like:
Set the relationship between the “clients” and “branches” tables in the “Links To” section, by using the drop-down list to associate the “branches.cid” field with the “clients.cid” field.
Next, go to the “Relation view” for the “clients” table, and tell phpMyAdmin to show the “cname” field in the “Choose Field to display” box.
Now, if you try inserting some data into the “branches” table, phpMyAdmin will, instead of allowing you free-form entry into the “cid” field, provide you with a drop-down selection list of all the “cname” values from the “clients” table, thus making it impossible for you to enter an incorrect or non-existent client ID.
Exercising this option has other advantages too - browse the “branches” table,
and you’ll see that the values in the “cid” column are clickable - just click a value and you’ll be transported to the corresponding record in the “clients” table. In fact, just hold your mouse over any of the “cid” values and you will see the corresponding “cname” value from the “clients” table as a neat little tool tip. Cool, huh?
Once you set up the remaining relationships between the tables, you’ll have a ready-to-use administration module…in a fraction of the time it would have taken you to code it in regular PHP!
Bookmark Bandit
Just as most browsers allow users to “bookmark” favourite Web sites, phpMyAdmin allows developers to bookmark complex queries, so that they can be easily reused over and over again. As an example, consider the following query, which returns a list of customers along with the services that they have opted for:
SELECT clients.cname, services.sname FROM branches, clients, branches_services, services WHERE branches.bid = branches_services.bid AND clients.cid = branches.cid AND branches_services.sid = services.sid
This query can easily be bookmarked by popping open the “Query window” (bottom left of the application) and entering the query above into the query box. Once a result set appears, simply scroll to the bottom of the results page, enter a name for the bookmark, and click the “Bookmark this SQL-Query” button to save the query to the bookmarks file.
When you need to execute the query again, use the “SQL” tab to obtain a drop-down list of all previously-bookmarked queries. Find the query you bookmarked earlier, select it and Bob’s your uncle!
You can also use variables in such bookmarked queries. For example, if you want to specify the “sid” of the service in the query on a per-instance basis, simply alter the query string to look like this,
SELECT clients.cname, services.sname FROM branches, clients, branches_services, services WHERE branches.bid = branches_services.bid AND clients.cid = branches.cid AND branches_services.sid = services.sid /* AND services.sid = [VARIABLE] */
and run the query. The first time around, phpMyAdmin will ignore the condition between the “/” and “/” comment marks and run the query after discounting that section of the query string. No matter; when the result set appears, bookmark this query and then use the “SQL” tab to run it again. This time, you’ll be asked for a value for the “sid” variable, as shown below.
While this is a good feature, be warned that it is still a little buggy: I could not use it with string variables correctly and, while it certainly offers a great way to store complex queries for reuse, it still needs some tweaking.
Looking Up The Dictionary
One of the most interesting features to be introduced in the latest version of phpMyAdmin is the ability to create a “database dictionary” in PDF format. For the uninformed, this is a comprehensive document that offers an overview of all the objects in a database - tables, columns, primary keys - together with a neat little relationship diagram that outlines the foreign key references between the tables.
In order to accomplish this Herculean task, phpMyAdmin leverages off the information provided when creating the foreign key relationships (discussed earlier). Table and column comments entered by the table designers are also included in the dictionary, thus making it a good reference document for all developers working with the database.
The best way to understand what a database dictionary is, is with an example. Select a database - I’ll use the “accounts” database created earlier - and use the “Edit PDF Pages” link at the bottom of the page to obtain the following screen:
Specify a name for the PDF document, (say, “Accounting Database”) and select the “Automatic layout” option. After clicking “Go”, the same page will reload and display some supplementary information, as shown below.
You can now select the tables to be included in the dictionary, together with some ancillary settings related to the formatting of the relationship diagram. You can either change the locations of the tables in the final diagram using X and Y coordinates, or use the “Toggle Scratchboard” button to open up a WYSIWYG editor that allows you to place each table exactly where you want it (note that this feature needs Internet Explorer 6.0 or equivalent).
You can also adjust various formatting options for the PDF file, using the options available:
-
Show grid - turn the grid on or off in the relationships diagram
-
Show color - turn colors on or off in the relationships diagram
-
Show dimension of tables - turn the display of the table dimensions on or off in the relationships diagram
-
Display all Tables with same width? - specifies whether the width of the table image be the same for all tables?
-
Data Dictionary - specifies whether table descriptions should be included
-
Data Dictionary Format - specifies whether the dictionary should be formatted as landscape or portrait
-
Paper size - specifies the paper size to be used for creating the document
All done? Click “Go”, and you should get a PDF file which contains the following:
History Lesson
In the concluding section of this tutorial, I will look at two features that at first glance seem too trivial to discuss. However, they’re both pretty useful - so let’s take a look.
The first feature is phpMyAdmin’s ability to keep track of all the SQL statements that you have executed within the session. In fact, this is very similar to a browser’s history module, which stores all the Web sites that you have visited in the recent past. You can access this history list by using the “SQL-history” tab of the “Query window”, as below:
There are a couple of parameters that you can use to tweak for this interesting feature: the $cfg[‘QueryHistoryMax’] variable sets the maximum number of queries to be stored in the history buffer at any given point in time, while the $cfg[‘QueryHistoryDB’] variable tells phpMyAdmin whether to store the history list in a MySQL database or in JavaScript variables. If you choose the latter, the SQL history will be lost as soon as the “Query window” is closed. For all practical purposes, I recommend using the database option to ensure that your queries are stored in the history buffer for a longer duration.
Finally, phpMyAdmin allows you to not only customize its colours, but also its language. So, regardless of whether you’re working in the freezing mountains of Siberia or struggling in the desert sands of Egypt, you can be sure that phpMyAdmin will be able to display the interface in your very own language.
To use this feature, simply navigate to the homepage of the application and use the “Language” drop-down list to change the language. Here’s what the result might look like:
If you’re one of the unfortunate few whose native language is not available in the drop-down list, don’t despair - the phpMyAdmin development team is looking for people to translate the interface into other languages. Why not get in touch?
That’s about it for this two-parter on the phpMyAdmin database administration tool. While the first part dealt with issues like installation, security, and analysis, I went a little further in this second part. I explained how you can effectively leverage on the interesting features offered by new versions of phpMyAdmin, beginning with a discussion of the mechanism to define relationships between tables and how phpMyAdmin can be used to ensure that “bad” data does not find its way into your tables. Next, I explained the bookmarks feature, which lets you “remember” important queries for future use, the procedure for generation of a database dictionary in PDF format on the fly, and wrapped things up with a quick look at the history and language capabilities of the application.
I hope you enjoyed reading this tutorial as much as I enjoyed writing it, and that it gave you some visibility of the true power and flexibility of phpMyAdmin. Until next time…be good!
Note: All examples in this article have been tested on MySQL 4.0.14. Examples are illustrative only, and are not meant for a production environment. Melonfire provides no warranties or support for the source code described in this article. YMMV!
This article was first published on 03 Oct 2003.