您当前的位置: 首页 » PHP/Perl编程 » 利用PHP实现 XML和MySQL的相互转换
利用PHP实现 XML和MySQL的相互转换
论坛链接
mysql2xml.php类文件：用于备份MySQL数据的！
PHP代码
<?php
class MySQL2XML {
protected $conn;
protected $result;
protected $tables;
protected $saveFolder = 'datas/';
public function __construct($config = NULL) {
if($config !== NULL && is_array($config)) {
$this->connect($config);
}
}
public function connect($config) {
$this->conn = mysql_connect($config['host'], $config['username'], $config['password']);
if($this->conn) {
mysql_select_db($config['database']);
return true;
}
return false;
}
public function setSaveFolder($folder) {
if(is_dir($folder)) {
$this->saveFolder = rtrim(str_replace("\\", "/", $folder),'/');
return true;
}
return false;
}
public function setTables($tables) {
if(is_array($tables)) {
$this->tables = $tables;
return true;
}
return false;
}
public function query($query) {
if(!isset($query) || trim($query) == '') return false;
$this->result = mysql_query($query);
if($this->result) return true;
return false;
}
public function toXML() {
if(!isset($this->tables)) return false;
foreach($this->tables as $table) {
$file = $this->saveFolder.$table.'.xml';
$fp = @fopen($file, 'w');
if(!$fp) exit('Can not write file');
fwrite($fp, $this->tableToXML($table));
fclose($fp);
unset($fp);
}
return true;
}
public function tableToXML($table) {
header("content-type:text/xml;charset=utf-8");
$xml = "<?xml version=\"1.0\" encoding=\"utf-8\" ?>\n<datas>\n";
$fields = $this->getFields($table);
$datas = $this->getDatas($table);
$cdata = array();
foreach($datas as $data) {
foreach($data as $key => $value)
$cdata[$key][] = $value;
}
foreach($fields as $element) {
$xml .= "\t<fields name=\"{$element['Field']}\" type=\"{$element['Type']}\" null=\"{$element['Null']}\" key=\"{$element['Key']}\" default=\"{$element['Default']}\" extra=\"{$element['Extra']}\">\n";
foreach($cdata[$element['Field']] as $value) {
$xml .= "\t\t<data>{$value}</data>\n";
}
$xml .= "\t</fields>\n";
}
$xml .= '</datas>';
return $xml;
}
protected function getFields($table) {
$query = "SHOW FIELDS FROM {$table}";
$this->query($query);
return $this->fetchAll();
}
protected function getDatas($table) {
$query = "SELECT * FROM {$table}";
$this->query($query);
return $this->fetchAll();
}
protected function fetch() {
if(is_resource($this->result)) {
return mysql_fetch_assoc($this->result);
}
return false;
}
protected function fetchAll() {
if(is_resource($this->result)) {
$return = array();
$row = NULL;
while($row = mysql_fetch_assoc($this->result)) {
$return[] = $row;
}
return $return;
}
return false;
}
}
?>
调用方法：
PHP代码
<?php
$xml = new MySQL2XML(array('host'=>'localhost', 'username'=>'root', 'password'=>'', 'database'=>'mysql'));
$xml->setTables(array('wp_term_relationships','wp_terms'));//设置备份的表
$xml->setSaveFolder('datas/');//保存备份文件的文件夹
$xml->toXML();//备份开始
?>
PHP代码
<?php
class MySQL2XML {
protected $conn;
protected $result;
protected $tables;
protected $saveFolder = 'datas/';
public function __construct($config = NULL) {
if($config !== NULL && is_array($config)) {
$this->connect($config);
}
}
public function connect($config) {
$this->conn = mysql_connect($config['host'], $config['username'], $config['password']);
if($this->conn) {
mysql_select_db($config['database']);
return true;
}
return false;
}
public function setSaveFolder($folder) {
if(is_dir($folder)) {
$this->saveFolder = rtrim(str_replace("\\", "/", $folder),'/');
return true;
}
return false;
}
public function setTables($tables) {
if(is_array($tables)) {
$this->tables = $tables;
return true;
}
return false;
}
public function query($query) {
if(!isset($query) || trim($query) == '') return false;
$this->result = mysql_query($query);
if($this->result) return true;
return false;
}
public function toXML() {
if(!isset($this->tables)) return false;
foreach($this->tables as $table) {
$file = $this->saveFolder.$table.'.xml';
$fp = @fopen($file, 'w');
if(!$fp) exit('Can not write file');
fwrite($fp, $this->tableToXML($table));
fclose($fp);
unset($fp);
}
return true;
}
public function tableToXML($table) {
header("content-type:text/xml;charset=utf-8");
$xml = "<?xml version=\"1.0\" encoding=\"utf-8\" ?>\n<datas>\n";
$fields = $this->getFields($table);
$datas = $this->getDatas($table);
$cdata = array();
foreach($datas as $data) {
foreach($data as $key => $value)
$cdata[$key][] = $value;
}
foreach($fields as $element) {
$xml .= "\t<fields name=\"{$element['Field']}\" type=\"{$element['Type']}\" null=\"{$element['Null']}\" key=\"{$element['Key']}\" default=\"{$element['Default']}\" extra=\"{$element['Extra']}\">\n";
foreach($cdata[$element['Field']] as $value) {
$xml .= "\t\t<data>{$value}</data>\n";
}
$xml .= "\t</fields>\n";
}
$xml .= '</datas>';
return $xml;
}
protected function getFields($table) {
$query = "SHOW FIELDS FROM {$table}";
$this->query($query);
return $this->fetchAll();
}
protected function getDatas($table) {
$query = "SELECT * FROM {$table}";
$this->query($query);
return $this->fetchAll();
}
protected function fetch() {
if(is_resource($this->result)) {
return mysql_fetch_assoc($this->result);
}
return false;
}
protected function fetchAll() {
if(is_resource($this->result)) {
$return = array();
$row = NULL;
while($row = mysql_fetch_assoc($this->result)) {
$return[] = $row;
}
return $return;
}
return false;
}
}
?>
调用方法：
PHP代码
<?php
$xml = new MySQL2XML(array('host'=>'localhost', 'username'=>'root', 'password'=>'', 'database'=>'mysql'));
$xml->setTables(array('wp_term_relationships','wp_terms'));//设置备份的表
$xml->setSaveFolder('datas/');//保存备份文件的文件夹
$xml->toXML();//备份开始
?>
#2楼发布者：lbsong时间：2010-02-21 09:01:35
还原数据的类：
xml2mysql.php类文件：用来还原MySQL数据的
PHP代码
<?php
require_once 'mysql2xml.php';
class XML2MySQL extends MySQL2XML {
private $XMLFiles = array();
private $tableName = NULL;
private $fields = array();
private $datas = array();
public function __construct($config = NULL) {
parent::__construct($config);
if(!function_exists('simplexml_load_file')) throw new Exception("Your server isn't suppost this class.");
}
public function setXMLFiles($file) {
if(is_array($file)) {
foreach($file as $f) {
if(file_exists($f)) {
$this->XMLFiles[] = $f;
return true;
} else return false;
}
} else {
if(file_exists($file)) {
$this->XMLFiles[] = $file;
return true;
} else return false;
}
}
public function getXMLFromFolder($dir) {
if(!is_dir($dir)) return false;
$dir = rtrim(str_replace("\\", "/", $dir), '/').'/';
$dp = @opendir($dir);
if(!$dp) throw new Exception("Can not open folder");
while(($f = readdir($dp)) !== false) {
if($f != '.' && $f != '..') {
if(!$this->setXMLFiles($dir.$f)) throw new Exception("Error:Files are not xml file or files are not exists.");
}
}
closedir($dp);
return true;
}
public function toMySQL() {
$buff = '';
foreach($this->XMLFiles as $xml) {
$this->getDataFromXML($xml);
$drop = 'DROP TABLE IF EXISTS `'.$this->tableName.'`;';
if($this->query($drop)) $buff .= 'Drop table <font color="red">['.$this->tableName.']</font> <font color="green">success</font>'."<br/>\n";
else $buff .= 'Drop table <font color="red">['.$this->tableName.']</font> <font color="red">fail</font>'."<br/>\n";
$pk = NULL;
$uk = NULL;
$sql = 'CREATE TABLE `'.$this->tableName."`(\n";
foreach($this->fields as $field) {
$sql .= '`'.$field['name'].'`'.' '.$field['type'].' '.($field['null'] == 'NO' ? 'NOT NULL ':'').(trim($field['extra']) != '' ? strtoupper($field['extra']).' ' : '').(trim($field['default']) != '' ? 'DEFAULT '."'".$field['default']."'" : '').','."\n";
if($field['key'] == 'PRI') $pk = $pk.(strpos($pk, 'PRIMARY KEY') !== FALSE ? '`'.$field['name'].'`,' : 'PRIMARY KEY (`'.$field['name'].'`,');
if($field['key'] == 'UNI') $uk = $uk.(strpos($uk, 'UNIQUE KEY') !== FALSE ? '`'.$field['name'].'`,' : "UNIQUE KEY `".$field['name']."` (`".$field['name']."`,");
$fields[$this->tableName][] = $field['name'];
}
if($pk !== NULL) {
$pk = rtrim($pk, ",")."),\n";
$sql .= $pk;
}
if($uk !== NULL) {
$uk = rtrim($uk, ",")."),\n";
$sql .= $uk;
}
$sql = rtrim($sql, ",\n");
$sql .= ');';
if($this->query($sql)) $buff .= 'Create table <font color="red">['.$this->tableName.']</font> <font color="green">success</font>'."<br/>\n";
else $buff .= 'Create table <font color="red">['.$this->tableName.']</font> <font color="red">fail</font>'."<br/>\n";
unset($sql);
$datas = 'INSERT INTO `'.$this->tableName.'` (';
foreach($fields as $table_name => $f) {
foreach($f as $element) {
$datas .= '`'.$element.'`,';
}
}
$this->datas = $this->r2l($this->datas);
$datas = rtrim($datas, ',').') VALUES ';
foreach($this->datas as $data) {
$datas .= "(";
foreach($data as $d) {
$datas .= "'".$d."',";
}
$datas = rtrim($datas, ',');
$datas .= '),';
}
$datas = rtrim($datas, ',').';';
if($this->query($datas)) $buff .= 'Insert data in table <font color="red">['.$this->tableName.']</font> <font color="green">success</font>'."<br/><br/>\n";
else $buff .= 'Insert data in table <font color="red">['.$this->tableName.']</font> <font color="red">fail</font>'."<br/><br/>\n";
unset($fields);
}
return $buff;
}
private function r2l($array) {
$temp = array();
for($i = 0; $i < count($array); $i++) {
for($j = 0; $j < count($array[0]); $j++) {
$temp[$j][$i] = $array[$i][$j];
}
}
return $temp;
}
private function getDataFromXML($xml) {
//Use the SimpleXML Object
$this->tableName = substr(basename($xml), 0, strlen(basename($xml)) - 4);
$simplexml = simplexml_load_file($xml);
$fields = array();
$index = 0;
foreach($simplexml->children() as $e) {
$fields[$index]['name'] = (string)$e->attributes()->name;
$fields[$index]['type'] = (string)$e->attributes()->type;
$fields[$index]['null'] = (string)$e->attributes()->null;
$fields[$index]['key'] = (string)$e->attributes()->key;
$fields[$index]['default'] = (string)$e->attributes()->default;
$fields[$index]['extra'] = (string)$e->attributes()->extra;
$index++;
}
$this->fields = $fields;
$datas = array();
$index = 0;
foreach($simplexml->children() as $e) {
foreach($e->children() as $d) {
$datas[$index][] = (string)$d;
}
$index++;
}
$this->datas = $datas;
}
}
?>
调用方法：
PHP代码
<?php
$s = new XML2MySQL(array('host'=>'localhost', 'username'=>'root', 'password'=>'', 'database'=>'mysql'));
$s->getXMLFromFolder('datas/');//备份文件的文件夹（就是装XML文件的文件夹）
echo $s->toMySQL();//此方法返回还原消息
?>
xml2mysql.php类文件：用来还原MySQL数据的
PHP代码
<?php
require_once 'mysql2xml.php';
class XML2MySQL extends MySQL2XML {
private $XMLFiles = array();
private $tableName = NULL;
private $fields = array();
private $datas = array();
public function __construct($config = NULL) {
parent::__construct($config);
if(!function_exists('simplexml_load_file')) throw new Exception("Your server isn't suppost this class.");
}
public function setXMLFiles($file) {
if(is_array($file)) {
foreach($file as $f) {
if(file_exists($f)) {
$this->XMLFiles[] = $f;
return true;
} else return false;
}
} else {
if(file_exists($file)) {
$this->XMLFiles[] = $file;
return true;
} else return false;
}
}
public function getXMLFromFolder($dir) {
if(!is_dir($dir)) return false;
$dir = rtrim(str_replace("\\", "/", $dir), '/').'/';
$dp = @opendir($dir);
if(!$dp) throw new Exception("Can not open folder");
while(($f = readdir($dp)) !== false) {
if($f != '.' && $f != '..') {
if(!$this->setXMLFiles($dir.$f)) throw new Exception("Error:Files are not xml file or files are not exists.");
}
}
closedir($dp);
return true;
}
public function toMySQL() {
$buff = '';
foreach($this->XMLFiles as $xml) {
$this->getDataFromXML($xml);
$drop = 'DROP TABLE IF EXISTS `'.$this->tableName.'`;';
if($this->query($drop)) $buff .= 'Drop table <font color="red">['.$this->tableName.']</font> <font color="green">success</font>'."<br/>\n";
else $buff .= 'Drop table <font color="red">['.$this->tableName.']</font> <font color="red">fail</font>'."<br/>\n";
$pk = NULL;
$uk = NULL;
$sql = 'CREATE TABLE `'.$this->tableName."`(\n";
foreach($this->fields as $field) {
$sql .= '`'.$field['name'].'`'.' '.$field['type'].' '.($field['null'] == 'NO' ? 'NOT NULL ':'').(trim($field['extra']) != '' ? strtoupper($field['extra']).' ' : '').(trim($field['default']) != '' ? 'DEFAULT '."'".$field['default']."'" : '').','."\n";
if($field['key'] == 'PRI') $pk = $pk.(strpos($pk, 'PRIMARY KEY') !== FALSE ? '`'.$field['name'].'`,' : 'PRIMARY KEY (`'.$field['name'].'`,');
if($field['key'] == 'UNI') $uk = $uk.(strpos($uk, 'UNIQUE KEY') !== FALSE ? '`'.$field['name'].'`,' : "UNIQUE KEY `".$field['name']."` (`".$field['name']."`,");
$fields[$this->tableName][] = $field['name'];
}
if($pk !== NULL) {
$pk = rtrim($pk, ",")."),\n";
$sql .= $pk;
}
if($uk !== NULL) {
$uk = rtrim($uk, ",")."),\n";
$sql .= $uk;
}
$sql = rtrim($sql, ",\n");
$sql .= ');';
if($this->query($sql)) $buff .= 'Create table <font color="red">['.$this->tableName.']</font> <font color="green">success</font>'."<br/>\n";
else $buff .= 'Create table <font color="red">['.$this->tableName.']</font> <font color="red">fail</font>'."<br/>\n";
unset($sql);
$datas = 'INSERT INTO `'.$this->tableName.'` (';
foreach($fields as $table_name => $f) {
foreach($f as $element) {
$datas .= '`'.$element.'`,';
}
}
$this->datas = $this->r2l($this->datas);
$datas = rtrim($datas, ',').') VALUES ';
foreach($this->datas as $data) {
$datas .= "(";
foreach($data as $d) {
$datas .= "'".$d."',";
}
$datas = rtrim($datas, ',');
$datas .= '),';
}
$datas = rtrim($datas, ',').';';
if($this->query($datas)) $buff .= 'Insert data in table <font color="red">['.$this->tableName.']</font> <font color="green">success</font>'."<br/><br/>\n";
else $buff .= 'Insert data in table <font color="red">['.$this->tableName.']</font> <font color="red">fail</font>'."<br/><br/>\n";
unset($fields);
}
return $buff;
}
private function r2l($array) {
$temp = array();
for($i = 0; $i < count($array); $i++) {
for($j = 0; $j < count($array[0]); $j++) {
$temp[$j][$i] = $array[$i][$j];
}
}
return $temp;
}
private function getDataFromXML($xml) {
//Use the SimpleXML Object
$this->tableName = substr(basename($xml), 0, strlen(basename($xml)) - 4);
$simplexml = simplexml_load_file($xml);
$fields = array();
$index = 0;
foreach($simplexml->children() as $e) {
$fields[$index]['name'] = (string)$e->attributes()->name;
$fields[$index]['type'] = (string)$e->attributes()->type;
$fields[$index]['null'] = (string)$e->attributes()->null;
$fields[$index]['key'] = (string)$e->attributes()->key;
$fields[$index]['default'] = (string)$e->attributes()->default;
$fields[$index]['extra'] = (string)$e->attributes()->extra;
$index++;
}
$this->fields = $fields;
$datas = array();
$index = 0;
foreach($simplexml->children() as $e) {
foreach($e->children() as $d) {
$datas[$index][] = (string)$d;
}
$index++;
}
$this->datas = $datas;
}
}
?>
调用方法：
PHP代码
<?php
$s = new XML2MySQL(array('host'=>'localhost', 'username'=>'root', 'password'=>'', 'database'=>'mysql'));
$s->getXMLFromFolder('datas/');//备份文件的文件夹（就是装XML文件的文件夹）
echo $s->toMySQL();//此方法返回还原消息
?>