This subpanel contains a table selector, from where we choose the tables and the format that we want. The SQL format is useful for our needs, as it creates standard SQL commands that would work on any SQL server. Other formats are available, depending on our needs.

We shall now discuss the formats (and the options available once they have been chosen) that can be selected in the Export subpanel.

We will start by clicking on Select All; we want all of the tables. We know that the tables are small, so the on-screen export will not be too large. For the moment, let's deselect the Extended inserts checkbox. To produce an export file, we would normally leave the Save as file checkbox selected, but to see the results on screen, we deselect it; we then click on Go.

The first part of the export comprises comments (starting with the characters --) that detail the utility (and version) that created the file, the date, and other environment information. We then see the CREATE and INSERT queries for each table.

Defining options for SQL export

SQL options are used to define exactly what information the export will contain. We may want to see the structure, the data, or both. Selecting Structure generates the section with CREATE queries, and selecting Data produces INSERT queries. The following screenshot depicts the general SQL options and the Structure options:

The general SQL options are:

• Add custom comment into header: We can add our own comments for this export (for example, Monthly backup), which will appear in the export headers (after the PHP version number). If the comment has more than one line, we must use the special character \n to separate each line.
• Comments: Removing the check mark here causes the export to have no comments at all.
• Enclose export in a transaction: Starting with MySQL 4.0.11, we can use the START TRANSACTION statement. This command, combined with SET AUTOCOMMIT=0 at the beginning and COMMIT at the end, asks MySQL to execute the import (when we will re-import this file) in one transaction, ensuring that all the changes are done as a whole.
• Disable foreign key checks: In the export file, we can add DROP TABLE statements. However, normally a table cannot be dropped if it's referenced in a foreign key constraint. This option overrides the verification by adding SET FOREIGN_KEY_CHECKS=0 to the export file. This override only lasts for the duration of the export.
• SQL compatibility mode: This lets us choose the flavor of SQL that we export. We must have some knowledge about the system into which we intend to import this file. Among the choices are MySQL 3.23, MySQL 4.0, Oracle, and ANSI.

The options in the Structure section are:

• Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT: Adds a DROP ... IF EXISTS statement before each CREATE statement, for example: DROP TABLE IF EXISTS `author`;. This way, we can ensure that the export file is executed on a database in which the same element already exists, updating its structure but destroying the previous elements' contents.
• Add IF NOT EXISTS: Adds the IF NOT EXISTS modifier to CREATE TABLE statements, avoiding an error during import if the table already exists.
• Add AUTO_INCREMENT value: Puts auto-increment information from the tables into the export, ensuring that the inserted rows in the tables will receive the next exact auto-increment ID value.
• Enclose table and field names with backquotes: In the MySQL world, backquotes are the normal way of protecting table and field names that may contain special characters. In most cases, it's useful to have them. However, backquotes are not recommended if the target server (where the export file will be imported) is running a SQL engine that does not support backquotes.
• Add CREATE PROCEDURE / FUNCTION / EVENT: This includes all procedures, functions, and event definitions found in this database, in the export.
• Add into comments: This adds information (in the form of SQL comments), which cannot be directly imported, but which nonetheless is valuable and human-readable table information. The amount of information here varies depending on the relational system settings (see Chapter 10, Benefiting from the Relational System). In fact, with an activated relational system, we would get the following choices:

Selecting Relations and MIME type would produce an additional section in the structure export:

