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

Source: Internet
Author: User
Tags import database
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 ();
}
}
?>

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.