Data from one db to another

[k0r54]

Hi,

First ill explain the scenario

I have two databases db1 & db2

db1 has a table called A with 4 columns named 1,2,3 & 4
db2 has a table called B with 4 columns named 5,6,7 & 8

How do i get information from
"db1,table A, column 2" to go into "db2,table B, column 8"
"db1,table A, column 3" to go into "db2,table B, column 5"

I hope you understand what i am trying to do I need the data from the above exmaple to be transfered from one to another.

Thanks Adam

[NogDog]

I think you could do something liket this (adding appropriate error checking):

$connx = mysql_connect('localhost', 'mysql_user', 'mysql_password');
$db1 = mysql_select_db('db1');
$result = mysql_query("SELECT col2 FROM table WHERE col1='something'");
$fields = mysql_fetch_array($result);
$db2 = mysql_select_db('db2');
$insert = mysql_query("UPDATE table SET col3='".$fields[0]."' WHERE col1='something');

[k0r54]

Hi

I think this may work or something anyway, my concern is process time?

The fields will be about 10000 rows??? of about 10 columns?

the entire db is 3.6Mb to give you an idea about the size

Will this take a while?


Thanks
Adam

[k0r54]

I also get an error msg with ur code

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in D:\apachefriends\xampp\htdocs\test\admin\members\update\update\update.php on line 20

my code is


$db1 = mysql_select_db('update');
$result = mysql_query("SELECT previewpath FROM animations");
$fields = mysql_fetch_array($result);
$db2 = mysql_select_db('update2');
$insert = mysql_query("UPDATE animations2 SET previewpath2='".$fields[0]."'");

[NogDog]

Time for some error-checking... :)

$db1 = mysql_select_db('update');
$result = mysql_query("SELECT previewpath FROM animations");
# check result:
if(!$result)
{
die "MySQL Error: " . mysql_error();
}
if(mysql_num_rows($result) < 1)
{
die "ERROR: nothing returned by query.";
}
$fields = mysql_fetch_array($result);
$db2 = mysql_select_db('update2');
$insert = mysql_query("UPDATE animations2 SET previewpath2='".$fields[0]."'");

[k0r54]

Hi, yes work ok now

Im still a little concerned of process time or crashing/timing out

There will be about 10000 rows (max) and i need to do this with about 7-10 columns on 10 different databases?

Will this work ok do you think and will the process time be reasonable.

Is there perhaps a way to time the process or something so i can put a gif up of loading ... and then jump to another page once the proccesses have completed?

Im not sure

Thanks
Adam

[NogDog]

My first thought is, should those 10 different databases be changed to 10 different tables within one database? This would at least get rid of the overhead of opening up all these DB's. Also, this might make it easier to do most of the work within MySQL itself, which would probably be faster. For instance (if you have MySQL 4.1 or later to support subqueries):

UPDATE table_1 SET col_3 = (SELECT col_2 FROM table_2 WHERE col_1 == 'something') WHERE col_1 = 'something';

(This would set the value in col_3 of table_1 to the value of col_2 in table_2 where table_1.col_1 = table_2.col_1.)

[k0r54]

UPDATE animations2 SET previewpath = ( SELECT previewpath
FROM animations)

Can anyone see why this isn't working

[96turnerri]

probably because this returns more than one result try adding a WHERE clause

SELECT previewpath FROM animations WHERE CONDITION

[NogDog]

Does SELECT previewpath FROM animations only return one row? If not, that might not work, but if they are all the same value you could use a DISTINCT qualifier or a LIMIT clause so that it only returns one item. (I think...we're getting into stuff I've not tried myself.)

[k0r54]

Hi what do i do if there are no conditions and just need ALL the data from that colum into the new column

[96turnerri]

lol nogdog, just beat you too it ;)

just use this
SELECT previewpath FROM animations LIMIT 1

[k0r54]

but that will only display 1 row corrent? how do i put ALL from animations2 into animations?

[k0r54]

Is there not a single string that will take the records from one table to another and if not i dont really want to use while because there will be up to about 10000 records, which is quite a bit

[k0r54]

But i dont quite understand, if i am limiting it to 1 how do i do all 10,000 rows???

OK!

Say if i have all 15 the tables in one db how would i get the data from table 3 column "Me" to table 7 column "You".

" " = the name of the column.

There is about 10,000 records so process time is quite important.

Thanks for ya help

k0r54

[DJsAC]

maybe i'm misunderstanding the whole thing, but why don't you just rename the table and columns? :confused: Wouldn't that be easier?

[k0r54]

Hi thanks

No the reason why is because i have 4 tables for instance i need to 3 of them to go into 1 table and the 4th to go in another.

I can do that for the single ones but not where im joining them.

Please look here, i have included a dummy sql file

http://www.webdeveloper.com/forum/showthread.php?s=&postid=275631#post275631