標籤：func restore amp mfile dir 檔案 失敗 strong 產生word
單表備份<?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"> <!--[if gte mso 9]><xml> <x:ExcelWorkbook> <x:ExcelWorksheets> <x:ExcelWorksheet> <x:Name></x:Name> <x:WorksheetOptions> <x:DisplayGridlines/> </x:WorksheetOptions> </x:ExcelWorksheet> </x:ExcelWorksheets> </x:ExcelWorkbook> </xml><![endif]--> </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 functionsfunction 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(); } }?>
php 備份資料庫代碼(產生word,excel,json,xml,sql)
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
