Serving Query Results for Download

17.10.1 Problem

You want to send database information to a browser for downloading rather than for display.

17.10.2 Solution

Unfortunately, there's no good way to force a download. A browser will process information sent to it according to the Content-Type: header value, and if it has a handler for that value, it will treat the information accordingly. However, you may be able to trick the browser by using a "generic" content type for which it's unlikely to have a handler.

17.10.3 Discussion

Earlier sections of this chapter discuss how to incorporate the results of database queries into web pages, to display them as paragraphs, lists, tables, or images. But what if you want to produce a query result that the user can download to a file instead? It's not difficult to generate the response itself: send a Content-Type: header preceding the information, such as text/plain for plain text, image/jpeg for a JPEG image, or application/pdf or application/msexcel for a PDF or Excel document. Then send a blank line and the content of the query result. The problem is that there's no way to force the browser to download the information. If it knows what to do with the response based on the content type, it will try to handle the information as it sees fit. If it knows how to display text or images, it will. If it thinks it's supposed to give a PDF or Excel document to a PDF viewer or to Excel, it will. Most browsers allow the user to select a download explicitly (for example, by right-clicking or control-clicking on a link), but that's a client-side mechanism. You have no access to it on the server end.

About the only thing you can do is try to fool the browser by faking the content type. The most generic type is application/octet-stream. Most users are unlikely to have any content handler specified for it, so if you send a response using that type, it's likely to trigger a download by the browser. The disadvantage of this, of course, is that the response contains a false indicator about the type of information it contains. You can try to alleviate this problem by suggesting a default filename for the browser to use when it saves the file. If the filename has a suffix indicative of the file type, such as .txt, .jpg, .pdf, or .xls, that may help the client (or the operating system on the client host) determine how to process the file. To suggest a name, include a Content-Disposition: header in the response that looks like this:

Content-disposition: attachment; filename="suggested_name"

The following PHP script, download.php, demonstrates one way to produce downloadable content. When first invoked, it presents a page containing a link that can be selected to initiate the download. The link points back to download.php but includes a download parameter. When you select the link, it reinvokes the script, which sees the parameter and responds by issuing a query, retrieving a result set, and sending it to the browser for downloading. The Content-Type: and Content-Disposition: headers in the response are set by invoking the header( ) function. (This must be done before the script produces any other output, or header( ) will have no effect.)

Select the following link to commence downloading: <a href="<?php print ($url); ?>">download</a>

download.php uses a couple of functions we haven't covered yet. get_self_path( ) returns the script's own pathname. This is used to construct a URL that points back to the script and that includes a download parameter. get_param_val( ) is used to determine whether that parameter is present. These functions are included in the Cookbook_Webutils.php file and are discussed further in Recipe 18.2 and Recipe 18.6.

Another possibility for producing downloadable content is to generate the query result, write it to a file on the server side, compress it, and send the result to the browser. The browser will likely run some kind of uncompress utility to recover the original file.

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References

MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois © 2008-2020.
If you may any questions please contact us: