sql statment INSERT INTO ....SELECT

[sneeuwvlok]

hi,

i've been trying to copy data from one database to antother using the
INSERT INTO ...SELECT statement. But for some reason it doesn't copy the data.

it looks something like this.

 $db1 = mysql_connect(localhost, al, hi);
 $db2 = mysql_connect(localhost, al2, hi2);

mysql_select_db('pacs', $db1);
mysql_select_db('radinfo', $db2);

 $res = mysql_query("SELECT id, test FROM ct", $db1);
 $res2 = mysql_query("INSERT INTO test (id1,test1) VALUES (id,test)", $db2); 

that didn't work so i tried this:

$res = mysql_query("INSERT INTO db1.test (id1), SELECT db2.ct.id FROM db2.ct ");

and that doesn't work either. Does anyone know what i'm doing wrong? Or have a better solution?

Thanks for your help!

[Roger_Ramjet]

Can't run a query on 2 seperate databases so go back to your first attempt and think about how you are going to pass the result from query 1 to query 2.

[superwormy]

Your first example is on the right track, except that you never pulled the data out of the result resource with a mysql_fetch_array() call.

What you need to do is, record by record, pull the data out of one database, and INSERT it into the other database with a seperate mysql_query() call.

[sneeuwvlok]

Thanks for your help, I finally got it working.