Export mysql database tables to php code to create same tables in other database?

Question

How do I Export mysql database tables to php code so that it allows me to create and populate same tables in other database?

I have a local database, I exported to sql syntax, then I get something like:

CREATE TABLE `boletinSuscritos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  `email` varchar(120) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

INSERT INTO `boletinSuscritos` VALUES(1, 'walter', 'waltermazzola@hotmail.com', '2010-03-24 12:53:12');
INSERT INTO `boletinSuscritos` VALUES(2, 'Paco', 'paco@arroba.com', '2010-03-24 12:56:56');

---

but I need it to be: (Is there any way to export the tables in this way)

$sql = "CREATE TABLE  boletinSuscritos  (
   id  int(11) NOT NULL AUTO_INCREMENT,
   name  varchar(120) NOT NULL,
   email  varchar(120) NOT NULL,
   date  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ( id )
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 )";

mysql_query($sql,$conexion);

 mysql_query("INSERT INTO boletinSuscritos  VALUES(1, 'walter', 'pepe@hotmail.com', '2010-03-24 12:53:12')");
 mysql_query("INSERT INTO boletinSuscritos  VALUES(2, 'Paco', 'paco@arroba.com', '2010-03-24 12:56:56')");

Answer 1

You can export your databases to a file using this command:

• mysqldump -u username -p password dbname > dump.sql

If you would prefer to only dump the structure of the database:

• mysqldump -u username -p password –no-data dbname > dump.sql

You can then import your database using a similar command:

• mysql -u username -p password dbname < dump.sql

Since you want to do this from within php, you can do something along these lines:

$result = 0;
$command = "mysqldump -u username -p password dbname > dump.sql";
exec($command,$output=array(),$result);
if($result) echo "yay, this has worked.\n";

You may be able to find more examples by figuring out how PHPMyAdmin does it, or checking out MySQL's Load Data function.