Codeigniter and generating CSV files

Question

This isn't a problem as such, more a request for an optimum way of exporting a query from Codeigniter as a downloadable CSV. In CI, I call a simple query (eg select * from members where country=123) from a model in a controller (eg members), along the lines of:

$data['membercountries_query'] = $this -> Queries_model -> get_members_by_country($country_id);

This goes off to a view where I display human-readable information on members in a country. I can easily generate a CSV of this result in the view or controller with $this->dbutil->csv_from_result($membercountries_query), and display it in the view. I would like to then present the user with a button/hyperlink "download result as CSV", which will generate a file save dialogue.

I've done a fair bit of googling and hunting on SO and there are many possible solutions of varying degrees of complexity. I thought of perhaps adding a controller function, (eg member/create_csv($data)), but couldn't think of a way to pass the CSV $data to it from a page link (that might be another question completely).

Could someone please advise on the simplest and quickest way to create a CSV download link? I'd prefer not, for security reasons, to save the CSV file to server and then create a link to the saved file.

It would be especially useful to be able to create a general helper/function which would take any query result and produce downloadable CSV.

I am grateful to the CI folk for producing the dbutil and download helpers, and the simplest way of doing this must involve both csv_from_result() and force_download(), but I just can't think at this late hour of a way to combine the two in a view (or whatever) :(

Solution 1

I've adoped a fairly simple solution based on the 3rd solution in the question "Download csv from codeigniter mysql", which is to have a model function:

function csv($query, $filename = 'CSV_Report.csv')
{
        $this->load->dbutil();
        $this->load->helper('file');
        $this->load->helper('download');
        $delimiter = ",";
        $newline = "\r\n";
        $data = $this->dbutil->csv_from_result($query, $delimiter, $newline);
        force_download($filename, $data);
}

In the controller where you generate the query, add a $csv boolean parameter, eg:

function membercountry($country_id = NULL, $csv = false)

and have a condition in the controller function which will call the model function if $csv is true or 1:

    if ($csv)
    {
        $date = date('dmy');
        $csvfile = "membercountry" . $date . ".csv";
        $this -> Queries_model -> csv($data['membercountries_query'], $csvfile);
    }

Then in the query result view, after displaying the results in human-readable form in the browser, present a hyperlink:

<a href="queries/membercountry/<?=$country_id?>/1">Download result as CSV</a>

When clicked, it re-calls the controller with $csv as true which then invokes the model function csv().

This is a fairly general solution for my application, which only needs a few extra lines in a controller function, and saves writing a CSV file to server or having a specific view with detailed and complex headers. It's not the solution, just a fairly simple quick-and-dirty solution which CI developers on here might find useful. Once again, a big thanks to the CI community for creating the dbutils class which makes outputting CSV a doddle.

Edit: it might be tidier from a MVC viewpoint to put the csv() function in the controller rather than the model, and call it by $this -> csv().

Solution 2

Blockquote In the Model

$this->load->dbutil();
$q=$this->db->query("select * from student order by idate desc");
$delimiter = ",";
$newline = "\r\n";
return $this->dbutil->csv_from_result($q,$delimiter,$newline);

In The Controller

$this->load->helper('download');
$this->load->model('mdl');
$row  = $this->mdl->getall();
$name = 'mycsv.csv';
force_download($name,$row);