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..