We help IT Professionals succeed at work.

Setup automated export of mysql data to tab delimited text file

nisupport
nisupport asked
on
Medium Priority
943 Views
Last Modified: 2008-03-31
How can I program an automated export to a tab delimited text file. I am using PHP and MySQL.
Comment
Watch Question

CERTIFIED EXPERT
Commented:
Use something like this for CSV

SELECT * INTO OUTFILE 'someFiileName.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM aTable;

or try this for a TAB separated file

SELECT * INTO OUTFILE 'someFiileName.csv'
  FIELDS TERMINATED BY '\t'
  LINES TERMINATED BY '\n'
  FROM aTable;


More info here http://dev.mysql.com/doc/refman/5.0/en/select.html
CERTIFIED EXPERT

Commented:
I've just noticed the "automated" part of your question and this only works well if you have full access to the server and I can only speak for Linux servers.

The trick is to put the above SQL into a PHP script, executing via mysql_query, then use the Lynx text browser to trigger the script via a cron job. So, to dump it every midnight of every day...

00 00 * * * lynx -dump http://www.some-domain.com/theMySQL-script.php > /dev/null

Author

Commented:
I tried the export script in a php file as shown below and nothing happened. Should it be setup differently?

$sql = "SELECT * INTO OUTFILE 'test.csv' FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' FROM h_contact";
      $result = mysql_query($sql) or die ("Error in query: $sql. " . mysql_error());

Author

Commented:
I got it to work. Thank you, is there a way allow the user to select where to save the file?

Author

Commented:
What about overwriting the file?
CERTIFIED EXPERT

Commented:
As far as I know, you should be able to write the file by specifying the full path name. Also, you should be able to overwrite simply by deleting the previous version like so

$file = "/home/myDir/test.csv";

// Delete
//
unlink( $file );

// Export
//
$sql = "SELECT * INTO OUTFILE '$file' FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' FROM h_contact";
      $result = mysql_query($sql) or die ("Error in query: $sql. " . mysql_error());


Should more or less do it

Author

Commented:
I get an error message:
Warning: unlink(filelocation) [function.unlink]: Permission denied in

Where can this permission be revised? Note: this is hosted on a windows server, not sure if that matters.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.