[php] How do I import a .sql file in mysql database using PHP?

I'm trying to import a .sql file through PHP code. However, my code shows this error:

There was an error during import. Please make sure the import file is saved in the same folder as this script and check your values:

MySQL Database Name:    test
MySQL User Name:    root
MySQL Password: NOTSHOWN
MySQL Host Name:    localhost
MySQL Import Filename:  dbbackupmember.sql

And this is my code:

<?php
//ENTER THE RELEVANT INFO BELOW
$mysqlDatabaseName ='test';
$mysqlUserName ='root';
$mysqlPassword ='';
$mysqlHostName ='localhost';
$mysqlImportFilename ='dbbackupmember.sql';
//DONT EDIT BELOW THIS LINE
//Export the database and output the status to the page
$command='mysql -h' .$mysqlHostName .' -u' .$mysqlUserName .' -p' .$mysqlPassword .' ' .$mysqlDatabaseName .' < ' .$mysqlImportFilename;
exec($command,$output=array(),$worked);
switch($worked){
    case 0:
        echo 'Import file <b>' .$mysqlImportFilename .'</b> successfully imported to database <b>' .$mysqlDatabaseName .'</b>';
        break;
    case 1:
        echo 'There was an error during import. Please make sure the import file is saved in the same folder as this script and check your values:<br/><br/><table><tr><td>MySQL Database Name:</td><td><b>' .$mysqlDatabaseName .'</b></td></tr><tr><td>MySQL User Name:</td><td><b>' .$mysqlUserName .'</b></td></tr><tr><td>MySQL Password:</td><td><b>NOTSHOWN</b></td></tr><tr><td>MySQL Host Name:</td><td><b>' .$mysqlHostName .'</b></td></tr><tr><td>MySQL Import Filename:</td><td><b>' .$mysqlImportFilename .'</b></td></tr></table>';
        break;
}
?>

What am I doing wrong? The SQL file is in the same directory.

This question is related to php mysql

The answer is


You can use the mysqli multi_query function as below:

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

$mysqli = new mysqli("localhost", "root", "pass", "testdb");

/* execute multi query */
$mysqli->multi_query($sql);

<?php
$host = "localhost";
$uname = "root";
$pass = "";
$database = "demo1"; //Change Your Database Name
$conn = new mysqli($host, $uname, $pass, $database);
$filename = 'users.sql'; //How to Create SQL File Step : url:http://localhost/phpmyadmin->detabase select->table select->Export(In Upper Toolbar)->Go:DOWNLOAD .SQL FILE
$op_data = '';
$lines = file($filename);
foreach ($lines as $line)
{
    if (substr($line, 0, 2) == '--' || $line == '')//This IF Remove Comment Inside SQL FILE
    {
        continue;
    }
    $op_data .= $line;
    if (substr(trim($line), -1, 1) == ';')//Breack Line Upto ';' NEW QUERY
    {
        $conn->query($op_data);
        $op_data = '';
    }
}
echo "Table Created Inside " . $database . " Database.......";
?>

<?php
system('mysql --user=USER --password=PASSWORD DATABASE< FOLDER/.sql');
?>

Warning: mysql_* extension is deprecated as of PHP 5.5.0, and has been removed as of PHP 7.0.0. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API.
Whenever possible, importing a file to MySQL should be delegated to MySQL client.

the answer from Raj is useful, but (because of file($filename)) it will fail if your mysql-dump not fits in memory

If you are on shared hosting and there are limitations like 30 MB and 12s Script runtime and you have to restore a x00MB mysql dump, you can use this script:

it will walk the dumpfile query for query, if the script execution deadline is near, it saves the current fileposition in a tmp file and a automatic browser reload will continue this process again and again ... If an error occurs, the reload will stop and an the error is shown ...

if you comeback from lunch your db will be restored ;-)

the noLimitDumpRestore.php:

// your config
$filename = 'yourGigaByteDump.sql';
$dbHost = 'localhost';
$dbUser = 'user';
$dbPass = '__pass__';
$dbName = 'dbname';
$maxRuntime = 8; // less then your max script execution limit


$deadline = time()+$maxRuntime; 
$progressFilename = $filename.'_filepointer'; // tmp file for progress
$errorFilename = $filename.'_error'; // tmp file for erro

