How to 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 massage all the values ​​to go to another database, but first I want to make sure the database has the correct fields before inserting.

For example, I’ll say that I’m going to copy data from tableA to tableB.

I can configure tableB in the same way as tableA, but in the future I can add columns to tableA and forget to add them to tableB, then my PHP script will try to insert data into a column that does not exist in table B, and this will fail.

So, I want to compare tableA with table B and any columns that tableA has, that tableB does not add them to tableB.

Can someone 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:

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
     */
}

Answer 2

SHOW COLUMNS FROM «table»

Answer 3

100% , , , . , devDB prodDB , . sql-alter- script, , . perl, , -, linux.

mySQLdiff, www.mysqldiff.org.

Answer 4

phpclasses, http://www.phpclasses.org/search.html?words=mysql+sync&x=0&y=0&go_search=1

Answer 5

, , :

function columns($table) {

    $columns = array();
    $sql = "desc $table";
    $q = mysql_query($sql);

    while ($r = mysql_fetch_array($q)) {

       $columns[] = $r[0];

    }

    return $columns;

}

:

function tables_different($table1, $table2) {

  $cols1 = columns($table1);
  $cols2 = columns($table2);

  return count(array_diff($cols1, $cols2)) ? true : false;

}

tables_different() script, , , .

, , , .

Answer 6

, , , ( , 100% - ).

, , , , , , , ..

PHP MDB2_Schema ( article).

PHP, Sundog, .

, .

./

Answer 7

, -

$sql = "SELECT * FROM tableA WHERE 1"

$results = mysql_fetch_assoc($sql);

$sql = "truncate table tableB";

// run truncate

foreach($result as $update){

   $sql = "Insert into table b VALUES(....)"

   // run insert
}

. , , tableB, , . , . , tableA , .

php, mysql.

Answer 8

TOAD MySQL Schema, , SQL- . .