bagaimana cara mengimpor .file sql ke dalam database mysql menggunakan php
bagaimana saya bisa melakukannya tolong bantu saya untuk memperbaiki masalah ini terima kasih
kode ini menunjukkan kesalahan ini..
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
saya menggunakan kode ini
<?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;
}
?>
Aku punya cara lain untuk melakukan hal ini, Cobalah ini
<?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";
?>
Ini bekerja untuk saya, Good Luck
jawaban dari Raj berguna, tapi (karena file($filename)) itu akan gagal jika anda mysql-dump tidak cocok di memori
Jika anda di shared hosting ada keterbatasan seperti 30 MB dan 12s Script runtime dan anda harus mengembalikan x00MB mysql dump, anda dapat menggunakan script ini:
itu akan berjalan dumpfile query untuk melakukan query, jika eksekusi script batas waktu dekat, menghemat arus fileposition di tmp file dan otomatis reload browser akan melanjutkan proses ini lagi dan lagi ... Jika terjadi kesalahan, reload akan berhenti dan kesalahan ditampilkan ...
jika anda kembali dari makan siang anda db akan dikembalikan ;-)
the noLimitDumpRestore.php: <?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!';
}
Anda dapat menggunakan mysqli multi_query fungsi sebagai berikut:
$sql = file_get_contents('mysqldump.sql');
$mysqli = new mysqli("localhost", "root", "pass", "testdb");
if (mysqli_connect_errno()) { /* check connection */
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
/* execute multi query */
if ($mysqli->multi_query($sql)) {
echo "success";
} else {
echo "error";
}
<?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.......";
?>
It's layak disebut Adminer naskah. It's database managemenet alat dalam satu file.
Hanya drop file php ke server anda melalui FTP atau sebaliknya dan anda memiliki penuh GUI di mana anda dapat mengimpor database, menjalankan perintah-perintah sql dll. Bagus!
Biji-bijian Script super dan menyimpan hari saya. Sedangkan mysql adalah disusutkan dan saya menulis ulang biji-Bijian jawaban menggunakan 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!';
}
Berikut ini's solusi sederhana
<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.";
}
}
?>
Pasti bekerja pada akhir saya. Patut dicoba.
Saya telah Menguji kode anda, hal ini menunjukkan kesalahan ketika anda sudah memiliki DB impor atau beberapa tabel dengan nama yang sama, juga Array kesalahan yang menunjukkan adalah karena anda tambahkan di dalam exec kurung, di sini adalah versi tetap:
<?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;
}
?>
// 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);
}
Solusi khusus karakter
$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);
Aku Hal yang anda dapat Mencoba Kode ini, It's Jalankan untuk Kasus saya:
<?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";
?>
Saya menggunakan kode ini dan MENJALANKAN SUKSES PENUH:
$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);
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).";
}
}
Saya menggunakan ini dan itu bekerja untuk saya
Saya pikir ini adalah yang terpendek kode
$user = "root";
$pass = "";
$host = "localhost";
$dbname = "dbname";
$sql_file = fopen('database.sql', 'r');
$sql = fread($sql_file, filesize('database.sql'));
$conn = new PDO("mysql:host=" . $host . ";dbname=" . $dbname , $user, $pass);
$conn->exec($sql);