What is the best way to create a csv file for download?

Question

I have a web application that creates "Work tickets". My client has requested that the reports page give them the ability to download a report as a csv file. I have this functionality in place. Essentially it is creating a CSV on the fly as the report table is being created. The csv is able to be downloaded if the table doesn't have a lot of data in it. What I have run into is that when the table is too big I reach the limit of characters that can be passed in a url. How can I get around this? It is driving me crazy. Below is an example of my php and html code.

PHP code to Create CSV Download

<?php
$out = '';


 $filename_prefix = 'UnInvoiced';





 if (isset($_REQUEST['csv_hdr'])) {
 $out .= $_REQUEST['csv_hdr'];
 $out .= "\n";
 }

 if (isset($_REQUEST['csv_output'])) {
 $out .= $_REQUEST['csv_output'];
 }


 $filename = $filename_prefix."_".date("Y-m-d_H-i",time());


 header("Content-type: application/vnd.ms-excel");
 header("Content-Encoding: UTF-8");
 header("Content-type: text/csv; charset=UTF-8");
 header("Content-disposition: csv" . date("Y-m-d") . ".csv");
 header("Content-disposition: filename=".$filename.".csv");
 print $out;



 exit;
 ?>

PHP Code to create the Initial Table on HTML page.

 echo "<div class='panel panel-primary'>
 <div class='panel-heading'>
 <h3 class='panel-title'>Un-Invoiced Tickets</h3>

 </div>
 <table class='table'>
 <thead>
 <tr class='filters'>
 <th>Date</th>
 <th>Ticket #</th>
 <th>Client</th>
 <th>Lease</th>
 <th>Ticket Total</th>
 <th>Ordered By</th>
 </tr>
 </thead>
 <tbody>";


  $conn = sqlsrv_connect($DBSERV, $connection);
  if (!$conn) {
  die('Could not connect: ' . sqlsrv_error($conn));
  }
  if ($client === 'All'){
  $sql="SELECT REPLACE(Client,',',' ') 
  AS Client,WorkTicketNum,Lease,Date,OrderedBy,WTTotal FROM WorkTicket WHERE Date BETWEEN '".$begin."' AND '".$end."' AND Invoiced IS NULL ORDER BY Client ";
                    }else{
                        $sql="SELECT REPLACE(Client,',',' ') AS Client,WorkTicketNum,Lease,Date,OrderedBy,WTTotal FROM WorkTicket WHERE Date BETWEEN '".$begin."' AND '".$end."' AND Invoiced IS NULL AND Client = '".$client."' ORDER BY Client "; 
                    }
                $result =sqlsrv_query($conn, $sql);

                while($row = sqlsrv_fetch_array($result,SQLSRV_FETCH_ASSOC)) { ?>
                   <tr>
                        <td> <?php echo $row['Date'];
                    $csv_output .= $row['Date'].", "; ?> </td>

                        <td> <?php echo $row['WorkTicketNum'];
                    $csv_output .= $row['WorkTicketNum'].", "; ?> </td>

                        <td> <?php echo $row['Client'];
                    $csv_output .= $row['Client'].", "; ?> </td>

                        <td> <?php echo $row['Lease'];
                    $csv_output .= $row['Lease'].", "; ?> </td>

                        <td> <?php echo $row['WTTotal'];
                    $csv_output .= $row['WTTotal'].", "; ?> </td>

                        <td> <?= $row['OrderedBy'];
                    $csv_output .= $row['OrderedBy']."\n "; ?> </td>
                         </tr>
              <?php
                }

               echo " </tbody>
        </table>
    </div>

 <br />

 <a class='btn btn-default' href='/processes/uninvoicedexport.php?  csv_hdr=".urlencode($csv_hdr)."&csv_output=".urlencode($csv_output)."'>Export to CSV</a>";                  
/*<form name='export' action='/processes/unsignedexport.php' method='post'   target='_BLANK'>
<input type='submit' value='Export table to CSV'>
<input type='hidden' value=".$csv_hdr." name='csv_hdr'>
<input type='hidden' value=".$csv_output." name='csv_output'>
</form>

<div id='ticketHint'><b></b></div>
</form>";*/
sqlsrv_close($conn);

?>

Someone had suggested that I make the csv be written to the SQL database and then retrieved when the download button is pressed but I cannot for the life of me figure out how to do that.

Answer 1

If your mysql user does not have permission to select into outfile and like the above comment, do not pass all the data to the url.

Try something like this (not tested, but should give you the idea):

<?php
if ($_REQUEST["myaction"]=="getcsvstuff") {
 // handle the conditions you passed into $_POST or $_REQUEST 
 // set your headers, do your query and output the csv results
} else {
 // print your form to pass what you would like 
 // you may have visual results of your query here in your table etc
 // just like your example 
?>
<form action="<?php $_SERVER["PHP_SELF"];?>" method="POST">
<input type="hidden" name="myaction" value="getcsvstuff" />
<input type="submit" value="go to csv results" />
... whatever else you want to pass to the above condition... 
</form>
<?php
}
?>

Answer 2

You can (and possibly should) use mysql for the output:

SELECT * FROM tableName INTO OUTFILE 'filename.csv'

This will generate the file for you without the need of passing data via your url. Of course, you can choose whatever fields you want to have exported.