how to import .sql file in mysql database using php

    |
  • Added:
  • |
  • In: Basic PHP

how to import .sql file in mysql database using php

how can i do this please help me to fix this issue thanks

this code showing 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 

i am using this 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; } ?> 
This Question Has 9 Answeres | Orginal Question | msalman
// 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); } 

It's worth mentioning the Adminer script. It's a database managemenet tool in a single file.

Simply drop the php file onto your server via FTP or otherwise and you have a full GUI where you can import a database, run sql commands etc. Nice!

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!'; } 
<?php system('mysql --user=USER --password=PASSWORD DATABASE< FOLDER/.sql'); ?> 

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

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, Good Luck

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

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

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

Search
I am...

Sajjad Hossain

I have five years of experience in web development sector. I love to do amazing projects and share my knowledge with all.

NEED HOSTING?
Are you searching for Good Quality hosting?
You can Try It!
Connect Social With PHPAns
Top