MySQL copy table to different database with same credentials

Question

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

Although you connect to a database, you can still access other databases using the method you currently do ( `databasename.tablename` ) as long as the user you connect as has the rights to that database and the database is on the same server (AFAIK) — Nigel Ren, Jan 28 '20 at 07:14
Does this answer your question? [How to copy table to another database](https://stackoverflow.com/questions/3932608/how-to-copy-a-table-from-one-mysql-database-to-another-mysql-database) — Daniel Protopopov, Jan 28 '20 at 07:53
@DanielProtopopov no it does not. I need an php script to copy certain tables. — Muiter, Jan 28 '20 at 08:11

score 1 · Answer 1 · answered Jan 28 '20 at 19:18

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.

score 0 · Accepted Answer · answered Jan 28 '20 at 18:45

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

MySQL copy table to different database with same credentials

2 Answers2