Exporting to CSV with date range

[icoso]

I understand there is an Export to CSV function built-in to nuBuilder, but it doesn't fit my needs. I don't want my users that can eport to have access to "export" any other tables except for one. So I thought I 'd build a form that lets the user pick the start date and end date and then do the export. I'm trying to use this code:

//==============================
//Run the Export to a file here: I have already created code that I use for reporting that grabs the data from the existing TaxCustomers table, processes it and updates it in a temporary table. I know this process works, because I haven't changed it except for the initial SQL statement for what records it selects AND I see the temporary table get created in myPHPAdmin and it contains the updated data. (see Topic: https://forums.nubuilder.cloud/viewtopic.php?f=20&t=10866) for reference. So by the time the PHP script reaches this export section the records exist in a Temporary table and that table name is in the variable $tabid.
//==============================

Code: Select all

// output headers so that the file is downloaded rather than displayed
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=ExpTaxCustomers.csv');

// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');

// output the column headings of my table
fputcsv($output, array('TaxCustomers_id', 'cust_prissn', 'cust_retdate', 'cust_lastname', 'cust_firstname', 'cust_midinit', 'cust_surname', 'cust_spouse',  'tax_office', 'tax_comments', 'count'));

// fetch the data from the temporary table $tabid - I know this table exists and has the correct data in it.  I checked my nudebug to make sure its using the correct table name.
$expsql = "SELECT * From $tabid";
nuDebug($expsql);
$t = nuRunQuery($expsql);


// loop over the rows, outputting them  this should output the rows to the output device.
while($r = db_fetch_array($t)){
   fputcsv($output, $r);
} //end While Loop

What is happening is I get that popup window that is showing my output of my records then a bunch of other info from the database. SO I think the process is working, its just not generating the download dialog box like the built-in CSV Export function does. Any suggestions or help with this?

[kev1n]

You could try this code and see if it works and what is different to yours.
https://github.com/smalos/nuBuilder4-Co ... dToCSV.php

[icoso]

Kev1n,

I've looked at that and everything seems to be working but whats appears to not be working is this:

Code: Select all

// output headers so that the file is downloaded rather than displayed
$fileName = "ExpTaxCustomers.csv";

header('Content-Type: application/excel');
header('Content-Encoding: UTF-8');
header('Content-Disposition: attachment; filename="' . $fileName . '"');

// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');

Instead of getting the dialog box to save the data, it appears to output it to the screen. Like it does if I use an echo command in my PHP or javascript.

Export1.png

I know the last part of my php code is working because the nuDebug function is showing the correct table and I see it in phpMyAdmin and the output that is going to the screen rather than a file.

Code: Select all

// fetch the data
$expsql = "SELECT * From $tabid";
nuDebug($expsql);
$t = nuRunQuery($expsql);

// loop over the rows, outputting them
while($r = db_fetch_row($t)){
   fputcsv($output, $r);
} //end While Loop

//Close the file pointer.
fclose($output);

This is the NuDebug screen showing the $expsql variable that I use in the last nuRunQuery($expsql) to retrieve the data after I've modified the SSNs in the table.

Export2.png

This is from the phpMyAdmin thats showing the table.

Export3.png

I just can't figure out why its outputting to the screen like it is rather than a file. Something is causing the PHP to fail because nuBuilder is not deleting the Temporary table after this procedure runs and its sending the output to the screen.

[icoso]

I figured it out. The Procedure was set to run in "Hidden" mode. Just being curious, I changed it to run "In a New Window", and it gave me my csv file as I wanted.

The only thing now that is happening is that the temporary table is NOT getting deleted after the procedure runs. How can I delete that temporary table?

[kev1n]

Where is the temporary table created ? I don't see it in your source code.
Just drop it at the end of the script with nuRunQuery and a DROP TABLE Statement.

[icoso]

Works! Thanks! Used the following.

Code: Select all

/ Drop the Temporary data table 
$dropsql = "DROP TABLE IF EXISTS $tabid";
nuDebug($dropsql);
$t = nuRunQuery($dropsql);