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

SHOW COLUMNS FROM «table»

Answer 2

You could write a function that returns the columns from the table such as this:

function columns($table) {

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

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

       $columns[] = $r[0];

    }

    return $columns;

}

Next, you can compare the columns, from the two tables:

function tables_different($table1, $table2) {

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

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

}

Now, you can integrate the tables_different() function into your data transfer script, running it each time to make sure the tables are the same.

Of course, you can make this fancier and have it tell you which columns are different between the two tables, making it more useful to synchronizing them.

Answer 3

This is a very complex task and as far as I know many have tried to solved it so far (unfortunately I am not aware of any 100% guaranteed solution).

I'd say that before jumping to implement your own solution you should take a look and read about the challenges you'll be facing by reading about Schema evolution, Schema Refactoring, Schema versioning, etc.

Afterwards, you can take a look at PHP MDB2_Schema (some more documentation in this article).

If you are not tied to PHP then you may also take a look at Sundog which provides a set of advanced schema refactorings.

Once you get a reliable schema migration tool for your app, migrating data will be just a trivial task.

./alex

Answer 4

I'm not a 100% sure this is what you're looking for but I used to do a little database maintenance a while back. We needed a way to make sure the devDB and the prodDB were identical in structure and I tracked down this nifty little tool. The tool creates a sql-alter-script that can be run on the database you would like to patch. It written in perl so I guess it should work cross platform but I have only tried it on linux.

The tool is called mySQLdiff, is freeware and can be downloaded at www.mysqldiff.org.

Answer 5

Probably the easiest way to do this would be

$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
}

But you need to be extremely careful here. Make sure that the only process that can write to tableB is the one that does the copy from tableA other wise you will have lost data. Also make sure that nothing can write to tableA once this process has begun.

The best practice for this would be to not do this in php but rather through mysql replication.

Answer 6

you could look into some phpclasses that do this for you http://www.phpclasses.org/search.html?words=mysql+sync&x=0&y=0&go_search=1

Answer 7

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