Loading .sql files from within PHP

Question

I'm creating an installation script for an application that I'm developing and need to create databases dynamically from within PHP. I've got it to create the database but now I need to load in several .sql files. I had planned to open the file and mysql_query it a line at a time - until I looked at the schema files and realised they aren't just one query per line.

So, how do I load an sql file from within PHP (as phpMyAdmin does with its import command)?

Urinate

Not least the phpMyAdmin's source itself :) – Thaumatology

@Anonymous : I was looking for 'the best' way. This place is supposed to be a one stop shop for answers (for the future as well) and therefore I feel asking questions answered elsewhere is quite fine. I did, however, look elsewhere first and could not find a great answer. – Urinate

RE: phpMyAdmin - it's source is somewhat helpful but very dependant on it's other functionality and is not suitable to my very strict timeframe and complexity requirement. – Urinate

To all those who argue in favor of answers apart from the accepted one: The question was how to load script from within PHP. The LOAD DATA solution solves the problem on MySQL side. Also, MySQL could be running on another machine and have no access to file system where PHP script is executed which should be considered. – Consistence

Execute mysql .sql dump files via php mysqli – Halfcock

Answer 1

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.

Conquian

Good point that hosted environments are more restrictive. The OP's question does not mention that the application needs to deploy in hosted environments. Hmm. The question of running SQL scripts in PHP comes up often enough that it'd be a great little project. – Ha

Yeah, I was gonna say that - you can't expect people to assume that you are in the most restrictive environment ever. Especially with virtual machines being offered all over everyone can have their own server at relatively little cost. – Bethea

I still don't see why he can't just read the .sql file into a string and execute it with PDO or mysqli. This is how I do it. PDO and mysqli support multiple queries. Admittedly, I haven't run any HUGE .sql files yet, but couldn't you just increase or remove PHP's max script exec time? – Hurling

phpMyAdmin's code base is sh*t. The file that does the import (phpMyAdmin/library/import/sql.php) makes heavy use of globals and many of comments have gramatical errors. Do you know of any other better examples. – Angelynanger

Checkout the answer below by Luis Granja https://mcmap.net/q/242806/-loading-sql-files-from-within-php – Alessi

Answer 2

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

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

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

Barth

I am using this answer. It worked well for me on a 643kb script. SO far so good. – Alessi

How about when the script is 643mb? Or in any case, larger than max_allowed_packet? – Ha

Perfect! Works for me with 1MB file so far. – Owing

This mostly worked for me. It doesn't seem to import the stored procedures or triggers, but does fine with DROP, TRUNCATE, SELECT, INSERT, UPDATE. – Brainpan

Is there any way to show errors concerning any of the SQL entries? $qr may equal false but no specific errors are given if there is a problem with any of the commands given in the file.sql, – Incomer

Based on my tests, this will work even if you have comments in your .sql files. I'm relieved I don't have to manually parse them out. @DawsonIrvine A suggestion: use transactions. – Obala

Answer 3

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                : [email protected]
*
*     $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');
}

Everything

This should be the accepted one. Worked like a charm, thanks. – Centime

This is the best solution to this problem I have come across – Goncourt

thanx to The phpBB Group and thank you Abu Sadat that you managed to notice that this piece of code is reusable for other projects as well. thanx again – Nitrile

Thanks!Works fine on my side too! Incase your mysql are deprecated, just change to mysqli function. php.net/manual/en/mysqli.query.php – Mayworm

Note that PhpBB is GPL-licensed. So if you use this code in your project, you must also release your project as GPL-licensed. – Shelton

@DewiMorgan just upload your database and than erase it will not effect you for any license issue. However its a very old solution of mine and recommending you to use PDO. – Everything

Answer 4

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:

• Running MySQL *.sql files in PHP
• is it possible to call a sql script from a stored procedure in another sql script?
• PHP: multiple SQL queries in one mysql_query statement

Ha

Answer 5

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

Own

@Graben Thanks. I was looking for solution and wasn't able to find any that can import big SQL files except Sypex Dumper, but it cannot be used in your own projects as it's portable ready-to-use solution. So I had to write my own and post it here, but question was asked a long time before my post :). – Own

Answer 6

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.

Acceptance

Answer 7

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).

Merilynmeringue

As I've learnt recently, this is because you need to use PDOStatement::nextRowset – Consubstantiation

Answer 8

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

Carducci

