• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
The Electric Toolbox Blog

The Electric Toolbox Blog

Linux, Apache, Nginx, MySQL, Javascript and PHP articles

  • Applications
  • FCKEditor
  • Apache
  • Windows
  • Contact Us
Home / PHP script to export table creation SQL from MySQL

PHP script to export table creation SQL from MySQL

I was trying to export the structure of a MySQL database using phpMyAdmin but it kept timing out and not showing me the full create script. I think phpMyAdmin uses the information schema to get this information and its on a host with hundreds of databases and tables so querying the information schema runs very slowly. Instead I knocked together a quick PHP script to dump the structure instead and share it here.

The PHP script

Substitute the username, password, hostname and database values so they are appropriate for you. Then call the script from a web browser (you may want to put it in a password protected directory) and it will show the create queries which can then be run directly in MySQL.

set_time_limit(0);

$username = 'XXXXXX';
$password = 'YYYYYY';
$hostname = 'ZZZZZZ';
$database = 'AAAAAA';

try {
	$pdo = new PDO("mysql:host={$hostname};dbname={$database}", $username, $password);
}
catch(PDOException $e) {
	die("Could not connect to the databasen");
}

echo '<pre>';
$stmt1 = $pdo->query('SHOW TABLES', PDO::FETCH_NUM);
foreach($stmt1->fetchAll() as $row) {
	$stmt2 = $pdo->query("SHOW CREATE TABLE `$row[0]`", PDO::FETCH_ASSOC);
	$table = $stmt2->fetch();
	echo "{$table['Create Table']};nn";
}
echo '</pre>';

The script itself is fairly self explanatory.

Note that the table name is enclosed with backticks; this prevents SQL errors if the table is named with a reserved word, which happened to me when I was writing the script.

The bit that echoes the create query terminates with a semi-colon; without this it’s all one query and will fail if you try to run it from phpMyAdmin or the MySQL command line.

Check Out These Related posts:

  1. Fetching data using PHP and PDO with bound placeholders
  2. MySQL utility commands
  3. Randomly ordering data with MySQL with a random value column
  4. PHP PDO DSN Connection String

Filed Under: MySql, PHP

Primary Sidebar

Categories

  • Apache
  • Applications
  • Article
  • Case Studies
  • Email Servers
  • FCKEditor
  • HTML And CSS
  • Javascript
  • Linux/Unix/BSD
  • Microsoft SQL Server
  • Miscellaneous Postings
  • MySql
  • Networking
  • Nginx Web Server
  • Offsite Articles
  • OSX
  • PHP
  • Quick Tips
  • RFC – Request for Comments
  • SilverStripe
  • VMWare
  • VPN
  • Windows
  • WordPress

Recent Posts

  • Vim Show Line Numbers
  • Add User To Group Linux
  • Chmod 777 Tutorial
  • How to Copy Directory Linux
  • Linux create user

Copyright © 2021. ElectricToolBox. All Rights Reserved.

  • Contact Us
  • Copyright Info
  • Privacy Policy
  • Sitemap