时时商务社区

标题: php 备份数据库代码(生成word,excel,json,xml,sql) [打印本页]

作者: qz234 时间: 2018-2-14 08:16

         单表备份
代码：
[U]复制代码[/U] 代码如下:
conn=mysql_connect($host,$user,$pass))
   die("can't connect to mysql sever");
   mysql_select_db($db,$this->conn);
   mysql_query("SET NAMES 'UTF-8'");
}
function execute($sql)
{
   return mysql_query($sql,$this->conn);
}
function findCount($sql)
{
      $result=$this->execute($sql);
      return mysql_num_rows($result);
}
function findBySql($sql)
{
      $array=array();
      $result=mysql_query($sql);
      $i=0;
      while($row=mysql_fetch_assoc($result))
         {
      $array[$i]=$row;
   $i++;
         }
      return $array;
}
//$con的几种情况
//空：返回全部记录
//array：eg. array('id'=>'1') 返回id=1的记录
//string :eg. 'id=1' 返回id=1的记录
function toExtJson($table,$start="0",$limit="10",$cons="")
{
   $sql=$this->generateSql($table,$cons);
   $totalNum=$this->findCount($sql);
   $result=$this->findBySql($sql." LIMIT ".$start." ,".$limit);
   $resultNum = count($result);//当前结果数
   $str="";
   $str.= "{";
   $str.= "'totalCount':'$totalNum',";
   $str.="'rows':";
   $str.="[";
   for($i=0;$i$val)
   {
   if($j$val)
   {
   if($k==0)
   {
   $sql.="where '";
   $sql.=$key;
   $sql.="'='";
   $sql.=$val."'";
   }else
   {
   $sql.="and '";
   $sql.=$key;
   $sql.="'='";
   $sql.=$val."'";
   }
   $k++;
   }
   }else
   {
   $sql.=" where ".$cons;
   }
   }
   return $sql;
}
function toExtXml($table,$start="0",$limit="10",$cons="")
{
   $sql=$this->generateSql($table,$cons);
   $totalNum=$this->findCount($sql);
   $result=$this->findBySql($sql." LIMIT ".$start." ,".$limit);
   $resultNum = count($result);//当前结果数
   header("Content-Type: text/xml");
   $xml="\n";
   $xml.="\n";
   $xml.="\t".$totalNum."\n";
   $xml.="\t\n";
   for($i=0;$i$val)
   $xml.="\t\t\t".$val."\n";
   $xml.="\t\t\n";
   }
      $xml.="\t\n";
      $xml.="\n";
      return $xml;
}
//输出word表格
function toWord($table,$mapping,$fileName)
{
   header('Content-type: application/doc');
      header('Content-Disposition: attachment; filename="'.$fileName.'.doc"');
      echo '


      '.$fileName.'

      ';
      echo'';
      if(is_array($mapping))
      {
      foreach($mapping as $key=>$val)
   echo''.$val.'';
      }
      echo'';
      $results=$this->findBySql('select * from '.$table);
      foreach($results as $result)
      {
      echo'';
      foreach($result as $key=>$val)
   echo''.$val.'';
   echo'';
      }
      echo'';
      echo'';
      echo'';
}
function toExcel($table,$mapping,$fileName)
{
   header("Content-type:application/vnd.ms-excel");
      header("Content-Disposition:filename=".$fileName.".xls");
   echo'
















      ';
      echo'';
         echo'';
      if(is_array($mapping))
      {
      foreach($mapping as $key=>$val)
   echo''.$val.'';
      }
      echo'';
      $results=$this->findBySql('select * from '.$table);
      foreach($results as $result)
      {
      echo'';
      foreach($result as $key=>$val)
   echo''.$val.'';
   echo'';
      }
      echo'';
      echo'';
      echo'';
}
function Backup($table)
{
   if(is_array ($table))
   {
   $str="";
   foreach($table as $tab)
   $str.=$this->get_table_content($tab);
   return $str;
   }else{
   return $this->get_table_content($table);
   }
}
function Backuptofile($table,$file)
{
   header("Content-disposition: filename=$file.sql");//所保存的文件名
   header("Content-type: application/octetstream");
   header("

ragma: no-cache");
   header("Expires: 0");
   if(is_array ($table))
   {
   $str="";
   foreach($table as $tab)
   $str.=$this->get_table_content($tab);
   echo $str;
   }else{
   echo $this->get_table_content($table);
   }
}
function Restore($table,$file="",$content="")
{
   //排除file，content都为空或者都不为空的情况
   if(($file==""&&$content=="")||($file!=""&&$content!=""))
   echo"参数错误";
   $this->truncate($table);
   if($file!="")
   {
   if($this->RestoreFromFile($file))
   return true;
   else
   return false;
   }
   if($content!="")
   {
   if($this->RestoreFromContent($content))
   return true;
   else
   return false;
   }
}
//清空表，以便恢复数据
function truncate($table)
{
   if(is_array ($table))
   {
   $str="";
   foreach($table as $tab)
   $this->execute("TRUNCATE TABLE $tab");
   }else{
   $this->execute("TRUNCATE TABLE $table");
   }
}
function get_table_content($table)
{
   $results=$this->findBySql("select * from $table");
   $temp = "";
   $crlf="
";
   foreach($results as $result)
   {
   /*(";
   foreach($result as $key=>$val)
   {
   $schema_insert .= " `".$key."`,";
   }
   $schema_insert = ereg_replace(",$", "", $schema_insert);
   $schema_insert .= ")
   */
   $schema_insert = "INSERT INTO  $table VALUES (";
   foreach($result as $key=>$val)
   {
   if($val != "")
   $schema_insert .= " '".addslashes($val)."',";
   else
   $schema_insert .= "NULL,";
   }
   $schema_insert = ereg_replace(",$", "", $schema_insert);
   $schema_insert .= ");$crlf";
   $temp = $temp.$schema_insert ;
   }
   return $temp;
}
function RestoreFromFile($file){
   if (false !== ($fp = fopen($file, 'r'))) {
   $sql_queries = trim(fread($fp, filesize($file)));
   $this->splitMySqlFile($pieces, $sql_queries);
   foreach ($pieces as $query) {
      if(!$this->execute(trim($query)))
      return false;
   }
   return true;
   }
   return false;
}
function RestoreFromContent($content)
{
   $content = trim($content);
   $this->splitMySqlFile($pieces, $content);
   foreach ($pieces as $query) {
   if(!$this->execute(trim($query)))
   return false;
   }
   return true;
}
function splitMySqlFile(&$ret, $sql)
{
   $sql= trim($sql);
   $sql=split(';',$sql);
   $arr=array();
   foreach($sql as $sq)
   {
      if($sq!="");
      $arr[]=$sq;
   }
   $ret=$arr;
   return true;
}
}
$db=new db();
// 生成 word
//$map=array('No','Name','Email','Age');
//echo  $db->toWord('test',$map,'档案');
// 生成 Excel
//$map=array('No','Name','Email','Age');
//echo  $db->toExcel('test',$map,'档案');
// 生成 Xml
//echo  $db->toExtXml('test',0,20);
// 生成 Json
//echo  $db->toExtJson('test',0,20);
//备份
//echo $db->Backuptofile('test','backup');
?>
整表备份
[U]复制代码[/U] 代码如下:
$link = mysql_connect(DB_HOST,DB_USER,DB_PASS);
$tables = mysql_list_tables(DB_NAME);
$cachetables = array(); $tableselected = array();
while ($table = mysql_fetch_row($tables))
{
$cachetables[$table[0]] = $table[0];
$tableselected[$table[0]] = 1;
}
$table = $cachetables;
$filename =  DB_NAME . "_" . date("Y_m_d_H_i_s") . ".sql";
$path = "sql/" . $filename;
$filehandle = fopen($path, "w");
$result = mysql_query("SHOW tables");
while ($currow = mysql_fetch_array($result))
{
if (isset($table[$currow[0]]))
{
   sqldumptable($currow[0], $filehandle);
   fwrite($filehandle, "\n\n\n");
}
}
fclose($filehandle);
$update_data = array('filename' => $filename, 'postdate' => mktime());
$db->insert('backup_db', $update_data);
// data dump functions
function sqldumptable($table, $fp = 0)
{
$tabledump = "DROP TABLE IF EXISTS " . $table . ";\n";
$result = mysql_fetch_array(mysql_query("SHOW CREATE TABLE " . $table));
//echo "SHOW CREATE TABLE $table";
$tabledump .= $result[1] . ";\r\n";
if ($fp) {
      fwrite($fp, $tabledump);
} else {
      echo $tabledump;
}
// get data
$rows = mysql_query("SELECT * FROM " . $table);
// $numfields=$DB->num_fields($rows);
$numfields = mysql_num_fields($rows);
while ($row = mysql_fetch_array($rows)) {
      $tabledump = "INSERT INTO " . $table . " VALUES(";
      $fieldcounter = -1;
      $firstfield = 1;
      // get each field's data
      while (++$fieldcounter
导入数据库
[U]复制代码[/U] 代码如下:


您可能感兴趣的文章:

php中实现xml与mysql数据相互转换的方法

php与XML、XSLT、Mysql的结合运用实现代码

PHP基于DOMDocument解析和生成xml的方法分析

PHP处理数组和XML之间的互相转换

php生成xml简单实例代码

PHP输出XML到页面的3种方法详解

php实现解析xml并生成sql语句的方法

欢迎光临时时商务社区 (http://bbs.4435.cn/)