[MySQL] Exporter une base de donnée

<?php

/***************************************************************
 * SQL_Export class
 * By Adam Globus-Hoenich, 2004 (adam@phenaproxima.net)
 * Use this class as freely as you like. It is 100% free and
 * modifiable :)
***************************************************************/

class SQL_Export
{
	var $cnx;
	var $db;
	var $server;
	var $port;
	var $user;
	var $password;
	var $table;
	var $tables;
	var $exported;

	function SQL_Export($server, $user, $password, $db, $tables)
	{
		$this->db = $db;
		$this->user = $user;
		$this->password = $password;
		
		$sa = explode(":", $server);
		$this->server = $sa[0];
		$this->port = $sa[1];
		unset($sa);

		$this->tables = $tables;

		$this->cnx = mysql_connect($this->server, $this->user, $this->password) or $this->error(mysql_error());
		mysql_select_db($this->db, $this->cnx) or $this->error(mysql_error());
	}


	function export()
	{
		foreach($this->tables as $t)
		{
			$this->table = $t;
			$header = $this->create_header();
			$data = $this->get_data();
			$this->$exported .= "###################\n# Dumping table $t\n###################\n\n$header" . $data . "\n";
		}
		
		return($this->$exported);
	}

	function create_header()
	{
		$fields = mysql_list_fields($this->db, $this->table, $this->cnx);
		$h = "CREATE TABLE `" . $this->table . "` (";
		
		for($i=0; $i<mysql_num_fields($fields); $i++)
		{
			$name = mysql_field_name($fields, $i);
			$flags = mysql_field_flags($fields, $i);
			$len = mysql_field_len($fields, $i);
			$type = mysql_field_type($fields, $i);

			$h .= "`$name` $type($len) $flags,";

			if(strpos($flags, "primary_key")) {
				$pkey = " PRIMARY KEY (`$name`)";
			}
		}
		
		$h = substr($h, 0, strlen($d) - 1);
		$h .= "$pkey) TYPE=MyISAM;\n\n";
		return($h);
	}

	function get_data()
	{
		$d = null;
		$data = mysql_query("SELECT * FROM `" . $this->table . "` WHERE 1", $this->cnx) or $this->error(mysql_error());
		
		while($cr = mysql_fetch_array($data, MYSQL_NUM))
		{
			$d .= "INSERT INTO `" . $this->table . "` VALUES (";

			for($i=0; $i<sizeof($cr); $i++)
			{
				if($cr[$i] == '') {
					$d .= 'NULL,';
				} else {
					$d .= "'$cr[$i]',";
				}
			}

			$d = substr($d, 0, strlen($d) - 1);
			$d .= ");\n";
		}

		return($d);
	}

	function error($err)
	{
		die($err);
	}
}

?>

<?php

	//Test code for the SQL_Export class. Replace the values below with
	//the values for your database (password must be plain text)

	$server = "localhost:3306";	//Port is not really necessary
	$username = "root";		//Username for MySQL server
	$password = "";			//Password for MySQL server
	$db = "myDB";			//Name of database

	//Connect to DB the old fashioned way and get the names of the tables on the server
	$cnx = mysql_connect($server, $username, $password) or die(mysql_error());
	mysql_select_db($db, $cnx) or die(mysql_error());
	$tables = mysql_list_tables($db) or die(mysql_error());

	//Create a list of tables to be exported
	$table_list = array();
	while($t = mysql_fetch_array($tables))
	{
		array_push($table_list, $t[0]);
	}

	//Instantiate the SQL_Export class
	require("SQL_Export.php");
	$e = new SQL_Export($server, $username, $password, $db, $table_list);
	//Run the export
	echo $e->export();

	//Clean up the joint
	mysql_close($e->cnx);
	mysql_close($cnx);

?>

SQL_Export class

(c) 2004 by Adam Globus-Hoenich
phreakpq@995.ca
www.phenaproxima.net

This is the readme file which should accompany the SQL_Export class (SQL_Export.php). Redistribute the file if you like, but this readme must always accompany it. You are free to use SQL_Export in any way you see fit and you can modify it if you want, but it stays copyrighted to me.

SQL_Export is a simple PHP class to export MySQL database data, one table at a time, in raw SQL format. You pass to it an array of table names to export, and it will return raw SQL. You can do this with two lines of code, so it's very easy to use. SQL_Export is meant to MySQL databases only.

Here's a simple example of how to use SQL_Export, assuming you want to export table1, table2, and table3 from the database 'sample':

require("SQL_Export.php");
$t = array("table1", "table2", "table3");
$exporter = new SQL_Export("localhost:3306", "username", "password", "sample", $t);
$sql = $exporter->export();

The variable $sql will be plain SQL which you can further work with, if you want.

I hope you find this class useful. Any comments, questions, bugs, or good jokes you've heard lately? Send it all to phreakpq@995.ca. Cheers folks.