Page 1 of 1
Copying data from database to another
Posted: Tue Apr 11, 2006 9:51 pm
by irisblaze
hey I know this is a stupid question, but I don't know how to do it

anyway here's the problem, I have 2 databases each one has its own tables and structures, so I can't just use phpmyadmin to do it, the only thing to do is to create a code for this on php, I tried to make 2 connections, 1 for each database but it didn't work, using mysql_select_db.
so the current solution im working on now is to store the data from the first database in arrays, and after i'm done with storing the data, i run another loop to store the data from the arrays to the other database. but guess what I have a table with more than 20000 records o.O each record have about 12 columns, so i need 12 arrays each one will store 20000 value, it's not a professional solution as you see.
can you tell me how to connect 2 different databases at the same time so I store the values in one variable only?
and is it safe to store 20000X12 = 240000 value in the memory at the same time? (Stupid question, i know it's not)
thanx in advance

confused
Posted: Wed Apr 12, 2006 2:34 am
by dibyendrah
I'm confused that why it is necessary to store all records in array...
If the two database has same table structure, you can either syncronize the database using SqlYog. If you don't want to syncronize, then just make the two connection to the database and while executing query just do it in both connection by selecting the database one by one.
Code: Select all
$conn1 = mysql_connect("1st_host_name", "username", "password");
$conn1 = mysql_connect("2nd_host_name", "username", "password");
$sql = "insert into sometable(field_names') values(field_values)";
mysql_select_db("database_of_1st_host", $conn1);
mysql_query($sql, $conn1);
mysql_select_db("database_of_2nd_host", $conn2);
mysql_query($sql, $conn2);
I think this is what you are looking for....OR I might have misunderstood.
Dibyendra

Posted: Wed Apr 12, 2006 2:40 am
by irisblaze
@ dibyendrah
If the two database has same table structure
I said they don't have the same table structures, and thanx for the code, I tried to make 2 different mysql_select_db but not 2 different mysql_connect as the two databases in the same host thanx, I'll try that
I think this is what you are looking for
I hope so, thanx again

Posted: Wed Apr 12, 2006 8:44 am
by feyd
you can run a query against two separate databases at the same time.
http://dev.mysql.com/doc/refman/4.1/en/ ... table.html
table references can have database prefixes:
Re: confused
Posted: Thu Apr 13, 2006 1:59 am
by dibyendrah
Sorry I made two connections and assigned to same variable.
The code must be :
Code: Select all
$conn1 = mysql_connect("1st_host_name", "username", "password");
$conn2 = mysql_connect("2nd_host_name", "username", "password");
$sql = "insert into sometable(field_names') values(field_values)";
mysql_select_db("database_of_1st_host", $conn1);
mysql_query($sql, $conn1);
mysql_select_db("database_of_2nd_host", $conn2);
mysql_query($sql, $conn2);
Anyway, is your problem solved ?
dibyendrah wrote:I'm confused that why it is necessary to store all records in array...
If the two database has same table structure, you can either syncronize the database using SqlYog. If you don't want to syncronize, then just make the two connection to the database and while executing query just do it in both connection by selecting the database one by one.
Code: Select all
$conn1 = mysql_connect("1st_host_name", "username", "password");
$conn1 = mysql_connect("2nd_host_name", "username", "password");
$sql = "insert into sometable(field_names') values(field_values)";
mysql_select_db("database_of_1st_host", $conn1);
mysql_query($sql, $conn1);
mysql_select_db("database_of_2nd_host", $conn2);
mysql_query($sql, $conn2);
I think this is what you are looking for....OR I might have misunderstood.
Dibyendra

Posted: Thu Apr 13, 2006 12:19 pm
by irisblaze
yeah thanx, actually i found feyd solution much easier, but I didn't read the page he suggested
Sorry I made two connections and assigned to same variable.
I knew what you meant anyway
