Forcing file download in PHP - inside Joomla framework

Question

I have some PHP code that runs a query on a database, saves the results to a csv file, and then allows the user to download the file. The problem is, the csv file contains page HTML around the actual csv content.

I've read all the related questions here already, including this one. Unfortunately my code exists within Joomla, so even if I try to redirect to a page that contains nothing but headers, Joomla automatically surrounds it with its own navigation code. This only happens at the time of download; if I look at the csv file that's saved on the server, it does not contain the HTML.

Can anyone help me out with a way to force a download of the actual csv file as it is on the server, rather than as the browser is editing it to be? I've tried using the header location, like this:

header('Location: ' . $filename);

but it opens the file in the browser, rather than forcing the save dialog.

Here's my current code:

//set dynamic filename
$filename = "customers.csv";
//open file to write csv
$fp = fopen($filename, 'w');

//get all data
$query = "select 
   c.firstname,c.lastname,c.email as customer_email, 
   a.email as address_email,c.phone as customer_phone,
   a.phone as address_phone,
   a.company,a.address1,a.address2,a.city,a.state,a.zip, c.last_signin
   from {$dbpre}customers c
   left  join  {$dbpre}customers_addresses a  on c.id = a.customer_id  order by c.last_signin desc";

$votes = mysql_query($query) or die ("File: " . __FILE__ . "<br />Line: " . __LINE__ . "<p>{$query}<p>" . mysql_error());
$counter = 1;
while ($row = mysql_fetch_array($votes,1)) {
    //put header row
    if ($counter == 1){
       $headerRow = array();
       foreach ($row as $key => $val)
          $headerRow[] = $key;
       fputcsv($fp, $headerRow);
    }
    //put data row
    fputcsv($fp, $row);
    $counter++;
}

//close file
fclose($fp);

//redirect to file
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=".$filename);
header("Content-Transfer-Encoding: binary");
readfile($filename); 
exit;

EDITS Full URL looks like this:

http://mysite.com/administrator/index.php?option=com_eimcart&task=customers

with the actual download link looking like this:

http://mysite.com/administrator/index.php?option=com_eimcart&task=customers&subtask=export

MORE EDITS Here's a shot of the page that the code is on; the generated file still is pulling in the html for the submenu. The code for the selected link (Export as CSV) is now

index.php?option=com_eimcart&task=customers&subtask=export&format=raw

Now here is a screenshot of the generated, saved file:

It shrank during the upload here, but the text highlighted in yellow is the html code for the subnav (list customers, add new customer, export as csv). Here's what my complete code looks like now; if I could just get rid of that last bit of html it would be perfect.

  $fp= fopen("php://output", 'w');

            $query = "select c.firstname,c.lastname,c.email as customer_email, 
                      a.email as address_email,c.phone as customer_phone,
                      a.phone as address_phone,  a.company, a.address1,
                      a.address2,a.city,a.state,a.zip,c.last_signin

                      from {$dbpre}customers c
                      left  join  {$dbpre}customers_addresses a on c.id = a.customer_id  
                      order by c.last_signin desc";

            $votes = mysql_query($query) or die ("File: " . __FILE__ . "<br />Line: " . __LINE__ . "<p>{$query}<p>" . mysql_error());
            $counter = 1;

            //redirect to file
            header("Content-type: application/octet-stream");
            header("Content-Disposition: attachment; filename=customers.csv");
             header("Content-Transfer-Encoding: binary");

            while ($row = mysql_fetch_array($votes,1)) {

                    //put header row
                    if ($counter == 1){
                            $headerRow = array();
                            foreach ($row as $key => $val)
                                    $headerRow[] = $key;

                            fputcsv($fp, $headerRow);
                    }

                    //put data row
                    fputcsv($fp, $row);
                $counter++;
            }

            //close file
            fclose($fp);

UPDATE FOR BJORN

Here's the code (I think) that worked for me. Use the RAW param in the link that calls the action:

index.php?option=com_eimcart&task=customers&subtask=export&format=raw

Because this was procedural, our link was in a file called customers.php, which looks like this:

