Ivan Villareal

I needed a process to move a table from one database to another, the databases are on separate servers, so renaming the table will not work, I did a little research and found this Table Syncer it seemed fine, the problem is that it was on ruby gem, and I said the problem because the server where this will be running don’t have this installed.

So instead of doing more research I spent that time developing my own php class to sync two tables on different server, I have little time to create this so I’m re-using a class I already did for handling several databases and it already has some useful methods, right now its 3:12pm I plan to finish this in 30 minutes lets see how it goes.

In the end I spent about an hour but I have a working file that can put in a cron job to sync the table on a regular basis.

I’ve changed the way I fetch the entire result, to avoid having problems exhausting the dedicated php memory with large arrays, instead I’m fetching one row at a time, another problem I had, was that the null values came blank so, when I was inserting on a unique index, I had an error, to avoid this I remove the empty values from the insert, and use the default table value instead, this results in a bit different tables Null instead of blanks, but because this isn’t important I didn’t want to spend more with this.

The final file, contains the db class and the sync class, and here it is:

< ?php
 
/**
* The purpose of this file is to sync
* tables between 2 databases
*/
 
class tableSync {
    private $_sourceDbUser = 'ivan';
    private $_sourceDbPass = 'pass';
    private $_sourceDbName = 'bounty';
    private $_sourceDbHost = 'localhost';
 
    private $_targetDbUser = 'ivan';
    private $_targetDbPass = 'pass';
    private $_targetDbName = 'testing';
    private $_targetDbHost = 'localhost';
 
    private $_sourceDb, $_targetDb, $_primaryKey, $_table;
 
    public function __construct($table)
    {
        $this->_sourceDb = new Db($this->_sourceDbUser, $this->_sourceDbPass, $this->_sourceDbName, $this->_sourceDbHost);
        $this->_targetDb = new Db($this->_targetDbUser, $this->_targetDbPass, $this->_targetDbName, $this->_targetDbHost);
        $this->_table = $table;
        $this->begin();
    }
 
    public function begin()
    {
      //Check that the table exists
        $sql = 'SHOW TABLES';
        $tbl = $this->_targetDb->fetchAssoc($sql);
 
        if (!in_array($this->_table, current($tbl))) {
                //create the table
                $sql = "SHOW CREATE TABLE `".$this->_table."`";
                $tbl = $this->_sourceDb->fetchAssoc($sql);
                $createQry = $tbl[0]['Create Table'];
                $res = $this->_targetDb->query($createQry);
        }
 
        //Get The primary Key
        $sql     = "SHOW indexes FROM ".$this->_table." WHERE Key_name = 'PRIMARY'";
        $indexes = $this->_sourceDb->fetchAssoc($sql);
        $this->_primaryKey = $indexes[0]['Column_name'];
        //Query source  table
        $sql     = 'SELECT * FROM '.$this->_table;
        $rowset  = $this->_sourceDb->query($sql);
        while ($row = mysql_fetch_assoc($rowset)) {
            //remove empty fields
            $newRow = array();
            foreach ($row as $field => $value) {
                if ($value != '') {
                    $newRow[$field] = $value;
                }
            }
            if ($this->_primaryKey != '') {
                echo "Inserting row ".$row[$this->_primaryKey] . "\n";
                $res = $this->_targetDb->insertUpdate($this->_table, $newRow, $this->_primaryKey, $row[$this->_primaryKey]);
            } else {
                $res = $this->_targetDb->queryInsert($this->_table, $newRow);
            }
        }
    }
}
 
class Db
{
	private	$_link;
	private $_affectedRows;
	private $_lastQueryStatus;
 
	/**
	* Instantiate the object
	**/
	public function __construct( $user, $pass, $dbName, $host = 'localhost')
	{
            $this->_link = mysql_connect($host, $user, $pass, true);
            mysql_select_db($dbName, $this->_link) or die('Could not select database');
            if (mysql_error()) {
                    printf("Connect failed: %s\n", mysql_error());
                    exit();
            } else {
                $sql = "SET NAMES `utf8`";
                mysql_query($sql, $this->_link);
                mysql_query("SET CHARACTER SET 'utf8';", $this->_link);
            }
	}
 
        public function query($sql)
	{
            $this->_lastQueryStatus = @mysql_query($sql, $this->_link) or die('Query failed: ' . mysql_error() . '<br /> SQL: '. $sql);
            if (!$this->_lastQueryStatus) {
                    $this->error("<b>MySQL Query fail:</b> $sql");
            }
            $this->_affectedRows    = @mysql_affected_rows();
            return $this->_lastQueryStatus;
	}
 
        public function fetchAssoc($sql)
	{
            $result = $this->query($sql);
            $table = array();
            while ($row = mysql_fetch_assoc($result)) {
                    $table[] = $row;
            }
            return $table;
	}
 
        public function escape($string)
	{
		if(get_magic_quotes_gpc()) $string = stripslashes($string);
		return mysql_real_escape_string($string);
	}
 
        public function fetchField($table, $field, $where = '1')
	{
	    $sql = "SELECT `$field` FROM `$table` WHERE $where";
	    $result = $this->query($sql);
	    $field = mysql_fetch_row($result);
	    return $field[0];
	}
 
        public function insertUpdate($table, $data, $field, $uniqueValue)
	{
	    $where = is_int($uniqueValue) ? "$field = $uniqueValue" : "$field = '".$this->escape($uniqueValue)."'";
	    $e = $this->fetchField($table, $field, $where);
	    if ($e) {
	        $res = $this->queryUpdate($table, $data, $where);
	    } else {
	        $res = $this->queryInsert($table, $data);
	    }
	    return $res;
	}
 
        public function queryInsert($table, $data)
	{
            $q="INSERT INTO `".$table."` ";
            $v='';
            $n='';
 
            foreach($data as $key=>$val) {
                    $n.="`$key`, ";
                    if(strtolower($val)=='null') $v.="NULL, ";
                    elseif(strtolower($val)=='now()') $v.="NOW(), ";
                    elseif(strtolower($val)=='utc_timestamp()') $v.="UTC_TIMESTAMP(), ";
                    else $v.= "'".$this->escape($val)."', ";
            }
            $q .= "(". rtrim($n, ', ') .") VALUES (". rtrim($v, ', ') .");";
            if($this->query($q)){
                    $result = mysql_insert_id();
            } else {
                    $result = false;
            }
            return $result;
	}
 
        public function queryUpdate($table, $data, $where='1')
        {
            $q="UPDATE `".$table."` SET ";
 
            foreach($data as $key=>$val) {
                if(strtolower($val)=='null') $q.= "`$key` = NULL, ";
                elseif(strtolower($val)=='now()') $q.= "`$key` = NOW(), ";
                else $q.= "`$key`='".$this->escape($val)."', ";
            }
            $q = rtrim($q, ', ') . ' WHERE '.$where.';';
 
            return $this->query($q);
        }
}
 
//Instantiate the tableSync sending the table name I want to sync
$sync = new tableSync('tod_whois_raider');