Copying data from database to another

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
irisblaze
Forum Newbie
Posts: 22
Joined: Sun Mar 19, 2006 3:24 am
Location: Palestine
Contact:

Copying data from database to another

Post 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 :D
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

confused

Post 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

:)
User avatar
irisblaze
Forum Newbie
Posts: 22
Joined: Sun Mar 19, 2006 3:24 am
Location: Palestine
Contact:

Post 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 :D
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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:

Code: Select all

`database`.`table`
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Re: confused

Post 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

:)
User avatar
irisblaze
Forum Newbie
Posts: 22
Joined: Sun Mar 19, 2006 3:24 am
Location: Palestine
Contact:

Post 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 :D
Post Reply