php 备份数据库代码(生成word,excel,json,xml,sql)

php经典分页类

//此页面为php3.php ? include signup/mysql.inc; switch($part){ case 1
:$table=bbs_php;break; case 2 :$table=bbs_mysql;break; case 3
:$table=bbs_html;break; case 4 :$table=bbs_js; } //echo $part; ? html
head titlexiaoyang/title meta content=text/html; charset=gb2312 script
language=JavaScript1.2 src=js/menu_comment.js/script script
language=JavaScript1.2 src=js/fw_menu.js/script script
language=JavaScript !– function MM_reloadPage(init) {//reloads the
window if Nav4 resized if (init==true) with (navigator) {if
((appName==Netscape)&&(parseInt(appVersion)==4)) {
document.MM_pgW=innerWidth; document.MM_pgH=innerHeight;
onresize=MM_reloadPage; }} else if (innerWidth!=document.MM_pgW ||
innerHeight!=document.MM_pgH) location.reload(); }
MM_reloadPage(true); // — /script /head body bgcolor=#FFFFFF
text=#000000 div id=Layer1 style=position:absolute; left:31px; top:1px;
width:560px; height:46px; z-index:10 script
language=JavaScript1.2fwLoadMenus();/script/div div id=Layer2
style=position:absolute; left:27px; top:41px; width:600px; height:15px;
z-index:2 hr color=#ff9999 /div div id=Layer3 style=position:absolute;
left:73px; top:10px; width:2px; height:330px; z-index:3;
background-color: #ff9999; layer-background-color: #ff9999; border:
1px none #000000/div div id=Layer4 style=position:absolute; left:560px;
top:10px; width:2px; height:330px; z-index:4; background-color:
#ff9999; layer-background-color: #ff9999; border: 1px none
#000000/div div id=Layer5 style=position:absolute; left:86px; top:58px;
width:464px; height:273px; z-index:5table width=100% border=0
cellspacing=1 cellpadding=0 height=20 trtd width=10%div align=centerfont
color=#CC33CC size=2人气/font/div /td td width=38%div align=centerfont
color=#CC33CC size=2文章题目/font/div /td td width=26%div
align=centerfont size=2 color=#CC33CC作者/font/div /td td width=26%div
align=centerfont color=#CC33CC size=2日期/font/div /td /tr ?$sql=select
* from .$table. order by row DESC; $result=mysql_query($sql)or
die(mysql_error()); $row1=mysql_num_rows($result);
$row_max_page=12;//分页计算 $page=ceil($row1/$row_max_page);
$left_row=$row1-$p*$row_max_page; $pre_left_row=$left_row+12;
$start_row=$left_row+$row_max_page+1; if($left_row=0) {
$start_row=$pre_left_row+1; $left_row=0; } $sql=select * from
.$table. where row’$start_row’ and row’$left_row’ order by row DESC;
$result=mysql_query($sql)or die(mysql_error());
while($row=mysql_fetch_array($result)){ static $i=0; $i++; if($i%2)
$color=CCCCFF; else $color=FFCCFF; echo tr;echo td width=10%
bgcolor=#.$color. ; echo div font size=2
color=#3333FF.$row[number]./font/div; echo /td; echo td width=38%
bgcolor=#.$color. ; echo div font size=2
color=#3333FFahref=’look.php?id=.$row[id].&&table=.$table.’.$row[title]./a/font/div;
echo /td; echo td width=26% bgcolor=#.$color. ; echo div font size=2
color=#3333FF.$row[userid]./font/div; echo /td; echo td width=26%
bgcolor=#.$color. ; echo div font size=2
color=#3333FF.$row[date1]./font/div; echo /td/tr; } echo/table;echo
BRdiv tabletrtdFONT SIZE=2 ; for($i=1;$i=$page;$i++) { echo a
href=’.$PHP_SELF.?p=.$i.&&table=.$table.'[.$i.]/a; } ? /FONT/td/tr
/table/div /div div id=Layer6 style=position:absolute; left:409px;
top:29px; width:118px; height:17px; z-index:6/div div id=Layer7
style=position:absolute; left:451px; top:28px; width:65px; height:12px;
z-index:8a href=say.php?table=?echo $table ;? target=_self
onMouseOver=window.status=”;return truefont size=2
color=#FF33FF我要发言/font/a/div p/p p/p /body /html

单表备份 代码:
复制代码 代码如下:

?phpclass db{public $conn,$db,$table,$user,$host,$unm,$pwd;public
$res;public $char;public $linkType;function
__construct($linkType=false,$char=gb2312){
$this-linkType=$linkType;//设定连接类型
$this-char=$char;//设定连接校对字符 $this-db=DB; $this-user=USER;
$this-host=HOST; $this-unm=UNM; $this-pwd=PWD; if($this-linkType) {
$this-conn=mysql_pconnect($this-host,$this-unm,$this-pwd) or
die(Database connection failure); } else {
$this-conn=mysql_connect($this-host,$this-unm,$this-pwd) or
die(Database connection failure); } mysql_select_db($this-db);
mysql_query(set names .$this-char); }function query($sql,$type=true){
//$type:默认的操作。 指代select操作 $this-res=mysql_query($sql) or
die(SQL Statement error !Please check it again);
$row=$type?mysql_num_rows($this-res):mysql_affected_rows();
$result[res]=$this-res; $result[row]=$row; return
$result;}//fetch()方法:获取所有的记录,并写入数组function fetch($sql){
$res=self::query($sql); while ($rs=mysql_fetch_array($res[res])) {
$result[]=$rs; } return $result;}//获取下一条记录function
fetchNext($filed,$currenID,$table){ $sql=select * from $table where
$filed$currenID limit 0,1 ; return
self::fetch($sql);}//获取前一条记录function
fetchPre($filed,$currenID,$table){ $sql=select * from $table where
$filed$currenID limit 0,1 ; return self::fetch($sql);} }class page
extends db {public $currentPage,$totalRecord,$totalPage,$pageSize;public
$start;public $flag;public $sql;function
__construct($sql,$pagesize=5,$flag=page){ $this-sql=$sql;
$this-pageSize=$pagesize; $this-flag=$flag;//设定翻页链接标识符
$row=parent::query($sql); $this-totalRecord=$row[row];
$this-totalPage=ceil($this-totalRecord/$this-pageSize);
$page=$_REQUEST[$this-flag]; if($page0||$page==) {
$this-currentPage=1; } else {
$page$this-totalPage?$this-currentPage=$this-totalPage:$this-currentPage=$page;
} $this-start=($this-currentPage-1)*$this-pageSize;
}//显示分页列表function show($page=10){ $str.=’div class=pages_btnsdiv
class=pages’; $str.=em .$this-totalRecord. /em;
$pre=$this-currentPage-1; if($pre!=0) { $str.=’a
href=?’.$this-flag.’=’.$pre.’ class=next/a’; }
if($this-currentPage=$page) { if($this-totalPage-$this-currentPage10) {
$start=$this-currentPage-($this-currentPage%10); $end=$this-totalPage; }
else { $start=$this-currentPage-2; $end=$start+$page-1; } } else {
$start=1; $end=10; } for ($i=$start;$i=$end;$i++) {
if($i==$this-currentPage) { $str.=strong.$i./strong; } else { $str.=a
href=?.$this-flag.=$i$i/a; } } $next=$this-currentPage+1; $str.=’a
href=?’.$this-flag.’=’.$next.’ class=next/a’; $str.=a
href=?.$this-flag.=.$this-totalPage. class=last….$this-totalPage./a;
$str.=kbdinput type=’text’ name=’custompage’ size=’3′
onkeydown=/if(event.keyCode==13)
{window.location='{$_SERVER[‘PHP_SELF’]}?{$this-flag}=’+this.value;
return false;}/ //kbd; $str.=/div/div; return
$str;}}??phpdefine(HOST,localhost);define(UNM,root);define(PWD,root);define(DB,test);require_once(‘db.class.php’);$db=new
db();$sql=select * from yy; $page=new page($sql,10); $sql.= limit
$page-start,$page-pageSize;$rs = $db-fetch($sql);?table width=100%
border=0 cellpadding=0 cellspacing=1 bgcolor=#FF0000 tr td height=25
bgcolor=#FFFFFFID/td td bgcolor=#FFFFFFUNM/td /tr ?php
for($i=0;$icount($rs);$i++) { ? tr td height=25
bgcolor=#FFFFFF?=$rs[$i][0]?/td td
bgcolor=#FFFFFF?=$rs[$i][1]?/td /tr ?php } ? tr td height=25
colspan=2 bgcolor=#FFFFFF?=$page-show()?/td /tr/table

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       $str.=”{“;    
       $count=count($result[$i]);   
       $j=1;   
       foreach($result[$i] as $key=>$val)   
       {   
       if($j       {   
       $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       $xml.=”ttn”;   
       foreach($result[$i] as $key=>$val)   
       $xml.=”ttt”.$val.””.$key.”>n”;   
       $xml.=”ttn”;   
       }   
        $xml.=”tn”;   
        $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=;   
           
          

   
       

‘.$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’          
xmlns:x=”urn:schemas-microsoft-com:office:excel” 
          
xmlns=”[url=;   
              
          

   
          

   
              
              
        ‘;    
        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(“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();   
      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, “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导入.sql文件
运行版本:php5,php4 使用的时候请选择
作者:panxp
邮件:coolpan123@gmail.com
*
*************/
    $file_dir = dirname(apizl_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    {
        $sql = $explode[$i];
        $result = mysql_query($sql);
        mysql_query(“set names ‘utf8′”);
        if ($result) {
            echo “成功:”.$i.”个查询
“;
        } else {
            echo “导入失败:”.mysql_error();
        }
    }
?>

发表评论

电子邮件地址不会被公开。 必填项已用*标注