Outputting DB contents

[dgraham]

Hey there. I'm looking for suggestions on the best way to output the contents of a single table from a DB as a CSV file or somesuch. I've tried doing it in the past with straight output buffering and generating the file straight to the browser for download, but that seems to cause problems with timeouts.

So, I started doing running a shell script on the server to generate the file and then just pointing the browser to that file when it was done, but now some people want me to do this, but shell scripts aren't allowed on their server.

Is there a better language I could be using here or something that I just don't know about/might have missed? I'm only posting in the PHP forum because that's what I've used in the past.

Thanks!

[Shmohel]

Do you have phpMyAdmin installed on your server? If so, they have a utility included to export your data into a CSV format.

[dgraham]

Interesting, thanks for the suggestion. The only problem is that they don't need/want access to PHPMyAdmin, I'd just like to have a simple thing that I could incorporate into their existing interface.

[carlh]

if you're using mysql you could you a select into outfile, like


$sQuery = 'SELECT * FROM db.tablename INTO OUTFILE \'C:\\result.txt\' '.
'FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\' '.
'LINES TERMINATED BY \'\n\' '.
'FROM tablename';
mysql_query($sQuery) or die('Failed to execute SELECT...'.
'INTO OUTFILE<hr>'.mysql_error());


Also I dunno if it's the way I have something setup, but sometimes I have trouble with the \\ passing from from php to mysql, cause they both want an escaped slash. What i did was define

$slash = '\\';
and the file as

$file = 'C:\\'.$slash.'result.txt';


then just send the outfile to $file
just make sure that MySQL has access to the file you're writing to, more info on http://dev.mysql.com/doc/refman/5.0/en/select.html if you scroll down to the into outfile section

[dgraham]

That a really interesting idea! Thanks a lot! I will try it out.