11.23.06

Import MySQL script file with your own PHP script to Import MySQL

Posted in PHP at by jason.michael

Okay, I use PHPMyAdmin for all of my MySQL administration.   I’ve learned that there may be better tools available, but I have been hooked on using PHPMyAdmin.  I have a big gripe with it though - if you want to import an sql script for execution, you must browse for a file to upload which limits you to a set number of bytes (around 2 MB I think) OR you can copy and paste the SQL into the textarea.  None of these options worked for me, because the amount of SQL code I had amounted to well over 2MB. 

I ‘Googled’ the term ‘php mysql import script’.  Did I find anything useful?  Not really.  Just alot of folks asking the same question, or tools that are integrated in other applications.  Boy that really stunk.  So I had to go ahead and write my own utility.  Here’s the code below. 

  • Note that I didn’t include a listing of my ‘common_new.php’ file - this is only a file that contains the dbconnect() function, which does nothing but connect me to my database with my login credentials - you can create your own library of functions to include)
  • Also note that I have some search patterns setup to massage my SQL data to work with my version of MySQL, you might use these examples to make additional changes.
  • Finally, also note that this is setup to drop the tables before reimporting them!  If you don’t want to lose tables and maybe just run a script that only does INSERT statements, then comment out the line: $page=$prepage.$page;  But if you have no CREATE TABLES statement to begin with, it shouldn’t matter - as I have this program setup, it only wants to drop tables that already are being created in your SQL

 

< ?

include ("common_new.php");

$filename="transaction_sheeet_dev.sql";

$page=file_get_contents($filename);

$pattern="/CREATE TABLE \`(.*?)\` \(/si";
preg_match_all($pattern,$page,$matches);

foreach ($matches[1] as $k=>$v)
{
 $prepage.=”DROP TABLE IF EXISTS`$v`;”;
}
// TAKE OUT THIS LINE IF YOU DON’T WANT TO DROP THE TABLES
$page=$prepage.$page;

$pattern=”/CREATE TABLE /si”;
$replace=”CREATE TABLE IF NOT EXISTS “;
$page=preg_replace($pattern,$replace,$page);

$pattern=”/\`timestamp\` timestamp NOT NULL .*?\,/si”;
$replace=”`timestamp` timestamp NULL,”;
$page=preg_replace($pattern,$replace,$page);
unset($matches);
$pattern=”/InnoDB/i”;
$replace=”MyISAM”;
$page=preg_replace($pattern,$replace,$page);
unset($matches);$pattern=”/latin1/i”;
$replace=”utf8″;
$page=preg_replace($pattern,$replace,$page);
unset($matches);
$pattern=”/(.*?)\;/si”;
$r=preg_match_all($pattern,$page,$matches);
$db=dbconnect();
foreach ($matches[1] as $k=>$v)
{
 $sql=$v;
 echo “executing:”. $v.”
“;
 $rst=mysql_query($sql,$db);
 //if (!$rst) die(”could not execute: $sql
MySQL said:”.mysql_error());
 
}
if (count($matches[1])==0) die(”found no matches”);

?>

 

 

Leave a Comment