Loading .sql files from within PHP

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.

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

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.

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

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

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

Seems to be the simplest answer

I use this all the time:

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

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

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

?>

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