migrate_data_export.php

<?php
/**
 * Dump MySQL database
 *
 * Here is an inline example:
 * <code>
 * $connection = @mysql_connect($dbhost,$dbuser,$dbpsw);
 * $dumper = new MySQLDump($dbname,'filename.sql',false,false);
 * $dumper->doDump();
 * </code>
 *
 * Special thanks to:
 * - Andrea Ingaglio <[email protected]> helping in development of all class code
 * - Dylan Pugh for precious advices halfing the size of the output file and for helping in debug
 *
 * @name    MySQLDump
 * @author  Daniele Vigan� - CreativeFactory.it <[email protected]>
 * @version 2.20 - 02/11/2007
 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
 */
error_reporting(E_ALL ^ E_NOTICE ^ E_DEPRECATED);
/**
 * Dump MySQLi database
 * Inline example:
 * <code>
 * $connection = mysqli_connect($dbhost,$dbuser,$dbpsw);
 * $dumper = new MySQLiDump($connection,$dbname,'filename.sql',false,false);
 * $dumper->getDatabaseData(); // the only method I needed from the class
 * </code>
 *
 * @author  Sumon Islam <[email protected]>
 * @version 1.0 - 24/08/2022
 */
class MySQLiDump
{
	/**
	 * @var MySQLi connection
	 */
	private $connection;
	/**
	 * @access private
	 */
	var $database = null;
	/**
	 * @access private
	 */
	var $compress = false;
	/**
	 * @access private
	 */
	var $hexValue = false;
	/**
	 * The output filename
	 * @access private
	 */
	var $filename = null;
	/**
	 * The pointer of the output file
	 * @access private
	 */
	var $file = null;
	/**
	 * @access private
	 */
	var $isWritten = false;
	function __construct($connection, $db, $filepath = 'dump.sql', $compress = false, $hexValue = false)
	{
		$this->compress = $compress;
		if (!$connection instanceof MySQLi)
			return false;
		$this->connection = $connection;
		$this->database = $db;
		$this->hexValue = $hexValue;
		if (!$this->setDatabase())
			return false;
		if (!$this->setOutputFile($filepath))
			return false;
	}
	function getError()
	{
		return mysqli_error($this->connection);
	}
	function setDatabase()
	{
		if (!mysqli_select_db($this->connection, $this->database))
			return false;
		return true;
	}
	/**
	 * Returns the database where the class is working on
	 * @return string
	 */
	function getDatabase()
	{
		return $this->database;
	}
	/**
	 * Sets the output file type (It can be made only if the file hasn't been already written)
	 * @param boolean $compress If it's true, the output file will be compressed
	 */
	function setCompress($compress)
	{
		if ($this->isWritten)
			return false;
		$this->compress = $compress;
		$this->openFile($this->filename);
		return true;
	}
	/**
	 * Returns if the output file is or not compressed
	 * @return boolean
	 */
	function getCompress()
	{
		return $this->compress;
	}
	/**
	 * Sets the output file
	 * @param string $filepath The file where the dump will be written
	 */
	function setOutputFile($filepath)
	{
		if ($this->isWritten)
			return false;
		$this->filename = $filepath;
		$this->file = $this->openFile($this->filename);
		//write the header of the file
		$this->writeHeader();
		return $this->file;
	}
	/**
	 * Write the header of the output file
	 */
	function writeHeader()
	{
		$this->write("-- MySQL dump\n");
		$this->write("--\n");
		$this->write("-- Host: " . $this->connection->host_info . "\n");
		$this->write("-- Database: " . $this->database . "\n");
		$this->write("--\n");
		$this->write("-- --------------------------------------------------\n");
		$this->write("-- Server version: " . $this->connection->server_info . "\n");
		$this->write("-- PHP Version: " . phpversion() . "\n");
		$this->write("-- Generated: " . date('Y-m-d H:i:s') . "\n");
		$this->write("-- --------------------------------------------------\n\n");
		$this->write("SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\";\n");
		$this->write("SET AUTOCOMMIT = 0;\n");
		$this->write("START TRANSACTION;\n");
		$this->write("SET time_zone = \"+00:00\";\n\n");
		$this->write("/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;\n");
		$this->write("/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;\n");
		$this->write("/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;\n");
		$this->write("/*!40101 SET NAMES utf8 */;\n");
	}
	/**
	 * Write the footer of the output file
	 */
	function writeFooter()
	{
		// commit transaction
		$this->write("\nCOMMIT;\n");
		$this->write("/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;\n");
		$this->write("/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;\n");
		$this->write("/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;\n");
	}
	/**
	 * Writes a string to the output file
	 * @param string $string The string to write
	 */
	function write($string)
	{
		if ($this->file)
			$this->saveToFile($this->file, $string);
	}
	/**
	 * Returns the output filename
	 * @return string
	 */
	function getOutputFile()
	{
		return $this->filename;
	}
	/**
	 * Writes to file the $table's data
	 * @param string $table The table name
	 * @param boolean $hexValue It defines if the output is base 16 or not
	 * @param string $method The method to use to dump the data (INSERT or REPLACE)
	 */
	function getTableData($table, $hexValue = true, $method = 'INSERT')
	{
		if (!$this->setDatabase($this->database))
			return false;
		// Header
		$data = "-- \n";
		$data .= "-- Dumping data for table `$table` \n";
		$data .= "-- \n\n";
		$records = mysqli_query($this->connection, 'SHOW FIELDS FROM `' . $table . '`');
		$num_fields = ($records) ? mysqli_num_rows($records) : 0;
		if ($num_fields == 0)
			return false;
		// Field names
		$selectStatement = "SELECT ";
		$insertStatement = "$method INTO `$table` (";
		$hexField = array();
		for ($x = 0; $x < $num_fields; $x++) {
			$record = mysqli_fetch_assoc($records);
			if (($hexValue) && ($this->isTextValue($record['Type']))) {
				$selectStatement .= 'HEX(`' . $record['Field'] . '`)';
				$hexField[$x] = true;
			} else
				$selectStatement .= '`' . $record['Field'] . '`';
			$insertStatement .= '`' . $record['Field'] . '`';
			$insertStatement .= ", ";
			$selectStatement .= ", ";
		}
		$insertStatement = @substr($insertStatement, 0, -2) . ') VALUES';
		$selectStatement = @substr($selectStatement, 0, -2) . ' FROM `' . $table . '`';
		$records = mysqli_query($this->connection, $selectStatement);
		$num_rows = ($records) ? mysqli_num_rows($records) : 0;
		$num_fields = mysqli_num_fields($records);
		// Dump data
		if ($num_rows > 0) {
			$data .= $insertStatement;
			for ($i = 0; $i < $num_rows; $i++) {
				$record = mysqli_fetch_assoc($records);
				$data .= ' (';
				for ($j = 0; $j < $num_fields; $j++) {
					$field_name = mysqli_fetch_field_direct($records, $j)->name;
					if ($hexField[$j] && (@strlen($record[$field_name]) > 0))
						$data .= "0x" . $record[$field_name];
					else
						$data .= "'" . @str_replace('\"', '"', mysqli_real_escape_string($this->connection, $record[$field_name])) . "'";
					$data .= ',';
				}
				$data = @substr($data, 0, -1) . ")";
				$data .= ($i < ($num_rows - 1)) ? ',' : ';';
				$data .= "\n";
				//if data in greather than 1MB save
				if (strlen($data) > 1048576) {
					$this->saveToFile($this->file, $data);
					$data = '';
				}
			}
			$data .= "\n-- --------------------------------------------------------\n\n";
			$this->saveToFile($this->file, $data);
		}
	}
	/**
	 * Writes to file all the selected database tables data
	 * @param boolean $hexValue It defines if the output is base-16 or not
	 * @param string $method The method to use to dump the data (INSERT or REPLACE)
	 */
	function getDatabaseData($hexValue = true, $method = 'INSERT')
	{
		$records = mysqli_query($this->connection, 'SHOW TABLES');
		if (!$records)
			return false;
		if (mysqli_num_rows($records) == 0)
			return false;
		while ($record = mysqli_fetch_row($records)) {
			$this->getTableData($record[0], $hexValue, $method);
		}
		$this->writeFooter();
	}
	/**
	 * @access private
	 */
	function isTextValue($field_type)
	{
		switch ($field_type) {
			case "tinytext":
			case "text":
			case "mediumtext":
			case "longtext":
			case "binary":
			case "varbinary":
			case "tinyblob":
			case "blob":
			case "mediumblob":
			case "longblob":
				return True;
				break;
			default:
				return False;
		}
	}
	/**
	 * @access private
	 */
	function openFile($filename)
	{
		$file = false;
		if ($this->compress)
			$file = @gzopen($filename, "w9");
		else
			$file = @fopen($filename, "w");
		return $file;
	}
	/**
	 * @access private
	 */
	function saveToFile($file, $data)
	{
		if (empty($file) or empty($data))
			return false;
		if ($this->compress)
			@gzwrite($file, $data);
		else
			@fwrite($file, $data);
		$this->isWritten = true;
	}
	/**
	 * @access private
	 */
	function closeFile($file)
	{
		if ($this->compress)
			@gzclose($file);
		else
			@fclose($file);
	}
	/**
	 * destructor - remove the file if it was not written or close or if any error occured
	 */
	function __destruct()
	{
		if (!$this->isWritten) {
			if ($this->compress)
				@gzclose($this->file);
			else
				@fclose($this->file);
			@unlink($this->file);
		}
	}
}