Loading .sql files from within PHP

小开

mysql_query("LOAD DATA LOCAL INFILE '/path/to/file' INTO TABLE mytable");

小开

Are you sure that its not one query per line? Your text editor may be wrapping lines, but in reality each query may be on a single line.

At any rate, olle's method seems best. If you have reasons to run queries one at time, you should be able to read in your file line by line, then use the semicolon at the end of each query to delimit. You're much better off reading in a file line by line than trying to split an enormous string, as it will be much kinder to your server's memory. Example:

$query  = '';
$handle = @fopen("/sqlfile.sql", "r");


if ($handle) {
while (!feof($handle)) {
$query.= fgets($handle, 4096);


if (substr(rtrim($query), -1) === ';') {
// ...run your query, then unset the string
$query = '';
}
}


fclose($handle);
}

Obviously, you'll need to consider transactions and the rest if you're running a whole lot of queries in a batch, but it's probably not a big deal for a new-install script.

小开

Unless you plan to import huge .sql files, just read the entire file into memory, and run it as a query.

It's been a while since I've used PHP, so, pseudo code:

all_query = read_file("/my/file.sql")
con = mysql_connect("localhost")
con.mysql_select_db("mydb")
con.mysql_query(all_query)
con.close()

Unless the files are huge (say, over several megabytes), there's no reason to execute it line-at-a-time, or try and split it into multiple queries (by splitting using ;, which as I commented on cam8001's answer, will break if the query has semi-colons within strings)..

小开

My suggestion would be to look at the sourcecode of PHPMyBackup. It's an automated PHP SQL loader. You will find that mysql_query only loads one query at a time, and projects like PHPMyAdmin and PHPMyBackup have already done the hard work for you of parsing the SQL the correct way. Please don't re-invent that wheel :P

小开

The simplest solution is to use shell_exec() to run the mysql client with the SQL script as input. This might run a little slower because it has to fork, but you can write the code in a couple of minutes and then get back to working on something useful. Writing a PHP script to run any SQL script could take you weeks.

Supporting SQL scripts is more complex than what people are describing here, unless you're certain that your script contains only a subset of the functionality of scripts. Below are some examples of things that may appear in an ordinary SQL script that make it complex to code a script to interpret it line by line.

-- Comment lines cannot be prepared as statements
-- This is a MySQL client tool builtin command.
-- It cannot be prepared or executed by server.
USE testdb;


-- This is a multi-line statement.
CREATE TABLE foo (
string VARCHAR(100)
);


-- This statement is not supported as a prepared statement.
LOAD DATA INFILE 'datafile.txt' INTO TABLE foo;


-- This statement is not terminated with a semicolon.
DELIMITER //


-- This multi-line statement contains a semicolon
-- but not as the statement terminator.
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM foo;
END
//

If you only support a subset of SQL scripts, excluding some corner cases such as those above, it's relatively easy to write a PHP script that reads a file and executes the SQL statements within the file. But if you want to support any valid SQL script, that's much more complex.

See also my answers to these related questions:

小开

mysqli can run multiple queries separated by a ;

you could read in the whole file and run it all at once using mysqli_multi_query()

But, I'll be the first to say that this isn't the most elegant solution.

小开

最佳答案

I'm getting the feeling that everyone here who's answered this question doesn't know what it's like to be a web application developer who allows people to install the application on their own servers. Shared hosting, especially, doesn't allow you to use SQL like the "LOAD DATA" query mentioned previously. Most shared hosts also don't allow you to use shell_exec.

Now, to answer the OP, your best bet is to just build out a PHP file that contains your queries in a variable and can just run them. If you're determined to parse .sql files, you should look into phpMyAdmin and get some ideas for getting data out of .sql files that way. Look around at other web applications that have installers and you'll see that, rather than use .sql files for their queries, they just package them up in PHP files and just run each string through mysql_query or whatever it is that they need to do.

小开

None of the solutions I have seen here deal with needing to change the delimiter while creating a stored procedure on a server where I can't count on having access to LOAD DATA INFILE. I was hoping to find that someone had already solved this without having to scour the phpMyAdmin code to figure it out. Like others, I too was in the process of looking for someone else's GPL'ed way of doing it since I am writing GPL code myself.

小开

