Select from a database and insert into a different database

PHP
MySQL

January 29, 2009

Here is an example of how to select some data from a table on one database and insert into a table of a different database. I am assuming that you are using the same mysql connection.

The MySQL USE command is used to select the appropriate database at the appropriate time. database1 is where the data is being selected from and database2 is where it is inserted to.

$handle  =  mysql_connect("localhost", "username", "password") or die(mysql_error());
 
  mysql_query("USE database1",$handle);
  $query = "SELECT * FROM table";
  $result = mysql_query($query);
 
  while ($data = mysql_fetch_object($result)){
      $variable1 = $data->column1;
      $variable2 = $data->column2;
 
      mysql_query("USE database2",$handle);
      $sql = "INSERT INTO table2 SET
              col1 = '$variable1',
              col2 = '$variable2'";
     	if (!mysql_query($sql)) {
  		echo '<p>Error adding data into database: ' . mysql_error() . '</p>';
  	  }
      mysql_query("USE database1",$handle);
 
  }

Comments

Submitted by Sadek (not verified) on Wed, 01/02/2012 - 19:24.

thanks!!!