Blair Wadman 1 minute read
February 18, 2009

Select from a database and insert into a different database

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);

  }