如何使用 Zend Framework 使用 mySQL 的 INTO OUTFILE 功能导出到 CSV

发布于 2024-10-10 06:43:53 字数 339 浏览 8 评论 0原文

我希望使用 Zend Framework 将大量数据导出到 CSV 文件中以供用户下载。有没有办法使用 Zend_Db 的功能并使用“INTO OUTFILE”语法将文件输出为 csv？基本上我希望能够调整我的 Zend_Db 模型以导出到 csv 文件而不是返回 PHP 数组。我想使用的 mysql 语法的一个例子是：

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text' 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
FROM test_table;

原文

I am looking to export a large amount of data into a CSV file for user download using Zend Framework. Is there any way to use Zend_Db's functionaity and use the "INTO OUTFILE" syntax to output the file as a csv? Basically I want to be able to adapt my Zend_Db models to export to a csv file instead of returning a PHP array. An example of the mysql syntax I want to use would be:

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text' 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
FROM test_table;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

风追烟花雨 2024-10-17 06:43:53

使用 Zend_Db $db 您可以直接发出任何请求：

$db->query($exportquery);

请注意，您需要数据库连接上使用的用户的 FILE 权限，这是一个非常高的权限（~~仅在某些版本的 MySQL 上为 root~~ 编辑：必须为服务器上的所有数据库设置）。因此，这不应该是您的主要 MySQL 连接，最好的办法是仅在 OUTFILE 查询所需的特定代码中打开特殊的高权限连接。

Using a Zend_Db $db you can put any request directly:

$db->query($exportquery);

Note that you'll need the FILE privilege on the user used on the DB connection, it's a very high privilege (~~only root on some versions of MySQL~~ edit: must be set for all databases on the server). So this should not be your main MySQL connection, the best thing would be to open the special high privilege connection only in the specific code needed for the OUTFILE query.

回复收藏 0 原文

淡淡の花香 2024-10-17 06:43:53

您可以尝试构建一个基本查询，将其转换为字符串然后从那里追加。优点是您可以使用 Zend 的绑定功能（至少对于 where 子句），以防万一您需要。就像这样：

$filename = 'tmp/test.csv';
$value = 'given';

 // build base query making use of Zend´s binding feature.
$select = $this->_db->select()
    ->from(test_table, array(a,b,a+b))
    ->where('column = ?', $value)
    ->__toString()

 // append the rest of the query.
.' INTO OUTFILE "'. $filename .'"'
.' FIELDS TERMINATED BY ";"'
.' OPTIONALLY ENCLOSED BY "\\""'
.' LINES TERMINATED BY "\\n"';

 // execute query.
$this->_db->query($select);

You can try to build a base query, convert it to a string and then append from there. The advantage being that you can use Zend's bind functionality (at least for the where clause), in case you need that. Like so:

$filename = 'tmp/test.csv';
$value = 'given';

 // build base query making use of Zend´s binding feature.
$select = $this->_db->select()
    ->from(test_table, array(a,b,a+b))
    ->where('column = ?', $value)
    ->__toString()

 // append the rest of the query.
.' INTO OUTFILE "'. $filename .'"'
.' FIELDS TERMINATED BY ";"'
.' OPTIONALLY ENCLOSED BY "\\""'
.' LINES TERMINATED BY "\\n"';

 // execute query.
$this->_db->query($select);

回复收藏 0 原文

~没有更多了~