mysql_connect($dbHost, $dbUser, $dbPass) OR die('connecting to host: '.$dbHost.' failed: '.mysql_error());
mysql_select_db($dbName) OR die('select db: '.$dbName.' failed: '.mysql_error());

($fp = fopen($filename, 'r')) OR die('failed to open file:'.$filename);

// check for previous error
if( file_exists($errorFilename) ){
    die('<pre> previous error: '.file_get_contents($errorFilename));
}

// activate automatic reload in browser
echo '<html><head> <meta http-equiv="refresh" content="'.($maxRuntime+2).'"><pre>';

// go to previous file position
$filePosition = 0;
if( file_exists($progressFilename) ){
    $filePosition = file_get_contents($progressFilename);
    fseek($fp, $filePosition);
}

$queryCount = 0;
$query = '';
while( $deadline>time() AND ($line=fgets($fp, 1024000)) ){
    if(substr($line,0,2)=='--' OR trim($line)=='' ){
        continue;
    }

    $query .= $line;
    if( substr(trim($query),-1)==';' ){
        if( !mysql_query($query) ){
            $error = 'Error performing query \'<strong>' . $query . '\': ' . mysql_error();
            file_put_contents($errorFilename, $error."\n");
            exit;
        }
        $query = '';
        file_put_contents($progressFilename, ftell($fp)); // save the current file position for 
        $queryCount++;
    }
}

if( feof($fp) ){
    echo 'dump successfully restored!';
}else{
    echo ftell($fp).'/'.filesize($filename).' '.(round(ftell($fp)/filesize($filename), 2)*100).'%'."\n";
    echo $queryCount.' queries processed! please reload or wait for automatic browser refresh!';
}

If you need a User Interface and if you want to use PDO

Here's a simple solution

<form method="post" enctype="multipart/form-data">
    <input type="text" name="db" placeholder="Databasename" />
    <input type="file" name="file">
    <input type="submit" name="submit" value="submit">
</form>

<?php

if(isset($_POST['submit'])){
    $query = file_get_contents($_FILES["file"]["name"]);
    $dbname = $_POST['db'];
    $con = new PDO("mysql:host=localhost;dbname=$dbname","root","");
    $stmt = $con->prepare($query);
    if($stmt->execute()){
        echo "Successfully imported to the $dbname.";
    }
}
?>

Definitely working on my end. Worth a try.


Solution special chars

 $link=mysql_connect($dbHost, $dbUser, $dbPass) OR die('connecting to host: '.$dbHost.' failed: '.mysql_error());
mysql_select_db($dbName) OR die('select db: '.$dbName.' failed: '.mysql_error());

//charset important
mysql_set_charset('utf8',$link);

As we all know MySQL was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0 ref so I have converted accepted answer to mysqli.

<?php
// Name of the file
$filename = 'db.sql';
// MySQL host
$mysql_host = 'localhost';
// MySQL username
$mysql_username = 'root';
// MySQL password
$mysql_password = '123456';
// Database name
$mysql_database = 'mydb';

$connection = mysqli_connect($mysql_host,$mysql_username,$mysql_password,$mysql_database) or die(mysqli_error($connection));

// 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
    mysqli_query($connection,$templine) or print('Error performing query \'<strong>' . $templine . '\': ' . mysqli_error($connection) . '<br /><br />');
    // Reset temp variable to empty
    $templine = '';
}
}
 echo "Tables imported successfully";
?>

I Thing you can Try this Code, It's Run for my Case:

_x000D_
_x000D_
<?php_x000D_
_x000D_
$con = mysqli_connect('localhost', 'root', 'NOTSHOWN', 'test');_x000D_
_x000D_
$filename = 'dbbackupmember.sql';_x000D_
$handle = fopen($filename, 'r+');_x000D_
$contents = fread($handle, filesize($filename));_x000D_
_x000D_
$sql = explode(";", $contents);_x000D_
foreach ($sql as $query) {_x000D_
 $result = mysqli_query($con, $query);_x000D_
 if ($result) {_x000D_
  echo "<tr><td><br></td></tr>";_x000D_
  echo "<tr><td>".$query."</td></tr>";_x000D_
  echo "<tr><td><br></td></tr>";_x000D_
 }_x000D_
}_x000D_
_x000D_
fclose($handle);_x000D_
echo "success";_x000D_
_x000D_
_x000D_
?>
_x000D_
_x000D_
_x000D_