FWIW, both phpMyBackup and phpMyAdmin are licensed under GPL. If you 'borrow' any of their code, you are obliged to make your own project GPL as well. – Ha

Yes, I agree. You do have a good point, but in the spirit of GPL, I would imagine just peeking at how others did it is still a valid option if one has to implement such a feature on his own . One sometimes have to re-create the wheel to circumvent GPL restrictions, for example! Still, it often is worth it in my opinion, as not all wheels are created equal. In this case a simple console 10-15 liner script could do this. – Gyre

Sure, it makes your code GPL, but the GPL doesn't matter if you aren't distributing your software - e.g. most web applications. (The AGPL being a notable exception, though AFAIK it's never been tested in court.) – Miscellaneous

Answer 9

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

Maladjusted

does not work if apache and mysql is different servers – Schoening

Answer 10

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 = '';
        }
    }
}

Vaccine

This skips lines that have comments at the end, but a real SQL statement before the comment on the same line. Also, SQL scripts support comments in /* */ format. And the statement delimiter isn't always ;. – Ha

Nice simple solution when you have a relatively bare file and need a quick simple parser. I had to modify the concatenation by adding a space to the beginning of each line to handle situations where a multi-line statement didn't have whitespace (e.g. ON UPDATE CASCADE)\nENGINE = InnoDB; parses, but CASCADE)\nENGINE = InnoDB\nCOMMENT = 'stuff'; does not) – Rainier

Answer 11

Briefly, the way I have done this is:

1. Read the file (a db dump eg $ mysqldump db > db.sql)

   $sql = file_get_contents(db.sql);
   

2. 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://mcmap.net/q/176523/-is-mysqli_multi_query-asynchronous

Chaunceychaunt

Answer 12

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.

Fluency

That won't always work.. What if you have a query like.. SELECT example FROM blah WHERE something = "something;" – Slog

if (substr(rtrim($query, -1) == ';') { is incorrect. It should be: if (substr(rtrim($query), -1) == ';') { – Urinate

file() reads the file split into lines just fine, and the code is a lot cleaner. – Hardison

The problem with file() is it reads an entire file into memory all at once, which is not ideal for large files. – Fluency

Answer 13

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();
    }
}

Borries

Answer 14

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)..

Slog

Unfortunately, mysql_query will only execute one query at a time ;) – Carducci

$query="SELECT * FROM posts LIMIT 1; SELECT * FROM posts LIMIT 1"; mysql_query($query); seems to run fine..? I guess you can't grab the results of each query, but if you're just loading the .sql file, surely all you need to check for is the query erroring? – Slog

Answer 15

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 = "";
    }
  }
 }

Scatterbrain

will drop on some like this: select id,any from some where any=='--' – Delubrum

Answer 16

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 = "";
  }
 }
}

Angelaangele

This skips lines that have comments at the end, but a real SQL statement before the comment on the same line. Also, SQL scripts support comments in /* */ format. And the statement delimiter isn't always ;. – Ha

This works well with views, functions, insert,alter... but not for all cases, such as when in your table you has rows with HTML with character " ; ", but this can be updated.. thanks i will try to update and repost... – Ferric

Answer 17

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);

Celadon

Answer 18

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

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

Globule

Answer 19

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

Seems to be the simplest answer

Solid

It is indeed the simplest answer. But people here want to complicate their lives. Blocking users from posting questions is the only thing they know to do best ! – Pauperism

Answer 20

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.

Hedjaz

I just happened to read the code I was referring to in this question the other day so it's fresh. We ended up just reading until we encountered an ; and executing that statement, noting in the comments that it should be improved. The project didn't go anywhere so we didn't come up with a better solution than that. – Urinate

Answer 21

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

Misspell

Answer 22

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);

Contaminant

Answer 23

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");

Tungus

Answer 24

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. ;-)

Aleuromancy

Answer 25

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);

Ashurbanipal

Answer 26

I use this all the time:

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

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

Mastrianni

This will work in most cases, but not if a string contains a ; character. – Urinate

Or if the .sql file is larger than your PHP's memory limit, or if the .sql file contains some client builtin commands, or a bunch of other cases. – Ha

Answer 27

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 = '';
}
}

?>

Rustcolored

Answer 28

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;
}
?>

Burdette

Answer 29

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.

Limbic

Answer 30

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;
  }

?>

Resupine

Answer 31

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';
*/
?>

Coshow