CODEWITHSUNDEEP
phpmysql
msalman
msalman

Reputation: 981

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.

Upvotes: 88

Views: 223402

Answers (16)

Waqar Alamgir
Waqar Alamgir

Reputation: 9968

If someone is using Laravel, then we can create a command for it like this:

namespace App\Console\Commands;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;
use Illuminate\Console\Command;

class ImportData extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'import:data';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Imports data';

    public function debug($msg): void
    {
        echo date('Y-m-d H:i:s '), $msg, '.', PHP_EOL;
    }

    /**
     * Execute the console command.
     */
    public function handle(): void
    {
        $this->debug('Data Import begin');
        $sql_dump = File::get( storage_path().'/data.sql');
        DB::connection()->getPdo()->exec($sql_dump);
        $this->debug('Data Imported');
    }
}

Copy the data to storage path. To import, run this command:

php artisan import:data

Upvotes: 0

Ram Kumawat
Ram Kumawat

Reputation: 21

Import sql file data with help from php code

// sql file path
$filename = 'database_file_name.sql';

// Add this function into your helper file and add any where that you want to use
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";
    }

// Calling import menthod
backup_tables('database_hostname','database_username','database_password','database_name');
// backup_tables function have 4paramater - database hostname, username, password and database name that you want import sql data.

If you want to backup/export database from php script

// Call the backup_tables for export the database
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);
    }

Upvotes: 0

Kazim Noorani
Kazim Noorani

Reputation: 323

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

Upvotes: 2

Grain
Grain

Reputation: 554

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

Upvotes: 39

ManojGeek
ManojGeek

Reputation: 2001

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.

I have got another way to do this, try this

<?php

// Name of the file
$filename = 'churc.sql';
// MySQL host
$mysql_host = 'localhost';
// MySQL username
$mysql_username = 'root';
// MySQL password
$mysql_password = '';
// Database name
$mysql_database = 'dump';

// Connect to MySQL server
mysql_connect($mysql_host, $mysql_username, $mysql_password) or die('Error connecting to MySQL server: ' . mysql_error());
// Select database
mysql_select_db($mysql_database) or die('Error selecting MySQL database: ' . mysql_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
    mysql_query($templine) or print('Error performing query \'<strong>' . $templine . '\': ' . mysql_error() . '<br /><br />');
    // Reset temp variable to empty
    $templine = '';
}
}
 echo "Tables imported successfully";
?>

This is working for me

Upvotes: 152

RahulD
RahulD

Reputation: 757

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

Upvotes: 59

Huzoor Bux
Huzoor Bux

Reputation: 1038

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

Upvotes: -1

JJ Labajo
JJ Labajo

Reputation: 364

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.

Upvotes: 2

Mohd Zamri Bin Mat Jusoh
Mohd Zamri Bin Mat Jusoh

Reputation: 19

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

Upvotes: -1

user8487829
user8487829

Reputation:

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

<?php

$con = mysqli_connect('localhost', 'root', 'NOTSHOWN', 'test');

$filename = 'dbbackupmember.sql';
$handle = fopen($filename, 'r+');
$contents = fread($handle, filesize($filename));

$sql = explode(";", $contents);
foreach ($sql as $query) {
	$result = mysqli_query($con, $query);
	if ($result) {
		echo "<tr><td><br></td></tr>";
		echo "<tr><td>".$query."</td></tr>";
		echo "<tr><td><br></td></tr>";
	}
}

fclose($handle);
echo "success";


?>

Upvotes: -2

Igweze Ebele Mark
Igweze Ebele Mark

Reputation: 89

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

Upvotes: 5

rai
rai

Reputation: 13

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

Upvotes: -2

Dedi Koswara
Dedi Koswara

Reputation: 47

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

Upvotes: -2

Patel Yatin
Patel Yatin

Reputation: 149

<?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.......";
?>

Upvotes: 12

Leonardo Filipe
Leonardo Filipe

Reputation: 1762

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

Upvotes: 10

Skylex
Skylex

Reputation: 75

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

Upvotes: 3

Related Questions