If you are using PHP version 7 or higher, try below script,

// Name of the file
$filename = 'sql.sql';
// MySQL host
$mysql_host = 'localhost';
// MySQL username
$mysql_username = 'username';
// MySQL password
$mysql_password = 'password';
// Database name
$mysql_database = 'database';
// Connect to MySQL server
$con = @new mysqli($mysql_host,$mysql_username,$mysql_password,$mysql_database);
// Check connection
if ($con->connect_errno) {
   echo "Failed to connect to MySQL: " . $con->connect_errno;
   echo "<br/>Error: " . $con->connect_error;
}
// 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
$con->query($templine) or print('Error performing query \'<strong>' . $templine . '\': ' . $con->error() . '<br /><br />');
    // Reset temp variable to empty
    $templine = '';
}
}
echo "Tables imported successfully";
$con->close($con);

function restoreDatabase($db_name,$file_path)
{

    //checking valid extension file
    $path_parts = pathinfo($file_path);
    $ext_file = $path_parts['extension'];
    $filename = $path_parts['basename'];

    if($ext_file == "sql")
    {
         $c = new Config();

         $confJson = $c->getConfig();
         $conf = json_decode($confJson);

         $dbhost   = "127.0.0.1";
         $dbuser   = $conf->db_username;  
         $dbpwd    = $conf->db_password;
         $dbname   = $db_name;   

         $dumpfile = $file_path;

         $is_file = file_exists($file_path);
         if($is_file == TRUE)
         {

             //passthru("/usr/bin/mysqldump --opt --host=$dbhost --user=$dbuser --password=$dbpwd $dbname < $dumpfile");

             //passthru("tail -1 $dumpfile");
             system('mysql --user='.$dbuser.' --password='.$dbpwd.' '.$db_name.' < '.$file_path);
             return "Database was restored from $filename ";

         }
         else 
         {
             return "Restore database was aborted due ".$filename." does not exist!";
         }


    }
    else
    {
        return "Invalid file format.Require sql file to restore this ".$db_name." database. ".$filename." is not sql file format\n(eg. mybackupfile.sql).";
    }
}

// Import data 
$filename = 'database_file_name.sql';
import_tables('localhost','root','','database_name',$filename);

function import_tables($host,$uname,$pass,$database, $filename,$tables = '*'){
    $connection = mysqli_connect($host,$uname,$pass)
    or die("Database Connection Failed");
    $selectdb = mysqli_select_db($connection, $database) or die("Database could not be selected"); 

$templine = '';
$lines = file($filename); // Read entire file

foreach ($lines as $line){
    // Skip it if it's a comment
    if (substr($line, 0, 2) == '--' || $line == '' || substr($line, 0, 2) == '/*' )
        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) == ';')
        {
            mysqli_query($connection, $templine)
            or print('Error performing query \'<strong>' . $templine . '\': ' . mysqli_error($connection) . '<br /><br />');
            $templine = '';
        }
    }
    echo "Tables imported successfully";
}




// Backup database from php script
backup_tables('hostname','UserName','pass','databses_name');

