使用PHP将mysql表导出为.txt或.doc文件 [英] Exporting mysql table to .txt or .doc file using PHP

问题描述

我有一个mysql表，保存用户每天发送的消息日志。我想要做的是将消息日志每天导出一次到文本文件中，我不知道该怎么做。我们的服务器有phpmyadmin，我可以手动将表格导出到一个文本文件中，但是我不知道如何A）每天自动导出该文件一次phpmyadmin，或者B）使用php代码编写导出。我想要导出的文件可供我网站的用户下载。该网站写在php。如果有任何其他信息，您需要回答这个问题，让我知道！

解决方案

小心滚动自己的，除非你处理像NULL，字符集等的东西。

第一个选择：

 <？php 
 $ pdo = new PDO（...）; 
 $ results = $ pdo-> query（SELECT * FROM myTable INTO OUTFILE'data.txt'）; 
 $ dummy = $ result-> fetchAll（）; 
  

data.txt文件将写在MySQL服务器上。该目录必须由mysqld进程的uid写入。它不会覆盖任何现有文件，并要求您具有 FILE SQL权限。

第二种替代方法：使用mysqldump输出到平面文本文件（如所提到的@OMG小马）：

  mysqldump -t -T< directory> <数据库> <表> 
  

这样就像 INTO OUTFILE ，它需要在MySQL服务器主机上运行，​​该目录必须由mysqld uid写入。

第三个选项：使用mysql客户端和输出文本运行查询：

  mysql -B -eSELECT * FROM MyTable< database> > mytable.txt 
  

这可以在任何主机上运行，​​并且不需要特殊权限或目录权限。但是NULL可能不会像在mysqldump或 INTO OUTFILE 中那样处理。

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!

解决方案

Be careful about rolling your own, unless you handle things like NULL, character sets, etc.

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 FILE SQL privilege.

Second alternative: use mysqldump to output to flat text file (as @OMG Ponies mentioned):

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.

Third option: run query with mysql client and output text:

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.

这篇关于使用PHP将mysql表导出为.txt或.doc文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！