使用 PHP 将 mysql 表导出到 .txt 或 .doc 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6574927/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Exporting mysql table to .txt or .doc file using PHP
提问by user446836
I have a mysql table that keeps a log of messages that users send every day. What I want to do is export the the message log once per day into a text file, and am not sure how to do this. Our server has phpmyadmin, and I can export the table into a text file manually, but I don't know how to either A) have phpmyadmin export this file automatically once per day, or B) write the exporting in php code. I want the exported files to be available to the users of my site for download. The site it written in php. If there is any other information you need to answer this question, let me know!
我有一个 mysql 表，用于保存用户每天发送的消息日志。我想要做的是每天将消息日志导出到一个文本文件中，但我不确定如何执行此操作。我们的服务器有phpmyadmin，我可以手动将表导出到文本文件中，但我不知道如何A）让phpmyadmin每天自动导出一次该文件，或者B）用php代码编写导出。我希望导出的文件可供我网站的用户下载。它用php编写的网站。如果您还需要其他信息来回答这个问题，请告诉我！
采纳答案by Bill Karwin
Be careful about rolling your own, unless you handle things like NULL, character sets, etc.
小心滚动你自己的，除非你处理像 NULL、字符集等。
First alternative:
第一种选择：
<?php
$pdo = new PDO(...);
$results = $pdo->query("SELECT * FROM myTable INTO OUTFILE 'data.txt'");
$dummy = $result->fetchAll();
The data.txt file will be written on the MySQL server. The directory must be writable by the uid of the mysqld process. It will not overwrite any existing file, and requires that you have the FILESQL privilege.
data.txt 文件将写入 MySQL 服务器。该目录必须可由 mysqld 进程的 uid 写入。它不会覆盖任何现有文件，并且需要您具有FILESQL 权限。
Second alternative: use mysqldump to output to flat text file (as @OMG Ponies mentioned):
第二种选择：使用 mysqldump 输出到纯文本文件（如@OMG Ponies 所述）：
mysqldump -t -T <directory> <database> <table>
This works like INTO OUTFILE, it needs to run on the MySQL server host, and the directory must be writable by the mysqld uid.
这就像INTO OUTFILE，它需要在 MySQL 服务器主机上运行，并且目录必须是 mysqld uid 可写的。
Third option: run query with mysql client and output text:
第三个选项：使用 mysql 客户端运行查询并输出文本：
mysql -B -e "SELECT * FROM MyTable" <database> > mytable.txt
This can be run on any host, and requires no special privileges or directory permissions. But NULL may not be handled as it would be with mysqldump or INTO OUTFILE.
这可以在任何主机上运行，并且不需要特殊权限或目录权限。但是 NULL 可能不会像使用 mysqldump 或INTO OUTFILE.
回答by anisha
<?php
$fh = fopen('data.txt', 'w');
$con = mysql_connect("localhost","root","");
mysql_select_db("db_name", $con);
/* insert field values into data.txt */
$result = mysql_query("SELECT * FROM table_name");
while ($row = mysql_fetch_array($result)) {
$last = end($row);
$num = mysql_num_fields($result) ;
for($i = 0; $i < $num; $i++) {
fwrite($fh, $row[$i]);
if ($row[$i] != $last)
fwrite($fh, ", ");
}
fwrite($fh, "\n");
}
fclose($fh);
?>
回答by EdoDodo
This simple PHP script will save all the data in a table to a .txt file. Records will be separated by new lines, and fields by a tab character. Here it is:
这个简单的 PHP 脚本会将表中的所有数据保存到 .txt 文件中。记录将由新行分隔，字段由制表符分隔。这里是：
<?
$fh = fopen('data.txt', 'w');
mysql_connect('host', 'username', 'password');
$result = mysql_query("SELECT * FROM myTable;");
while ($row = mysql_fetch_array($result)) {
$last = end($row);
foreach ($row as $item) {
fwrite($fh, $item);
if ($item != $last)
fwrite($fh, "\t");
}
fwrite($fh, "\n");
}
fclose($fh);
?>
回答by David
Well the main issue with this is script is that you did not select a database, that is, using "mysql_select_db" before "mysql_query". Then remove the semi-colon from "myTable(;)". Also create a blank text file within your root folder, this is where your data would be stored.Your script should work fine afterwards. This solution is for future users who might need this script to aid their design.
那么这个脚本的主要问题是您没有选择数据库，即在“mysql_query”之前使用“mysql_select_db”。然后从“myTable(;)”中删除分号。同时在您的根文件夹中创建一个空白文本文件，这是您的数据将被存储的地方。您的脚本之后应该可以正常工作。此解决方案适用于可能需要此脚本来帮助他们设计的未来用户。
回答by anisha
The answer I had posted earlier will work perfectly only when last field value in a row is not equal to any other field value in that same row.Bcoz the checking for comma separation is based on the last field value,now it changed to last field index.
我之前发布的答案只有在一行中的最后一个字段值不等于同一行中的任何其他字段值时才能完美运行。Bcoz 检查逗号分隔是基于最后一个字段值，现在它更改为最后一个字段指数。
If u use the previous code,then u cant get the proper comma separation near the field value which is equal to last field value in that row,other ways the code is also correct.
如果您使用以前的代码，那么您无法在字段值附近获得正确的逗号分隔，该值等于该行中的最后一个字段值，其他方式代码也是正确的。
<?php
$fh = fopen('data.txt', 'w');
$con = mysql_connect("localhost","root","");
mysql_select_db("db_name", $con);
/* insert field values into data.txt */
$result = mysql_query("SELECT * FROM table_name");
while ($row = mysql_fetch_array($result)) {
$num = mysql_num_fields($result) ;
$last = $num - 1;
for($i = 0; $i < $num; $i++) {
fwrite($fh, $row[$i]);
if ($i != $last) {
fwrite($fh, ",");
}
}
fwrite($fh, "\n");
}
fclose($fh);
?>
