Copy database to another database on server

Question

I have a problem. I am not very good at this. :) Let's say this is the DB anna_subscribers and i want to copy this database everytime i call this .php file to DB anna_newsletter. How can i edit this code so that it will not download the file but copy the database to another database on this server? This is the code of this file that executes the download:

<?
require_once('../../../../../initialize.php');
if(!$session->isLogedIn()) {
exit;
}
ini_set('memory_limit','16M');

$q=$db->get($db->query('SELECT sqlUrl, sqlDatabase, sqlUserName, sqlPassword, ID, importShopID, lastOrderUpdate FROM com_som_shops WHERE ID="3"'));
$dbhandle = new Database(false);
$dbhandle->open_connection(''.$q['sqlUrl'].'', ''.$crypt->decrypt($q['sqlUserName']).'', ''.$crypt->decrypt($q['sqlPassword']).'', ''.$crypt->decrypt($q['sqlDatabase']).'');   
$dbhandle->query("SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'");

$db_un_chQ = $dbhandle->query("SELECT * FROM newsletter_subscriber WHERE subscriber_status='1' AND store_id='3' AND customer_id='0'");
$string="";

while($db_un_chR=$dbhandle->fetch($db_un_chQ)) {
    $string.= $db_un_chR["subscriber_email"].",\n";
}

$dbhandle->close_connection();

header("Content-Type: text/plain; charset=utf-8");
header('Content-Disposition: attachment; filename="exportSubscrtiptionKondomanija_'.date("d.m.Y-H.i").'.txt"');

echo $string;
?>

Do i change ti with something like this:

INSERT INTO anna_newsletter..subscribers
SELECT * FROM anna_subscribers..subscribers

DB Structure: anna_subscribers has:

`ID``email``date_added`

DB Structure: anna_newsletter has:

`id``userID``email``custom_fields``list``unsubscribed``bounced``bounce_soft``complaint``l‌ast_campaing``last_ares``timestamp``join_date``confirmed``messageID`

Then I think that your anwser it's almost right: INSERT INTO anna_newsletter.subscribers SELECT * FROM anna_subscribers.subscribers — Serpes, Jan 21 '15 at 09:34
@Serpes Are you sure? But do i need to also make a connection to the new DB like: `$dbhost = 'localhost'; $dbuser = 'anna_anna01'; $dbpass = 'pass'; $dbname = 'anna_newsletter';` — Anna Simona, Jan 21 '15 at 09:38
Depend on your self, u can use sql query or create local copy from one table and insert or create table if not exist using php. — Hendra Nucleo, Jan 21 '15 at 09:38
And do you have access for both database? is it same db type? — Hendra Nucleo, Jan 21 '15 at 09:40
@Nucleo 1985 Yes i do have access to both DB. I think it is. :) It's on the same server. Just the passwords are different. — Anna Simona, Jan 21 '15 at 09:41
@AnnaSimona Ok, so its easy. So i think it can go by executing php script. What about table structure? is it exactly same ? — Hendra Nucleo, Jan 21 '15 at 09:44
Well, it depends if the both databases are in the same server. If not, I think that you have to fetch the data first and then insert in the other sever — Serpes, Jan 21 '15 at 09:44
@Nucleo 1985 No the table structures are not exactly the same. One has more tables and different names. Just a secound. — Anna Simona, Jan 21 '15 at 09:47
@Serpes The databases are on the same server, yes. :) Yes the user for both DB is anna_anna01. — Anna Simona, Jan 21 '15 at 09:49
DB: anna_subscribers has: `ID``email``date_added` DB: anna_subscribers has: `id``userID``email``custom_fields``list``unsubscribed``bounced``bounce_soft``complaint``last_campaing``last_ares``timestamp``join_date``confirmed``messageID` — Anna Simona, Jan 21 '15 at 09:51
@AnnaSimona Ok, it can be done by execute php script like i told above. Do you use google chat ? or you can send the sql file (if not contain important data) from source table & table structure from target table to my email. I can make the script for you to execute. — Hendra Nucleo, Jan 21 '15 at 09:52
Please refer `http://stackoverflow.com/questions/14065580/copy-tables-with-data-to-another-database-in-sql-server-2008` — Penny, Jan 21 '15 at 09:53
Please respond to the comments by updating the question - reading through the comments to understand the question is a bit tedious... — Neville Kuyt, Jan 21 '15 at 09:56

score 0 · Answer 1 · answered Jan 21 '15 at 09:52

0

Just specify databases in query:

 INSERT INTO DESINATION_DATABASE.subscribers SELECT * FROM SOURCE_DATABASE.subscribers

answered Jan 21 '15 at 09:52

Mikrobi

341
1
4

score 0 · Answer 2 · answered Mar 19 '18 at 13:37

You can like this:

<?php

$dblink1=mysql_connect('$ip1', '$user1', '$pass1'); // connect server 1
mysql_select_db('$database1',$dblink1);  // select database 1

$dblink1=mysql_connect('$ip2', '$user2', '$pass2'); // connect server 2 
mysql_select_db('$database2',$dblink2); // select database 2

$table='tabletest';

$tableinfo = mysql_fetch_array(mysql_query("SHOW CREATE TABLE $table  ",$dblink1)); // get structure from table on server 1

mysql_query(" $tableinfo[1] ",$dblink2); // use found structure to make table on server 2

$result = mysql_query("SELECT * FROM $table  ",$dblink1); // select all content     
while ($row = mysql_fetch_array($result, MYSQL_ASSOC) ) {       
       mysql_query("INSERT INTO $table (".implode(", ",array_keys($row)).") VALUES ('".implode("', '",array_values($row))."')",$dblink2); // insert one row into new table
}

 mysql_close($dblink1);
 mysql_close($dblink2);

 ?>

Copy database to another database on server

2 Answers2