欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  php教程

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

程序员文章站 2022-06-11 23:33:16
...

本篇文章是对php备份数据库代码(生成word,excel,json,xml,sql)进行了详细的分析介绍,需要的朋友参考下

单表备份
代码:

复制代码 代码如下:


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.="\t\n";
for($i=0;$i $xml.="\t\t\n";
foreach($result[$i] as $key=>$val)
$xml.="\t\t\t".$val."".$key.">\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="[url=http://www.w3.org/TR/REC-html40][/url]">



'.$fileName.'

';
echo'';
if(is_array($mapping))
{
foreach($mapping as $key=>$val)
echo'';
}
echo'';
$results=$this->findBySql('select * from '.$table);
foreach($results as $result)
{
echo'';
foreach($result as $key=>$val)
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="[url=http://www.w3.org/TR/REC-html40][/url]">





';
echo'';
echo'';
if(is_array($mapping))
{
foreach($mapping as $key=>$val)
echo'';
}
echo'';
$results=$this->findBySql('select * from '.$table);
foreach($results as $result)
{
echo'';
foreach($result as $key=>$val)
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');
?>

整表备份

复制代码 代码如下: