2

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;
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Brendan
  • 21
  • 1
  • 2

2 Answers2

0

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);
WoodrowShigeru
  • 1,418
  • 1
  • 18
  • 25
0

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.

regilero
  • 29,806
  • 6
  • 60
  • 99
  • yes, you're right, the real problem I had with this privilage was that it was not database specific, must be granted on \*.\*. This combined with the ability to access any file that MySQL can access... make this privilege a very high one. – regilero Dec 30 '10 at 09:02
  • Addendum » Use [`$this->_db`](http://framework.zend.com/apidoc/1.9/Zend_Db/Table/Zend_Db_Table_Abstract.html#$_db) to get to the database if you're in a table scope (i.e. in an extended `Zend_Db_Table_Abstract` class), resulting in: `$this->_db->query()` – WoodrowShigeru Apr 15 '15 at 14:14