asked on

two connections at same time

Hey,
i have problem. I have two different databases on localhost. I need to copy from one database table to another database table. How can i do that? I need to do with PHP, as i see, it is impossible to connect to two databases at same time :/ Or i am wrong?

hernst42

You need to rewrite you code to be able to connect to two databases. Each mysql_ command has a linkindentifier which you can use to specify the connection

$link1 = mysql_connect('localhost', 'mysql_user', 'mysql_password');
$link2 = mysql_connect('localhost', 'mysql_user', 'mysql_password');
 
$res = mysql_query('Select * from table1', $link1);
while ($row = mysql_fetch($res)) {
   mysql_query('insert into table2', $link2)
}

Open in new window

ASKER CERTIFIED SOLUTION

psadac

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

virmaior

If the tables are both accessible to the same MySQL user, then psadac is correct...

here's a speed up for hernst's code:

$link1 = mysql_connect('localhost', 'mysql_user', 'mysql_password');
$link2 = mysql_connect('localhost', 'mysql_user', 'mysql_password');
 
$res = mysql_query('Select * from table1', $link1);
$row_count = 0;
$fields = '';
while ($row = mysql_fetch_array($res)) {
    $row_count++;
    if ($row_count == 1) {   
       $i = 0;
       while ($i < mysql_num_fields($res)) {
           if ($i != 0) { echo ','; }
           $field_info = mysql_fetch_field($res);
           $fields .= $field_info->name;
        }
       $base_query = 'INSERT INTO table2 (' . $fields . ') VALUES '
       $rows = '';
       }
       $rows .= '(';
       foreach($row as $column) {
         $rows .= '\'' . $column . '\'';
       }       
       $rows .= ')';
   if (($row_count % 100) == 0) {  
      mysql_query($base_query . $rows, $link2); 
      $rows = '';
   } else { $rows .= ', '; }
}
 
 
   if (($row_count % 100) != 0) {  
      mysql_query($base_query . $rows, $link2); 
   }

Open in new window

virmaior

(missing ; on line 16)