Doing More with phpMyAdmin (Part 2) - The Ground Work
( Page 2 of 7 )

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 (created it in the first part of this article) 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.

>>> More PHP Articles          >>> More By Harish Kamath, (c) Melonfire