Some PHP libraries can parse a SQL file made of multiple SQL statements, explode it properly (not using a simple ";" explode, naturally), and the execute them.

For instance, check Phing's PDOSQLExecTask

小开

I use this all the time:

$sql = explode(";",file_get_contents('[your dump file].sql'));//


foreach($sql as $query)
mysql_query($query);

小开

Works on Navicat dumps. Might need to dump the first /* */ comment navicat puts in.

$file_content = file('myfile.sql');
$query = "";
foreach($file_content as $sql_line){
if(trim($sql_line) != "" && strpos($sql_line, "--") === false){
$query .= $sql_line;
if (substr(rtrim($query), -1) == ';'){
echo $query;
$result = mysql_query($query)or die(mysql_error());
$query = "";
}
}
}

小开

phpBB uses a few functions to parse their files. They are rather well-commented (what an exception!) so you can easily know what they do (I got this solution from http://www.frihost.com/forums/vt-8194.html). here is the solution an I've used it a lot:

<?php
ini_set('memory_limit', '5120M');
set_time_limit ( 0 );
/***************************************************************************
*                             sql_parse.php
*                              -------------------
*     begin                : Thu May 31, 2001
*     copyright            : (C) 2001 The phpBB Group
*     email                : support@phpbb.com
*
*     $Id: sql_parse.php,v 1.8 2002/03/18 23:53:12 psotfx Exp $
*
****************************************************************************/


/***************************************************************************
*
*   This program is free software; you can redistribute it and/or modify
*   it under the terms of the GNU General Public License as published by
*   the Free Software Foundation; either version 2 of the License, or
*   (at your option) any later version.
*
***************************************************************************/


/***************************************************************************
*
*   These functions are mainly for use in the db_utilities under the admin
*   however in order to make these functions available elsewhere, specifically
*   in the installation phase of phpBB I have seperated out a couple of
*   functions into this file.  JLH
*
\***************************************************************************/


//
// remove_comments will strip the sql comment lines out of an uploaded sql file
// specifically for mssql and postgres type files in the install....
//
function remove_comments(&$output)
{
$lines = explode("\n", $output);
$output = "";


// try to keep mem. use down
$linecount = count($lines);


$in_comment = false;
for($i = 0; $i < $linecount; $i++)
{
if( preg_match("/^\/\*/", preg_quote($lines[$i])) )
{
$in_comment = true;
}


if( !$in_comment )
{
$output .= $lines[$i] . "\n";
}


if( preg_match("/\*\/$/", preg_quote($lines[$i])) )
{
$in_comment = false;
}
}


unset($lines);
return $output;
}


//
// remove_remarks will strip the sql comment lines out of an uploaded sql file
//
function remove_remarks($sql)
{
$lines = explode("\n", $sql);


// try to keep mem. use down
$sql = "";


$linecount = count($lines);
$output = "";


for ($i = 0; $i < $linecount; $i++)
{
if (($i != ($linecount - 1)) || (strlen($lines[$i]) > 0))
{
if (isset($lines[$i][0]) && $lines[$i][0] != "#")
{
$output .= $lines[$i] . "\n";
}
else
{
$output .= "\n";
}
// Trading a bit of speed for lower mem. use here.
$lines[$i] = "";
}
}


return $output;


}


//
// split_sql_file will split an uploaded sql file into single sql statements.
// Note: expects trim() to have already been run on $sql.
//
function split_sql_file($sql, $delimiter)
{
// Split up our string into "possible" SQL statements.
$tokens = explode($delimiter, $sql);


// try to save mem.
$sql = "";
$output = array();


// we don't actually care about the matches preg gives us.
$matches = array();


// this is faster than calling count($oktens) every time thru the loop.
$token_count = count($tokens);
for ($i = 0; $i < $token_count; $i++)
{
// Don't wanna add an empty string as the last thing in the array.
if (($i != ($token_count - 1)) || (strlen($tokens[$i] > 0)))
{
// This is the total number of single quotes in the token.
$total_quotes = preg_match_all("/'/", $tokens[$i], $matches);
// Counts single quotes that are preceded by an odd number of backslashes,
// which means they're escaped quotes.
$escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$i], $matches);


$unescaped_quotes = $total_quotes - $escaped_quotes;


// If the number of unescaped quotes is even, then the delimiter did NOT occur inside a string literal.
if (($unescaped_quotes % 2) == 0)
{
// It's a complete sql statement.
$output[] = $tokens[$i];
// save memory.
$tokens[$i] = "";
}
else
{
// incomplete sql statement. keep adding tokens until we have a complete one.
// $temp will hold what we have so far.
$temp = $tokens[$i] . $delimiter;
// save memory..
$tokens[$i] = "";


// Do we have a complete statement yet?
$complete_stmt = false;


for ($j = $i + 1; (!$complete_stmt && ($j < $token_count)); $j++)
{
// This is the total number of single quotes in the token.
$total_quotes = preg_match_all("/'/", $tokens[$j], $matches);
// Counts single quotes that are preceded by an odd number of backslashes,
// which means they're escaped quotes.
$escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$j], $matches);


$unescaped_quotes = $total_quotes - $escaped_quotes;


if (($unescaped_quotes % 2) == 1)
{
// odd number of unescaped quotes. In combination with the previous incomplete
// statement(s), we now have a complete statement. (2 odds always make an even)
$output[] = $temp . $tokens[$j];


// save memory.
$tokens[$j] = "";
$temp = "";


// exit the loop.
$complete_stmt = true;
// make sure the outer loop continues at the right point.
$i = $j;
}
else
{
// even number of unescaped quotes. We still don't have a complete statement.
// (1 odd and 1 even always make an odd)
$temp .= $tokens[$j] . $delimiter;
// save memory.
$tokens[$j] = "";
}


} // for..
} // else
}
}


