Import MySQL Queries from a File Too Big for phpMyAdmin

The other day, I exported a giant database table from phpMyAdmin on my local machine and went to import it into a client's (shared) hosting environment. It exceeded the upload_max_filesize, and I had no way of updating that setting on the shared host. So, instead of tediously splitting the file up into multiple small parts, I wrote the script below that breaks the phpMyAdmin export into individual MySQL queries and then executes them.

<?php

ini_set(max_execution_time,600); //let the script run for 10 minutes (600 seconds)

$self = 'import-mysql.php'; //the url of this page
$files_folder = './exported-mysql/'; //the folder where you've uploaded your MySQL files
$files = array('example.sql','animals.sql'); //list all available files here

if (array_key_exists('file',$_GET) && (is_string($_GET['file'])) { $file = strip_tags($_GET['file']); } else { $file = false; } //grab the selected file from the query string

if ($file) { //a file has been selected. import it
	if (!in_array($file,$files)) { custom_die('Invalid file selected.'); }
	$full_file = $files_folder.$file;
	$qty = import_mysql_queries($full_file,array('show_queries'=>false)); //this function is below
	echo '<p>Done. '.$file.' was successfully imported. '.number_format($qty).' queries were performed.</p>';
	echo '<p><a href="'.$self.'">Start Over</a></p>';
} else { //if no file is selected, show a menu
	echo '<p>Select a file to continue:</p><ul>';
	foreach ($files as $file) {
		echo '<li><a href="'.$self.'?file='.$file.'">'.$file.'</a></li>';
	}
	echo '</ul>';
}


function import_mysql_queries($file,$args=array()) {
	foreach (array('line_break','show_queries') as $key) {
		if (array_key_exists($key,$args)) { $$key = $args[$key]; } else { $$key = false; }
	}
	unset($args);

	if ($line_break) { $lb = $line_break; }
	else { $lb = "\n"; } // default line break;
	unset($line_break);

	if (!file_exists($file)) { custom_die('File does not exist.'); }
	$handle = fopen($file, "r");
	$contents = fread($handle, filesize($file));
	fclose($handle);

	$contents = preg_replace('!/\*.*?\*/;!s', '', $contents); //remove /* */ comments with semi-colon
	$contents = preg_replace('!/\*.*?\*/!s', '', $contents); //remove /* */ comments without semi-colon

	$contents = trim($contents); //clean up whitespace

	while (strpos($contents,$lb.$lb) !== false) { $contents = str_replace($lb.$lb,$lb,$contents); } //remove double line breaks

	//echo '<pre>'.$contents.'</pre>';

	$queries = array();
	$lines = explode($lb,$contents);
	$query = '';
	foreach ($lines as $key => $line) {
		$line = trim($line);
		if (substr($line,0,2) != '--') { //if it's not a comment
			$query .= ' '.$line; //add current line to query
			if (substr($line,-1) == ';') { $queries[] = trim($query); $query = ''; } //if line ends in semi-colon, it is the end of the query, so save it and move on
		}
		unset($lines[$key]); //free up memory by deleting this
	}

	//echo '<pre>'.print_r($queries,true).'</pre>';

	$qty = count($queries);
	foreach ($queries as $key => $query) {
		if ($show_queries) { echo '<p>'.$query.' - '; }
		dbQuery($query);
		if ($show_queries) { echo dbAffectedRows().' row(s) affected.</p>'; }
		unset($queries[$key]); //free up memory by deleting this
	}

	return $qty;
}

?>

You'll need to upload the .sql files somewhere on your server via FTP and then update those variables near the top of the script to reference them. The reason we do this instead of a form file upload should be obvious… the upload_max_filesize setting would prevent us from uploading a giant file here just like it would in phpMyAdmin.

Hopefully this will save you some time.

Note: There are a couple of custom functions referenced in there (custom_die, dbQuery, and dbAffectedRows). Click the links to view and download the code for them.


Comments

Loading…

This post was published on April 20th, 2015 by Robert James Reese in the following categories: MySQL and PHP. Before using any of the code or other content in this post, you must read and agree to our terms of use.