CODEWITHSUNDEEP
phpmysql
Muiter
Muiter

Reputation: 1520

MySQL copy table to different database with same credentials

I am using the standard MySQL functions that PHP has built in for copying one table to an new table. This works perfect when the tables are in the same database. But I want to copy it to another databasename with same user and password.

Any suggestions how to achive this? (Since $database can only contain 1 databasename)

Error shown is Table 'torzahttp_rsw.torzahttp_rsw.kwaliteit' doesn't exist torzahttp_rswis the database name, and kwaliteit the table name. Why is the databasename used twice?

// Create a new MySQL database connection
if (!$con = new mysqli('localhost', $username, $password, $database)) {
    die('An error occurred while connecting to the MySQL server!<br><br>' . $con->connect_error);
}

// Create an array of MySQL queries to run
$sql = array(
    'DROP TABLE IF EXISTS `backup_db.backup_table`;',
    'CREATE TABLE `backup_db.backup_table` SELECT * FROM `live_db.live_table`'
);

// Run the MySQL queries
if (sizeof($sql) > 0) {
    foreach ($sql as $query) {
        if (!$con->query($query)) {
            die('A MySQL error has occurred!<br><br>' . $con->error);
        }
    }
}

$con->close();

Upvotes: 2

Views: 655

Answers (2)

miken32
miken32

Reputation: 42714

From the MySQL manual:

If any components of a multiple-part name require quoting, quote them individually rather than quoting the name as a whole. For example, write `my-table`.`my-column`, not `my-table.my-column`.

Your code:

$sql = array(
    'DROP TABLE IF EXISTS `backup_db.backup_table`;',
    'CREATE TABLE `backup_db.backup_table` SELECT * FROM `live_db.live_table`'
);

should look like this:

$sql = array(
    'DROP TABLE IF EXISTS `backup_db`.`backup_table`;',
    'CREATE TABLE `backup_db`.`backup_table` SELECT * FROM `live_db`.`live_table`'
);

Or, just drop the backticks altogether.

Upvotes: 1

Muiter
Muiter

Reputation: 1520

I have used this solution to create an mysql dump first and then processioning it.

//DB 
$user = 'xxxxx';
$password = 'xxxxx';
$host = 'localhost';
$database_old = 'old_db';
$database_new = 'new_db';

$table = 'kwaliteit';
$file = 'kwaliteit.sql';

exec('mysqldump --user='.$user.' --password='.$password.' --host='.$host.' '.$database_old.' '.$table.' > uploads/'.$file);

exec('mysql --user='.$user.' --password='.$password.' --host='.$host.' '.$database_new.' < uploads/'.$file);

unlink('uploads/'.$file);

Upvotes: 0

Related Questions