[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Setup automated export of mysql data to tab delimited text file

Posted on 2008-01-25
7
Medium Priority
?
916 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.
0
Comment
Question by:nisupport
  • 4
  • 3
7 Comments
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 2000 total points
ID: 20743546
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
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 20744162
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
 

Author Comment

by:nisupport
ID: 20828194
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 

Author Comment

by:nisupport
ID: 20828232
I got it to work. Thank you, is there a way allow the user to select where to save the file?
0
 

Author Comment

by:nisupport
ID: 20828265
What about overwriting the file?
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 20828502
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
 

Author Comment

by:nisupport
ID: 21005936
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question