I've built a plugin for a client so that they can download data as a CSV file. It's been set up so that when the user clicks on a link in the menu, the CSV should just automatically download. However, it doesn't quite work like that, and just loads the function as a page in the WordPress backend.
This is the code I have for the function:
function download_payment_csv() {
include 'lib/connection.php';
$csv_output = '';
$values = $db->query('SELECT * FROM tbPayments ORDER BY date DESC');
$i=0;
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j].",";
}
$csv_output .= "
";
}
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private", false);
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"report.csv\";" );
header("Content-Transfer-Encoding: binary");
echo $csv_output;
}
And as I said, it just returns a blank screen. Any help would be appreciated!
EDIT So this is the code I'm now working with, taking bits from what's been said already.
function download_payment_csv() {
include 'lib/connection.php';
$csv_output = '';
$values = load_payment_csv();
$fp = fopen("php://output", "w");
$file = 'test_export';
$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-Type: text/csv");
header("Content-Disposition: attachment; filename=".$filename.".csv");
// Disable caching
header("Cache-Control: no-cache, no-store, must-revalidate"); // HTTP 1.1
header("Pragma: no-cache"); // HTTP 1.0
header("Expires: 0"); // Proxies
header("Content-Transfer-Encoding: UTF-8");
if(count($values) > 0) {
foreach($values as $result) {
fputcsv($fp, $result);
}
}
fclose($fp);
}
This generates a CSV, but there is a problem with it.The problem is that when viewing the page it doesn't download it as a CSV, it just outputs the contents of the CSV in to the page. However, adding this function to the top of the plugin:
add_action('admin_init','download_payment_csv');
This then triggers a download when the menu link is clicked, which is fine. But it triggers it for every menu item in the plugin, which is wrong. It should only trigger when the download link is clicked.
You'll need to change some header information
header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename="report.csv"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
After that make use of php://output for providing the data directly to the browser, this will prevent the blank page.
for example:
$outstream = fopen("php://output", "w");
foreach($result as $result)
{
fputcsv($outstream, $result);
}
fclose($outstream);
exit();
php://output is a read only stream that allows you provide data directly to the requester.
EDIT: Also you should make use of $wpdb
Try this:
<?php
class CSVExport
{
/**
* Constructor
*/
public function __construct()
{
if(isset($_GET['download_report']))
{
$csv = $this->generate_csv();
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private", false);
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"report.csv\";" );
header("Content-Transfer-Encoding: binary");
echo $csv;
exit;
}
// Add extra menu items for admins
add_action('admin_menu', array($this, 'admin_menu'));
// Create end-points
add_filter('query_vars', array($this, 'query_vars'));
add_action('parse_request', array($this, 'parse_request'));
}
/**
* Add extra menu items for admins
*/
public function admin_menu()
{
add_menu_page('Download Report', 'Download Report', 'manage_options', 'download_report', array($this, 'download_report'));
}
/**
* Allow for custom query variables
*/
public function query_vars($query_vars)
{
$query_vars[] = 'download_report';
return $query_vars;
}
/**
* Parse the request
*/
public function parse_request(&$wp)
{
if(array_key_exists('download_report', $wp->query_vars))
{
$this->download_report();
exit;
}
}
/**
* Download report
*/
public function download_report()
{
echo '<div class="wrap">';
echo '<div id="icon-tools" class="icon32"></div>';
echo '<h2>Download Report</h2>';
//$url = site_url();
echo '<p><a href="site_url()/wp-admin/admin.php?page=download_report&download_report">Export the Subscribers</a>';
}
/**
* Converting data to CSV
*/
public function generate_csv()
{
$csv_output = '';
$table = 'users';
$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output = $csv_output . $row['Field'].",";
$i++;
}
}
$csv_output .= "
";
$values = mysql_query("SELECT * FROM ".$table."");
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j].",";
}
$csv_output .= "
";
}
return $csv_output;
}
}
// Instantiate a singleton of this plugin
$csvExport = new CSVExport();
/** * Query For Top Title Row */
$results = $wpdb->get_results("SHOW COLUMNS FROM $table" );
if(count($results) > 0){
foreach($results as $result){
$csv_output .= str_replace('_',' ',$result->Field).", "; // , or ;
}
}
$csv_output .= "
";
/** * Query For All Required Data */
$results = $wpdb->get_results("SELECT * FROM $table",ARRAY_A );
if(count($results) > 0){
foreach($results as $result){
$result = array_values($result);
$result = implode(", ", $result);
$csv_output .= $result."
";
}
}
/** * Prepare Filename And CSV File to export */
$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
Putting this all in a function should do the trick