Setup automated export of mysql data to tab delimited text file

How can I program an automated export to a tab delimited text file. I am using PHP and MySQL.
nisupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Beverley PortlockCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Beverley PortlockCommented:
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
0
nisupportAuthor 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());
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

nisupportAuthor Commented:
I got it to work. Thank you, is there a way allow the user to select where to save the file?
0
nisupportAuthor Commented:
What about overwriting the file?
0
Beverley PortlockCommented:
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
0
nisupportAuthor 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.