Load an SQL file, like phpMyAdmin does

[mikeatrpi]

I've got a file full of SQL commands, whitespace, and comments produced by mysqldump. I want to load that data back into my database using PHP. I can't do a system() or exec() call, and I tried splitting it up based on semicolons but that fails sometimes too.

Is there a function available somewhere that will take a huge SQL file and break it up into individual queries? Thank you.

[nnichols]

I suggest you take a look at the code that phpmyadmin uses for this. It is quite simple, strips out comments and breaks individual queries up and executes them.

[MacCoilin]

Hello,

I had this problem a couple of days ago. I had a 11MB sql dump from my local PhpMyAdmin. It contained the table structure for ONE table and 93,823 insert statements for that table (the data).

I did n't have ftp access to my MySQL database on my hosting company's server and I did n't have shell access. Therefore, I had to use a php script. NNichols suggested reading the data in from a file.

Basically, I read the information from a file into an array,then looped through the array to "insert" the data into the database table".

The main issue I had was that I could not to this in one go. I had to split the original large sql file into smaller files. When I tried to do it all at once, I kept getting a internal server 500 error.

I'll give the script at the end.

Firstly, I copied the table structure from the sql dump file into PhpMyAdmin on my hosting company's server to write the table structure.

Next I copied in 9 stages (9 different ftp uploads) the data to the hosting server and ran my php script 9 times to complete the full database table write. I cut from the main sql dump file on my local computer into another file with the same name as that in my php script on the hosting server, uploaded the file,then ran the script.

It is important that the sql insert statements are all on seperate lines when you are reading them into an array. Therefore, make sure word wrap is off in note pad or whatever text editor you are using.

Also PhpMyAdmin automatically puts ";"s at the end of the insert statements. You will need to use a text editor to remove these with a replace function. I used replace ");" with ")". I found notepad could not do this on my computer. The file was to big, therefore, I used Word.

If you have multiple tables (with associated sql statements for the data) in your dump file you may need to carefully plan your ftp uploads.

PHP script follows..

<?php

$db="yourdatabase name";

$mysql_link = mysql_connect("localhost","username","password");
mysql_select_db($db,$mysql_link);

// Reading the data into an array

$arr = file("yoursqldumpstatements.txt");
//loop through each array element
for($i=0; $i<sizeof($arr); $i++) {
//array element data is the sql statement for mysql_query function.
mysql_query($arr[$i]);
}
?>

I hope that makes some sort of sense, it does work.

Brian MacCoilin

[nnichols]

MacCoilin's way is great, but it will only work if your dump was not optimized as the extended inserts would not work.

[mikeatrpi]

Thank you both. I ended up using a combination of your script, Brian, and some code I found on the web. My file contains 15 table definitions and associated data, but the file is well under a meg.

$filename = realpath(".") . '\cms\clean.sql';	
$handle = open ($filename, "rb");
$contents = fread ($handle, filesize ($filename));
$contents .= "\n\n";
fclose ($handle);
mysql_select_db($dbname);
$queries = explode(";\r\n", $contents);
$querycount = count($queries)-1;
for($i=0; $i < $querycount; $i++) {
	$result = mysql_query($queries[$i]);
	if(mysql_errno() != 0) 
		echo '<br>' . mysql_errno() . ": " . mysql_error(). "\n";
}

The key was explode on ;\r\n (I'm on windows, hence the \r) for getting this working. Thank you again very much!