return $output;
}


$dbms_schema = 'yourfile.sql';


$sql_query = @fread(@fopen($dbms_schema, 'r'), @filesize($dbms_schema)) or die('problem ');
$sql_query = remove_remarks($sql_query);
$sql_query = split_sql_file($sql_query, ';');


$host = 'localhost';
$user = 'user';
$pass = 'pass';
$db = 'database_name';


// mysql_* is deprecated, prefer using mysqli_* instead
// mysql_connect($host,$user,$pass) or die('error connection');
// mysql_select_db($db) or die('error database selection');
$connection = mysqli_connect($host,$user,$pass) or die('error connection');
mysqli_select_db($connection, $db) or die('error database selection');


$i=1;
foreach($sql_query as $sql){
echo $i++;
echo "<br />";
// mysql_* is deprecated, prefer using mysqli_* instead
// mysql_query($sql) or die('error in query');
mysqli_query($connection, $sql) or die('error in query');
}

小开

$db = new PDO($dsn, $user, $password);


$sql = file_get_contents('file.sql');


$qr = $db->exec($sql);

小开

Just to restate the problem for everyone:

PHP's mysql_query, automatically end-delimits each SQL commands, and additionally is very vague about doing so in its manual. Everything beyond one command will yield an error.

On the other mysql_query is fine with a string containing SQL-style comments, \n, \r..

The limitation of mysql_query reveals itself in that the SQL parser reports the problem to be directly at the next command e.g.

 You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'INSERT INTO `outputdb:`
(`intid`, `entry_id`, `definition`) VALUES...

