Couldn't find a good answer to my question anywhere. 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 (which I'm not quite sure what that even is -- maybe my question is How the heck do you use shell?).
My silly idea (aren't they all?) 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. Please help.
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.
