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

Source: Internet
Author: User
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 :...

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.