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) :(

• dave: stackoverflow.com/questions/20840274/…
• Fred Riley: Thanks for the pointer. I did see the couple of solutions in that thread before posting here, and if I don't come across anything simpler I'll perhaps use them. I'd prefer not to have to set scads of headers if I can avoid it, not least because in future browsers/servers the header settings may not work.

Answer 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().

• cfnerd: Note: This worked great, but a reminder that the query itself has to be done in Active Record Class method (rather than a straight query). Well, at least that is how I had to do it in version 3

Answer 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);

Answer 3

you just need to to is download csv helper file for codeigniter and run you script like it

 $this->db->select('*')->from('*'); 
 $query = $this->db->get('your_table');
$this->load->helper('csv');
query_to_csv($query, TRUE, 'filename.csv');

• Fred Riley: Thanks, Ijaz. Is the helper you refer to the "Excel CSV Helper" (github.com/EllisLab/CodeIgniter/wiki/Excel-CSV-helper)?