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=97they 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 =3D '/Library/Webserver/Documents/';
$dir =3D 'temp/';
$path =3D $docroot.$dir;
$id_string =3D uniqid('',1);
$filename =3D 'apps-'.$id_string.'.txt';
$fh =3Dfopen($path.$filename,'w') or die($php_errormsg);
fputs($fh,$app_string);
fclose($fh) or die($php_errormsg);
echo 'Here\'s your file! Download now!
';
echo '<a href=3D"/'.$dir.$filename.'">'.$filename.'</a>';

As this is the first time of done anything like this, I'd appreciate
any comments.

Thanks!

Michael

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo [at] postgresql.org so that your
message can get through to the mailing list cleanly

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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo [at] postgresql.org)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello!

Michael Glaesemann (nie 26. pa=C5=BAdziernik 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=E2=80=94they 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=3D"file.php?type=3Dwhatever">get file here</a>

2) The script "file.php" sets the content type header to text/plain and jus=
t
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=3DISO-8859-2");
// See RFC 2183 [49] (which updates RFC 1806) for details. Content-Disposit=
ion
is not part of HTTP standard, but is widely used.
header("Content-Disposition: attachment; filename=3Danka.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+AKCTZt4M6iu63kFRBgxeE7kjAvuZZgCf ZI3b
QlXY7b+UpgvSqsojFoLY1UI=3D
=3D1CVb
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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: get file here
>
> 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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo [at] postgresql.org)

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']:"Postgr eSQL_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>




---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster