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

天梯梦

浏览: 13730983 次
性别:
来自: 洛杉矶

最近访客更多访客>>

xierui54

简_单

bcrun

hur5457

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

Php / Pear / Mysql / Node.js

SQL XML Excel PHP json

单表备份

代码：

<?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');


?>

整表备份

<?
backup_tables('localhost','root','root','test');


/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{
  
  $link = mysql_connect($host,$user,$pass);
  mysql_select_db($name,$link);
  
  //get all of the tables
  if($tables == '*')
  {
    $tables = array();
    $result = mysql_query('SHOW TABLES');
    while($row = mysql_fetch_row($result)) $tables[] = $row[0];
  }
  else $tables = is_array($tables) ? $tables : explode(',',$tables);
  
  //cycle through
  foreach($tables as $table)
  {
    $result = mysql_query('SELECT * FROM '.$table);
    $num_fields = mysql_num_fields($result);
    
    $return.= 'DROP TABLE '.$table.';';
    $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
    $return.= "\n\n".$row2[1].";\n\n";
    
    for ($i = 0; $i < $num_fields; $i++) 
    {
      while($row = mysql_fetch_row($result))
      {
        $return.= 'INSERT INTO '.$table.' VALUES(';
        for($j=0; $j<$num_fields; $j++) 
        {
          $row[$j] = addslashes($row[$j]);
          $row[$j] = ereg_replace("\n","\\n",$row[$j]);
          if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
          if ($j<($num_fields-1)) { $return.= ','; }
        }
        $return.= ");\n";
      }
    }
    $return.="\n\n\n";
  }
  
  //save file
  $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
  fwrite($handle,$return);
  fclose($handle);
}

?>

或者：

$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 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] . ";\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
邮件: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 版本 **/
	$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函数前面加&符号和变量前面加&符 ... | php 封装JavaScript类

2010-04-25 23:11
浏览 6783
评论(0)
分类:编程语言
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

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

评论

发表评论

相关推荐

最近访客 更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

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

评论

发表评论

相关推荐

PHP: 在类(class)中加载动态函数, 变量函数或半变量函数 variable function/method

MySQL入门 (七) : 储存引擎与资料型态

MySQL入门 (六) : 字元集与资料库

MySQL入门 (五) : CRUD 与资料维护

MySQL入门 (四) : JOIN 与UNION 查询

PHP: 关键字global 和 超全局变量$GLOBALS的用法、解释、区别

MySQL入门 (三) : 运算式与函式

MySQL入门 (二) : SELECT 基础查询

MySQL入门 (一) : 资料库概论与MySQL的安装

MySQL入门 (九) : 子查询 Subquery

PHP: 用readonly取代disabled来获取input值 submit a disabled input in a form could not ge

PHP7革新与性能优化

Mysql: 图解 inner join、left join、right join、full outer join、union、union all的区别

Comet 反Ajax: 基于jQuery与PHP实现Ajax长轮询(LongPoll)

PHP：ServerPush (Comet推送) 技术的探讨

PHP: 手把手编写自己的 MVC 框架实例教程

PHP5: mysqli 插入, 查询, 更新和删除 Insert Update Delete Using mysqli (CRUD)

MongoDB 教程索引 (附有视频)

Node.js 模块之Nimble流程控制

Node.js web应用模块之Supervisor

最近访客更多访客>>

PHP: 关键字global 和超全局变量$GLOBALS的用法、解释、区别