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="\n"; $xml.="\n"; $xml.="\t".$totalNum."\n"; $xml.="\t\n"; for($i=0;$i<$resultNum;$i++){ $xml.="\t\t\n"; foreach($result[$i] as $key=>$val) $xml.="\t\t\t<".$key.">".$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 ' xmlns:w="urn:schemas-microsoft-com:office:word" xmlns="http://www.w3.org/TR/REC-html40"> '.$fileName.' '; echo' if(is_array($mapping)) } $results=$this->findBySql('select * from '.$table); foreach($result as $key=>$val) echo' } '; { foreach($mapping as $key=>$val) echo' '; echo' '; foreach($results as $result) { 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="http://www.w3.org/TR/REC-html40"> '; echo' echo' if(is_array($mapping)) } $results=$this->findBySql('select * from '.$table); foreach($result as $key=>$val) echo' } '; '; { foreach($mapping as $key=>$val) echo' '; echo' '; foreach($results as $result) { 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(); 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'); ?> 複製代碼 2、整表備份 1 2 下一頁 尾頁

