利用php导出mysql数据库为excel表格的方法很多，最简单的就直接使用php fputcsv函数了，还有就是直接输入csv格式也是可以了，要生成excel标准格式我们需使用第三方插件了
方法一，利用fputcsv
| 代码如下 |
复制代码 |
|
// 输出Excel文件头，可把user.csv换成你要的文件名
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="user.csv"');
header('Cache-Control: max-age=0');
// 从数据库中获取数据，为了节省内存，不要把数据一次性读到内存，从句柄中一行一行读即可
$sql = 'select * from tbl where ……';
$stmt = $db->query($sql);
// 打开PHP文件句柄，php://output 表示直接输出到浏览器
$fp = fopen('php://output', 'a');
// 输出Excel列名信息
$head = array('姓名', '性别', '年龄', 'Email', '电话', '……');
foreach ($head as $i => $v) {
// CSV的Excel支持GBK编码，一定要转换，否则乱码
$head[$i] = iconv('utf-8', 'gbk', $v);
}
// 将数据通过fputcsv写到文件句柄
fputcsv($fp, $head);
// 计数器
$cnt = 0;
// 每隔$limit行，刷新一下输出buffer，不要太大，也不要太小
$limit = 100000;
// 逐行取出数据，不浪费内存
while ($row = $stmt->fetch(Zend_Db::FETCH_NUM)) {
$cnt ++;
if ($limit == $cnt) { //刷新一下输出buffer，防止由于数据过多造成问题
ob_flush();
flush();
$cnt = 0;
}
foreach ($row as $i => $v) {
$row[$i] = iconv('utf-8', 'gbk', $v);
}
fputcsv($fp, $row);
}
|
方法二，直接在浏览器用header输出csv格式的数据
| 代码如下 |
复制代码 |
|
<?php
/*连接数据库*/
$DB_Server = "localhost";
$DB_Username = "root";
$DB_Password = "123456";
$DB_DBName = "mydb"; //目标数据库名
$DB_TBLName = "mytable"; //目标表名
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect.");
mysql_query("Set Names 'utf8'");
$savename = date("YmjHis"); //导出excel文件名
$file_type = "vnd.ms-excel";
$file_ending = "xls";
header("Content-Type: application/$file_type;charset=utf8");
header("Content-Disposition: attachment; filename=".$savename.".$file_ending");
//header("Pragma: no-cache");
/*写入备注信息*/
$now_date = date("Y-m-j H:i:s");
$title = "数据库名:$DB_DBName,数据表:$DB_TBLName,备份日期:$now_date";
echo("$titlen");
/*查询数据库*/
$sql = "Select * from $DB_TBLName";
$ALT_Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database");
$result = @mysql_query($sql,$Connect) or die(mysql_error());
/*写入表字段名*/
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . ",";
}
echo "n";
/*写入表数据*/
$sep = ",t";
while($row = mysql_fetch_row($result)) {
$data = "";
for($i=0; $i<mysql_num_fields($result);$i++) {
if(!isset($row[$i]))
$data .= "NULL".$sep; //处理NULL字段
elseif ($row[$i] != "")
$data .= "$row[$i]".$sep;
else
$data .= "".$sep; //处理空字段
}
echo $data."n";
}
?>
|
例3，第二个差不多了
| 代码如下 |
复制代码 |
|
//搜索
$start_time = strtotime($start_date);
$end_time = strtotime($end_date);
$sql = "select a.*,b.order_amount,b.money_paid from ".$ecs->table('invoice')." as a ".
" left join ".$ecs->table('order_info')." as b on a.order_id=b.order_sn".
" where a.add_time >=".$start_time." and a.add_time <=".$end_time." ";
$temp_list = $db->getAll($sql);
if($temp_list){//有数据
$Html='<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><body>'.chr(13).chr(10);
$Html.='<table width="700" border="1" align="center" cellpadding="2" cellspacing="1">
<tr align="center">
<td align="center" nowrap="nowrap">时间：</td>
<td align="center" nowrap="nowrap" colspan="9">'.$start_date.'~'.$end_date.'</td>
</tr>
<tr align="center">
<td align="center" nowrap="nowrap">编号</td>
<td align="center" nowrap="nowrap">发票类型</td>
<td align="center" nowrap="nowrap">发票抬头</td>
<td align="center" nowrap="nowrap">发票内容</td>
<td align="center" nowrap="nowrap">订单号</td>
<td align="center" nowrap="nowrap">金额</td>
<td align="center" nowrap="nowrap">添加日期</td>
<td align="center" nowrap="nowrap">收件人</td>
<td align="center" nowrap="nowrap">联系方式</td>
<td align="center" nowrap="nowrap">地址</td>
</tr>';
//取得符合条件的数组
for($i=0;$i<count($temp_list);$i++){
$temp_i = $i+1;
if($temp_list[$i][order_amount]==0){
$temp_money = $temp_list[$i][money_paid];
}else{
$temp_money = $temp_list[$i][order_amount];
}
$temp_time = date('Y-m-d', $temp_list[$i]['add_time']);
$Html.='<tr align="center">
<td align="center" nowrap="nowrap">'.$temp_i.'</td>
<td align="center" nowrap="nowrap">'.$temp_list[$i][type_name].'</td>
<td align="center" nowrap="nowrap">'.$temp_list[$i][top].'</td>
<td align="center" nowrap="nowrap">'.$temp_list[$i][content].'</td>
<td align="center" nowrap="nowrap" style="vnd.ms-excel.numberformat:@">'.$temp_list[$i][order_id].'</td>
<td align="center" nowrap="nowrap">'.$temp_money.'</td>
<td align="center" nowrap="nowrap">'.$temp_time.'</td>
<td align="center" nowrap="nowrap">'.$temp_list[$i][user_name].'</td>
<td align="center" nowrap="nowrap">'.$temp_list[$i][mobile].' '.$temp_list[$i][tel].' </td>
<td align="center" nowrap="nowrap">'.$temp_list[$i][address].'</td>
</tr>';
}
$Html.='</table>';
$Html.='</body></html>';
$mime_type = 'application/vnd.ms-excel';
header('Content-Type: ' . $mime_type);
header('Content-Disposition: attachment; filename="invoice.xls"');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
echo $Html;
|
有时excel会自动把数字转换格式，于是有些手机号码，身份证之类的就乱了，因此可以在导出时，先定义好
| 代码如下 |
复制代码 |
|
<td align="center" nowrap="nowrap" style="vnd.ms-excel.numberformat:@">'.$temp_list[$i][order_id].'</td>
|
在php中生成csv文件的方法很是简单，这里我来介绍一下自己用到的一个实例，把数组直接转换成csv文件输出，有需要了解的朋友可参考。
例
| 代码如下 |
复制代码 |
|
<?php
$data = array(
array( 'row_1_col_1', 'row_1_col_2', 'row_1_col_3' ),
array( 'row_2_col_1', 'row_2_col_2', 'row_2_col_3' ),
array( 'row_3_col_1', 'row_3_col_2', 'row_3_col_3' ),
);
$filename = "example";
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename={$filename}.csv");
header("Pragma: no-cache");
header("Expires: 0");
outputCSV($data);
function outputCSV($data) {
$outputBuffer = fopen("php://output", 'w');
foreach($data as $val) {
foreach ($val as $key => $val2) {
$val[$key] = iconv('utf-8', 'gbk', $val2);// CSV的Excel支持GBK编码，一定要转换，否则乱码
}
fputcsv($outputBuffer, $val);
}
fclose($outputBuffer);
}
?>
|
例2
看一个读取csv文件的实例
读取cvs，使用fgetcsv()文件指针中读入一行并解析 CSV 字段
比如说我要读取如下csv文件
| 代码如下 |
复制代码 |
|
<?php
/** by www.111cn.net */
$row = 1;
$handle = fopen("file.csv","r");
//fgetcsv() 解析读入的行并找出 CSV格式的字段然后返回一个包含这些字段的数组。
while ($data = fgetcsv($handle, 1000, ",")) {
$num = count($data);
echo "<p> $num fields in line $row: <br>n";
$row++;
for ($c=0; $c < $num; $c++) {
//注意中文乱码问题
$data[$c]=iconv("gbk", "utf-8//IGNORE",$data[$c]);
echo $data[$c] . "<br>n";
}
}
fclose($handle);
?>
|
本文章来给各位同学介绍一篇关于Yii多数据库主从读写分离实例，希望此文章对各位同学会有所帮助哦。
Yii框架数据库多数据库、主从、读写分离 实现
功能描述：
1.实现主从数据库读写分离 主库：写 从库(可多个)：读
2.主数据库无法连接时 可设置从数据库是否 可写
3.所有从数据库无法连接时 可设置主数据库是否 可读
4.如果从数据库连接失败 可设置N秒内不再连接
利用yii扩展实现：
| 代码如下 |
复制代码 |
|
<?php
/**
* 主数据库 写 从数据库（可多个）读
* 实现主从数据库 读写分离 主服务器无法连接 从服务器可切换写功能
* 从务器无法连接 主服务器可切换读功
* by lmt
* */
class DbConnectionMan extends CDbConnection {
public $timeout = 10; //连接超时时间
public $markDeadSeconds = 600; //如果从数据库连接失败 600秒内不再连接
//用 cache 作为缓存全局标记
public $cacheID = 'cache';
/**
* @var array $slaves.Slave database connection(Read) config array.
* 配置符合 CDbConnection.
* @example
* 'components'=>array(
* 'db'=>array(
* 'connectionString'=>'mysql://<master>',
* 'slaves'=>array(
* array('connectionString'=>'mysql://<slave01>'),
* array('connectionString'=>'mysql://<slave02>'),
* )
* )
* )
* */
public $slaves = array();
/**
*
* 从数据库状态 false 则只用主数据库
* @var bool $enableSlave
* */
public $enableSlave = true;
/**
* @var slavesWrite 紧急情况主数据库无法连接 切换从服务器（读写）.
*/
public $slavesWrite = false;
/**
* @var masterRead 紧急情况从主数据库无法连接 切换从住服务器（读写）.
*/
public $masterRead = false;
/**
* @var _slave
*/
private $_slave;
/**
* @var _disableWrite 从服务器（只读）.
*/
private $_disableWrite = true;
/**
*
* 重写 createCommand 方法,1.开启从库 2.存在从库 3.当前不处于一个事务中 4.从库读数据
* @param string $sql
* @return CDbCommand
* */
public function createCommand($sql = null) {
if ($this->enableSlave && !empty($this->slaves) && is_string($sql) && !$this->getCurrentTransaction() && self::isReadOperation($sql) && ($slave = $this->getSlave())
) {
return $slave->createCommand($sql);
} else {
if (!$this->masterRead) {
if ($this->_disableWrite && !self::isReadOperation($sql)) {
throw new CDbException("Master db server is not available now!Disallow write operation on slave server!");
}
}
return parent::createCommand($sql);
}
}
/**
* 获得从服务器连接资源
* @return CDbConnection
* */
public function getSlave() {
if (!isset($this->_slave)) {
shuffle($this->slaves);
foreach ($this->slaves as $slaveConfig) {
if ($this->_isDeadServer($slaveConfig['connectionString'])) {
continue;
}
if (!isset($slaveConfig['class']))
$slaveConfig['class'] = 'CDbConnection';
$slaveConfig['autoConnect'] = false;
try {
if ($slave = Yii::createComponent($slaveConfig)) {
Yii::app()->setComponent('dbslave', $slave);
$slave->setAttribute(PDO::ATTR_TIMEOUT, $this->timeout);
$slave->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
$slave->setActive(true);
$this->_slave = $slave;
break;
}
} catch (Exception $e) {
$this->_markDeadServer($slaveConfig['connectionString']);
Yii::log("Slave database connection failed!ntConnection string:{$slaveConfig['connectionString']}", 'warning');
continue;
}
}
if (!isset($this->_slave)) {
$this->_slave = null;
$this->enableSlave = false;
}
}
return $this->_slave;
}
public function setActive($value) {
if ($value != $this->getActive()) {
if ($value) {
try {
if ($this->_isDeadServer($this->connectionString)) {
throw new CDbException('Master db server is already dead!');
}
//PDO::ATTR_TIMEOUT must set before pdo instance create
$this->setAttribute(PDO::ATTR_TIMEOUT, $this->timeout);
$this->open();
} catch (Exception $e) {
$this->_markDeadServer($this->connectionString);
$slave = $this->getSlave();
Yii::log($e->getMessage(), CLogger::LEVEL_ERROR, 'exception.CDbException');
if ($slave) {
$this->connectionString = $slave->connectionString;
$this->username = $slave->username;
$this->password = $slave->password;
if ($this->slavesWrite) {
$this->_disableWrite = false;
}
$this->open();
} else { //Slave also unavailable
if ($this->masterRead) {
$this->connectionString = $this->connectionString;
$this->username = $this->username;
$this->password = $this->password;
$this->open();
} else {
throw new CDbException(Yii::t('yii', 'CDbConnection failed to open the DB connection.'), (int) $e->getCode(), $e->errorInfo);
}
}
}
} else {
$this->close();
}
}
}
/**
* 检测读操作 sql 语句
*
* 关键字： SELECT,DECRIBE,SHOW ...
* 写操作:UPDATE,INSERT,DELETE ...
* */
public static function isReadOperation($sql) {
$sql = substr(ltrim($sql), 0, 10);
$sql = str_ireplace(array('SELECT', 'SHOW', 'DESCRIBE', 'PRAGMA'), '^O^', $sql); //^O^,magic smile
return strpos($sql, '^O^') === 0;
}
/**
* 检测从服务器是否被标记 失败.
*/
private function _isDeadServer($c) {
$cache = Yii::app()->{$this->cacheID};
if ($cache && $cache->get('DeadServer::' . $c) == 1) {
return true;
}
return false;
}
/**
* 标记失败的slaves.
*/
private function _markDeadServer($c) {
$cache = Yii::app()->{$this->cacheID};
if ($cache) {
$cache->set('DeadServer::' . $c, 1, $this->markDeadSeconds);
}
}
}
|
main.php配置：components 数组中
| 代码如下 |
复制代码 |
|
'db'=>array(
'class'=>'application.extensions.DbConnectionMan',//扩展路径
'connectionString' => 'mysql:host=192.168.1.128;dbname=db_xcpt',//主数据库 写
'emulatePrepare' => true,
'username' => 'root',
'password' => 'root',
'charset' => 'utf8',
'tablePrefix' => 'xcpt_', //表前缀
'enableSlave'=>true,//从数据库启用
'urgencyWrite'=>true,//紧急情况 主数据库无法连接 启用从数据库 写功能
'masterRead'=>true,//紧急情况 从数据库无法连接 启用主数据库 读功能
'slaves'=>array(//从数据库
array( //slave1
'connectionString'=>'mysql:host=localhost;dbname=db_xcpt',
'emulatePrepare' => true,
'username'=>'root',
'password'=>'root',
'charset' => 'utf8',
'tablePrefix' => 'xcpt_', //表前缀
),
array( //slave2
'connectionString'=>'mysql:host=localhost;dbname=db_xcpt',
'emulatePrepare' => true,
'username'=>'root',
'password'=>'root',
'charset' => 'utf8',
'tablePrefix' => 'xcpt_', //表前缀
),
),
),
|
php与mysql本来就是天生的一对了，下面我来介绍在php程序中怎么调用在mysql中己经写好的存储过程，大家一起来看看具体实现方法。
Mysql存储过程创建语法
| 代码如下 |
复制代码 |
|
CREATE PROCEDURE和CREATE FUNCTION
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
routine_body:
Valid SQL procedure statement or statements
|
当我们看完以后，就可以开始写一些简单的存储过程了.
首先建立存储过程, Create procedure（子程序）、Create function（函数）
| 代码如下 |
复制代码 |
|
Create procedure sp_Name ([proc_parameter ])
routine_body
|
这里的参数类型可以是 IN OUT INOUTT，意思和单词的意思是一样的，IN 表示是传进来的参数， OUT 是表示传出去的参数，INOUT 是表示传进来但最终传回的参数。
| 代码如下 |
复制代码 |
|
Create functionsp_Name ([func_parameter ])
Returns type
Routine_body
|
Returns type 指定了返回的类型，这里给定的类型与返回值的类型要是一样的，否则会报错。
下面是一个简单的例子:
| 代码如下 |
复制代码 |
|
mysql> delimiter //
mysql> create procedure g
-> begin
-> select version() i
-> end
-> //
Query OK, 0 rows affected
mysql> call getversion(@a
-> //
Query OK, 0 rows affected
mysql> select @a;
-> //
+---------------------+
| @a |
+---------------------+
| 5.0.45-community-nt |
+---------------------+
1 row in set (0.05 sec)
|
一个获取当前mysql版本的存储过程.那么php怎么与mysql的存储过程相结合呢.
//以下来自百度知道:
| 代码如下 |
复制代码 |
|
Drop table if exists user;
Create table user(
Id int unsigned not null auto_increment,
Name varchar(20) not null,
Pwd char(32) not null,
Primary key(Id)
);
|
添加用户的存储过程：
| 代码如下 |
复制代码 |
|
Delimiter //
Create procedure insertuser(in username varchar(20),in userpwd varchar(32))
Begin
Insert into welefen.user(Name,Pwd) values (username,md5(userpwd));
End
//
|
验证用户的存储过程：
| 代码如下 |
复制代码 |
|
Delimiter //
Create procedure validateuser(in username varchar(20),out param1)
Begin
Select Pwd into param1 from welefen.user where Name=username;
End
//
|
修改密码的存储过程：
| 代码如下 |
复制代码 |
|
Delimiter //
Create procedure modifyPwd(in username varchar(20),in userpwd varchar(32))
Begin
Update welefen.user set Pwd=md5(userpwd) where Name=username;
End
//
|
删除用户的存储过程：
| 代码如下 |
复制代码 |
Delimiter //
Create procedure deleteuser(in username varchar(20))
Begin
delete from welefen.user where Name=username;
End
// |
在客户端，我们给出如下的程序：
| 代码如下 |
复制代码 |
|
<?php
if (!mysql_connect("localhost","root","welefen")){
echo "连接数据库失败";
}
if (!mysql_select_db("welefen")){
echo "选择数据库表失败<br>";
}
$insert_user=array("welefen","welefen");//这里的welefen分别为用户名、密码
if (mysql_query("call insertuser('$insert_user[0]','$insert_user[1]')")){
echo "添加用户$insert_user[0]成功<br>";
}else {
echo "添加用户$insert_user[0]失败<br>";
}
$validate_user=array("welefen","welefen");//这里的welefen分别为用户名、密码
mysql_query("call validateuser('$validate_user[0]',@a)");
$Pwd=mysql_query("select @a");
$result=mysql_fetch_array($Pwd);
if ($result[0]==md5($validate_user[1])){
echo "用户$validate_user[0]验证正确<br>";
}else {
echo "用户$validate_user[0]验证错误<br>";
}
$modify_Pwd=array("welefen","weilefeng"); //welefen为用户名weilefeng为新密码
if (mysql_query("call modifyPwd('$modify_Pwd[0]','$modify_Pwd[1]')")){
echo "用户$modigy_Pwd[0]的密码修改成功<br>";
}else {
echo "用户$modigy_Pwd[0]的密码修改失败<br>";
}
$delete_user=array("welefen"); //welefen为用户名
if (mysql_query("call deleteuser('$delete_user[0]')")){
echo "用户$delete_user[0]删除成功<br>";
}else {
echo "用户$delete_user[0]删除失败<br>";
}
?>
|
这样就完成了,php调用mysql的存储过程,其实这些简单的应用,就用不上存储过程了,实际的应用是比这个复杂的多. 可以看出，建立了mysql的存储过程可以极大的减少了客服端的压力,但是增加了数据库服务的压力,各种利弊得实际去衡量.
本文章来给大家介绍一个PHP利用XML备份MySQL数据库实例，这种方法个人认为只适用小数据量，并且安全性要求不高的用户了。
以下是在Linux下通过Apache+PHP对Mysql数据库的备份的文件代码：
文件一、Listtable.php （文件列出数据库中的所有表格，供选择备份）
请选择要备份的表格:
| 代码如下 |
复制代码 |
$con=mysql_connect('localhost','root','xswlily');
$lists=mysql_list_tables("embed",$con);
//数据库连接代码
$i=0;
while($i$tb_name=mysql_tablename($lists,$i);
echo "".$tb_name."
";
//列出所有的表格
$i++;}
?>
|
文件二、Backup.php
| 代码如下 |
复制代码 |
|
"") header("Location:listtable.php");?>
$con=mysql_connect('localhost','root','xswlily');
$query="select * from $table ";
//数据库查询
$result=mysql_db_query("embed",$query,$con);
$filestr="<"."?xml version="1.0" encoding="GB2312"?".">";
$filestr.="<".$table."s>";
while ($row=mysql_fetch_array($result))
//列出所有的记录
{$filestr.="<".$table.">";
$fields=mysql_list_fields("embed",$table,$con);
$j=0;
//$num_fields=mysql_field_name($fields,$j);
//echo $num_fields;
while ($j$num_fields=mysql_field_name($fields,$j);
$filestr.="<".$num_fields.">";
$filestr.=$row[$j];
$filestr.="";
$j++;}
$filestr.="";
}
$filestr.="";
echo $filestr;
//以下是文件操作代码
$filename=$table.".xml";
$fp=fopen("$filename","w");
fwrite($fp,$filestr);
fclose($fp);
Echo "数据表".$table."已经备份成功！";?>
|
通过以上文件的操作就可以实现对数据库中选定的表格进行备份.
以上主要介绍了通过PHP实现XML备份数据库的操作方法，其实并不复杂，通过XML，我们可以备份各种各样的数据库，当然也可以通过相关的方法将备份的XML文档恢复到数据库中
标签：[!--infotagslink--]