How to export tables to json/word/xml/excel_PHP in php

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 :...