Export structure and data (like in PhpMyAdmin)

Question

I want to export data and structure from MySQL database using PHP. I know about SELECT INTO OUTFILE command but I want to have the file like the one which is generated by PhpMyAdmin in Export window, so all the CREATE TABLE and INSERT INTO.

HDo you know how PhpMyAdmn generates those files? I was browsing through the code and I've found the command SELECT INTO OUTFILE in it. But I'm not sure is this command is used to generate that structure. Is there any other command to do this, or the exported file is created manualy using information about tables schema?

Answer 1

You can do this using SHOW CREATE TABLE.

Answer 2

I have made a php script to backup mysql tables (only data). this will export the mysql data to a sql file which is fully compatible with phpmyadmin import

may be this could help you

mysql data backup script in php: http://dl.dropbox.com/u/18434517/mysql_backup.php

Answer 3

Here you can find a comprehensive solution to dump mysql structure and data like in PMA (and without using exec, passthru etc.):

https://github.com/antarasi/MySQL-Dump-with-Foreign-keys

It is fork of dszymczuk project with my enhancements.

The usage is simple

<?php
//MySQL connection parameters
$dbhost = 'localhost';
$dbuser = 'dbuser';
$dbpsw = 'pass';
$dbname = 'dbname';

//Connects to mysql server
$connessione = @mysql_connect($dbhost,$dbuser,$dbpsw);

//Set encoding
mysql_query("SET CHARSET utf8");
mysql_query("SET NAMES 'utf8' COLLATE 'utf8_general_ci'");

//Includes class
require_once('FKMySQLDump.php');


//Creates a new instance of FKMySQLDump: it exports without compress and base-16 file
$dumper = new FKMySQLDump($dbname,'fk_dump.sql',false,false);

$params = array(
    //'skip_structure' => TRUE,
    //'skip_data' => TRUE,
);

//Make dump
$dumper->doFKDump($params);

?>

works like a charm :-)