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>