switch ($r['subtask']){
    case 'add':
    case 'edit':
        //if the form is submitted then go to validation
                include("subnav.php");
        if ($r['custFormSubmitted'] == "true")
            include("validate.php");
        else
            include("showForm.php");
        break;

    case 'delete':
                include("subnav.php");
        include("process.php");
            break;

    case 'resetpass':
                include("subnav.php");
        include("resetpassword");
            break;

    case 'export':
        include("export_csv.php");
            break;


    default:
                include("subnav.php");
        include("list.php");
        break;
}

So when a user clicked on the link above, the export_csv.php file is automatically included. That file contains all the actual code:

<?
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=customers.csv");
header("Content-Transfer-Encoding: binary");
$fp= fopen("php://output", 'w');


//get all data
$query = "select 
    c.firstname,c.lastname,c.email as customer_email, 
    a.email as address_email,c.phone as customer_phone,
    a.phone as address_phone,
    a.company,a.address1,a.address2,a.city,a.state,a.zip, c.last_signin

    from {$dbpre}customers c

    left  join  {$dbpre}customers_addresses a  on c.id = a.customer_id  order by c.last_signin desc";


$votes = mysql_query($query) or die ("File: " . __FILE__ . "<br />Line: " . __LINE__ . "<p>{$query}<p>" . mysql_error());
$counter = 1;

while ($row = mysql_fetch_array($votes,1)) {

    //put header row
    if ($counter == 1){
        $headerRow = array();
        foreach ($row as $key => $val)
            $headerRow[] = $key;

        fputcsv($fp, $headerRow);
    }

    //put data row
    fputcsv($fp, $row);
    $counter++;
}

//close file
fclose($fp);

Solution 1

This is a piece of sample code that I just cooked up to help you out. Use it as an action method in your controller.

function get_csv() {
        $file = JPATH_ADMINISTRATOR . DS . 'test.csv';

        // Test to ensure that the file exists.
        if(!file_exists($file)) die("I'm sorry, the file doesn't seem to exist.");

        // Send file headers
        header("Content-type: text/csv");
        header("Content-Disposition: attachment;filename=test.csv");

        // Send the file contents.
        readfile($file);
    }

This alone will not be enough, because the file you download will still contain the surrounding html. To get rid of it and only receive the csv file's contents you need to add format=raw parameter to your request. In my case the method is inside the com_csvexample component, so the url would be:

/index.php?option=com_csvexample&task=get_csv&format=raw

EDIT

In order to avoid using an intermediate file substitute

//set dynamic filename
$filename = "customers.csv";
//open file to write csv
$fp = fopen($filename, 'w');

with

//open the output stream for writing
//this will allow using fputcsv later in the code
$fp= fopen("php://output", 'w');

Using this method you have to move the code that sends headers before anything is written to the output. You also won't need the call to the readfile function.

Solution 2

Add this method to your controller:

function exportcsv() {
  $model = & $this->getModel('export');
  $model->exportToCSV();
}

Then add a new model called export.php, code below. You will need to change or extend the code to your situation.

<?php
/**
* @package TTVideo
* @author Martin Rose
* @website www.toughtomato.com
* @version 2.0
* @copyright Copyright (C) 2010 Open Source Matters. All rights reserved.
* @license http://www.gnu.org/copyleft/gpl.html GNU/GPL
*/

//No direct acesss
defined('_JEXEC') or die();
jimport('joomla.application.component.model');
jimport( 'joomla.filesystem.file' );
jimport( 'joomla.filesystem.archive' );
jimport( 'joomla.environment.response' );

class TTVideoModelExport extends JModel 
{

  function exportToCSV() {
    $files = array();
    $file = $this->__createCSVFile('#__ttvideo');
    if ($file != '') $files[] .= $file;
    $file = $this->__createCSVFile('#__ttvideo_ratings');
    if ($file != '') $files[] .= $file;
    $file = $this->__createCSVFile('#__ttvideo_settings');
    if ($file != '') $files[] .= $file;
    // zip up csv files to be delivered
    $random = rand(1, 99999);
    $archive_filename = JPATH_SITE.DS.'tmp'.DS.'ttvideo_'. strval($random) .'_'.date('Y-m-d').'.zip';
    $this->__zip($files, $archive_filename);
    // deliver file
    $this->__deliverFile($archive_filename);
    // clean up
    JFile::delete($archive_filename);
    foreach($files as $file) JFile::delete(JPATH_SITE.DS.'tmp'.DS.$file);
  }

