Tutorial: Having fun with phpMyAdmin's MIME-transformations & PDF-features
Table of contents
Changs/News to this Tutorial
2003-11-03: I have added a link to my 
PayPal donation account. If you enjoy my tutorial and would like to thank me, I'd be grateful to any donations. Thank you!
2003-11-03: As of phpMyAdmin 2.5.4, you can now use checkboxes to delete multiple rows when browsing a table. The change in this interface has led to a change in this tutorial, where the text/plain-PHP transformation is used.
As from then on, nested forms cannot be displayed within the phpMyAdmin resultset display. However, the functionality of self-executing forms as shown in this tutorial, can still be used with a minor modification. Instead of using a <form> container to wrap within the transformation, the default form-target for the multi-row delete is now used. Any change related to this is marked with // New 2003-11-03. Please pay attention, that this changes only apply to phpMyAdmin 2.5.5-dev and later, because some changes had to be introduced to the official phpMyAdmin code.
Introduction
This tutorial is targeted at both novice users and advanced users. Even though I describe many steps in detail for those of you who are not so used to phpMyAdmins interface, I also describe some of the features to be used by advanced users. I tried to create this tutorial with an easy structure, so everyone can skip sections he's not interested in. Even though this page is largely sized, I decided to put everything on a single page to allow quick scrolling to anywhere you like. Please feel free to link to this tutorial wherever you want and give me some feedback (and have a look at the Acknowledgements at the end).
Coloring conventions:
- Items on the interface of phpMyAdmin you can click on or see
- Data you may have to enter
- References to configuration directives or special words
References to files, tables or field names- Notes
- Links
Since the 2.5.0 release of phpMyAdmin, you have a completely extensive and powerful way to change the way your data is represented in phpMyAdmin's browsing display. As I've often had the need to store data in a MySQL table in a compressed format, or binary data, or serialized variables, or whatever I had the wish to alter the way my data is shown to me on the backend. I wanted to bypass programming an additional backend just to display my own data, always using partial code of phpMyAdmin.
But instead, I decided to add a kind-of-plugin architecture into phpMyAdmin, so that everyone can have a gain of that.
So, what are transformations exactly: Plug-Ins to rewrite the (even binary!) contents of any column seen in the phpMyAdmin Browsing-mode to any kind you like, as well as the active insertion of PHP- or JavaScript-code.
To give you a brief look of what you can do, here's a screenshot:
And then, already for a longer time, phpMyAdmin offers PDF generation on the fly. You can generate a data dictionary with your table layout and even show foreign keys and relations in the PDF. Everything with a beautiful graphical Entity-Relationship model and the ability to layout the tables with an intuitive scratchboard (since version 2.5.2). I will show you one by one how to set up relations and how to model a nice PDF page.
After having worked through this tutorial you will be able to:
- know how the additional phpMyAdmin features using special seperate tables work and how to set them up
- store pictures inside a MySQL table and view them from within phpMyAdmin
- put specific transformations on any of your columns
- create your own transformations, even with active PHP-code in it!
- create PDF pages of your table layout, with relations and individually positioned ER-models
At the end of this tutorial I'll also give you some hints on getting the most out of your phpMyAdmin config file, showing you some configuration directives which seem to get less public attention, as I've often gotten aware of.
Requirements
- phpMyAdmin >= 2.5.0 (the later, the better). If you haven't already installed this, please go to the official phpMyAdmin documentation website and read their fine documentation!
- A PHP-enabled server supporting file uploads (see phpMyAdmin FAQ 1.8 and 1.22)
- FTP/SSH access to your installation, OR an administrator willing to make the changes for you. If you use a global installation of a mass webhoster, always remember that you are free to upgrade/install your own phpMyAdmin installation in your webspace. You are not dependant on predefined installations!
- A MySQL account
- A webbrowser (I prefer Mozilla/Firebird),
an FTP-Client (like SmartFTP)
and the editor of your choice (like Komodo)
To use the transformation feature, you have to set up special phpMyAdmin tables. If you have already done so, skip this section. If you haven't, it really is quite easy and I'll describe it in graphic detail:
- Open your phpMyAdmin installation with your browser of choice
- Open the file
phpMyAdmin/scripts/create_tables.sql
- Edit this file: If you are not allowed to create new databases in your MySQL account, you have to change those lines:
DROP DATABASE IF EXISTS `phpmyadmin`;
CREATE DATABASE IF NOT EXISTS `phpmyadmin`;
USE `phpmyadmin`;
GRANT SELECT, INSERT, DELETE, UPDATE ON `phpmyadmin`.* TO
'pma'@localhost;
USE `NAME OF THE DATABASE YOU HAVE ACCESS TO`
- If your MySQL server is on a windows machine please pay attention to the naming scheme of these tables. There is a default directive lower_case_table_names in your
my.ini/my.cnf file, which is set to '1'. This leads to MySQL converting all characters to lowercase and will result in trouble with the mixed casing of phpMyAdmin. Either set the variable to 0 (if you have access to the configuration) or adjust the create_tables.sql to only use lowercase table names. Note: Windows MySQL servers are seldom used in production environments, so the phpMyAdmin default obeys standard Linux naming schemes.
- Save the file.
- On your phpMyAdmin installation, click on the link Query window to the bottom left of your screen.
If you can't see this link, please check your $cfg['QueryFrame'] directive. Clicking on the link will either open a new browser window or show a window in the right phpMyAdmin frame, depending on your configuration.
- In the new window you click on the Import files tab.
- You should now have the ability to hit a Browse... button to enter the location of a textfile which should get uploaded. Click on this button and then choose the
create_tables.sql file you edited earlier on. Then click on Go.
If you can't see this button, your installation has file uploads disabled. Either you correct that and allow file uploads (see phpMyAdmin FAQ 1.8 and 1.22), or you upload the create_tables.sql file to your $cfg['UploadDir'] location and then choose the file in a dropdown. If you can't even do that, you have to copy&paste the whole contents of the create_tables.sql file into the SQL query box and click on Go.
- The file should now have been inserted, and the new tables created. If not, check any error messages. The errors most likely to happen are related to permissions or file upload problems. You can fix them by having a look at the phpMyAdmin Documentation.
- Now, to make phpMyAdmin aware of its tables, you have to edit your
config.inc.php[3] file and find your server configuration block. This should look like:
$cfg['Servers'][$i]['pmadb'] = ''; // 'phpmyadmin' - see scripts/create_tables.sql
$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'
- Finally, save your
config.inc.php[3] file and reload your phpMyAdmin installation page. To see if everything works fine, click on any database you have and look at the bottom of the page on the right hand of the screen. If there is no error message, everything worked fine. If there is an error, click on the link nearby and you will see, what errors you got.
Note: You can only see the error message, when $cfg['PmaNoRelation_DisableWarning'] is set to FALSE (which is default)
Using MIME types
Now we'll begin with some serious action. We will set up our first MIME type in a table.
- Click on a database in the left frame, where you want a table to be created.
- On the right frame you now scroll the page down to where you can input a new table name.
- We will use the name tutorial for this table. Enter it in the field Name:. For our first try, we are going to use 3 fields. Enter 3 in the field Fields: and click on Go.
- You will then see the setup for your table. Let's enter the following values:
(
Note: Don't be suprised by the look of this page. I used
$cfg['DefaultPropDisplay'] = 'vertical' for this tutorial. This way, more items fit a page. Look at the end of this tutorial for a more detailed description of this configuration directive)
- Fields: id, picture and html.
- Types: (in the same order of fields) should be INT, LONGBLOB, TEXT.
- As none of our fields need a specified length, we leave that field empty
- Attributes: UNSIGNED for the field
id, and the other ones empty.
- Null: Set all three fields to not null
- Default: Leave all three fields empty
- Extra: auto_increment for the field
id, and the other ones empty.
- Comments: Enter ID, Binary picture data, HTML formatted text.
- Now comes the fun part: Use image/jpeg as the MIME-type for the field
picture. Then use text/plain for the field html. Leave the dropdown empty for the field id.
- Now choose the transformation image/jpeg: inline for the field
picture. Use text/plain: formatted for the field html. Again, leave the dropdown empty for the field id.
- Check the radio button for the PRIMARY value on the field
ID. It is vital to always use a primary key for a table containing BLOB transformations.
- At last, check the FULLTEXT checkbox for the field
html.
- Click on Save. You should see an SQL-Query like this on the follow-up page:
CREATE TABLE `tutorial` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`picture` LONGBLOB NOT NULL ,
`html` TEXT NOT NULL ,
PRIMARY KEY ( `id` ) ,
FULLTEXT (
`html`
)
) COMMENT = 'Tutorial';
On the table properties page, you can now see the MIME-types of the fields directly inside the Type cell of the table.
You are now ready to insert your first picture and see it presented in hydrocolor and prollby-Surround! Here's how to do it (you need file uploads enabled for this!):
- In the right frame, click in the Insert tab.
- You'll see the familar insertion page. In the insertion area, you should see the dropdown/input fields to store uploaded data.
- Let's click on the Browse... button inside the
picture field. Choose a nice file from your harddisk, reasonable sized (about 50kb for a first test).
- You can also already enter any HTML code you'd like in the
html field
- Then, click on Go and have some patience while the file is uploaded.
- After the file is uploaded, you should see a query telling you which data was inserted.
Click on the Browse tab on the page to see the data.
-
You can now see the picture you uploaded and even the HTML code inside the phpMyAdmin interface. If the file doesn't display, you may experience some oddities with the gdlib of PHP. If so, please upgrade your phpMyAdmin to the latest version, some issues have been fixed there.
Now, isn't that pretty? You can now begin to make up all your fine tables using the transformations. There are many more transformations to use, some even have options to them. Let's see how we can utilize the options:
- Open the Structure page of the
tutorial table.
- Find the Add new field: input field on the right frame, just below the table information. Enter 1 in the input field and click on Go
- We will now add a new field. Use this data:
- Field: creation.
- Type: TIMESTAMP.
- Length: empty
- Attributes: empty
- Null: not null
- Default: empty
- Extra: empty
- Comments: Date of creation.
- MIME-Type: text/plain
- Transformation: text/plain: dateformat
- Now the important part, the options. You can already see by the three ***, that on the bottom of the page we specify which format we want the options to be represented. You can also find a link to a list of all available transformations. Now would be a good time to click on the link, to see what options we need.
- Now that we have seen the transformation list, we know that the transformation text/plain: dateformat takes two options. The first is a time offset, the second a specific time string (see the PHP.net documentation).
- We now enter '0','%A, %d.%m.%Y %H:%m' as the options. This will add no offset to the date, but instead will use a different time display option for display. Ever got annoyed of those
TIMESTAMP fields in a MySQL table you couldn't even read? Now you can let the storage still be data-centric, but you can easily decode it on the phpMyAdmin backend!
- Now, leave the other fields all in their default state
- Click on Save. You should see an SQL-Query like this on the follow-up page:
ALTER TABLE `tutorial` ADD `creation` TIMESTAMP NOT NULL ;
- Now that the new field is there, it already contains some data. Let's see the Browse mode and how our field got handled.
-
Yes, not only you can now see your nifty picture and HTML code, but also a smoothly converted timestamp.
Now would be a time where you can play around with those nice transformations. Try some other ones, use some options...and then come back for the REAL stuff:
Creating your own transformations
The basic usage of the transformations is covered in the phpMyAdmin Documentation, Chapter: Transformations. Please look there for a more detailed coverage. I will exemplary tell you how to create a new transformation. For primitive types of transformations you can look at the libraries/transformations/text_plain__substr.inc.php file. Let's do something more advanced here: An PHP-applet with inlined FORM-elements on your phpMyAdmin browsing page.
There could be various places of usage for those applets, because you can basically put ANY PHP-code inside of a cell. Let's say you have an automated news processing system. You want to browse the table of subscribed users with phpMyAdmin and directly send a newsletter to a customer. Now, here's how to do that magic -- from this point on, you should have some PHP knowledge because it will make use of it's scripting capabilities:
- Create a table called customers:
CREATE TABLE `customers` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 128 ) NOT NULL ,
`email` VARCHAR( 128 ) NOT NULL ,
PRIMARY KEY ( `id` )
) COMMENT = 'Customers';
- Create a table called newsletters:
CREATE TABLE `newsletters` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 128 ) NOT NULL,
PRIMARY KEY ( `id` )
) COMMENT = 'Newsletters';
- Insert just some values to both tables to have a testing basis:
INSERT INTO `newsletters` (`name` ) VALUES
('Business News'),
('Corporate News'),
('Personal News');
INSERT INTO `customers` (`name` , `email` ) VALUES
('John Doe', 'john@doe.org'),
('No Body', 'no@body.org'),
('utilized', 'me@slashdot.org');
- Now we will create a new transformation:
- You need bash-console access to your phpMyAdmin directory to do this the easy way.:
garvin@42:~/phpMyAdmin/$
cd libraries/transformations
garvin@42:~/phpMyAdmin/libraries/transformations/$
./template_generator.sh 'text_plain__php' 'PHP-Applet'
To do later:
cd ../../lang
./add_message.sh '$strTransformation_text_plain__php' 'No Description'
Created text_plain__php.inc.php3
New TRANSFORM FUNCTION text_plain__php.inc.php3 added.
text_plain__substr.inc.php, as this is the smallest transformation.
- That was the easiest part of it. A new file has been created. Let's take a look at it:
/* $Id: TEMPLATE,v 1.2 2003/03/09 11:16:23 lem9 Exp $ */
// vim: expandtab sw=4 ts=4 sts=4:
/**
* Plugin function TEMPLATE (Garvin Hicking).
* -----------------------------------------
*
* For instructions, read the libraries/transformations/README file.
*
* The string ENTER_FILENAME_HERE shall be substituted with the filename without the '.inc.php3'
* extension. For further information regarding naming conventions see the README file.
*/
if (!defined('PMA_TRANSFORMATION_TEXT_PLAIN__PHP')){
define('PMA_TRANSFORMATION_TEXT_PLAIN__PHP', 1);
function PMA_transformation_text_plain__php($buffer, $options = array()) {
// possibly use a global transform and feed it with special options:
// include('./libraries/transformations/global.inc.php3');
// further operations on $buffer using the $options[] array.
return $buffer;
}
}
- This file is going to be our skeleton where we will add our PHP functionality to. Currently it only loops the buffer (read: the data which is really contained in your column and which is used to transform the output) through. Now let's see what needs to be done. We will use the transformation on the field which contains an email adress. This means we have one input variable which our script gets as the $buffer string. We can use this data to create a form with an input field, then return that data to be displayed inside phpMyAdmin. We also need the 'submit' functionality part to check whether a user clicked on the
SUBMIT-button our form displayed to him. This, in effect, means we have to get a grip of the phpMyAdmin variables to generate all those hidden input fields to get to the same page we left from, when a user clicks on submit. Sounds scary? It ain't. :)
- Use this piece of fine code, expandable at your own will, and replace the function-code of your
text_plain__php.inc.php file:
// New 2003-11-03
if (!defined('PMA_TRANSFORMATION_TEXT_PLAIN__PHP')){
define('PMA_TRANSFORMATION_TEXT_PLAIN__PHP', 1);
function PMA_transformation_text_plain__php($buffer, $options = array()) {
$html = '';
// THIS ONE HERE IS INTERESTING!
// We feed the buffer to the input field to detect
// whether a user clicked on exactly this item.
$html .= '<input type="hidden" name="php_applet[' . urlencode($buffer) . ']"';
$html .= ' value="' . urlencode($buffer) . '" />';
$html .= 'Send some newsletter to ' . $buffer . '<br />';
// Let's take advantage of the phpMyAdmin connection already established.
$sql = 'SELECT id, name FROM newsletters ORDER BY name';
$result = @PMA_mysql_query($sql);
echo PMA_mysql_error();
// We don't need no education...we don't need no error control...
$html .= '<select name="php_applet_select">' . "\n";
while ($row = PMA_mysql_fetch_array($result)) {
// CHECK HERE if we have submitted the form and want to mail that newsletter now
if (isset($HTTP_POST_VARS['php_applet'])
&& isset($HTTP_POST_VARS['php_applet'][urlencode($buffer)])
&& $HTTP_POST_VARS['php_applet'][urlencode($buffer)]
== urlencode($buffer)) {
// direct hit. We found the mole.
$override = 'Newsletter #' . $HTTP_POST_VARS['php_applet_select'] . ','
. ' Text: ' . $HTTP_POST_VARS['php_applet_text'] . ' sent to '
. $buffer . '.<br />';
}
$html .= '<option value="' . $row['id'] . '">'
. htmlentities($row['name'])
. '</option>' . "\n";
}
$html .= '</select><br />' . "\n";
$html .= '<label for="php_applet_text">Additional text: </label>';
$html .= '<input type="text" id="php_applet_text" '
. ' name="php_applet_text" value="additional text?" />';
$html .= '<br />' . "\n";
$html .= '<input type="submit" name="navig" value="make my day" />' . "\n";
return (isset($override) ? $override : $html);
}
}
- Now save that file and experiment with it. Think about how you can use it for advanced customers who may have gotten used to navigate through their data using phpMyAdmin. No need to create a seperate backend for them, and no need to fiddle with much of phpMyAdmin's code. Just create transformations for them and place them in the columns they are using.
Executing PHP-code stored in a column
Using the example above, it could also come to your mind to execute arbitrary PHP code. Even though this is a possibly malicious and dangerous method to do, you could just add a transform function like the one above and replace the line return $buffer; with return eval($buffer)..... yes. It's that easy. :-)
Think of other transformations you could use. How about storing encrypted data in your MySQL data and using a PHP-applet to decode that info? So only the ones using phpMyAdmin and your access codes can successfully view your sensible data. If anybody manages to steal your MySQL accounts and/or data, he may not yet have access to your phpMyAdmin installation, rendering the stolen data useless!
Creating relations
In this chapter, we will setup a table layout and create relating columns. This will not only make insertion of data easier in phpMyAdmin, but also prepares the way for the PDF features. Let's begin with constructing an example.
Say we have a company who sells used cars. Our company buys some cars, and sells some cars and has a solid customer base. We also keep some spare parts for our cars to get repaired. So we will set up the following tables:
- Customers:
- Cars
- Companies
- Source of supply
- cars_id: Cars»id
- companies_id: Companies»id
- Sales
- cars_id: Cars»id
- customers_id: Customers»id
In the scheme above you can already see which tables relate to each other. Thinking about that before actually creating the tables is always a good idea. Having done so, we use phpMyAdmin to click together the following tables. Either do so like we did above with the tutorial table, or just copy&paste the following SQL code into the SQL tab or inside the Query window
CREATE TABLE `customers` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 255 ) NOT NULL ,
`address` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) COMMENT = 'Customers';
CREATE TABLE `cars` (
`id` int(10) unsigned NOT NULL auto_increment,
`manufacturer` varchar(255) NOT NULL default '',
`model` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) COMMENT='Cars';
CREATE TABLE `companies` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 255 ) NOT NULL ,
`address` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) COMMENT = 'Companies';
CREATE TABLE `source_of_supply` (
`cars_id` INT UNSIGNED NOT NULL ,
`companies_id` INT UNSIGNED NOT NULL ,
INDEX ( `cars_id` ),
INDEX (`companies_id` )
) COMMENT = 'Source of Supply';
CREATE TABLE `sales` (
`cars_id` INT UNSIGNED NOT NULL ,
`customers_id` INT UNSIGNED NOT NULL ,
INDEX ( `cars_id` ),
INDEX ( `customers_id` )
) COMMENT = 'Sales';
Of course we also want some data in it. Either use the Insert tab in the right frame on the tables
or you use the following SQL code:
INSERT INTO
`cars` (`manufacturer` , `model` )
VALUES
('Corvette', 'Stingray'),
('Ford', 'Probe GT'),
('Porsche', 'Boxster'),
('Fiat', 'Punto Cabrio'),
('Opel', 'Kadett E'),
('Mercedes', 'SL500'),
('VW', 'Golf 4'),
('Pontiac', 'Firebird'),
('Lamborghini', 'Diablo');
INSERT INTO
`customers` (`name`, `address`)
VALUES
('Garvin Hicking', 'Germany'),
('Emba', 'Germany'),
('Andreas Bertram', 'Germany'),
('Marc Delisle', 'France'),
('Sebastian Bergmann', 'Siegburg, Germany');
INSERT INTO
`companies` (`name`, `address`)
VALUES
('Used Cars Sell-a-lot', 'USofA'),
('Ford Berger', 'Remagen, Germany'),
('Opelhaus', 'Germany'),
('Mobile.de', 'Germany');
Pay attention that I did not fill any values for neither the source_of_supply nor the sales tables. That's why we are using relations for, to ease that up.
- And now just let's setup the relations. In the left frame, click on our first table
customers.
- In the right frame click on Relation view. If you don't have such a link, your special phpMyAdmin tables (as explained in the Requirements section) don't exist
- In the new window, just open the dropdown below the title Choose Field to display.
Whenever we later refer to this table (like when inserting values), we can define a field here which shows us the information we need. When we link there, we can know nothing of the table by just refering to the ID (like the
RDBMS does), instead we refer to it by name. And that's the field we want to define as display field: name. You usually use the field as a display field which holds the most valuable and unique data for you, human.
- Having selected the field, phpMyAdmin auto-updates the form and saves our entry.
- What we do now is to define display fields for all the remaining tables just the same way. These are the fields we choose to display:
Cars: modelCompanies: nameSource of supply: noneSales: none
You may wonder why we didn't define any display field on the last two tables. This is because they are never refered to directly. We just use it as a helper table which does our internal relationing between two tables. This is called an n:m relation. So these two tables are later the part where you would do your daily work in: You usually have your customers ready and then just relate a bought car to a customer or company but never actually but distinct content-based data in those two tables.
Now that we have set up the display fields, we can do the next job. We have to define the relations between each table, because at the moment neither MySQL nor phpMyAdmin does know anything on where to map the new display fields to. Of course, that will be changed:
- Again, choose the first table
customers and there click on Relation view.
- This time, we're not going to alter anything in the display field area, but instead use the dropdowns below the field Links to.
- We could put a link for any field which exists in our table. The targets are always the primary keys of all other tables in the same database.
- Remember what I said about which were the only tables we need to define relations in? Right, only the tables
source_of_supply and sales. So what are we doing with this table here? Right again. Nothing.
- So let's briefly check over to the Relation View of the right table, which is
source_of_supply. Open the first dropdown called car_id below the field Links to and choose the entry labeled cars->id.
- For the next field
company_id select the dropdown field companies->id.
- Then click on Go and save the relation. Congratulations, your first relation is now ready to use. But before checking that out, let us relate the last table as well
- Select the table
sales and then enter the Relation view the same way you did earlier. For the two dropdowns you relate the following:
cars_id: cars->idcustomers_id: customers->id
Click on Go. And now you're ready to see what your actions created.
- To see what the relations are good for, enter the Insert tab of the table
source_of_supply, because we want to tell our system which cars we get from which company. You now see this screen:
So, you lost the ability to directly enter any values. Instead you have the dropdown list of available entries in the corresponding tables. You can now click together some relations you want and insert them. Just choose any item off both dropdowns and click on Go. Do so a several times to make a few entries.
- Now you can Browse your tables and see how that is done:
You now see the values you inserted with an active hyperlink inside the column data. The title of the hyperlink describes the linked value from the related table (mouse-over on it!). And by clicking on that link you are directly taken to the related table to browse the data matching your value! So, by clicking on that 7 link, we are taken to the details of car #7:
Now that you have successfully inserted related values for the table
source_of_supply, you can do so equivalently on the table sales, don't you? You are now done to use an easy method of inserting new values. You no longer have to upen up many table simultaneously or memorize IDs.
Using relations for PDF display
In this chapter you will learn to set up an PDF page containing all that valuable tables we created for our test-database. We will use the already created and related tables for that. The first thing is to create a new PDF page. For that, click on your database and find the link in the right frame titled Edit PDF pages
On this next page you can give your new PDF name a page title. We will call it Used Cars Sell-A-Lot Inc. Database scheme. To make your life easier we will create an automatic layout of your elements at first. So check the box called Automatic layout as well.
After having clicked on Go you will see a page looking like this:
Now there are really a lot of options, so I'll describe them in detail:
- The first section on the upper hand of the screen shows you a list of available PDF pages. The one we just created is highlighted. When you later click on the link Edit PDF pages on a database property page, you will always first get to the selection of which page to edit. Choose one from the dropdown and then select the action on the right side. If you want to edit a page, which you usually do, you highlight that radio button and click on Go. As our PDF-page is already selected, you don't have to do that right now.
- Below this dropdown you again have the possibility to create another page, just like we did earlier.
- Now here's the interesting part where you can put your tables in the PDF page together. You see a table with a number of dropdowns with your table names in them. You can always add and delete table names, and change the order of them. To do so, you could just select a different table of a dropdown, maybe check the Delete-button of another entry and then click on Go. But because we want all our precious tables displayed, we won't do that now. It would only be handy if you had many more tables which wouldn't fit on a single PDF-page. For more tables to display nicely, you would create more PDF pages in the upper section and then only choose a limited amount of tables per page, and put the other tables on other pages.
- Instead, let's pay attention to those X and Y settings. They represent the layout order of our tables on the PDF page. Their values reflect coordinates, having the point of origin at the upper left side of a page. Their maximum value is dependant on the size of the page. The page size we are now using is A4, resulting in a coordinate size of 595.28 x 841.89 pixels.
- You could now begin to take a sheet of paper, draw a coordinate system in it and try to layout your tables so they relate nicely to each other and then copy the values to the X/Y fields. phpMyAdmin does some nice standard distribution in a circle by default, but let's face it: You are individual and want your own layout. So, as of phpMyAdmin 2.5.1, you can do that using a nifty WYSIWYG DHTML control. To enable it, click on the Toggle scratchboard button just above the coordinate table.
- As you already can see, this control reflects the layout of all elements, the automatic page layout created for us. You can take some time and drag the items around by clicking on them. Now after you are satisfied with the order, let's take a look at our PDF creation options. Make sure to click on Go after you changed the coordinates. If you don't, your changes won't be applied to the PDF!
- Below the title Display PDF schema you see multiple checkboxes:
- Show grid
This makes gridlines visible in the PDF layout
- Show color
Creates individual colors for your tables in the PDF layout
- Show dimension of tables
Includes the pixel sizes of your tables in the PDF layout
- display all Tables with same width?
This makes all tables equally sized. If you have huge differences in the length of the table names, it's better to not check this option
- Data Dictionary
This includes detailed options about your tables in the PDF, as field names and types and so on.
- Data Dictionary Format
With this option you can control whether to horizontally or vertically align the pages. Changing this may be necessary if the length of your field names is above normal.
- Paper size
You can tune the size of your PDF with this option. Changing the paper size will also result in an alternation of the scratchboard above, to reflect the changes in dimensions.
- Now we will just let all settings by default: Check Show color, Data Dictionary and Paper size of A4
- Click on Go, and your browser will offer you to download or view the PDF page. You need Adobe Acrobat Reader or something like that to render the page.
- Now you should look at a pretty PDF with a data dictionary and on the last page, a great ER-scheme. Depending on how you ordered your layout, it will look something like that:
So you can see easily, which fields of which table are related to other tables. Candy!
- If you want to look at the PDF any time later, you can select a page to view (including the options) directly at the database property page:
Of course, any changes made to relations, or the addition of fields will get automatically applied to the PDF facility. Only if you add some tables, you have to select them in the dropdown in the coordinate editor and add them to the PDF as well.
Some configuration directives in detail.
As promised, here is my top list of configuration directives often left unattended:
- $cfg['Servers'][$i]['verbose_check']
This is set to TRUE by default. If you know you have used the right create_tables.sql file to create your phpMyAdmin relational setup, set this to FALSE. It will take a feasible performance gain.
- $cfg['LeftFrameLight']
Setting this to FALSE shows a very nice DHTML menu, but with some overhead for many databases. A not commonly known feature of thise mode to FALSE is that you can have nested sublevels of many tables. Say you have a structure like phpbb_*, serendipity_*, voting_* you normally see them all under each other. But if you utilize the $cfg['LeftFrameTableSeparator'] setting and set this to _ you will immediately see, that you now only have three main levels on your page: phpbb, serendipity and voting. The all allow explorer-style folding, when clicked on. And guess what, this is stackable to an infinite level. Look at $cfg['LeftFrameTableLevel'] for fine-tuning as well!
- $cfg['ShowAll']
A very useful feature for those having larger table and want to see all entries at once
- $cfg['TextareaAutoSelect']
Getting tired of those auto-selected textarea fields? Turn this to FALSE and you regain selecting control. :-)
- $cfg['DefaultPropDisplay']
When creating new columns, did you ever get annoyed about scrolling horizontally that much? Set this option to vertical and the whole table gets flipped, offering more space for more input fields.
- $cfg['QueryHistoryDB']
Ever wanted to keep an all-time history of your SQL queries entered manually? The set this to TRUE (if you have the additional phpMyAdmin tables activated) and make it so!
- $cfg['UploadDir']
Because this is empty by default, many may have missed the great flexibility this directive offers. Set it to a directory readable by the webserver and you can execute/upload pre-stored files. Enough of that nasty 1MB form upload stuff, just upload it via FTP and the timeout issues will vanish
- $cfg['SaveDir']
As powerful as the UploadDir-directive. This allows you to store dumps directly on the server, without having to immediately download it to your client.
Acknowledgements
This tutorial was done by Garvin Hicking in July 2003. I am a german webdeveloper near Cologne, Germany and a phpMyAdmin-Developer.
My thanks and appreciations go to the whole phpMyAdmin team. Thanks for all the effort and mostly for the whole fun I have by letting me be part of this project.
Please feel free to link to this tutorial wherever you want. And drop me a note, I always appreciate any feedback you have on this. Please do also understand that it is not allowed to reproduce this text to any kind of media without my permission. All copyrights and copylefts are (C)2003 by Garvin Hicking.
Print version
