Download Query Results To Excel...problem

[mdemetri2]

Hi

 

Need some advice on this. I have some code that I found to download query results into excel. I have a form that allows the user to specify some filters, submit, and see results in a table. I then have a download option, which links to a download page that holds the following code, but the query is passed in the url (not good security I know!):

 

<?php
$code = $_GET['sqlcode'];
$sql = stripslashes($code);
// DB TABLE Exporter
//
// How to use:
//
// Place this file in a safe place, edit the info just below here
// browse to the file, enjoy!
// CHANGE THIS STUFF FOR WHAT YOU NEED TO DO
 $dbhost  = "xxxx";
 $dbuser  = "xxxx";
 $dbpass  = "xxxx";
 $dbname  = "xxxx";
 $dbtable = "download";

// END CHANGING STUFF
// first thing that we are going to do is make some functions for writing out
// and excel file. These functions do some hex writing and to be honest I got
// them from some where else but hey it works so I am not going to question it
// just reuse

// This one makes the beginning of the xls file
function xlsBOF() {
   echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
   return;
}
// This one makes the end of the xls file
function xlsEOF() {
   echo pack("ss", 0x0A, 0x00);
   return;
}
// this will write text in the cell you specify
function xlsWriteLabel($Row, $Col, $Value ) {
   $L = strlen($Value);
   echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
   echo $Value;
   return;
}
// make the connection an DB query
$dbc = mysql_connect( $dbhost , $dbuser , $dbpass ) or die( mysql_error() );
mysql_select_db( $dbname );
mysql_query("set sql_big_selects=1");
$q = $sql;
$qr = mysql_query( $q ) or die( mysql_error() );

//Ok now we are going to send some headers so that this
//thing that we are going make comes out of browser
//as an xls file.

header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
//this line is important its makes the file name
header("Content-Disposition: attachment;filename=export_".$dbtable.".xls ");
header("Content-Transfer-Encoding: binary ");
// start the file
xlsBOF();
// these will be used for keeping things in order.
$col = 0;
$row = 0;
// This tells us that we are on the first row
$first = true;
while( $qrow = mysql_fetch_assoc( $qr ) )
{
   // Ok we are on the first row
   // lets make some headers of sorts
   if( $first )
   {
    foreach( $qrow as $k => $v )
    {
	    // take the key and make label
	    // make it uppper case and replace _ with ' '
	    xlsWriteLabel( $row, $col, strtoupper( ereg_replace( "_" , " " , $k ) ) );
	    $col++;
    }
    // prepare for the first real data row
    $col = 0;
    $row++;
    $first = false;
   }
   // go through the data
   foreach( $qrow as $k => $v )
   {
    // write it out
    xlsWriteLabel( $row, $col, $v );
    $col++;
   }
   // reset col and goto next row
   $col = 0;
   $row++;
}
xlsEOF();
exit();
?>

 

Problem is, the query is to large to fit into the url, so is not passed across. Can I pass the query someother way, or I was thinking, can I just put this excel code onto the same page and make it work when the download icon is pressed?

 

Thanks you!

[deoiub]

How long is the query turning out to be?

 

disregarding the security concerns that you seem to realise exist, I'd consider switching the form method to "post" and subsequent changes on the action page to see if that works.

 

-D

[mdemetri2]

Hi deoiub

 

Thing is, the download option as it is does work where filters are used specifically, rather than use the ALL option, this introduces too many variables and tries to pass them via the url which makes it too long - works fine in firefox as its limit is apparently 80,000 characters.

 

I did wonder about the POST method but didn't know where / how to implement it. I will keep looking!

[deoiub]

 

I did wonder about the POST method but didn't know where / how to implement it. I will keep looking!

 

I'm assuming the following:

 

- that the form your users fill out is on a web page, with a <form action="...">....</form>.

- you then have an action page that uses the form data to query a database and show the results in an HTML table?

- then at the bottom of that page, you have a link that says 'download to excel' where you construct a long url in the format: url.com/script.php?var1=content1&var2=cotent2...

 

If my assumptions are correct, then put a form at the bottom of the action page with hidden inputs with your variables, and a submit button instead of a link.

 

<form action='excel_dump.php' method='post'>

 

<input type='hidden' name='var1' value='content1'>

 

<input type='hidden' name='var2' value='content2'>

<input type='submit' value='download to excel'>

</form>

[mdemetri2]

deoiub, you assumptions are correct, smart.

 

the download to excel creates: url.com/dl2excelscriptpage.php?sqlcode=SELECT blah blah blah.........

 

the link echos the query submitted when the user submits their search, the page that creates the excel files takes the sqlcode variable passed in the url to sumbit the query and generate excel file from the results. this as I say fails when the sql is too long for the url

 

so what you are saying is replicate the search from but so that it submits to the page which does the download to excel......I understand, I just need to spend some time on this! thank you ever so much for your help!

[kicken]

Just move the excel stuff to the same page as your current search form, then branch based on whether you want to output for excel or output the html results.

 

Eg:

 

<?php

///do all your 
///query stuff here

if (isset($_GET['action']) && $_GET['action']=='download'){
  //Do excel output
}
else {
  //Do HTML output
}

 

Then for the download all you would do is re-submit the form but with an extra action=download option.