nisupport
asked on
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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());
$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());
ASKER
I got it to work. Thank you, is there a way allow the user to select where to save the file?
ASKER
What about overwriting the file?
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
$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
ASKER
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.
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.
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