function backup_tables($host,$user,$pass,$name,$tables = '*'){
    $link = mysqli_connect($host,$user,$pass);
    if (mysqli_connect_errno()){
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    mysqli_select_db($link,$name);
    //get all of the tables
    if($tables == '*'){
        $tables = array();
        $result = mysqli_query($link,'SHOW TABLES');
        while($row = mysqli_fetch_row($result))
        {
            $tables[] = $row[0];
        }
    }else{
        $tables = is_array($tables) ? $tables : explode(',',$tables);
    }

    $return = '';
    foreach($tables as $table)
    {
        $result = mysqli_query($link,'SELECT * FROM '.$table);
        $num_fields = mysqli_num_fields($result);
        $row_query = mysqli_query($link,'SHOW CREATE TABLE '.$table);
        $row2 = mysqli_fetch_row($row_query);
        $return.= "\n\n".$row2[1].";\n\n";

        for ($i = 0; $i < $num_fields; $i++) 
        {
            while($row = mysqli_fetch_row($result))
            {
                $return.= 'INSERT INTO '.$table.' VALUES(';
                for($j=0; $j < $num_fields; $j++) 
                {
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = str_replace("\n", '\n', $row[$j]);
                    if (isset($row[$j])) { 
                        $return.= '"'.$row[$j].'"' ; 
                    } else { 
                        $return.= '""'; 
                    }
                    if ($j < ($num_fields-1)) { $return.= ','; }
                }
                $return.= ");\n";
            }
        }
        $return.="\n\n\n";
    }

    //save file
    $handle = fopen('backup-'.date("d_m_Y__h_i_s_A").'-'.(md5(implode(',',$tables))).'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);
}

I have Test your code, this error shows when you already have the DB imported or with some tables with the same name, also the Array error that shows is because you add in in the exec parenthesis, here is the fixed version:

<?php
//ENTER THE RELEVANT INFO BELOW
$mysqlDatabaseName ='test';
$mysqlUserName ='root';
$mysqlPassword ='';
$mysqlHostName ='localhost';
$mysqlImportFilename ='dbbackupmember.sql';
//DONT EDIT BELOW THIS LINE
//Export the database and output the status to the page
$command='mysql -h' .$mysqlHostName .' -u' .$mysqlUserName .' -p' .$mysqlPassword .' ' .$mysqlDatabaseName .' < ' .$mysqlImportFilename;
$output=array();
exec($command,$output,$worked);
switch($worked){
    case 0:
        echo 'Import file <b>' .$mysqlImportFilename .'</b> successfully imported to database <b>' .$mysqlDatabaseName .'</b>';
        break;
    case 1:
        echo 'There was an error during import.';
        break;
}
?> 

I use this code and RUN SUCCESS FULL:

$filename = 'apptoko-2016-12-23.sql'; //change to ur .sql file
                            $handle = fopen($filename, "r+");
                            $contents = fread($handle, filesize($filename));

                            $sql = explode(";",$contents);// 
                            foreach($sql as $query){
                                $result=mysql_query($query);
                                if ($result){
                                 echo '<tr><td><BR></td></tr>';
                                 echo '<tr><td>' . $query . ' <b>SUCCESS</b></td></tr>';
                                 echo '<tr><td><BR></td></tr>';
                                }
                            }
                            fclose($handle);

Grain Script is superb and save my day. Meanwhile mysql is depreciated and I rewrote Grain answer using PDO.

    $server  =  'localhost'; 
    $username   = 'root'; 
    $password   = 'your password';  
    $database = 'sample_db';

    /* PDO connection start */
    $conn = new PDO("mysql:host=$server; dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);         
    $conn->exec("SET CHARACTER SET utf8");     
    /* PDO connection end */

    // your config
    $filename = 'yourFile.sql';

    $maxRuntime = 8; // less then your max script execution limit


    $deadline = time()+$maxRuntime; 
    $progressFilename = $filename.'_filepointer'; // tmp file for progress
    $errorFilename = $filename.'_error'; // tmp file for erro



    ($fp = fopen($filename, 'r')) OR die('failed to open file:'.$filename);

    // check for previous error
    if( file_exists($errorFilename) ){
        die('<pre> previous error: '.file_get_contents($errorFilename));
    }

    // activate automatic reload in browser
    echo '<html><head> <meta http-equiv="refresh" content="'.($maxRuntime+2).'"><pre>';

    // go to previous file position
    $filePosition = 0;
    if( file_exists($progressFilename) ){
        $filePosition = file_get_contents($progressFilename);
        fseek($fp, $filePosition);
    }

    $queryCount = 0;
    $query = '';
    while( $deadline>time() AND ($line=fgets($fp, 1024000)) ){
        if(substr($line,0,2)=='--' OR trim($line)=='' ){
            continue;
        }

        $query .= $line;
        if( substr(trim($query),-1)==';' ){

            $igweze_prep= $conn->prepare($query);

            if(!($igweze_prep->execute())){ 
                $error = 'Error performing query \'<strong>' . $query . '\': ' . print_r($conn->errorInfo());
                file_put_contents($errorFilename, $error."\n");
                exit;
            }
            $query = '';
            file_put_contents($progressFilename, ftell($fp)); // save the current file position for 
            $queryCount++;
        }
    }

    if( feof($fp) ){
        echo 'dump successfully restored!';
    }else{
        echo ftell($fp).'/'.filesize($filename).' '.(round(ftell($fp)/filesize($filename), 2)*100).'%'."\n";
        echo $queryCount.' queries processed! please reload or wait for automatic browser refresh!';
    }