Output a CSV file and Save

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
IGGt
Forum Contributor
Posts: 173
Joined: Thu Nov 26, 2009 9:22 am

Output a CSV file and Save

Post 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;
}
?>
 
IGGt
Forum Contributor
Posts: 173
Joined: Thu Nov 26, 2009 9:22 am

Re: Output a CSV file and Save

Post 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?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Output a CSV file and Save

Post by pickle »

Changing the content-type to "application/octet-stream" should force the browser to ask the user where to save the file.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
IGGt
Forum Contributor
Posts: 173
Joined: Thu Nov 26, 2009 9:22 am

Re: Output a CSV file and Save

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Output a CSV file and Save

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
IGGt
Forum Contributor
Posts: 173
Joined: Thu Nov 26, 2009 9:22 am

Re: Output a CSV file and Save

Post 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?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Output a CSV file and Save

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
IGGt
Forum Contributor
Posts: 173
Joined: Thu Nov 26, 2009 9:22 am

Re: Output a CSV file and Save

Post 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.
Post Reply