As an HTML5 and CSS3 programmer, it’s almost a guarantee that you can build a wonderful SQL data structure. And because it’s wonderful, you probably will want to export it for a number of reasons:
You want a backup. Just in case something goes wrong!
You want to move to a production server. It’s smart to work on a local (offline) server while you figure things out, but eventually you’ll need to move to a live server. Moving the actual database files is tricky, but you can easily move a script.
You want to perform data analysis. You may want to put your data in a spreadsheet for further analysis or in a comma-separated text file to be read by programs without SQL access.
You want to document the table structure. The structure of a data set is extremely important when you start writing programs using that structure. Having the table structure available in a word-processing or PDF format can be very useful.
MySQL (and thus phpMyAdmin) has some really nice tools for exporting your data in a number of formats.
The different styles of output are used for different purposes:
CSV (comma-separated value) format: A plain ASCII comma-separated format. Each record is stored on its own line, and each field is separated by a comma. CSV is nice because it’s universal. Most spreadsheet programs can read CSV data natively, and it’s very easy to write a program to read CSV data, even if your server doesn’t support MySQL.
If you want to back up your data to move to another server, CSV is a good choice.
The data file created using the specified options looks like the following:
"contactID","name","company","email" "1","Bill Gates","Microsoft","[email protected]" "2","Steve Jobs","Apple","[email protected]" "3","Linus Torvalds","Linux Foundation","[email protected]" "4","Andy Harris","Wiley Press","[email protected]"
The CSV format often uses commas and quotes, so if these characters appear in your data, you may encounter problems. Be sure to test your data and use some of the other delimiters if you have problems.
MS Excel and Open Document Spreadsheet: These are the two currently supported spreadsheet formats. Exporting your data using one of these formats gives you a spreadsheet file that you can easily manipulate, which is handy when you want to do charts or data analysis based on your data.
Word-processing formats: Several formats are available to create documentation for your project. Typically, you use these formats to describe the format of the data and the current contents. LaTeX and PDF are special formats used for printing.
How to export SQL code
One of the neatest tricks is to have phpMyAdmin build an entire SQL script for re-creating your database.
The resulting code is as follows:
-- phpMyAdmin SQL Dump -- version 3.3.9 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Jul 10, 2013 at 08:30 PM -- Server version: 5.5.8 -- PHP Version: 5.3.5 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Database: 'haio' -- -- -------------------------------------------------------- -- -- Table structure for table 'contact' -- DROP TABLE IF EXISTS contact; CREATE TABLE IF NOT EXISTS contact ( contactID int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, company varchar(30) DEFAULT NULL, email varchar(50) DEFAULT NULL, PRIMARY KEY (contactID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table 'contact' -- INSERT INTO contact (contactID, `name`, company, email) VALUES (1, 'Bill Gates', 'Microsoft', '[email protected]'), (2, 'Steve Jobs', 'Apple', '[email protected]'), (3, 'Linus Torvalds', 'Linux Foundation', '[email protected]'), (4, 'Andy Harris', 'Wiley Press', '[email protected]');
You can see that phpMyAdmin made a pretty decent script that you can use to re-create this database. You can easily use this script to rebuild the database if it gets corrupted or to copy the data structure to a different implementation of MySQL.
Generally, you use this feature for both purposes. Copy your data structure and data every once in a while (just in case Godzilla attacks your server or something).
Typically, you build your data on one server and want to migrate it to another server. The easiest way to do so is by building the database on one server. You can then export the script for building the SQL file and load it into the second server.
How to create XML data
One more approach to saving data is through XML. phpMyAdmin creates a standard form of XML encapsulating the data. The XML output looks like this:
<<?xml version="1.0" encoding="utf-8"?>
<!--
- phpMyAdmin XML Dump
- version 3.3.9
- http://www.phpmyadmin.net
-
- Host: localhost
- Generation Time: Jul 10, 2013 at 08:32 PM
- Server version: 5.5.8
- PHP Version: 5.3.5
→
<pma_xml_export version="1.0" <br/>xmlns:pma="http://www.phpmyadmin.net/some_doc_url/">
<!--
- Structure schemas
→
<pma:structure_schemas>
<pma:database name="haio" collation="latin1_swedish_ci" charset="latin1">
<pma:table name="contact">
CREATE TABLE `contact` (
`contactID` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`company` varchar(30) DEFAULT NULL,
`email` varchar(50) DEFAULT NULL,
PRIMARY KEY (`contactID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
</pma:table>
</pma:database>
</pma:structure_schemas>
<!--
- Database: 'haio'
→
<database name="haio">
<!-- Table contact →
<table name="contact">
<column name="contactID">1</column>
<column name="name">Bill Gates</column>
<column name="company">Microsoft</column>
<column name="email">[email protected]</column>
</table>
<table name="contact">
<column name="contactID">2</column>
<column name="name">Steve Jobs</column>
<column name="company">Apple</column>
<column name="email">[email protected]</column>
</table>
<table name="contact">
<column name="contactID">3</column>
<column name="name">Linus Torvalds</column>
<column name="company">Linux Foundation</column>
<column name="email">[email protected]</column>
</table>
<table name="contact">
<column name="contactID">4</column>
<column name="name">Andy Harris</column>
<column name="company">Wiley Press</column>
<column name="email">[email protected]</column>
</table>
</database>
</pma_xml_export>
XML is commonly used as a common data language, especially in AJAX applications.