Home  >  Article  >  Backend Development  >  PHP backup database code (generate word, excel, json, xml, sql)_PHP tutorial

PHP backup database code (generate word, excel, json, xml, sql)_PHP tutorial

WBOY
WBOYOriginal
2016-07-21 15:03:46817browse

Single table backup
Code:

Copy code The code is as follows:

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="n";   
       $xml.="n";   
       $xml.="t".$totalNum."n";   
       $xml.="tn";   
       for($i=0;$i<$resultNum;$i++){   
       $xml.="ttn";   
       foreach($result[$i] as $key=>$val)   
       $xml.="ttt<".$key.">".$val."n";   
       $xml.="tt
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 '           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]">   
           
              
        '.$fileName.'   
           
        ';    
        echo'';   
        if(is_array($mapping))   
        {   
          foreach($mapping as $key=>$val)   
       echo'';   
        }   
        echo'';   
        $results=$this->findBySql('select * from '.$table);   
        foreach($results as $result)   
        {   
          echo'';   
          foreach($result as $key=>$val)   
       echo'';   
       echo'';   
        }   
        echo'
'.$val.'
'.$val.'
';   
        echo'';   
        echo'';   
    }   

    function toExcel($table,$mapping,$fileName)   
    {   
      header("Content-type:application/vnd.ms-excel");   
         header("Content-Disposition:filename=".$fileName.".xls");   
      echo'           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]">   
              
              
              
              
              
        ';    
        echo'';   
           echo'';   
        if(is_array($mapping))   
        {   
          foreach($mapping as $key=>$val)   
       echo'';   
        }   
        echo'';   
        $results=$this->findBySql('select * from '.$table);   
        foreach($results as $result)   
        {   
          echo'';   
          foreach($result as $key=>$val)   
       echo'';   
       echo'';   
        }   
        echo'
'.$val.'
'.$val.'
';   
        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("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="
";   
      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();                                                                                                                } 
 $ret=$arr; 
      return true;                                                               >//$map=array('No','Name','Email','Age');
//echo $db->toWord('test',$map,'File');

// Generate Excel
//$map=array('No','Name','Email','Age');
//echo $db->toExcel( 'test',$map,'file');

// Generate Xml
//echo $db->toExtXml('test',0,20);

// Generate Json
//echo $db->toExtJson('test',0,20);

//Backup
//echo $db->Backuptofile('test ','backup'); 
?>


Backup of the entire table



Copy code

The code is as follows:

$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, "nnn");
   }
}
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] . ";rn";
    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 import .sql file
Running version: php5, php4 Please select
Author: panxp
Email: coolpan123@gmail .com
*
*************/
    $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 version **/
    $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."个查询
";
        } else {
            echo "导入失败:".mysql_error();
        }
    }
?>

www.bkjia.comtruehttp://www.bkjia.com/PHPjc/327829.htmlTechArticle单表备份 代码: 复制代码 代码如下: ?php class Db { var $conn; function Db($host="localhost",$user="root",$pass="root",$db="test") { if(!$this-conn=mysql_connect($hos...
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn