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.

1.   <?php
2.   
3.   ini_set(max_execution_time,600); //let the script run for 10 minutes (600 seconds)
4.   
5.   $self = 'import-mysql.php'; //the url of this page
6.   $files_folder = './exported-mysql/'; //the folder where you've uploaded your MySQL files
7.   $files = array('example.sql','animals.sql'); //list all available files here
8.   
9.   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
10.  
11.  if ($file) { //a file has been selected. import it
12.  	if (!in_array($file,$files)) { custom_die('Invalid file selected.'); }
13.  	$full_file = $files_folder.$file;
14.  	$qty = import_mysql_queries($full_file,array('show_queries'=>false)); //this function is below
15.  	echo '<p>Done. '.$file.' was successfully imported. '.number_format($qty).' queries were performed.</p>';
16.  	echo '<p><a href="'.$self.'">Start Over</a></p>';
17.  } else { //if no file is selected, show a menu
18.  	echo '<p>Select a file to continue:</p><ul>';
19.  	foreach ($files as $file) {
20.  		echo '<li><a href="'.$self.'?file='.$file.'">'.$file.'</a></li>';
21.  	}
22.  	echo '</ul>';
23.  }
24.  
25.  
26.  function import_mysql_queries($file,$args=array()) {
27.  	foreach (array('line_break','show_queries') as $key) {
28.  		if (array_key_exists($key,$args)) { $$key = $args[$key]; } else { $$key = false; }
29.  	}
30.  	unset($args);
31.  
32.  	if ($line_break) { $lb = $line_break; }
33.  	else { $lb = "\n"; } // default line break;
34.  	unset($line_break);
35.  
36.  	if (!file_exists($file)) { custom_die('File does not exist.'); }
37.  	$handle = fopen($file, "r");
38.  	$contents = fread($handle, filesize($file));
39.  	fclose($handle);
40.  
41.  	$contents = preg_replace('!/\*.*?\*/;!s', '', $contents); //remove /* */ comments with semi-colon
42.  	$contents = preg_replace('!/\*.*?\*/!s', '', $contents); //remove /* */ comments without semi-colon
43.  
44.  	$contents = trim($contents); //clean up whitespace
45.  
46.  	while (strpos($contents,$lb.$lb) !== false) { $contents = str_replace($lb.$lb,$lb,$contents); } //remove double line breaks
47.  
48.  	//echo '<pre>'.$contents.'</pre>';
49.  
50.  	$queries = array();
51.  	$lines = explode($lb,$contents);
52.  	$query = '';
53.  	foreach ($lines as $key => $line) {
54.  		$line = trim($line);
55.  		if (substr($line,0,2) != '--') { //if it's not a comment
56.  			$query .= ' '.$line; //add current line to query
57.  			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
58.  		}
59.  		unset($lines[$key]); //free up memory by deleting this
60.  	}
61.  
62.  	//echo '<pre>'.print_r($queries,true).'</pre>';
63.  
64.  	$qty = count($queries);
65.  	foreach ($queries as $key => $query) {
66.  		if ($show_queries) { echo '<p>'.$query.' - '; }
67.  		dbQuery($query);
68.  		if ($show_queries) { echo dbAffectedRows().' row(s) affected.</p>'; }
69.  		unset($queries[$key]); //free up memory by deleting this
70.  	}
71.  
72.  	return $qty;
73.  }
74.  
75.  ?>

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.