error_reporting(E_ALL);

set_time_limit(0);
date_default_timezone_set('Europe/Amsterdam');

$nocache = 'SQL_NO_CACHE ';
$username = 'root';
$password = 'usbw';
$hostname = 'localhost';
$port     = '3307';

$enter = (php_sapi_name() == "cli") ? PHP_EOL : '<br />';

$update = false;

echo '<h2>Export Mysql Structure & Data to ZIP</h2>' . $enter . $enter;
flush();
try {
    $con = "mysql:host=" . $hostname . ";port=" . $port;
    $pdoCon = new PDO($con, $username,  $password, array(PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION));
	$pdoCon->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

	$dbs = $pdoCon->query("SHOW DATABASES")->fetchAll();

	echo '<pre>' . $enter;
	foreach ($dbs as $database) {
		if (!in_array($database[0], array('information_schema', 'performance_schema', 'phpmyadmin', 'mysql'))) {
			flush();
			echo date("Y-m-d H:i:s") . '::database:  ' . $database[0] . $enter;
			$pdoCon->query("USE `" . $database[0] . "`");

			$tables = $pdoCon->query("SHOW TABLES")->fetchAll();

			foreach ($tables as $table) {
                flush();
				echo date("Y-m-d H:i:s") . '::--table: ' . $table[0] . ' [';

				$zip = new ZipArchive;
				$res = $zip->open($database[0] . '-mysql.zip', ZipArchive::CREATE);
				$locate = $zip->locateName($database[0] . '-' . $table[0] . '-scheme.sql');
				$zip->close();

				if ($locate == false OR $update == true) {
					try {
						$scheme = $pdoCon->query("SHOW CREATE TABLE `" . $table[0] . "`");

						if ($scheme != false) {
							$scheme = $scheme->fetch();

							$create = $scheme[1];
							$create = preg_replace('/AUTO_INCREMENT=([0-9]+) /i', '', $create);

							$zip = new ZipArchive;
							$res = $zip->open($database[0] . '-mysql.zip', ZipArchive::CREATE);
							if ($res === TRUE) {
								$zip->addFromString($database[0] . '-' . $table[0] . '-scheme.sql', $create);
								$zip->close();

								echo 'STRUCT:ZIP ';
							}

							$zip = new ZipArchive;
							$res = $zip->open($database[0] . '-mysql.zip', ZipArchive::CREATE);
							$locate = $zip->locateName($database[0] . '-' . $table[0] . '-data.sql');
							$zip->close();

							if ($locate == false OR $update == true) {
								try {
									$data = $pdoCon->query("SELECT * FROM `" . $table[0] . "`");

									$tmp = 'tmp-' . $database[0] . '-' . $table[0] . '-data.sql';
									$fd = fopen($tmp, "w+");
									while($insert = $data->fetch(PDO::FETCH_ASSOC)) {
										fputs($fd, "INSERT INTO `" . $table[0] . "` VALUES ('" . implode("','", $insert) . "')\r\n");
									}
									echo 'DATA:TEMP ';
									fclose($fd);

									$zip = new ZipArchive;
									$res = $zip->open($database[0] . '-mysql.zip', ZipArchive::CREATE);
									if ($res === TRUE) {
										$zip->addFile($tmp, $database[0] . '-' . $table[0] . '-data.sql');
										$zip->close();
										unlink($tmp);
										echo 'DATA:ZIP ';
									}
								}
								catch (Exception $e) {
									echo ' ** ERRORRRR';
								}
							}
							else {
								echo 'INFO: No data update needed';
							}
						}
						else {
							echo 'ERROR: table corrupt, can not access scheme';
						}
					}
					catch(Exception $e) {
						echo 'ERROR: table corrupt, can not access scheme';
					}
				}
				else {
					echo 'INFO: No scheme update needed';
				}

				echo ']' . $enter;
				flush();
			}
		}
	}
}
catch(Exception $e) {
    die("Error creating database connection");
}