如何使用PHP同步两个数据库表? [英] How can i synchronize two database tables with PHP?
问题描述
我需要使用PHP将数据从一个MySQL数据库复制到另一个数据库.
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.
例如说我要从表A复制数据到表B.
For example say I am going to be copying data from tableA to tableB.
我可以将tableB设置为类似于tableA,但是将来我可能会将列添加到tableA中,而忘记将它们添加到tableB中,那么我的PHP脚本将尝试将数据插入到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.
所以我要做的是将tableA与tableB进行比较,而tableA具有该tableB的任何列都没有将它们添加到tableB.
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.
谁能告诉我该怎么做?
推荐答案
感谢大家,基于您的所有帮助,我编写了一个PHP类,该类将表A中的所有列复制到表B(如果它们尚不存在):
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
*/
}
这篇关于如何使用PHP同步两个数据库表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!