7 posts by 2 authors in: Forums > CMS Builder
Last Post: January 20   (RSS)

Hi Jerry,

Something that may be helpful here is the fputcsv() function; this is a built-in PHP function that will take an array of values and write them as a line in CSV format to a file. You can find the documentation and some examples here: https://www.php.net/manual/en/function.fputcsv.php

You could modify your code to do something along these lines:

<?php

...

// open CSV file for writing
$myfile = fopen("newfile.csv", "w") or die("Unable to open file!");

// arrange headers into array and write to CSV file
$headers = [$var0, $var1, $var2, $var3, $var4];
fputcsv($myfile, $headers);

?>

<?php foreach ($booksRecords as $record): ?>

  ...

  <?php
  // arrange report values into array and write to CSV file
  $csvLine = [$title, $location, $publisher, $publication_date, $purchase_date];
  fputcsv($myfile, $csvLine);
  ?>

<?php endforeach; ?>

<?php
// close CSV file
fclose($myfile);
?>

Let me know if that does the trick or if you have any other questions.

Thanks!

Daniel
PHP Programmer
interactivetools.com

Hi Daniel,

Wow, thanks for getting back  so quickly on this.

Seems like a very elegant and simple solution and I'm very close.

The $headers line works as planned, but the $csvline variable output only repeats the field values from the last record for each row.

Here's the code I'm using based on your suggestion and I've attached a screenshot of the output issue.

Thanks as always,

Jerry Kornbluth

<!--************************************
Report Output Begin
*************************************-->

<?php foreach ($booksRecords as $record): ?>
<?php if(@$_REQUEST['save']):?>

<?php if($record['title'] ):?>
<?php // Replace all commas with space and dash
$title = $record['title'];
$title = preg_replace("[,]", " -", $title); ?>
<?php echo htmlencode($title) ?>,
<?php else : ?>
,
<?php endif ?>

<?php if(($location_visible == 1)):?>
<?php if($record['location'] ):?>
<?php // Replace all commas with space and dash
$location = $record['location:label'];
$location = preg_replace("[,]", " -", $location); ?>
<?php echo htmlencode($location)?>,<?php else : ?>
,
<?php endif ?> <?php endif ?>

<?php if(($publisher_visible == 1)):?>
<?php if($record['publisher']):?>
<?php // Replace all commas with space and dash
$publisher = $record['publisher'];
$publisher = preg_replace("[,]", " -", $publisher); ?>
<?php echo htmlencode($publisher )?>,<?php else : ?>
,
<?php endif ?><?php endif ?>

<?php if(($publication_date_visible == 1)):?>
<?php if($record['publication_date'] ):?>
<?php // Replace all commas with space and dash
$publication_date = $record['publication_date'];
$publication_date = preg_replace("[,]", " -", $publication_date); ?>
<?php echo htmlencode($publication_date )?>,<?php else : ?>
,
<?php endif ?> <?php endif ?>

<?php if(($purchase_date_visible == 1)):?>
<?php if($record['purchase_date']):?>
<?php // Replace all commas with space and dash
$purchase_date = $record['purchase_date'];
$purchase_date = preg_replace("[,]", " -", $purchase_date); ?>
<?php echo htmlencode($purchase_date )?>,<?php else : ?>
,
<?php endif ?><?php endif ?>
<?php endforeach ?>

<!--************************************
Report Output End
*************************************-->

<!--************************************
Save the csv file Begin
*************************************-->

<?php

// open CSV file for writing
$myfile = fopen("newfile.csv", "w") or die("Unable to open file!");

// arrange headers into array and write to CSV file
$headers = [$var0, $var1, $var2, $var3, $var4];
fputcsv($myfile, $headers);

?>

<?php foreach ($booksRecords as $record): ?>

<?php
// arrange report values into array and write to CSV file
$csvLine = [@$title, @$location, @$publisher, @$publication_date, @$purchase_date];
fputcsv($myfile, $csvLine);
?>

<?php endforeach; ?>

<?php
// close CSV file
fclose($myfile);
?>

<!--************************************
Save the csv file End
*************************************-->

The first CMS Builder reference book is now available on-line!

Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Attachments:

csv output.jpg 208K

Hi Jerry,

You'll want to place the fputcsv($csvline...) code at the end - but inside of - the original foreach loop; this way it will call fputcsv() for each record with the correct variable contents set. This means you'll also need to put your fopen and fputcsv($headers...) code above that foreach.

Hope that helps!

Thanks,

Daniel
PHP Programmer
interactivetools.com

By gkornbluth - January 20 - edited: January 20

Hi Daniel,

Almost there...

It seems that since there's always a complete set of variables in the $headers array, when one of those column variables is set to not show ( $publisher_visible = 0  for example) an empty column still shows in the Excel spreadsheet.

Same thing is happening in the $ csvLine rows

Screen shot uploaded as well as the complete php file

I'm sure that there's some magic way to eliminate empty columns (where $publisher_visible = 0 for example), but I haven't been able to figure that one out.

Thanks,

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!

Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Attachments:

headers.jpg 244K

spread5.php 50K

Hi Jerry,

You can address this by starting with an empty array and then only adding the elements when visible. Something like this:

<?php

$headers = [];

$headers[] = $var0;
if ($location_visible) { $headers[] = $var1; }
if ($publisher_visible) { $headers[] = $var2; }
... etc

?>

The same should work for the $csvLine array as well.

Thanks,

Daniel
PHP Programmer
interactivetools.com

By gkornbluth - January 20 - edited: January 21

Daniel,

Thank you so much for this.

I've implemented your last suggestions and they work perfectly.

The report page now allows for:

A criteria search in both text, and pull down fields to limit the records returned.

Choosing which fields (columns) will appear in the final report.

Removing any stray commas from the search results

Automatically creating a .csv file from the report for Excel, including a column header line.

I also added some code to redirect to an auto download page for the csv file.

The final pages are attached for anyone who can learn from them.

Best,

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!

Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Attachments:

spread7.php 52K

downloadcsv.php 1K