In php, how to export tables to jsonwordxmlexcel from the database. Database information export: word, excel, json, xml, SQL database recovery: From SQL, from file usage: first create a test database mytest, and then create a table PHP code in it: export database information: word, excel, json, xml, SQL
Database recovery: From SQL, from files
Usage:
Create a new test database mytest, and then create a table in it.
PHP code:
The following is a code snippet:
--
-- Table structure 'test'
--
Create table 'test '(
'Id' int (11) not null auto_increment,
'Name' varchar (100) not null,
'Email 'varchar (200) not null,
'Age' int (3) not null,
Primary key ('id ')
) ENGINE = MyISAM default charset = utf8 AUTO_INCREMENT = 3;
--
-- Export the table data 'test'
--
Insert into 'test' ('id', 'name', 'Email ', 'age') VALUES
(1, 'pjq518', [email =] 'pjq518 @ 126.com '[/email], 22 ),
(2, 'xiaoyu ', [email =] 'xiaoyu @ 126.com' [/email], 21 );
1. export json files that can be conveniently called by ext.
PHP code:
The following is a code snippet:
Databases $ db = new db ();
Echo response $ db-> toExtJson ('test ');
// The output result is
// {'Totalcount': '2', 'rows ': [{'id': '1', 'name': 'pjq518', 'Email ': 'pjq518 @ 126.com ', 'age': '22'}, {'id': '2', 'name': 'xiaoyu', 'Email ': 'xiaoyu @ 126.com ', 'age': '21'}]}
ToExtJson (cost $ table, cost $ start = "0", cost $ limit = "10", cost $ cons = "") has four parameters, tables $ table is the table name, tables $ cons is the condition, and can be string or array
2. export xml
PHP code:
The following is a code snippet:
Databases $ db = new db ();
Echo response $ db-> toExtXml ('test ');
// Output result
3. export excel and word
PHP code:
The following is a code snippet:
Databases $ db = new db ();
// ToExcel
Response $ map = array ('no', 'name', 'Email ', 'age'); // header
Databases $ db-> toExcel ('test', tables $ map, 'Archives ');
// Export the word table
// Response $ db-> toWord ('test', response $ map, 'Archive ');
// The result is as follows:
PHP code:
Class Db {
Var $ conn;
/*************************************** ************************************
* Connect to the database
* Return: MySQL connection id. if the connection fails, FALSE is returned.
**************************************** **********************************/
Function Db ($ host = "localhost", $ user = "root", $ pass = "123456", $ db = "juren_gaokao "){
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 '");
}
/*************************************** ************************************
* Execute an SQL query
* Return: queries the structure set resource.
**************************************** **********************************/
Function execute ($ SQL ){
Return mysql_query ($ SQL, $ this-> conn );
}
/*************************************** ************************************
* Returns the number of rows in the constructor.
* Return: number
**************************************** **********************************/
Function findCount ($ SQL ){
$ Result = $ this-> execute ($ SQL );
Return mysql_num_rows ($ result );
}
/*************************************** ************************************
* Execute an SQL query
* Return: array
**************************************** **********************************/
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
* Return: json format data
**************************************** ***********************************/
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': 'salary $ 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;
}
/*************************************** ************************************
* $ Table: table name
* $ Cons: SQL condition
* Return: SQL statement
**************************************** **********************************/
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;
}
/*************************************** ************************************
* $ Table: table name
* $ Cons: condition
* Return: XML format file
**************************************** **********************************/
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 =' ';
$ Xml. =" ";
$ Xml. =" ". $ TotalNum ." ";
$ Xml. =" ";
For ($ I = 0; $ I <$ resultNum; $ I ++ ){
$ Xml. =" ";
Foreach ($ result [$ I] as $ key => $ val)
$ Xml. = "<". $ key. ">". $ val ." ";
$ Xml. ="";
}
$ Xml. ="";
$ Xml. ="";
Return $ xml;
}
/*************************************** ************************************
* $ Table: table name
* $ Mapping: array format header information $ map = array ('no', 'name', 'Email ', 'age ');
* $ FileName: name of the WORD file
* Return: WORD format file
**************************************** **********************************/
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'';
}
/*************************************** ************************************
* $ Table: table name
* $ Mapping: array format header information $ map = array ('no', 'name', 'Email ', 'age ');
* $ FileName: Excel file name
* Return: Excel files
**************************************** **********************************/
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 );
}
}
/*************************************** ************************************
* Back up database data to files
* $ Table: table name
* $ File: file name
**************************************** **********************************/
Function Backuptofile ($ table, $ file ){
Header ("Content-disposition: filename = response $ 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 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 orders $ tab ");
} Else {
$ This-> execute ("truncate table limit $ table ");
}
}
Function get_table_content ($ table ){
$ Results = $ this-> findBySql ("select * from $ table ");
$ Temp = "";
$ Crlf = "rn ";
Foreach ($ results as $ result ){
/*(";
Foreach (response $ result as response $ key => values $ val)
{
$ Schema_insert. = "" '. Keys $ key ."',";
}
$ Schema_insert = ereg_replace (", region $", "", region $ schema_insert );
$ Schema_insert. = ")
*/
$ Schema_insert = "insert into values $ table VALUES (";
Foreach ($ result as $ key => $ val ){
If ($ val! = "")
$ Schema_insert. = "'". addslashes ($ val )."',";
Else
$ Schema_insert. = "NULL ,";
}
$ Schema_insert = ereg_replace (", region $", "", $ schema_insert );
$ Schema_insert. = "); explain $ 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;
}
Recover database: From SQL, use the file: first create a test database mytest, and then create a table in PHP code :...