Обсуждение: Saving result to file for download
Hi all! I made a PHP script that formats and saves the output of a query to a file and provides a link to the file for download. I've never done this before and would appreciate any feedback. Here are things I'd like to improve or am concerned about. I've looked through Sklar & Trachtenberg's 'PHP Cookbook', googled ('PHP postgresql saving result file download' gives you a wide assortment of links!), and attempted to search the archives of pgsql-php, but I keep timing out after 60 seconds. Has anyone else been experiencing problems searching the archives? 1. Right now the file is permanently saved in a directory (used only for saving these results files, unimaginatively named /temp) in the web root of the server (in my case /Library/Webserver/Documents on Mac OS X 10.2). I'd rather it be a temporary file so I wouldn't have to worry about clearing out the files if a lot of people generate results files. I'm not concerned that people won't be able to come back to the results file at a later date—they can just generate a new one. Perhaps I should make a cron job to clear out the folder every once in a while? 2. Security. I've changed the owner on /temp to www (the webserver) so that PHP can write to the directory. Here are the permissions. drwxr-xr-x 23 www admin 782 Oct 27 00:05 temp I'm guessing I should change the permissions to drwxr--r-- (or even drw-r--r--) as there's no reason there should be execute permissions on the directory. If anyone's curious, here's the file handling part of the code. Truly nothing special. If anyone would like to see anything else, I'd be happy to oblige. $docroot = '/Library/Webserver/Documents/'; $dir = 'temp/'; $path = $docroot.$dir; $id_string = uniqid('',1); $filename = 'apps-'.$id_string.'.txt'; $fh =fopen($path.$filename,'w') or die($php_errormsg); fputs($fh,$app_string); fclose($fh) or die($php_errormsg); echo 'Here\'s your file! Download now!<br />'; echo '<a href="/'.$dir.$filename.'">'.$filename.'</a>'; As this is the first time of done anything like this, I'd appreciate any comments. Thanks! Michael
Just a quick follow up. On Monday, Oct 27, 2003, at 00:43 Asia/Tokyo, Michael Glaesemann (me!) wrote: > I'm guessing I should change the permissions to drwxr--r-- (or even > drw-r--r--) as there's no reason there should be execute permissions > on the directory. I changed the permissions to drw-r--r-- and found out that execute permissions is *definitely* necessary. Otherwise no one can open the directory! Learn something new everyday. Michael
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hello! Michael Glaesemann (nie 26. październik 2003 16:43): > Hi all! > > I made a PHP script that formats and saves the output of a query to a > file and provides a link to the file for download. I've never done this > before and would appreciate any feedback. Here are things I'd like to > improve or am concerned about. > [...] > 1. Right now the file is permanently saved in a directory [...] > [...] > I'd rather it be a temporary file so I wouldn't have to worry > about clearing out the files if a lot of people generate results files. > I'm not concerned that people won't be able to come back to the results > file at a later date—they can just generate a new one. Why have you decided to store results in a file? Is that file to be really big? or takes long to prepare? Maybe generating the file 'on the fly' would be easier: 1) Provide a link to a script that generates the file (eg: <a href="file.php?type=whatever">get file here</a> 2) The script "file.php" sets the content type header to text/plain and just outputs the result of a query. I am not sure how to set HTTP header on Your webserver (with apache there is a PHP function header(string) ) You may find two HTTP headers interesting (examples from my script): header("Content-type: text/plain; charset=ISO-8859-2"); // See RFC 2183 [49] (which updates RFC 1806) for details. Content-Disposition is not part of HTTP standard, but is widely used. header("Content-Disposition: attachment; filename=anka.txt"); After this you just output your data. That should work. You would then have no troubles with filenames-conflicts, disk space wasting and so on. However some browsers may ignore the content-disposition header and suggest the filename 'file.php' when saving. I suppose that's not a big problem... Bye, M.P. - -- [http://skoot.qi.pl for GPG keys] "A computer programmer is someone who, when told to "Go to Hell", sees the "Go to", rather than the destination, as harmful." -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE/nXhmvkWo15WV1rkRAjJ+AKCTZt4M6iu63kFRBgxeE7kjAvuZZgCfZI3b QlXY7b+UpgvSqsojFoLY1UI= =1CVb -----END PGP SIGNATURE-----
Hi M.P. On Tuesday, Oct 28, 2003, at 04:56 Asia/Tokyo, Mariusz Pekala wrote: > > Why have you decided to store results in a file? Is that file to be > really > big? or takes long to prepare? Actually the file is pretty small (less than 8K) and is pretty quick to make (less than 2 seconds). I just didn't know how to do it any other way. > Maybe generating the file 'on the fly' would be easier: > > 1) Provide a link to a script that generates the file > (eg: <a href="file.php?type=whatever">get file here</a> > > 2) The script "file.php" sets the content type header to text/plain > and just > outputs the result of a query. I am not sure how to set HTTP header on > Your > webserver (with apache there is a PHP function header(string) ) This sounds like *exactly* what I am after. I'll give it a try! Thanks for taking the time to respond. I knew there should be a better way but didn't know where to even start looking. I really appreciate your help. Michael
This script initially displays a form for you to enter connection details, a query, and a return type. The form then POSTs these details to itself. When the script detects that form data is being POSTed to it, it connects to the PG databasew specified in the POST data, and runs the query. The query is then rendered into the desired output format, and the correct headers are sent to the browser to save the file. Note - I don't seem to be getting consistent behaviour with the fle name disposition header - not sure if this is IIS or IE6 fouling up. ################################################################### <?php if ($_POST){ $connection = pg_connect("host='". $_POST['db']['host'] . "' port='". $_POST['db']['port'] . "' dbname='". $_POST['db']['db'] . "' user='". $_POST['db']['user'] . "' password='". $_POST['db']['password'] . "'"); if ($connection) { $result = pg_query($connection,$_POST['query']); $output_filename = ($_POST['output_filename']?$_POST['output_filename']:"PostgreSQL_results"); switch($_POST['output_format']){ case "xml": header("Content-type: text/xml"); header("Content-Disposition: attachment; " . $output_filename . ".xml"); $output .= ("<?xml version=\"1.0\"?>\n<results>\n"); for($i=0;$i<pg_num_rows($result);$i++){ $output .= "\t<row number=\"$i\">\n"; foreach(pg_fetch_assoc($result,$i) AS $field=>$value) $output .= "\t\t<column name=\"$field\">$value</column>\n"; $output .= "\t</row>\n"; } $output .= "</results>\n"; break; case "csv": header("Content-type: text/csv"); header("Content-Disposition: attachment; " . $output_filename . ".csv"); foreach(pg_fetch_assoc($result,0) AS $field=>$value) $output .= "\"$field\","; $output = rtrim($output,",") . "\n"; for($i=0;$i<pg_num_rows($result);$i++){ foreach(pg_fetch_assoc($result,$i) AS $field=>$value) $output .= "$value,"; $output = rtrim($output,",") . "\n"; } break; default: header("Content-type: text/plain"); header("Content-Disposition: attachment; " . $output_filename . ".txt"); foreach(pg_fetch_assoc($result,0) AS $field=>$value) $output .= "\"$field\"\t\t"; $output = rtrim($output,"\t") . "\n"; for($i=0;$i<pg_num_rows($result);$i++){ foreach(pg_fetch_assoc($result,$i) AS $field=>$value) $output .= "$value\t\t"; $output = rtrim($output,"\t") . "\n"; } break; } print($output); die(); } } ?> <html> <head> <title> Downl query results demo </title> </head> <body> <form action="<?php print($_SERVER['SCRIPT_NAME']);?>" method="POST" enctype="multipart/form-data"> <table> <tr><td>PostgreSQL server:</td><td><input type="text" name="db[host]" value="localhost"/></td></tr> <tr><td>PostgreSQL database:</td><td><input type="text" name="db[db]" value="test"/></td></tr> <tr><td>PostgreSQL port:</td><td><input type="text" name="db[port]" value="5432"/></td></tr> <tr><td>PostgreSQL user:</td><td><input type="text" name="db[user]"/> password: <input type="password" name="db[password]"/></td></tr> <tr><td>Query:</td><td><input type="text" size="50" name="query" value="SELECT * FROM pg_catalog.pg_tables"/></td></tr> <tr><td>Output filename:</td><td><input type="text" name="output_filename" value="output"/></td></tr> <tr><td>Output type:</td><td> <select name="output_format"> <option value="xml">xml</option> <option value="">plain text</option> <option value="csv">csv</option> </select> </td></tr> </table> <input type="submit" value="Save result"/></td></tr> </body> </html>