Page 1 of 1

Output a CSV file and Save

Posted: Tue Dec 15, 2009 5:30 am
by IGGt
I have a set of pages which are all used to put together various MySQl queries, they then pass their query to another page which generates a CSV file. At the minute though it opens a box that asks whether you want to open or save the file.
How can I change this behaviour so it automatically saves the file instead.

The file is:

Code: Select all

 
<?php
function exportMysqlToCsv($Query,$filename)
{
    $csv_terminated = "\n";
    $csv_separator = ",";
    $csv_enclosed = '"';
    $csv_escaped = "\\";
    $sql_query = $Query;
 
    // Gets the data from the database
    $result = mysql_query($sql_query);
    $fields_cnt = mysql_num_fields($result);
    $schema_insert = '';
    for ($i = 0; $i < $fields_cnt; $i++)
    {
        $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,
            stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;
        $schema_insert .= $l;
        $schema_insert .= $csv_separator;
    } // end for
    $out = trim(substr($schema_insert, 0, -1));
    $out .= $csv_terminated;
    // Format the data
    while ($row = mysql_fetch_array($result))
    {
        $schema_insert = '';
        for ($j = 0; $j < $fields_cnt; $j++)
        {
            if ($row[$j] == '0' || $row[$j] != '')
            {
                if ($csv_enclosed == '')
                {
                    $schema_insert .= $row[$j];
                } else
                {
                    $schema_insert .= $csv_enclosed .
                    str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;
                }
            } else
            {
                $schema_insert .= '';
            }
            if ($j < $fields_cnt - 1)
            {
                $schema_insert .= $csv_separator;
            }
        } // end for
        $out .= $schema_insert;
        $out .= $csv_terminated;
    } // end while
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-Length: " . strlen($out));
    // Output to browser with appropriate mime type, you choose ;)
    //header("Content-type: text/x-csv");
    //header("Content-type: text/csv");
    header("Content-type: application/csv");
    header("Content-Disposition: attachment; filename=$filename");
    //echo "Download the file <a href=\"$filename\">here</a>";
    echo $out;
    exit;
}
?>
 

Re: Output a CSV file and Save

Posted: Tue Dec 15, 2009 9:04 am
by IGGt
If there is an easier way of doing this instead of using my current code, please feel free to suggest that rather than modifying the existing code?

Re: Output a CSV file and Save

Posted: Tue Dec 15, 2009 9:48 am
by pickle
Changing the content-type to "application/octet-stream" should force the browser to ask the user where to save the file.

Re: Output a CSV file and Save

Posted: Tue Dec 15, 2009 10:21 am
by IGGt
Cheers, but that's what I'm trying to avoid. I need to specify a location in the php script, and then it should save it to that location automatically, without the user doing anything.

Re: Output a CSV file and Save

Posted: Tue Dec 15, 2009 1:56 pm
by pickle
You can't. Not because it's hard to do, or because I don't know how to do it, but because if browsers let webpages do that, it would be a tremendous security problem.

Re: Output a CSV file and Save

Posted: Wed Dec 16, 2009 2:49 am
by IGGt
That seems fair enough when you put it like that. What if I was to use a MySQL Select .. into outfile query, would that work?

Re: Output a CSV file and Save

Posted: Wed Dec 16, 2009 2:26 pm
by pickle
That would be a query run on the server and allow you to specify where on the server to store the resulting file. It would have nothing to do with the client's browser.

Re: Output a CSV file and Save

Posted: Thu Dec 17, 2009 2:36 am
by IGGt
That's right. The ultimate aim being to run the query, save the file for reference purposes, and then email it out to the client.

So specifying where to save it on the server is fine.