Recipe 18.9. Serving Query Results for Download
ProblemYou want to send database information to a browser for downloading rather than for display. SolutionUnfortunately, 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. DiscussionEarlier 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 enable the user to select a download explicitly (for example, by right-clicking or Ctrl-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.) <?php # download.php - retrieve result set and send it to user as a download # rather than for display in a web page require_once "Cookbook.php"; require_once "Cookbook_Webutils.php"; $title = "Result Set Downloading Example"; # If no download parameter is present, display instruction page if (!get_param_val ("download")) { # construct self-referential URL that includes download parameter $url = get_self_path () . "?download=1"; ?> <html> <head> <title><?php print ($title); ?></title> </head> <body bgcolor="white"> <p> Select the following link to commence downloading: <a href="<?php print ($url); ?>">download</a> </p> </body> </html> <?php exit (); } # end of "if" # The download parameter was present; retrieve a result set and send # it to the client as a tab-delimited, newline-terminated document. # Use a content type of application/octet-stream in an attempt to # trigger a download response by the browser, and suggest a default # filename of "result.txt". $conn =& Cookbook::connect (); if (PEAR::isError ($conn)) die ("Cannot connect to server: " . htmlspecialchars ($conn->getMessage ())); $stmt = "SELECT * FROM profile"; $result =& $conn->query ($stmt); if (PEAR::isError ($result)) die ("Cannot execute query: " . htmlspecialchars ($result->getMessage ())); header ("Content-Type: application/octet-stream"); header ("Content-Disposition: attachment; filename=\"result.txt\""); while ($row =& $result->fetchRow ()) print (join ("\t", $row) . "\n"); $result->free (); $conn->disconnect (); ?> download.php uses a couple of functions that we haven't covered yet:
These functions are included in the Cookbook_Webutils.php file and are discussed further in Recipes Section 19.1 and Section 19.5. Another way to produce 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 likely will run some kind of uncompress utility to recover the original file. |
