[FIXED] How does the phpMyAdmin export feature work?

Issue

If I were to want to create a PHP function that does the same thing as the Export tab in phpMyAdmin, how could I do it? I don't know if there is a MySQL function that does this or if phpMyAdmin just builds the export file (in SQL that is) manually. Without shell access. Just using PHP.

I tried the documentation for mysqldump, but that seemed to require using the shell. I'm not quite sure what that even is -- maybe my question is: how do you use shell?

My silly idea is to allow non-technical users to build a site on one server (say a localhost) using MySQL then export the site, database and all, to another server (eg. a remote server).

I think I'm pretty clear on the Import process.

Solution

You can check the phpMyAdmin source code (an advantage of open-source software). Check the export.php script and the supporting functions in the libraries/export/sql.php script file.

In summary, what phpMyAdmin does is:

• get a list of the tables in the given database (SHOW TABLES FROM...),
• get the create query for each table (SHOW CREATE TABLE...),
• parse it and extract column definitions from it,
• get all data (SELECT * FROM...)
• build a query according to column data.

I've written similar code for my own apps (for backup purposes, when the GPL license of phpMyAdmin doesn't allow me to use it), however I use DESCRIBE to get column definitions. I think they rather parse the SHOW CREATE TABLE output because contains more information than DESCRIBE output.

This way to generate SQL sentences requires a bit of care to handle the escaping but it allows for some flexibility, as you can convert types, filter or sanitize data, etc. It is also a lot slower than using a tool like mysqldump and you should take care of not consuming all available memory (write soon, write often, don't keep everything in memory).

If you will implement a migration process (from server to server) maybe it would be easier to do it with some shell scripting and calling mysqldump directly, unless you will do everything with PHP.

Answered By - MV.