Here is a quick solution: (assuming well formatted SQL;

$sqlCmds = preg_split("/[\n|\t]*;[\n|\t]*[\n|\r]$/", $sqlDump);

小开

This The Best Code For restore sql by php can use 100% Goooood! Thank A lot

$file_content = file('myfile.sql');
$query = "";
foreach($file_content as $sql_line){
if(trim($sql_line) != "" && strpos($sql_line, "--") === false){
$query .= $sql_line;
if (substr(rtrim($query), -1) == ';'){
echo $query;
$result = mysql_query($query)or die(mysql_error());
$query = "";
}
}
}

小开

Many hosts will not allow you to create your own database through PHP, but you seem to have solved that.
Once the DB has been created, you can manipulate and populate it simply:

mysql_connect("localhost");
mysql_query("SOURCE file.sql");

小开

Try This:

// SQL File
$SQLFile = 'YourSQLFile.sql';


// Server Name
$hostname = 'localhost';


// User Name
$db_user = 'root';


// User Password
$db_password = '';


// DBName
$database_name = 'YourDBName';


// Connect MySQL
$link = mysql_connect($hostname, $db_user, $db_password);


if (!$link) {
die("MySQL Connection error");
}


// Select MySQL DB
mysql_select_db($database_name, $link) or die("Wrong MySQL Database");


// Function For Run Multiple Query From .SQL File
function MultiQuery($sqlfile, $sqldelimiter = ';') {
set_time_limit(0);


if (is_file($sqlfile) === true) {
$sqlfile = fopen($sqlfile, 'r');


if (is_resource($sqlfile) === true) {
$query = array();
echo "<table cellspacing='3' cellpadding='3' border='0'>";


while (feof($sqlfile) === false) {
$query[] = fgets($sqlfile);


if (preg_match('~' . preg_quote($sqldelimiter, '~') . '\s*$~iS', end($query)) === 1) {
$query = trim(implode('', $query));


if (mysql_query($query) === false) {
echo '<tr><td>ERROR:</td><td> ' . $query . '</td></tr>';
} else {
echo '<tr><td>SUCCESS:</td><td>' . $query . '</td></tr>';
}


while (ob_get_level() &gt; 0) {
ob_end_flush();
}


flush();
}


if (is_string($query) === true) {
$query = array();
}
}
echo "</table>";


return fclose($sqlfile);
}
}


return false;
}


/* * * Use Function Like This: ** */


MultiQuery($SQLFile);

小开

I hope the following code will solve your problem pretty well.

//Empty all tables' contents


$result_t = mysql_query("SHOW TABLES");
while($row = mysql_fetch_assoc($result_t))
{
mysql_query("TRUNCATE " . $row['Tables_in_' . $mysql_database]);
}
// Temporary variable, used to store current query
$templine = '';
// Read in entire file
$lines = file($filename);
// Loop through each line
foreach ($lines as $line)
{
// Skip it if it's a comment
if (substr($line, 0, 2) == '--' || $line == '')
continue;


// Add this line to the current segment
$templine .= $line;
// If it has a semicolon at the end, it's the end of the query
if (substr(trim($line), -1, 1) == ';')
{
// Perform the query
mysql_query($templine) or print('Error performing query \'<strong>' . $templine . '\': ' . mysql_error() . '<br /><br />');
// Reset temp variable to empty
$templine = '';
}
}


?>

小开

The easiest and fastest way to load & parse phpmyadmin dump or mysql dump file..

$ mysql -u username -p -h localhost dbname < dumpfile.sql

小开

An updated solution of Plahcinski solution. Alternatively you can use fopen and fread for bigger files:

$fp = file('database.sql', FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
$query = '';
foreach ($fp as $line) {
if ($line != '' && strpos($line, '--') === false) {
$query .= $line;
if (substr($query, -1) == ';') {
mysql_query($query);
$query = '';
}
}
}

小开

In my projects I've used next solution:

<?php


/**
* Import SQL from file
*
* @param string path to sql file
*/
function sqlImport($file)
{


$delimiter = ';';
$file = fopen($file, 'r');
$isFirstRow = true;
$isMultiLineComment = false;
$sql = '';


while (!feof($file)) {


$row = fgets($file);


// remove BOM for utf-8 encoded file
if ($isFirstRow) {
$row = preg_replace('/^\x{EF}\x{BB}\x{BF}/', '', $row);
$isFirstRow = false;
}


// 1. ignore empty string and comment row
if (trim($row) == '' || preg_match('/^\s*(#|--\s)/sUi', $row)) {
continue;
}


// 2. clear comments
$row = trim(clearSQL($row, $isMultiLineComment));


// 3. parse delimiter row
if (preg_match('/^DELIMITER\s+[^ ]+/sUi', $row)) {
$delimiter = preg_replace('/^DELIMITER\s+([^ ]+)$/sUi', '$1', $row);
continue;
}


// 4. separate sql queries by delimiter
$offset = 0;
while (strpos($row, $delimiter, $offset) !== false) {
$delimiterOffset = strpos($row, $delimiter, $offset);
if (isQuoted($delimiterOffset, $row)) {
$offset = $delimiterOffset + strlen($delimiter);
} else {
$sql = trim($sql . ' ' . trim(substr($row, 0, $delimiterOffset)));
query($sql);


$row = substr($row, $delimiterOffset + strlen($delimiter));
$offset = 0;
$sql = '';
}
}
$sql = trim($sql . ' ' . $row);
}
if (strlen($sql) > 0) {
query($row);
}


fclose($file);
}


/**
* Remove comments from sql
*
* @param string sql
* @param boolean is multicomment line
* @return string
*/
function clearSQL($sql, &$isMultiComment)
{
if ($isMultiComment) {
if (preg_match('#\*/#sUi', $sql)) {
$sql = preg_replace('#^.*\*/\s*#sUi', '', $sql);
$isMultiComment = false;
} else {
$sql = '';
}
if(trim($sql) == ''){
return $sql;
}
}


$offset = 0;
while (preg_match('{--\s|#|/\*[^!]}sUi', $sql, $matched, PREG_OFFSET_CAPTURE, $offset)) {
list($comment, $foundOn) = $matched[0];
if (isQuoted($foundOn, $sql)) {
$offset = $foundOn + strlen($comment);
} else {
if (substr($comment, 0, 2) == '/*') {
$closedOn = strpos($sql, '*/', $foundOn);
if ($closedOn !== false) {
$sql = substr($sql, 0, $foundOn) . substr($sql, $closedOn + 2);
} else {
$sql = substr($sql, 0, $foundOn);
$isMultiComment = true;
}
} else {
$sql = substr($sql, 0, $foundOn);
break;
}
}
}
return $sql;
}


/**
* Check if "offset" position is quoted
*
* @param int $offset
* @param string $text
* @return boolean
*/
function isQuoted($offset, $text)
{
if ($offset > strlen($text))
$offset = strlen($text);


$isQuoted = false;
for ($i = 0; $i < $offset; $i++) {
if ($text[$i] == "'")
$isQuoted = !$isQuoted;
if ($text[$i] == "\\" && $isQuoted)
$i++;
}
return $isQuoted;
}


function query($sql)
{
global $mysqli;
//echo '#<strong>SQL CODE TO RUN:</strong><br>' . htmlspecialchars($sql) . ';<br><br>';
if (!$query = $mysqli->query($sql)) {
throw new Exception("Cannot execute request to the database {$sql}: " . $mysqli->error);
}
}


set_time_limit(0);


$mysqli = new mysqli('localhost', 'root', '', 'test');
$mysqli->set_charset("utf8");


header('Content-Type: text/html;charset=utf-8');
sqlImport('import.sql');


echo "Peak MB: ", memory_get_peak_usage(true)/1024/1024;

On test sql file (41Mb) memory peak usage: 3.25Mb

小开

this actually worked for me:

/* load sql-commands from a sql file */
function loadSQLFromFile($url)
{
// ini_set ( 'memory_limit', '512M' );
// set_time_limit ( 0 );


global $settings_database_name;
global $mysqli_object; global $worked; $worked = false;


$sql_query = "";


// read line by line
$lines = file($url);
$count = count($lines);


for($i = 0;$i<$count;$i++)
{
$line = $lines[$i];
$cmd3 = substr($line, 0, 3);
$cmd4 = substr($line, 0, 4);
$cmd6 = substr($line, 0, 6);
if($cmd3 == "USE")
{
// cut away USE ``;
$settings_database_name = substr($line, 5, -3);
}
else if($cmd4 == "DROP")
{
$mysqli_object->query($line); // execute this line
}
else if(($cmd6 == "INSERT") || ($cmd6 == "CREATE"))
{
// sum all lines up until ; is detected
$multiline = $line;
while(!strstr($line, ';'))
{
$i++;
$line = $lines[$i];
$multiline .= $line;
}
$multiline = str_replace("\n", "", $multiline); // remove newlines/linebreaks
$mysqli_object->query($multiline); // execute this line
}
}


return $worked;
}
?>

小开

Some guys (Plahcinski) suggested this code:

$file_content = file('myfile.sql');
$query = "";
foreach($file_content as $sql_line){
if(trim($sql_line) != "" && strpos($sql_line, "--") === false){
$query .= $sql_line;
if (substr(rtrim($query), -1) == ';'){
echo $query;
$result = mysql_query($query)or die(mysql_error());
$query = "";
}
}
}

but I would update it with the one which worked for me:

 //selecting my database
$database = 'databaseTitleInFile';
$selectDatabase = mysql_select_db($database, $con);
if(! $selectDatabase )
{
die('Could not select the database: ' . mysql_error());
}
echo "The database " . $database . " selected successfully\n";
//reading the file
$file_path='..\yourPath\to\File';
if(!file_exists($file_path)){
echo "File Not Exists";
}
$file_content = file_get_contents($file_path);
$array = explode("\n", $file_content)
//making queries
$query = "";
foreach($array as $sql_line){
$sql_line=trim($sql_line);
if($sql_line != "" && substr($sql_line, 0, 2) === "--" && strpos($sql_line, "/*") === false){
$query .= $sql_line;
if (substr(rtrim($query), -1) == ';'){
$result = mysql_query($query)or die(mysql_error());
$query = "";
}
}
}

because it is more comprehensive. ;-)

小开

This may be helpful -->

More or less what it does is to first take the string given to the function (the file_get_contents() value of your file.sql) and remove all the line breaks. Then it splits the data by the ";" character. Next it goes into a while loop, looking at each line of the array that is created. If the line contains the " ` " character, it will know it is a query and execture the myquery() function for the given line data.

Code:

function myquery($query) {


mysql_connect(dbhost, dbuser, dbpass);


mysql_select_db(dbname);


$result = mysql_query($query);


if (!mysql_errno() && @mysql_num_rows($result) > 0) {
}


else {


$result="not";
}
mysql_close();


return $result;


}






function mybatchquery ($str) {


$sql = str_replace("\n","",$str)


$sql = explode(";",$str);


$x=0;


while (isset($str[$x])) {


if (preg_match("/(\w|\W)+`(\w|\W)+) {


myquery($str[$x]);


}


$x++


}


return TRUE;


}








function myrows($result) {


$rows = @mysql_num_rows($result);


return $rows;
}








function myarray($result) {


$array = mysql_fetch_array($result);


return $array;
}








function myescape($query) {


$escape = mysql_escape_string($query);


return $escape;
}






$str = file_get_contents("foo.sql");
mybatchquery($str);

小开

I have an environment where no mysql tool or phpmyadmin just my php application connecting to a mysql server on a different host but I need to run scripts exported by mysqldump or myadmin. To solve the problem I created a script multi_query as I mentioned here

It can process mysqldump output and phpmyadmin exports without mysql command line tool. I also made some logic to process multiple migration files based on timestamp stored in DB like Rails. I know it needs more error handling but currently does the work for me.

Check it out: https://github.com/kepes/php-migration

It's pure php and don't need any other tools. If you don't process user input with it only scripts made by developers or export tools you can use it safely.

小开

Since I can't comment on answer, beware to use following solution:

$db = new PDO($dsn, $user, $password);


$sql = file_get_contents('file.sql');


$qr = $db->exec($sql);

There is a bug in PHP PDO https://bugs.php.net/bug.php?id=61613

db->exec('SELECT 1; invalidstatement; SELECT 2');

won't error out or return false (tested on PHP 5.5.14).

小开

Briefly, the way I have done this is:

Read the file (a db dump eg $ mysqldump db > db.sql)
```
$sql = file_get_contents(db.sql);
```

Import it using mysqli::multi_query

if ($mysqli->multi_query($sql)) {
$mysqli->close();
} else {
throw new Exception ($mysqli->error);
}

Watch out mysqli_query supports async queries. More here: http://php.net/manual/en/mysqli.multi-query.php and here https://stackoverflow.com/a/6652908/2002493

小开

I noticed that the PostgreSQL PDO driver does not allow you to run scripts separated by semicolons. In order to run a .sql file on any database using PDO it is necessary to split the statements in PHP code yourself. Here is a solution that seems to work quite well:

https://github.com/diontruter/migrate/blob/master/src/Diontruter/Migrate/SqlScriptParser.php

The referenced class has done the trick for me in a database independent way, please message me if there are any issues. Here is how you could use the script after adding it to your project:

$pdo = new PDO($connectionString, $userName, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$parser = new SqlScriptParser();
$sqlStatements = $parser->parse($fileName);
foreach ($sqlStatements as $statement) {
$distilled = $parser->removeComments($statement);
if (!empty($distilled)) {
$statement = $pdo->prepare($sql);
$affectedRows = $statement->execute();
}
}

小开

This is from a project I am working on. Basically takes any text file and extracts the SQL statements while ignoring comments and gratuitous line breaks.

<?php


/*
ingestSql(string) : string


Read the contents of a SQL batch file, stripping away comments and
joining statements that are broken over multiple lines with the goal
of producing lines of sql statements that can be successfully executed
by PDO exec() or execute() functions.


For example:
-- My SQL Batch
CREATE TABLE foo(
bar VARCHAR(80),
baz INT NOT NULL);


Becomes:
CREATE TABLE foo(bar VARCHAR(80), baz INT NOT NULL);
*/


function ingestSql($sqlFilePath=__DIR__ . "/create-db.sql") {
$sqlFile = file($sqlFilePath);
$ingestedSql = "";
$statement = "";
foreach($sqlFile as $line) {


// Ignore anything between a double-dash and the end of the line.
$commentStart = strpos($line, "--");
if ($commentStart !== false) {
$line = substr($line, 0, $commentStart);
}


// Only process non-blank lines.
if (strlen($line)) {


// Remove any leading and trailing whitespace and append what's
// left of the line to the current statement.
$line = trim($line);
$statement .= $line;


// A semi-colon ends the current statement.  Otherwise what was a
// newline becomes a single space;
if (substr($statement, -1) == ";") {
$ingestedSql .= $statement;
$statement = "\n";
}
else {
$statement .= " ";
}
}
}


return $ingestedSql;
}


?>

小开

    $sql = file_get_contents("sql.sql");

Seems to be the simplest answer

小开

PHP Code

The code I found on this page worked for me. This code can load a specified SQL file and import it into a MySQL database. I tested this code with a WordPress database exported to SQL using phpMyAdmin and it worked fine. (Scroll down to see the commented version)

<?php
$conn = new mysqli('localhost', 'root', '' , 'sql_auto_test_table');


$query = '';
$sqlScript = file('sqlFileName.sql');


foreach ($sqlScript as $line)   {


$startWith = substr(trim($line), 0 ,2);
$endWith = substr(trim($line), -1 ,1);
    

if (empty($line) || $startWith == '--' || $startWith == '/*' || $startWith == '//') {
continue;
}
    

$query = $query . $line . "/*<br>*/";
if ($endWith == ';') {
mysqli_query($conn,$query) or die('<div>Problem in executing the SQL query <b>,<br><br>' . $query. '</b><br><br>'.$conn->error.'</div>');
$query= '';
}
}
echo '<div>SQL file imported successfully</div>';
?>

Potential Fixes

I had to add the following lines at the top of the .sql file to avoid a few DEFAULT VALUE errors in some DATE columns. Alternatively, you can try executing the following queries before executing your SQL file if you receive a similar error.

SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';

In addition, substitute the violent die() function with a better error-handling mechanism.

Explanation

In case you want, I added a few comment lines to explain the behavior.

<?php
$conn = new mysqli('localhost', 'root', '' , 'db_name');


$query = ''; //Set an empty query variable to hold the query
$sqlScript = file('mySqlFile.sql'); //Set the sql file location


//Read each line of the file
foreach ($sqlScript as $line)   {


//Get the starting character and the ending character of each line
$startWith = substr(trim($line), 0 ,2);
$endWith = substr(trim($line), -1 ,1);
    

//Check for empty or comment lines. (If the line starts with --,/*,// or the line is empty, skip to the next line)
if (empty($line) || $startWith == '--' || $startWith == '/*' || $startWith == '//') {
continue;
}
    

//Add the line to the query. (Additional optional commented out <br> tag added to query for easy error identification)
$query = $query . $line . "/*<br>*/";
//If the line end with a ";" assume the last query has ended in this line
if ($endWith == ';') {
//Therefore, try to execute the query. Upon failure, display the last formed query with the SQL error message
mysqli_query($conn,$query) or die('<div>Problem in executing the SQL query <b>,<br><br>' . $query. '</b><br><br>'.$conn->error.'</div>');
//Reset the query variable and continue to loop the next lines
$query= '';
}
}
//If nothing went wrong, display a success message after looping through all the lines in the sql file
echo '<div>SQL file imported successfully</div>';


/*
If failed with an invalid DEFAULT value for a DATE column error, try adding the following lines to the top of your SQL file. Otherwise, execute these lines before executing your .sql file.
SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
*/
?>