Php backup database code (generate word, excel, json, xml, SQL)

This article provides a detailed analysis of php backup database code (word, excel, json, xml, SQL). For more information, see Single table backup
Code:

The 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 situations of $ con
// Null: all records are returned.
// Array: eg. array ('id' => '1') returns the record with id = 1
// String: eg. 'Id = 1' returns the record with 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); // number of current 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 condition
$ 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); // number of current 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 \ N ";
Foreach ($ result [$ I] as $ key => $ val)
$ Xml. = "\ t <". $ key. ">". $ val ." \ N ";
$ Xml. = "\ t \ N ";
}
$ Xml. = "\ t \ N ";
$ Xml. =" \ N ";
Return $ xml;
}

// Output the word table
Function toWord ($ table, $ mapping, $ fileName)
{
Header ('content-type: application/doc ');
Header ('content-Disposition: attachment; filename = "'.w.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]">


'. $ 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]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"); // 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 the case where file and content are empty or none are empty
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 restore 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 ();

// Generate word
// $ Map = array ('no', 'name', 'Email ', 'age ');
// Echo $ db-> toWord ('test', $ map, 'Archives ');

// Generate an Excel file
// $ 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

The 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 ");
}
}
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

The code is as follows:

/************
*
PHP import. SQL file
Running version: php5, select when using php4
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 "file cannot be opened ";
}
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. "Queries
";
} Else {
Echo "import failed:". mysql_error ();
}
}
?>