Doing More with phpMyAdmin (Part 2)

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.

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. {mospagebreak title=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 (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. {mospagebreak title=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:
  1. 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’); 
    


  2. 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’); 
    


  3. 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’); 
    


  4. 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. {mospagebreak title=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 theclients 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, as seen here.

    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! {mospagebreak title=Bookmark Bandit} Just as most browsers allow users to bookmark favorite 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] */ 
    


    Now, 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 seen here.

    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. {mospagebreak title=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 this 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 here.

    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). Example.

    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 this. {mospagebreak title=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 seen here.

    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 colors, 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!

    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.
[gp-comments width="770" linklove="off" ]

antalya escort bayan antalya escort bayan Antalya escort diyarbakir escort