note 31839 deleted from ref.odbc by sniper
| From: | sniper@php.net | Date: | Tue, 04 Nov 2003 00:38:19 +0000 |
| Subject: | note 31839 deleted from ref.odbc by sniper | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-59764@lists.php.net to get a copy of this message | ||
Note Submitter: mitchind@telusplanet.net
----
Thanks to the great documentation here, I was able to combine several examples and figure out the
rest to create a generic routine to export a SQL query from an ODBC database to Excel format. It
should work on most platforms and browsers. Usage is simple and described in code - but basically
you just need to format the SQL query and supply it as a hidden input in another page's form
and then use this script as the "ACTION" parameter.
Ideally I'd like to add a pop up message if the number of records exceeds the MAX_RECS value -
tell the user and get a confirmation to continue. Maybe someone else can add that for me - my
knowledge of PHP has been stretched with this module.
<?php
// exportExcel.php
// PHP Script to Export Results of ODBC SQL Query directly into Excel
// Get SQL Statement from Form variable
// No caching of script results
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); // Date in the past
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
// always modified
header("Cache-Control: no-store, no-cache, must-revalidate"); // HTTP/1.1
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache"); // HTTP/1.0
// Variables dependent on application and input form
$DSN = "YOUR DSN NAME"; // Database ODBC DSN Name
$DB_USER = ""; // Database ODBC Username
$DB_PWD = ""; // Database ODBC Password
$MAX_RECS = "100"; // Maximum number of records to export (ignored if empty)
$SQL_FORM_FIELD = "SQL"; // Input Form Field with SQL Query String
$NUM_RECS_FORM_FIELD = "nRecs" // Input Form Field with Number of Records
$ALLOW_REFER_FROM = "www.yourwebsitename.com"; // Only Allow usage from this website
if (empty($_SERVER['HTTP_REFERER']))
die("No Referal Website");
$refer = $_SERVER['HTTP_REFERER'];
$pos = strpos("$refer", "$ALLOW_REFER_FROM") or die("Not Coming From
Correct Website");
// Read in SQL statment from form (regardless of GET or POST method)
reset($_GET);
reset($_POST);
if (empty($_REQUEST["$SQL_FORM_FIELD"]))
die("No Input Form Field Defined");
$sqlQuery = $_REQUEST["$SQL_FORM_FIELD"];
// Get rid of the slashes that PHP inserts and will screw up ODBC SQL queries
$sqlQuery = stripslashes("$sqlQuery");
// Connect to data source
$db = odbc_connect("$DSN","$DB_USER","$DB_PWD");
// Get Number of Records in query if already known
if (!empty($_REQUEST["$NUM_RECS_FORM_FIELD"])) {
$numrecs = $_REQUEST["$NUM_RECS_FORM_FIELD"]; // Check this value against Max
}
else {
$numrecs = 0; // Unknown .. better set upper limit
}
// Attach MaxRecs limitation if defined and less than number of records in query
// or if Number of Records is unknown
if (!empty($MAX_RECS)) {
if ($numrecs == 0) || ($MAX_RECS < $numrecs) {
$sqlQuery = str_replace("SELECT ", "SELECT TOP $MAX_RECS ",
"$sqlQuery");
}
}
// Fetch query results
$result = odbc_exec($db, "$sqlQuery") or die("Query failed");
// Build page of results
// Open as Excel file in browser
header('Content-type: application/vnd.ms-excel');
?>
<style type="text/css">
<!--
body {font: 10pt/12pt Tahoma, Verdana, Helvetica, sans-serif; color: indigo; margin: .25in .5in }
table {FONT-FAMILY:Tahoma; font-size:8pt; color:Navy; border-color:Black; border-style:Solid;
border-width:1px;}
td {background-color:AntiqueWhite;}
//-->
</style>
<?php
// returns table with basic formatting
odbc_result_all($result, "border=\"1\" class=\"def\"");
//disconnect from database
odbc_close($db);
?>