mysql - How can i synchronize two database tables with PHP?

Question

I need to use PHP to copy data from one MySQL database to another.

I can build and array of all the values to go into the other database but first I want to make sure the database has the correct fields before inserting.

For example say I am going to be copying data from tableA to tableB.

I can set up tableB to look just like tableA but in the future I may add columns to tableA and forget to add them to tableB, then my PHP script will try to insert data into a column that doesn't exist in tableB and it will fail.

So what I want to do is compare tableA to tableB and any columns that tableA has that tableB doesn't have add them to tableB.

Can anyone tell me how to do this?

Answer 1

Thanks everyone, based on all your help I was able to write a PHP class that copies any columns from table A to table B if they are not already there:

class MatchTable 
{
    var $_table_one_name;
    var $_table_two_name;

    var $_table_one_db_user;
    var $_table_one_db_pass;
    var $_table_one_db_host;
    var $_table_one_db_name;

    var $_table_two_db_user;
    var $_table_two_db_pass;
    var $_table_two_db_host;
    var $_table_two_db_name;

    var $_table_one_columns = array();
    var $_table_two_columns = array();
    var $_table_one_types = array();
    var $_table_two_types = array();

    var $_table_one_link;
    var $_table_two_link;

    var $_isTest;


    function MatchTable($isLive = true)
    {
        $this->_isTest = !$isLive;
    }

    function matchTables($table1, $table2)
    {
        $this->_table_one_name = $table1;
        $this->_table_two_name = $table2;

        if(isset($this->_table_one_db_pass))
        {
            $this->db_connect('ONE');
        }
        list($this->_table_one_columns,$this->_table_one_types) = $this->getColumns($this->_table_one_name);

        if(isset($this->_table_two_db_pass))
        {
            $this->db_connect('TWO');
        }
        list($this->_table_two_columns,$this->_table_two_types) = $this->getColumns($this->_table_two_name);

        $this->addAdditionalColumns($this->getAdditionalColumns());
    }

    function setTableOneConnection($host, $user, $pass, $name)
    {
        $this->_table_one_db_host = $host;
        $this->_table_one_db_user = $user;
        $this->_table_one_db_pass = $pass;
        $this->_table_one_db_name = $name;
    }

    function setTableTwoConnection($host, $user, $pass, $name)
    {
        $this->_table_two_db_host = $host;
        $this->_table_two_db_user = $user;
        $this->_table_two_db_pass = $pass;
        $this->_table_two_db_name = $name;
    }

    function db_connect($table)
    {
        switch(strtoupper($table))
        {
            case 'ONE':
                $host = $this->_table_one_db_host;
                $user = $this->_table_one_db_user;
                $pass = $this->_table_one_db_pass;
                $name = $this->_table_one_db_name;
                $link = $this->_table_one_link = mysql_connect($host, $user, $pass, true);
                mysql_select_db($name) or die(mysql_error());
            break;
            case 'TWO';
                $host = $this->_table_two_db_host;
                $user = $this->_table_two_db_user;
                $pass = $this->_table_two_db_pass;
                $name = $this->_table_two_db_name;
                $link = $this->_table_two_link = mysql_connect($host, $user, $pass, true);
                mysql_select_db($name) or die(mysql_error());
            break;
            default:
                die('Improper parameter in MatchTable->db_connect() expecting "one" or "two".');
            break;
        }
        if (!$link) {
            die('Could not connect: ' . mysql_error());
        }
    }

    function getColumns($table_name)
    {
        $columns = array();
        $types = array();
        $qry = 'SHOW COLUMNS FROM '.$table_name;
        $result = mysql_query($qry) or die(mysql_error());
        while($row = mysql_fetch_assoc($result))
        {
            $field = $row['Field'];
            $type = $row['Type'];
            /*
            $column = array('Field' => $field, 'Type' => $type);
            array_push($columns, $column);
            */
            $types[$field] = $type;
            array_push($columns, $field);
        }
        $arr = array($columns, $types);
        return $arr;
    }

    function getAdditionalColumns()
    {
        $additional = array_diff($this->_table_one_columns,$this->_table_two_columns);
        return $additional;
    }

    function addAdditionalColumns($additional)
    {
        $qry = '';
        foreach($additional as $field)
        {
            $qry = 'ALTER TABLE '.$this->_table_two_name.' ADD '.$field.' '.$this->_table_one_types[$field].'; ';

            if($this->_isTest)
            {
                echo $qry.'<br><br>';
            }
            else
            {
                mysql_query($qry) or die(mysql_error());
            }
        }
    }

    /**
     * End of Class
     */
}