PHP Backup Database code (build word,excel,json,xml,sql) _php Tutorial

Single-table backup
Code:
Copy CodeThe code is as follows:
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;
}

Several cases of $con
Empty: Return all records
Array:eg. Array (' id ' = ' 1 ') returns record of id=1
String:eg. ' Id=1 ' returns records of Id=1
function Toextjson ($table, $start = "0", $limit = "Ten", $cons = "")
{
$sql = $this->generatesql ($table, $cons);
$totalNum = $this->findcount ($sql);
$result = $this->findbysql ($sql. " LIMIT ". $start.", ". $limit);
$resultNum = count ($result);//Current number of results
$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 conditions
$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 = "Ten", $cons = "")
{
$sql = $this->generatesql ($table, $cons);
$totalNum = $this->findcount ($sql);
$result = $this->findbysql ($sql. " LIMIT ". $start.", ". $limit);
$resultNum = count ($result);//Current number of results
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;
}

Output Word table
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]http://www.w3.org/tr/rec-html40[/url]" >


<title>'. $fileName. '</title>

';
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 ");
Echoxmlns:x= "Urn:schemas-microsoft-com:office:excel"
Xmlns= "[Url=http://www.w3.org/tr/rec-html40]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");//the Saved file name
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 = "")
{
Exclude file,content are empty or both are not empty cases
if ($file = = "" && $content = = "") | | ($file! = "" && $content! = ""))
echo "Parameter Error";
$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;
}
}

Clear the table to recover data
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 ();

Build Word
$map =array (' No ', ' Name ', ' Email ', ' age ');
echo $db->toword (' Test ', $map, ' archives ');

Build Excel
$map =array (' No ', ' Name ', ' Email ', ' age ');
echo $db->toexcel (' Test ', $map, ' archives ');

Generate XML
echo $db->toextxml (' Test ', 0,20);

Generate Json
echo $db->toextjson (' Test ', 0,20);

Backup
echo $db->backuptofile (' Test ', ' backup ');
?>

Full Table Backup
Copy CodeThe code is as follows:
$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);
}

Import Database
Copy CodeThe code is as follows:
/************
*
PHP Import. sql file
Run version: PHP5,PHP4 when used, select
Author: panxp
Mail: 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 version **/
$get _sql_data = file_get_contents ($file _name, $file _dir);
/**
* PHP4 Version
if (file_exists ($file _dir. /". $file _name))
{
$get _sql_data = fopen ($file _dir. " /". $file _name," R ");
if (! $get _sql_data)
{
echo "Cannot open file";
}
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 "Success:". $i. " A query
";
} else {
echo "Import failed:". Mysql_error ();
}
}
?>

http://www.bkjia.com/PHPjc/327829.html www.bkjia.com true http://www.bkjia.com/PHPjc/327829.html techarticle Single-table backup code: Copy the Code as follows:? PHP class db {var $conn; function Db ($host = "localhost", $user = "root", $pass = "root", $db = "Test") {I F (! $this-conn=mysql_connect ($hos ...

