Laravel download query results as CSV

Question

I am trying to download a query as CSV and face currently two issues:

A file is created in the public folder. It contains the query data. That is really bad, because it should not exist in the public folder.
A file is also downloaded, but the downloaded file is empty.

Here is the function:

public function get_chatmessages(Request $data) {

    try {  
        if ($data->chat_to_user) {              
            $result = DB::connection('mysql_live')->table('user_chatmessages')
                ->where(function($query) use($data) {
                    $query->where('from_user', $data->chat_from_user)->where('to_user', $data->chat_to_user);
                })->orWhere(function($query) use($data) {
                    $query->where('to_user', $data->chat_from_user)->where('from_user', $data->chat_to_user);
                })->orderBy('date_added', 'asc')->get();
        } else {
            $result = DB::connection('mysql_live')->table('user_chatmessages')
            ->where('from_user', $data->chat_from_user)
            ->orWhere('to_user', $data->chat_from_user)
            ->orderBy('date_added', 'asc')
            ->get();
        }

        //\Log::info($data);
        //\Log::info($result);

        $headers = array(
            "Content-type" => "text/csv",
            "Content-Disposition" => "attachment; filename=file.csv",
            "Pragma" => "no-cache",
            "Cache-Control" => "must-revalidate, post-check=0, pre-check=0",
            "Expires" => "0"
        );

        $columns = array('from_user', 'to_user', 'message', 'date_added');
        $callback = function() use ($result, $columns) {
            $file = fopen('output_chat.csv', 'w');
            fputcsv($file, $columns);

            foreach($result as $res) {
                fputcsv($file, array($res->from_user, $res->to_user, $res->message, $res->date_added));
            }
            fclose($file);
        };

        //return response()->download('output_chat.csv', 'DL_output_chat.csv', $headers);
        //return response()->make($callback, 200, $headers);
        return response()->stream($callback, 200, $headers);  

    } catch (\Exception $e) {  
        return redirect('home')->with('error', $e->getMessage());  
    } 

    return redirect('home')->with('error', 'Etwas ist schief gelaufen');  
}

Try renaming that line `$file = fopen('output_chat.csv', 'w');` to `$file = fopen('php://output', 'w');` — Rahul, Sep 13 '19 at 11:53
Instead of opening an actual file, write to the PHP output buffer directly - see last part (“Update”) of https://stackoverflow.com/a/16251849/10283047 — misorude, Sep 13 '19 at 11:56
Yeah it worked. The original data is deleted automatically after the DL? I am not very familiar with php output buffer. Is it secure or do I have to delete something manually? — Roman, Sep 13 '19 at 11:58

score 3 · Accepted Answer · answered Sep 13 '19 at 11:57

3

I made little changes in your snippet regarding php://output

$headers = [
    "Content-type"        => "text/csv",
    "Content-Disposition" => "attachment; filename=output_chat.csv", // <- name of file
    "Pragma"              => "no-cache",
    "Cache-Control"       => "must-revalidate, post-check=0, pre-check=0",
    "Expires"             => "0",
];
$columns  = ['from_user', 'to_user', 'message', 'date_added'];
$callback = function () use ($result, $columns) {
    $file = fopen('php://output', 'w'); //<-here. name of file is written in headers
    fputcsv($file, $columns);
    foreach ($result as $res) {
        fputcsv($file, [$res->from_user, $res->to_user, $res->message, $res->date_added]);
    }
    fclose($file);
};

answered Sep 13 '19 at 11:57

Rahul

18,271
7
41
60

Yeah it worked. The original data is deleted automatically after the DL? I am not very familiar with php output buffer. Is it secure or do I have to delete something manually? – Roman Sep 13 '19 at 12:02
for security use storage folder, laravel won't give access to that folder anyway. But in code, you can(of course you want that). The public folder is not secure because can access via URL. – Rahul Sep 13 '19 at 12:05
But we use now the output buffer. Where is its location? So instead of output buffer I should write to storage? – Roman Sep 13 '19 at 12:07
You want to store it internally, not to give it to download to end-user, right? – Rahul Sep 13 '19 at 12:11
I dont want to store. I want to download it and thats it. There should be no trace of the file on the server. – Roman Sep 13 '19 at 12:15
Its possible that the output buffer is what I need, I am just trying to understand whether it creates somewhere on the server the file contents and whether I need to clean the output buffer after the download. – Roman Sep 13 '19 at 12:16
https://laracasts.com/discuss/channels/laravel/how-to-uploaddownload-files-with-s3 Follow these steps. – Rahul Sep 13 '19 at 12:17

score 0 · Answer 2 · answered Sep 13 '19 at 11:57

0

For Laravel, there are many packages which provide us such implementations. And for you to create download csv file with your database records you can use Laravel Excel package.

This package has many useful features. try to use it.

answered Sep 13 '19 at 11:57

Lakhwinder Singh

5,536
5
27
52

score 0 · Answer 3 · answered Sep 13 '19 at 11:57

You can decide where you save files - you can do this using Laravel's Storage facade.

private function pathToPrivateStorage()
{
    return '/private/CSVs';
}

Storage::put($this->pathToPrivateStorage, $YOURCSVFILE);

You can read more about storage here.

score 0 · Answer 4 · edited Oct 17 '19 at 19:53

0

Try $file = fopen('php://output', 'w'); in place of $file = fopen('output_chat.csv', 'w');

edited Oct 17 '19 at 19:53

Community

1
1

answered Sep 13 '19 at 11:59

KSWeb Production

1
4

Laravel download query results as CSV

4 Answers4