export a subset to csv

Hi Bob,

I have a working CSV export (thanks to max for his example). This is doing the business 9if you need all rows and all fields), but I have been asked to supply a CSV with only a subset of the table fields to be included (all rows are needed).

The example Max provides (see below), grabs all the fields in the table, can you assist on how we only grab a subset? (e.g field 1, 3, 4,8 out of a 20 field table)

Thanks

Anthony

<?php
		 //export to csv start here
		 
		   $database =& JFactory::getDBO();       
       $tablename = $regoInfoTable;
       $tables = array( $tablename );
       $result = $database->getTableFields( $tables );
       $table_fields = array_keys($result[$tablename]);
       
       $database->setQuery( "SELECT * FROM ".$tablename."" );
       $datarows = $database->loadObjectList();
      
       $titcol = 0;
       foreach($table_fields as $table_field){
          if($titcol){$csvline .=",";}
          $csvline .= $table_field;
          $titcol++;
       }
       $csvline .="\n";
             
       $datacol = 0;
       $rowcount = 1;
       foreach($datarows as $datarow){
          foreach($table_fields as $table_field){
             if($datacol){$csvline .=",";}
             $csvline .= '"'.addslashes($datarow->$table_field).'"';
             $datacol++;
          }
          $csvline .="\n";
          $datacol = 0;
          $rowcount++;
       }
       
       if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
          $UserBrowser = "Opera";
       }
       elseif (ereg('MSIE ([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
          $UserBrowser = "IE";
       } else {
          $UserBrowser = '';
       }
       $mime_type = ($UserBrowser == 'IE' || $UserBrowser == 'Opera') ? 'application/octetstream' : 'application/octet-stream';
       @ob_end_clean();
       ob_start();

       header('Content-Type: ' . $mime_type);
       header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');

       if ($UserBrowser == 'IE') {
          header('Content-Disposition: inline; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
          header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
          header('Pragma: public');
       }
       else {
          header('Content-Disposition: attachment; filename="' . $tablename." - ".date("j_n_Y").'.csv"');
          header('Pragma: no-cache');
       }
       print $csvline;
       exit();
?>

Hi Bob,

Its amazing how a cup of tea and a good lie down can help clear the fog :-)
By changing

 $tables = array( $tablename );
       $result = $database->getTableFields( $tables );
       $table_fields = array_keys($result[$tablename]);
       $database->setQuery( "SELECT * FROM ".$tablename."" );

to

 $table_fields = array("field1", "field2");
       $database->setQuery( "SELECT `field1`, `field2` FROM ".$tablename."" );

One gets the desired result.

Sorry to bother you, but sometimes just writing it down triggers the answer.

Hi ajw3208,

No problem - I would eventually have gotten round to posting something like that. (It also lets you change the order of the fields and to do some MySQL pre-processing if that's needed.)

You can also simplify Max's code using a couple of other PHP commands. Here's a little for I wrote a few days ago to output a CSV file

<?php
/* ensure that this file is called by another file */
defined('_JEXEC') or die('Restricted access');

jimport('joomla.user.helper');
$fp = fopen(JPATH_SITE.DS.'components'.DS.'com_chronocontact'.DS.'includes'.DS.'my_file.csv', 'w');

$db =& JFactory::getDBO();
$query = "
  SELECT `field1`, `field2` FROM `$tablename`
";
$db->setQuery($query);
$data = $db->loadAssocList();

foreach ( $data as $d ) {
  fputcsv($fp, $d);
}
fclose($fp);
?>

Bob

Hi Bob,

Thanks for the reply.

Following on from that thought, how would you zip up the file you created and password protect the resulting zip file?

aj

Hi aj,

You can add a few lines to put the file into a zip using the PHP ZipArchive library. From a quick scan of the docs I don't see any way of password protecting the zip but you could password protect the download.

Bob