Home arrow PHP arrow Page 2 - Doing More with phpMyAdmin (Part 2)

The Ground Work - PHP

phpIn the first segment of this two-part tutorial, I gave you a quick overview of some of the interesting new features available in phpMyAdmin. In this concluding segment, find out how to use phpMyAdmin to define relationships between tables, maintain a log of commonly-used queries and create entity-relationship diagrams.

TABLE OF CONTENTS:
  1. Doing More with phpMyAdmin (Part 2)
  2. The Ground Work
  3. Total Recall
  4. Tangled Relationships
  5. Bookmark Bandit
  6. Looking Up the Dictionary
  7. History Lesson
By: Harish Kamath, (c) Melonfire
Rating: starstarstarstarstar / 41
December 08, 2003

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
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
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: