Home >Backend Development >PHP Tutorial >In php, how to export the database table to json/word/xml/excel_PHP tutorial
In php, how to export the database table to json/word/xml/excel_PHP tutorial
- WBOYOriginal
- 2016-07-14 10:08:18931browse
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.="";
$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 '
'.$fileName.'
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]">
';
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 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'';
';
| '.$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 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;
}
Statement：
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
Previous article：First experience of Symfony 2.1 project development_PHP tutorialNext article：First experience of Symfony 2.1 project development_PHP tutorial