0

I have two Database, A & B how I can copy data from A to B using MySQL, i did like there something wrong, there are two same tables called members in A & B, and i want copy username from A.members to B.members. there is error ! can I do that using Mysql, anyone can help me to solve this problem because I must connect to databases A & B in same time then query data

    mysql_connect("$host", "$user", "$pass"); mysql_select_db("A");
    mysql_connect("$host", "$user2", "$pass2"); mysql_select_db("B"); 
   //host A = host B same host 
    $MySQL="Select username FROM A.members INSERT INTO B.members"; 
    $result= mysql_query($MySQL);
Kamran
  • 2,711
  • 2
  • 17
  • 24
Moulay Omar
  • 1
  • 5
  • Why do you need to do this? – gbestard Apr 29 '15 at 13:54
  • Looks like you're trying to sync the 2 db's together. Have you looked into MySQL replication http://dev.mysql.com/doc/refman/5.0/en/replication.html ?? Unless they are 2 completely different and unrelated db's then you'll have to do it in code. – Ye. Apr 29 '15 at 13:56
  • That looks like a weird syntax for INSERT...SELECT. – Don't Panic Apr 29 '15 at 13:57
  • possible duplicate of [How do you connect to multiple MySQL databases on a single webpage?](http://stackoverflow.com/questions/274892/how-do-you-connect-to-multiple-mysql-databases-on-a-single-webpage) – YesItsMe Apr 29 '15 at 14:03
  • 1
    You have an error, so why don't you add it to your question? – Dinistro Apr 29 '15 at 14:12

2 Answers2

0

If you are connecting to the same host, you only need to make the connection once. And setting the default database using mysql_select_db("A"); should not be necessary because you are specifying which database to use in your statement.

The SQL for your insert should be:

INSERT INTO B.members (username) SELECT username FROM A.members

Check out the documentation for INSERT...SELECT.

Don't Panic
  • 41,125
  • 10
  • 61
  • 80
-1

create two connection and use it in query eg:

  $con = mysqli_connect("localhost","root","","db1");
        $con2 = mysqli_connect("localhost","root","","db2");
         $query1 = mysqli_query($con1,"SELECT * FROM tbl1 order by ID desc ");
         while($row = $query1 ->fetch_assoc())
      {

         $query2 = mysqli_query($con2,"insert into  tbl2 (fields) values('".$row['field
']."')");

}
Pranay Aher
  • 444
  • 4
  • 11
  • but if $query1 select just all from table 1, and the $query2 select all from table2. me i need to copy data from table 1 in database1 first to second table2 in database 2 !! not the same i think! – Moulay Omar Apr 29 '15 at 16:00