In php, how to export the database table to json/word/xml/excel

Database information export: word, excel, json, xml, sql

Database recovery: from sql, from file

Specific usage:

First create a new test database mytest, and then create a table in it

PHP code:

The following is the 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 data in the table `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 that can be easily called by ext

PHP code:

The following is the code snippet:

$db=new db();

echo $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( $table, $start="0", $limit="10", $cons="") has 4 parameters, $table is the table name, $cons is the condition, which can be string or array

2. Export xml

PHP code:

The following is the code snippet:

$db=new db();

echo $db->toExtXml('test');

//Output results

3. Export excel and word

PHP code:

The following is the code snippet:

$db=new db();

//toExcel

$map=array('No','Name','Email','Age');//Header

$db->toExcel('test', $map,'file');

//Export word table

// $db->toWord('test', $map,'file');

//The effect is as shown below

PHP code:

class Db {

var $conn;

/***************************************************** *************************

* Connect to database

* return: MySQL connection identifier, failure returns FALSE

****************************************************** ****************************/

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 SQL query

* return: Query structure set resource

**************************************************** ****************************/

function execute($sql) {

return mysql_query($sql,$this->conn);

}

/************************************************ *******************************

* Returns the number of rows in the structure set

* return: number number

**************************************************************************/

function findCount($sql) {

$result=$this->execute($sql);

return mysql_num_rows($result);

}

/***************************************************** *************************

* Execute SQL query

* return: array 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

*Empty: Return all records

*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);//当前结果数

$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;

}

/***************************************************** *************************

* $table: table name

* $cons:sql condition

* return: SQL statement

****************************************************** ****************************/

function generateSql($table,$cons) {

$sql="";//sql条件

$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);//当前结果数

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.="";

return $xml;

}

/***************************************************** *************************

* $table: table name

* $mapping: array format header information $map=array('No','Name','Email','Age');

* $fileName: WORD file name

* return: WORD format file

****************************************************** ****************************/

function toWord($table,$mapping,$fileName) {

header('Content-type: application/doc');

header('Content-Disposition: attachment; filename="'.$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'

'.$val.'

echo'';

}

/***************************************************** *************************

* $table: table name

* $mapping: array format header information $map=array('No','Name','Email','Age');

* $fileName: Excel file name

* return: Excel format file

**************************************************** ****************************/

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'

'.$val.'

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 file

* $table: table name

* $file: file name

****************************************************** ****************************/

function Backuptofile($table,$file) {

header("Content-disposition: filename= $file.sql");//所保存的文件名

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="") {

//排除file，content都为空或者都不为空的情况

if(($file==""&&$content=="")||($file!=""&&$content!=""))

echo"参数错误";

$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;

}

//清空表，以便恢复数据

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="rn";

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;

}

Related labels：

e excel json php word xml information exist Export how database surface

source：php.cn

Previous article：First experience of Symfony 2.1 project development_PHP tutorial Next article：Changes in json_encode Chinese transcoding in PHP5.4 [Make json understand Chinese better]_PHP tutorial

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

In php, how to export the database table to json/word/xml/excel_PHP tutorial