--
-- COMMENTS FOR TABLE `book`:
-- `isbn`
-- 'book number'
-- `page_count`
-- 'approximate'
-- `author_id`
-- 'see author table'
--
--
-- MIME TYPES FOR TABLE `book`:
-- `cover_photo`
-- 'image_jpeg'
-- `date_released`
-- 'text_plain`
-- `description'
-- 'text_plain'
--
-- RELATIONS FOR TABLE `book`:
-- `author_id`
-- 'author' -> 'id' 
--

The following screenshot displays options relevant to a Data export:

The options available in the Data section are:

• Complete inserts: Generates the following export for the author table:

  INSERT INTO `author` (`id`, `name`, `phone`) VALUES (1, 'John Smith', '+01 445 789-1234');
  INSERT INTO `author` (`id`, `name`, `phone`) VALUES (2, 'Maria Sunshine', '+01 455 444-5683');
  

  Notice that every column name is present in every statement. The resulting file is bigger, but will prove more portable on various SQL systems, with the added benefit of being better documented.

• Extended inserts: Packs the whole table data into a single INSERT statement:

  INSERT INTO `author` VALUES (1, 'John Smith','+01 445 789-1234'), (2, 'Maria Sunshine', '+01 455 444-5683');
  

  This method of inserting data is faster than using multiple INSERT statements, but is less convenient because it makes reading the resultant file harder. Extended inserts also produces a smaller file, but each line of this file is not executable in itself because each line does not have an INSERT statement. If you cannot import the complete file in one operation, you cannot split the file with a text editor and import it chunk by chunk.

• Maximal length of created query: The single INSERT statement generated for Extended inserts might become too big and could cause problems. Hence, we set a limit on the number of characters for the length of this statement.
• Use delayed inserts: Adds the DELAYED modifier to INSERT statements. This accelerates the INSERT operation because it's queued to the server, which will execute it when the table is not in use. This is a MySQL non-standard extension, available only for MyISAM, MEMORY, and ARCHIVE tables.
• Use ignore inserts: Normally, at import time, we cannot insert duplicate values for unique keys, as this would abort the insert operation. This option adds the IGNORE modifier to INSERT and UPDATE statements, thus skipping the rows that generate duplicate key errors.
• Use hexadecimal for BLOB: This option makes phpMyAdmin encode the contents of BLOB fields in 0x format. Such a format is useful because, depending on the software that will be used to manipulate the export file (for example, a text editor or mail program), handling a file containing 8-bit data can be problematic. However, using this option will produce an export of the BLOB that can be twice the size of a file generated without this option.
• Export type: The choices are INSERT, UPDATE, and REPLACE. The most well-known of these types is the default INSERT—using INSERT statements to import back our data. At import time, however, we could be in a situation where a table already exists and contains valuable data, and we just want to update the fields that are in the current table we are exporting. UPDATE generates statements such as UPDATE `author` SET `id` = 1, `name` = 'John Smith', `phone` = '111 1111' WHERE `id` = '1'; updating a row when the same primary or unique key is found. The third possibility, REPLACE, produces statements such as REPLACE INTO `author` VALUES (1, 'John Smith', '111-1111');. These act like an INSERT statement for new rows and update existing rows based on primary or unique keys.

In the previous examples, the results of the export operation were displayed on screen and, of course, no compression was made on the data. We can choose to transmit the export file via HTTP by checking the Save as file checkbox. This triggers a Save dialog in the browser, which ultimately saves the file on our local workstation.

$cfg['Export']['file_template_table'] = '__TABLE__';
$cfg['Export']['file_template_database'] = '__DB__';
$cfg['Export']['file_template_server'] = '__SERVER__';

$cfg['ZipDump'] = TRUE;
$cfg['GZipDump'] = TRUE;
$cfg['BZipDump'] = TRUE;

Choosing a character set

This section explains a little-known feature—the possibility of choosing the exact character set for our exported file.

This feature is activated by setting $cfg['AllowAnywhereRecoding'] to TRUE. We can see here the effect on the interface:

When this parameter is switched on, phpMyAdmin verifies that the conditions for recoding are met. For the actual encoding of data, the PHP component of the web server must support the iconv or the recode module. If this is not the case and the parameter has been set to TRUE, the following error message will be generated:

Couldn't load the iconv or recode extension needed for charset conversion. Either configure PHP to enable these extensions or disable charset conversion in phpMyAdmin.

If this message is displayed, consult your system's documentation (PHP or the operating system) for the installation procedure.

Another parameter ($cfg['RecodingEngine']) specifies the actual recoding engine—the choices being auto, iconv, and recode. If it's set to auto, phpMyAdmin will first try the iconv module and then the recode module.

Kanji support

If phpMyAdmin detects the use of the Japanese language, it checks whether PHP supports the mb_convert_encoding() multibyte strings function. If it does, additional radio buttons—export, import, and query box—are displayed on the following pages, so that we can choose between the EUC-JP and SJIS Japanese encodings.

Here is an example taken from the Export page:

The comma-separated value (CSV) format is understood by a lot of programs, and you may find it useful for exchanging data. Note that it's a data-only format—there is no SQL structure here:

The available options are:

Finally, we select the author table.

The result is:

"id","name","phone"
"1","John Smith","+01 445 789-1234"
"2","Maria Sunshine","+01 455 444-5683"

This export mode produces a CSV file specially formatted for Microsoft Excel. We can select the exact Microsoft Excel edition:

It's possible to create a PDF report of a table by exporting in PDF. This feature works on only one table at a time, and we must click on the Save as file checkbox for normal operation. We can add a title for this report, and it also gets automatically paginated. Non-textual (BLOB) data, as found in the book table, is discarded from this export format.

Here, we test it on the author table:

PDF is interesting because of its inherent vectorial nature—the results can be zoomed. Let's have a look at the generated report, as seen in Adobe Reader:

This export format directly produces a .doc file suitable for all software that understands the Word 2000 format. We find options similar to those in the Microsoft Excel export, and a few more. We can independently export the table's Structure and Data.

Note that, for this format and the Excel format, we can choose many tables for one export. However, unpleasant results happen if one of these tables has non-textual data. Here are the results for the author table:

LaTeX is a typesetting language. phpMyAdmin can generate a .tex file that represents the table's structure and/or data in a sideways tabular format.

The available options are:

The XML format is very popular these days for data exchange. Choosing XML in the Export interface yields no choice for options. What follows is the output of the author table:

<?xml version="1.0" encoding="utf-8" ?>
<!--
-
- phpMyAdmin XML Dump
- version 3.3.2
- http://www.phpmyadmin.net
-
- Host: localhost
- Generation Time: May 16, 2010 at 09:30 AM
- Server version: 5.1.45
- PHP Version: 5.3.2
-->
<!--
- Database: 'marc_book'
-->
<marc_book>
<!-- Table author -->
<author>
<id>1</id>
<name>John Smith</name>
<phone>+01 445-789-1234</phone>
</author>
<author>
<id>2</id>
<name>Maria Sunshine</name>
<phone>333-3333</phone>
</author>
</marc_book>

The open document spreadsheet format is a subset of the open document (http://en.wikipedia.org/wiki/OpenDocument), which was made popular with the OpenOffice.org office suite. We need to choose only one table to be exported in order to have a coherent spreadsheet. Here is our author table, exported into a file named author.ods, and subsequently looked at from OpenOffice:

Open document text is another subset of the open document standard, this time oriented towards text processing. The available options are as shown in the following screenshot:

Our author table is now exported and appears as follows when viewed in OpenOffice:

YAML stands for YAML Ain't Markup Language. YAML is a human-readable data serialization format; its official site is http://www.yaml.org. This format has no option that we can choose from within phpMyAdmin. Here is the YAML export for the author table:

1:
id: 1
name: John Smith
phone: +01 445-789-1234
2:
id: 2
name: Maria Sunshine
phone: 333-3333

The CodeGen option might some day support many formats related to code development. Currently, it can export in NHibernate Object-relation mapping (ORM) format. For more details, please refer to http://en.wikipedia.org/wiki/Nhibernate.

Texy! is a formatting tool (http://texy.info/en/) with its own simplified syntax. Here is an example of export in Texy! format:

===Database marc_book
== Table structure for table author
|------
|Field|Type|Null|Default
|------
|//**id**//|int(11)|Yes|NULL
|name|varchar(30)|Yes|NULL
|phone|varchar(30)|Yes|NULL
== Dumping data for table author
|1|John Smith|+01 445 789-1234
|2|Maria Sunshine|333-3333

In PHP, associative arrays can hold text data; therefore, a PHP array export format is available. It produces a file containing, for example:

<?php
// marc_book.author
$author = array(
array('id'=>1,'name'=>'John Smith','phone'=>'+1 445 789-1234'),
array('id'=>2,'name'=>'Maria Sunshine','phone'=>'333-3333')
);

Thanks to the PHPExcel library (http://www.codeplex.com/PHPExcel), which is included in phpMyAdmin, it's possible to produce a native Excel 97-2003 or Excel 2007 export file. For this to work, we need a work directory under the main phpMyAdmin directory. This work directory can be created on a Linux system with mkdir tmp ; chmod o+rwx tmp. We also need to set the $cfg['TempDir'] parameter in config.inc.php to'./tmp'.

This export format directly produces a .xls or .xlsx file suitable for all software that understands these formats. We can specify which string should replace a NULL value. The Put fields names in the first row option, when activated, generates the table's column names as the first line of the spreadsheet. Again, the Save as file checkbox should be checked. This produces a file where each table's column becomes a spreadsheet column:

MediaWiki (http://www.mediawiki.org/wiki/MediaWiki) is a popular wiki package which supports the ubiquitous Wikipedia. This wiki software implements a formatting language in which it's possible to describe data in tabular format. Choosing this export format in phpMyAdmin produces a file that can be pasted in a wiki page that we are editing.