  private function __createCSVFile($table_name) {
    $db = $this->getDBO();
    $csv_output = '';

    // get table column names
    $db->setQuery("SHOW COLUMNS FROM `$table_name`");
    $columns = $db->loadObjectList();

    foreach ($columns as $column) {
      $csv_output .= $column->Field.'; ';
    }
    $csv_output .= "\n";

    // get table data
    $db->setQuery("SELECT * FROM `$table_name`");
    $rows = $db->loadObjectList();
    $num_rows = count($rows);
    if ($num_rows > 0) {
      foreach($rows as $row) {
        foreach($row as $col_name => $value) {
          $csv_output .= $value.'; ';
        }
        $csv_output .= "\n";
      }
    }
    $filename = substr($table_name, 3).'.csv';
    $file = JPATH_SITE.DS.'tmp'.DS.$filename;
    // write file to temp directory
    if (JFile::write($file, $csv_output)) return $filename;
    else return '';
  }

  private function __deliverFile($archive_filename) {
    $filesize = filesize($archive_filename);
    JResponse::setHeader('Content-Type', 'application/zip');
    JResponse::setHeader('Content-Transfer-Encoding', 'Binary');
    JResponse::setHeader('Content-Disposition', 'attachment; filename=ttvideo_'.date('Y-m-d').'.zip');
    JResponse::setHeader('Content-Length', $filesize);
    echo JFile::read($archive_filename);
  }

  /* creates a compressed zip file */
  private function __zip($files, $destination = '') {
    $zip_adapter = & JArchive::getAdapter('zip'); // compression type
    $filesToZip[] = array();
    foreach ($files as $file) {
      $data = JFile::read(JPATH_SITE.DS.'tmp'.DS.$file); 
      $filesToZip[] = array('name' => $file, 'data' => $data); 
    }
    if (!$zip_adapter->create( $destination, $filesToZip, array() )) {
      global $mainframe; 
      $mainframe->enqueueMessage('Error creating zip file.', 'message'); 
    }
  }


}
?>

Then go to your default view.php and add a custom buttom, e.g.

// custom export to set raw format for download
$bar = & JToolBar::getInstance('toolbar');
$bar->appendButton( 'Link', 'export', 'Export CSV', 'index.php?option=com_ttvideo&task=export&format=raw' );

Good luck!

Comments

[Pekka]

You will probably need to turn off Joomla's URL rewriting for this specific case. Can you show some full URLs and your .htaccess file?

[EmmyS]

Editing OP to include requested info.

[Pekka]

Ahh okay, that is different: Joomla probably slaps the header/footer onto the output in the index file, this can't be fixed by adding a rule in .htaccess... Needs a Joomla expert to sort out

[EmmyS]

Not sure where to look for .htaccess file on our dev server - in the site's root directory, we have a file called htaccess.txt, but I don't think that's what you're referring to. We do have one on our live site, but I would prefer not to mess with it, since I don't know what other things a change would affect.

[Pekka]

never mind the htaccess file, changing it won't help in this case. Yup, best not to mess with it

[silvo]

I just analysed your code a little more in depth. Is there any reason that you want the csv file to be stored on the server or is it just created so that you can send it to the browser later on? If you don't need it to stay on the server you may output the csv directly to the output stream with echo, saving yourself a lot of trouble... Just remember to send the headers first :)

[EmmyS]

This is code I inherited so I was working with what was there. There's really no reason to keep the file on the server. Could you elaborate on sending via echo? Will this still create a file the user will download to their computer? Or will it just display the csv in the browser?

[silvo]

Please have a look at my ammended answer. Printing the csv directly to the output is no different functionally than what your code is doing now. The behaviour of the browser depends on the headers that you send. As you are sending a header informing the browser that the content is in fact an attachment it will trigger a file download.

[EmmyS]

Thanks, silvo. I'm dealing with a crisis for another client, but as soon as I have a chance I'll try this out.

[EmmyS]

OK, I just tried all of this, and it still creates the file surrounded by HTML. I'm adding screenshots with notes to my original post.

[silvo]

That html is output by your component, not by the core joomla framework. You need to find the place where it's produced and prevent it from being printed if the export task is selected...

[EmmyS]

Thanks, Martin, but this code (which I inherited) is done procedurally, not in mvc.