1

So my following code generated a CSV based on specified tables and generates file and saves to downloads/filename.csv however its not asking the user to download once its generated. Any ideas why?

Here is the code:

PHP

    header("Content-type: text/csv"); 

        // Connect to the database
        $mysqli = new mysqli(DATABASE_HOST, DATABASE_USER, DATABASE_PASS, DATABASE_NAME);

        // output any connection error
        if ($mysqli->connect_error) {
            die('Error : ('.$mysqli->connect_errno .') '. $mysqli->connect_error);
        }

        $tables = array('invoices', 'customers', 'invoice_items'); // array of tables need to export

        $file_name = 'invoice-export-'.date('d-m-Y').'.csv';   // file name
        $file_path = 'downloads/'.$file_name; // file path

        $file = fopen($file_path, "w"); // open a file in write mode
        chmod($file_path, 0777);    // set the file permission

        // loop for tables
        foreach($tables as $table) {
            $table_column = array();
            $query_table_columns = "SHOW COLUMNS FROM $table";

            // fetch table field names
            if ($result_column = mysqli_query($mysqli, $query_table_columns)) {
                while ($column = $result_column->fetch_row()) {
                    $table_column[] = $column[0];
                }
            }

            // Format array as CSV and write to file pointer
            fputcsv($file, $table_column, ",", '"');

            $query_table_columns_data = "SELECT * FROM $table";

            if ($result_column_data = mysqli_query($mysqli, $query_table_columns_data)) {

                // fetch table fields data
                while ($column_data = $result_column_data->fetch_row()) {
                    $table_column_data = array();
                    foreach($column_data as $data) {
                        $table_column_data[] = $data;
                    }

                    // Format array as CSV and write to file pointer
                    fputcsv($file, $table_column_data, ",", '"');
                }
            }
}

    // close file pointer
    fclose($file);

    // ask either save or open
    header("Pragma: public");
    header("Expires: 0");
    header("Content-Type: application/octet-stream");
    header("Content-Disposition: attachment; filename='{$file_name}';" );
    header("Content-Transfer-Encoding: binary");

    // open a saved file to read data
    $fhandle = fopen($file_path, 'r');
    fpassthru($fhandle);
    fclose($fhandle);
    $mysqli->close();
    die;
James
  • 1,668
  • 19
  • 50
  • if there are columns you dont need, just remove them from your query. also post what output this generates currently, and eg what you want to remove.. – amdixon May 10 '15 at 10:42
  • 1
    you can check this: http://stackoverflow.com/questions/4249432/export-to-csv-via-php – Nik Drosakis May 10 '15 at 10:43

1 Answers1

0

This should allow you to select the field you want to write to the CSV file, and their order.

// loop over the rows, outputting them
while($row = $results->fetch_assoc()) {
    $data = [
       $row["myfirstfield]",
       $row["mysecondfield"],
       ....
       ];
    fputcsv($output, $data);
 }
Michel Billaud
  • 1,758
  • 11
  • 14
  • Thanks mate, ill give it a go now and let you know. – James May 10 '15 at 10:45
  • PHP Parse error: syntax error, unexpected '[' in /home/ambnews/public_html/invoice/response.php on line 52, – James May 10 '15 at 10:56
  • I guess you're using an older version of PHP..... replace `$data = [....]` with `$data = array(....)` or upgrade from an ancient version of PHP (<= 5.3.0) to a supported version that allows short array syntax – Mark Baker May 10 '15 at 10:59
  • Ok, i changed it but getting: PHP Parse error: syntax error, unexpected ',', expecting ']' in /home/ambnews/public_html/invoice/response.php on line 79 - ill update the question with latest code now. – James May 10 '15 at 11:04