1

I'm trying to make a script that will copy data in tables from one database server to another database server.

This is what I've tried using PDO.

So here is how I get both connections using PDO (sample.php):

<?php
class DBOne {
   static $db ;
   private $dbh ;
   private function PDO_DBConnect(){
     $db_type = 'mysql'; 
     $db_name = 'database1';
     $user = 'guest' ;    $password = 'guest' ;
     $host = 'server1' ; 
    try {
        $dsn = "$db_type:host=$host;dbname=$db_name"; 
        $this->dbh = new PDO ( $dsn, $user, $password); 
        $this->dbh->setAttribute(PDO::ATTR_PERSISTENT, true);
        $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     } catch ( PDOException $e ) {
        print "Error!: " . $e->getMessage () . "\
" ;      die () ; 
     }  
   }
   public static function getInstance ( ) {
     if (! isset ( PDO_DBConnect::$db )) {
        PDO_DBConnect::$db = new PDO_DBConnect ( ) ;
     }
     return PDO_DBConnect::$db->dbh;
  }
}

class DBTWO {
   static $db ;
   private $dbh ;
   private function PDO_DBConnect(){
     $db_type = 'mysql'; 
     $db_name = 'database2';
     $user = 'root' ;    $password = 'admin' ;
     $host = 'server2' ; 
    try {
        $dsn = "$db_type:host=$host;dbname=$db_name"; 
        $this->dbh = new PDO ( $dsn, $user, $password); 
        $this->dbh->setAttribute(PDO::ATTR_PERSISTENT, true);
        $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     } catch ( PDOException $e ) {
        print "Error!: " . $e->getMessage () . "\
" ;      die () ; 
     }  
   }
   public static function getInstance ( ) {
     if (! isset ( PDO_DBConnect::$db )) {
        PDO_DBConnect::$db = new PDO_DBConnect ( ) ;
     }
     return PDO_DBConnect::$db->dbh;
  }
}

?>

This is for the query in copying data..

<?php
//session_start();
require_once 'sample.php';

    $o_DbA = DBOne::getInstance(); //Get Database A
    $o_DbB = DBTWO::getInstance(); // Get Database B
    $sql = 'SELECT * from table1';
    $stmt = $o_DBA->prepare($sql);
    $stmt->execute();
    $db_A_results = $stmt->fetch(PDO::FETCH_ASSOC);

    foreach($db_A_results as $results){
      foreach($results as $key => $value){
        $stmt = $o_DbB ->prepare("INSERT INTO `table2` (`id`, `control_number`) VALUES (:id, :control_number)");
        $stmt->bindParam(':id', $key);
        $stmt->bindParam(':control_number', $value);
        $stmt->execute();
      }
    }
?>

What am I missing? I doesn't copy the data's in the table... thanks in advance..

NewbieKid
  • 87
  • 7
  • first of all print the first table results `print_r($db_A_results);` and see if it contain records. – Akam Jan 07 '20 at 05:45
  • @Akam it only shows nothing..but im sure the connection is right.. – NewbieKid Jan 07 '20 at 05:53
  • Then first correct this part and then work on insert. – Akam Jan 07 '20 at 05:56
  • Have you created the tables into whom you are copying the data in the new database? – Amanjot Kaur Jan 07 '20 at 06:48
  • Also, close the $stmt query when its work is done. Read more here: $stmt->close(); – Amanjot Kaur Jan 07 '20 at 06:51
  • @AmanjotKaur Yes I've created the tables... I also close $stmt query.. but still no luck.. – NewbieKid Jan 07 '20 at 06:58
  • show me the structure of your new table with data types and all the errors you are getting. – Amanjot Kaur Jan 07 '20 at 07:01
  • Also, change `print "Error!: " . $e->getMessage () . "\";` to `print "Error!: " . $e->getMessage () . "/";` Read more about backslashes here: https://stackoverflow.com/questions/3415683/in-php-do-i-need-to-escape-backslashes – Amanjot Kaur Jan 07 '20 at 07:04
  • I don't get any errors. It only shows a blank page... And Also when I removed the $o_DbA = DBOne::getInstance(); and $o_DbB = DBTWO::getInstance(); and replace with o_DbB = new PDO('mysql:host=server2;dbname=database2', 'root', 'admin' ); it prints the table... I guess it doesnt get the connection ... – NewbieKid Jan 07 '20 at 07:13
  • @AmanjotKaur There might be a problem in database connection.. – NewbieKid Jan 07 '20 at 07:24
  • I ran your code and it says PDO_DBConnect() not found – Amanjot Kaur Jan 07 '20 at 07:32

0 Answers0