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

Use TOAD for MySQL Schema compare tool, it will look at the tables, show you the differences visually and generate SQL queries to synchronize the tables structure. Also it does data compare.

Answer 3

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 4

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