Importing a sql file into a remote mysql db with php

Question

New to php and mysql, I am trying to insert a exported sql file into a remote DB. I am try this code from 1and1 but it is not working.

$create_install_db_server = 'testdb.example.com';
$create_install_db_username = 'test123';
$create_install_db_password = 'test789';
$create_install_db_name = 'test';
$sqlfile = '/home/path/to/localsql.sql';

$command='mysql -h' . $create_install_db_server .' -u' . $create_install_db_username .' -p' . $create_install_db_password .' ' . $create_install_db_name .' < ' . $sqlfile;
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.';
        break;
}

The code doesn't show any specific error, It's just "There was an error during import.", Anyway I can get specific error message? — Srikanth Koneru
– Srikanth Koneru, Commented Jan 5, 2016 at 5:10
That is the echo your code is doing...but why not echo the error you're getting from running the command? — wogsland
– wogsland, Commented Jan 5, 2016 at 5:14
The $output is an empty array, so I am not getting any details about the error. — Srikanth Koneru
– Srikanth Koneru, Commented Jan 5, 2016 at 5:44

Community · Accepted Answer · 2017-05-23 11:53:41Z

2

@srikanth, I have an another way to do this, try this one

<?php
// Name of the file
$filename = 'churc.sql';
// MySQL host
$mysql_host = 'testdb.example.com';
// MySQL username
$mysql_username = 'test123';
// MySQL password
$mysql_password = 'test789';
// Database name
$mysql_database = 'Test';

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

?>

Hope this helps you. :)

Note: mysql usage is deprecated, instead you can make use of Mysqli,PDO like below.

$db = new PDO($mysql_host, $mysql_username, $mysql_password);

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

$qr = $db->exec($sql);

for executing big sql files my above answer is not enough so i suggest you to take a look at @Abu sadat answer here

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Jan 5, 2016 at 7:21

vrn53593

3061 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Srikanth Koneru Over a year ago

If the sql file is huge, which option is better? The exec() or yours?

vrn53593 Over a year ago

Good question @srikanth.. I usually prefer exec() for big files. You can look at this explanation example

vrn53593 Over a year ago

I have edited my answer according to your question, go through it @srikanth

AkshayS · Accepted Answer · 2016-01-05 05:23:08Z

-1

Try the following things

Do an echo of the command before you try to execute it
Try running the command on the terminal - check if this is successful
If it is successful, replace your exec with a simple command (like 'touch /tmp/abc.txt') and check if a file is getting created.

By doing this, you are trying to find out if there is a problem with your mysql command or there is an issue with the exec function in php

answered Jan 5, 2016 at 5:23

AkshayS

341 bronze badge

1 Comment

AkshayS Over a year ago

If your command line query works and the php exec does not work... open your terminal and type visudo - find the line 'requiretty' and replace it with '!requiretty'

Collectives™ on Stack Overflow

Importing a sql file into a remote mysql db with php

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related