Replies

• skione Posts: 7Questions: 0Answers: 0

  May 2011 edited May 2011

  I figured out how to do what i needed by using a custom button:
  [code]
  $('#ReportTable').dataTable({
  'sDom': 'T<"clear">lfrtip',
  "oTableTools": {
  "aButtons": [
  "copy",
  "csv",
  "pdf",
  "print",
  {
  "sExtends": "text",
  "sButtonText": "Export All",
  "fnComplete": function ( nButton, oConfig, oFlash, sFlash ) {
  $.ajax({
  url: 'report_exporter.php',
  dataType: 'json',
  type: 'post',
  data: $('#form1').serialize(),
  error: function() {
  return true;
  },
  success: function(data) {
  if( data )
  {
  if (data.status == 'success') {
  $("#ExportedFileSection").show();
  $("#ExportedFile").attr("href", data.filename);
  }
  }
  }
  });
  }
  }
  ]
  },
  'bProcessing': true,
  'bServerSide': true,
  'sAjaxSource': 'report_generator.php?action=Generate',
  'fnServerData': function ( sSource, aoData, fnCallback ) {
  $('#ReportForm :input').each(function(){
  aoData.push({"name": $(this).attr('name'),"value":$(this).val()});
  });
  
  $.ajax({
  'dataType': 'json',
  'type': 'POST',
  'url': sSource,
  'data': aoData,
  'success': fnCallback
  });
  }
  } );
  [/code]
  I store the query that is being paginated by the server side processing script in a session variable then report_exporter.php gets that query and generates the CSV in the background returning the file name. The ajax call get's the name and updates a link on the page so the user can download it.
  
  Here are the functions I use the generate the CSV:
  [code]
  public function ExportData($sql) {
  $out = '';
  $header = '';
  $results = $this->runQuery($sql);
  $fields = $this->GetQueryFields($results);
  
  foreach ($fields as $f) {
  $header .= '"'.$f.'",';
  }
  
  $out .= rtrim($header,',')."\n";
  
  while ($row = mysql_fetch_array($results))
  {
  $csv_row = '';
  foreach ($fields as $f) {
  $csv_row .= '"' . $row[$f] . '",';
  }
  $out .= rtrim($csv_row,',')."\n";
  }
  $filename = $this->OutPutData($out);
  return $filename;
  }
  
  private function OutPutData($out) {
  $time = $this->current_time();
  $length = strlen($out);
  $filename = "tmp/".md5($time.$length).".csv";
  $fd = fopen($filename, "w");
  $bytes = fwrite($fd, $out . "\n");
  fclose($fd);
  return $filename;
  }
  [/code]
  
  And here is a code snippet used at the end of report generator:
  [code]
  $sQuery = $_SESSION['sQuery'];
  $filename = ExportData($sQuery);
  $output=Array( 'status'=>'success',
  'query'=>$sQuery,
  'filename'=>$filename);
  echo json_encode($output);
  [/code]
  
  I can't post the entire solution as the code is wrapped up in various classes but I think you can get the idea.