php export mysql data with dynamic columns to csv

Question

iam new to php, i know how to export mysql results with static columns to csv, but in some cases i will have to export dynamically without specifying the column names. i have tried this below, i would need help to make this work please.

PHP

<?php
if(isset($_POST['action']) && $_POST['action'] == 'test_export_to_csv'){


$head_qry = mysql_query("SELECT `header_display_name` FROM expense_heading WHERE expense_id='$expense_id' order by col_order");

$columnValues = Array();

while ( $row = mysql_fetch_assoc($head_qry) ) {

  $columnValues[] = $row['columnname'];

}
$filename = "uploads/reports/".$invoice_id.".csv";
$file = fopen($filename, 'w+');

fputcsv($file,$columnValues);   

$data_qry="SELECT $columnValues from invoice_detail where invoice_id='$invoice_id'";

$data_result=mysql_query($data_qry);
while($result=mysql_fetch_assoc($data_result)){
    $report_array=array($result[$columnValues]);
    fputcsv($file, $report_array);
}
fclose($file);

if(ini_get('zlib.output_compression'))
ini_set('zlib.output_compression', 'Off');


header("Pragma: public"); // required
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false); // required for certain browsers
header("Content-Type: $ctype");

// change, added quotes to allow spaces in filenames
header("Content-Disposition: attachment; filename=\"".basename($filename)."\";" );
header("Content-Transfer-Encoding: binary");
header("Content-Length: ".filesize($filename));
readfile("$filename");
exit();  
}

1. Don't use the deprecated and insecure mysql_*-functions. They have been deprecated since PHP 5.5 (in 2013) and were completely removed in PHP 7 (in 2015). Use MySQLi or PDO instead. 2. You are open to SQL Injections and should really use Prepared Statements instead of concatenating your queries, which can be used if you use the above mentioned MySQLi or PDO. — M. Eriksson
– M. Eriksson, Commented Jul 30, 2017 at 10:47
Where are the variables $expense_id and $invoice_id defined? — M. Eriksson
– M. Eriksson, Commented Jul 30, 2017 at 10:49
$columnValues is an array. You definitely need to use implode to pass it to query text. — u_mulder
– u_mulder, Commented Jul 30, 2017 at 10:49

u_mulder · Accepted Answer · 2017-07-30 11:10:42Z

1

I suppose something like this should work:

$columnValues = Array();

while ( $row = mysql_fetch_assoc($head_qry) ) {
  $columnValues[] = $row['columnname'];
}
$filename = "uploads/reports/".$invoice_id.".csv";
$file = fopen($filename, 'w+');

fputcsv($file,$columnValues);   

// don't use `array_keys`
$columnValuesStr = implode(', ', $columnValues);

$data_qry = "SELECT $columnValuesStr from invoice_detail where invoice_id='$invoice_id'";

$data_result = mysql_query($data_qry);
while ($result = mysql_fetch_assoc($data_result)) {
    // `$result` already an array which holds values of selected fields,
    // you can pass it directly to `fputcsv`
    fputcsv($file, $result);
}

answered Jul 30, 2017 at 11:10

u_mulder

54.8k5 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

php export mysql data with dynamic columns to csv

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related