數據庫信息導出：word，excel，json，xml，sql 數據庫恢復：從sql，從文件 具體用法: 首先新建測試用數據庫mytest，然後在裡面建張表 PHP代碼: 以下是代碼片段： -- -- 表的結構 `test` -- CREATE TABLE `test` ( `id` int(11) NOT NULL auto_increment, `name` varchar(100) NOT NULL, `email` varchar(200) NOT NULL, `age` int(3) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ; -- -- 導出表中的數據 `test` -- INSERT INTO `test` (`id`, `name`, `email`, `age`) VALUES (1, 'pjq518', [email=]'[email protected]'[/email], 22), (2, 'xiaoyu', [email=]'[email protected]'[/email], 21); 1.導出ext能方便調用的json PHP代碼: 以下是代碼片段： $db=new db(); echo $db->toExtJson('test'); //輸出結果為 //{'totalCount':'2','rows':[{'id':'1','name':'pjq518','email':'[email protected]','age':'22'},{'id':'2','name':'xiaoyu','email':'[email protected]','age':'21'}]} toExtJson( $table, $start="0", $limit="10", $cons="")有4個參數， $table為表名， $cons為條件，可以為string或array 2、導出xml PHP代碼: 以下是代碼片段： $db=new db(); echo $db->toExtXml('test'); //輸出結果 3、導出excel和word PHP代碼: 以下是代碼片段： $db=new db(); //toExcel $map=array('No','Name','Email','Age');//表頭 $db->toExcel('test', $map,'檔案'); //導出word表格 // $db->toWord('test', $map,'檔案'); //效果如下圖 PHP代碼: <?php class Db { var $conn; /*************************************************************************** * 連接數據庫 * return：MySQL 連接標識,失敗返回FALSE **************************************************************************/ function Db($host="localhost",$user="root",$pass="123456",$db="juren_gaokao") { 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'"); } /*************************************************************************** * 執行SQL查詢 * return:查詢結構集 resource **************************************************************************/ function execute($sql) { return mysql_query($sql,$this->conn); } /*************************************************************************** * 返回結構集中行數 * return：number 數字 **************************************************************************/ function findCount($sql) { $result=$this->execute($sql); return mysql_num_rows($result); } /*************************************************************************** * 執行SQL查詢 * return：array 數組 **************************************************************************/ 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的記錄 * return：json 格式數據 ***************************************************************************/ 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; } /*************************************************************************** * $table:表名 * $cons:sql條件 * return:SQL語句 **************************************************************************/ 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; } /*************************************************************************** * $table:表名 * $cons：條件 * return：XML格式文件 **************************************************************************/ 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" ?>'; $xml.="<xml>"; $xml.="<totalCount>".$totalNum."</totalCount>"; $xml.="<items>"; for($i=0;$i<$resultNum;$i++) { $xml.="<item>"; foreach($result[$i] as $key=>$val) $xml.="<".$key.">".$val."</".$key.">"; $xml.="</item>"; } $xml.="</items>"; $xml.="</xml>"; return $xml; } /*************************************************************************** * $table:表名 * $mapping：數組格式頭信息$map=array('No','Name','Email','Age'); * $fileName：WORD文件名稱 * return：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>'; } /*************************************************************************** * $table:表名 * $mapping：數組格式頭信息$map=array('No','Name','Email','Age'); * $fileName：Excel文件名稱 * return：Excel格式文件 **************************************************************************/ 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); } } /*************************************************************************** * 備份數據庫數據到文件 * $table：表名 * $file：文件名 **************************************************************************/ 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="rn"; 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; }
