程式師世界 >> 編程語言 >> 網頁編程 >> PHP編程 >> 關於PHP編程 >> php 備份數據庫代碼(生成word,excel,json,xml,sql)

php 備份數據庫代碼(生成word,excel,json,xml,sql)

編輯：關於PHP編程

單表備份
代碼：
復制代碼代碼如下:
<?php
class Db
{
    var $conn;

    function Db($host="localhost",$user="root",$pass="root",$db="test")
    {
      if(!$this->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<$resultNum;$i++){
       $str.="{";
       $count=count($result[$i]);
       $j=1;
       foreach($result[$i] as $key=>$val)
       {
       if($j<$count)
       {
       $str.="'".$key."':'".$val."',";
       }
       elseif($j==$count)
       {
       $str.="'".$key."':'".$val."'";
       }
       $j++;
                }

       $str.="}";
       if ($i != $resultNum-1) {
                 $str.= ",";
             }
      }
      $str.="]";
      $str.="}";
      return $str;
    }

    function generateSql($table,$cons)
    {
        $sql="";//sql條件
       $sql="select * from ".$table;
       if($cons!="")
       {
       if(is_array($cons))
       {
         $k=0;
         foreach($cons as $key=>$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="<?xml version=\"1.0\" encoding=\"utf-8\" ?>\n";
       $xml.="<xml>\n";
       $xml.="\t<totalCount>".$totalNum."</totalCount>\n";
       $xml.="\t<items>\n";
       for($i=0;$i<$resultNum;$i++){
       $xml.="\t\t<item>\n";
       foreach($result[$i] as $key=>$val)
       $xml.="\t\t\t<".$key.">".$val."</".$key.">\n";
       $xml.="\t\t</item>\n";
       }
        $xml.="\t</items>\n";
        $xml.="</xml>\n";
        return $xml;
    }

    //輸出word表格
    function toWord($table,$mapping,$fileName)
    {
       header('Content-type: application/doc');
          header('Content-Disposition: attachment; filename="'.$fileName.'.doc"');
          echo '<html xmlns:o="urn:schemas-microsoft-com:office:office"
           xmlns:w="urn:schemas-microsoft-com:office:word"
           xmlns="[url=http://www.w3.org/TR/REC-html40]http://www.w3.org/TR/REC-html40[/url]">
        <head>
           <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
        <title>'.$fileName.'</title>
        </head>
        <body>';
        echo'<table border=1><tr>';
        if(is_array($mapping))
        {
          foreach($mapping as $key=>$val)
       echo'<td>'.$val.'</td>';
        }
        echo'</tr>';
        $results=$this->findBySql('select * from '.$table);
        foreach($results as $result)
        {
          echo'<tr>';
          foreach($result as $key=>$val)
       echo'<td>'.$val.'</td>';
       echo'</tr>';
        }
        echo'</table>';
        echo'</body>';
        echo'</html>';
    }

    function toExcel($table,$mapping,$fileName)
    {
      header("Content-type:application/vnd.ms-excel");
         header("Content-Disposition:filename=".$fileName.".xls");
      echo'<html xmlns:o="urn:schemas-microsoft-com:office:office"
           xmlns:x="urn:schemas-microsoft-com:office:excel"
           xmlns="[url=http://www.w3.org/TR/REC-html40]http://www.w3.org/TR/REC-html40[/url]">
           <head>
           <meta http-equiv="expires" content="Mon, 06 Jan 1999 00:00:01 GMT">
           <meta http-equiv=Content-Type content="text/html; charset=iso-8859-1">
           
           </head>
        <body link=blue vlink=purple leftmargin=0 topmargin=0>';
        echo'<table width="100%" border="0" cellspacing="0" cellpadding="0">';
           echo'<tr>';
        if(is_array($mapping))
        {
          foreach($mapping as $key=>$val)
       echo'<td>'.$val.'</td>';
        }
        echo'</tr>';
        $results=$this->findBySql('select * from '.$table);
        foreach($results as $result)
        {
          echo'<tr>';
          foreach($result as $key=>$val)
       echo'<td>'.$val.'</td>';
       echo'</tr>';
        }
        echo'</table>';
        echo'</body>';
        echo'</html>';
    }

    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("Pragma: 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="<br>";
      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');
?>

整表備份
復制代碼代碼如下:
$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 < $numfields) {
            if (!$firstfield) {
                $tabledump .= ", ";
            } else {
                $firstfield = 0;
            }
            if (!isset($row[$fieldcounter])) {
                $tabledump .= "NULL";
            } else {
                $tabledump .= "'" . mysql_escape_string($row[$fieldcounter]) . "'";
            }
        }
        $tabledump .= ");\n";
        if ($fp) {
            fwrite($fp, $tabledump);
        } else {
            echo $tabledump;
        }
    }
    mysql_free_result($rows);
}

導入數據庫
復制代碼代碼如下:
<?php
/************
*
PHP導入.sql文件
運行版本:php5,php4 使用的時候請選擇
作者:panxp
郵件:[email protected]
*
*************/
    $file_dir = dirname(__FILE__);
    $file_name = "2010-05-09-bak.sql";
    $conn = mysql_connect(DB_HOST,DB_USER,DB_PASS);
    mysql_select_db(DB_NAME, $conn);
    /** PHP5 版本 **/
    $get_sql_data = file_get_contents($file_name, $file_dir);
    /**
    * PHP4 版本
    if(file_exists($file_dir."/".$file_name))
    {
        $get_sql_data = fopen($file_dir."/".$file_name,"r");
        if(!$get_sql_data)
        {
            echo "不能打開文件";
        }
        else
        {
            $get_sql_data = fread($get_sql_data, filesize ($file_dir."/".$file_name));
        }
    }
    ***/
    $explode = explode(";", $get_sql_data);
    $cnt = count($explode);
    for ($i=0; $i<$cnt; $i++)
    {
        $sql = $explode[$i];
        $result = mysql_query($sql);
        mysql_query("set names 'utf8'");
        if ($result) {
            echo "成功:".$i."個查詢<br>";
        } else {
            echo "導入失敗:".mysql_error();
        }
    }
?>