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

By gkornbluth - January 19 - edited: January 20

Hi all,

I’ve got a complex search form that outputs a custom report page.

I’m working on automatically creating a .csv file from the page to use in Excel. (Right now I’m copying the output of the report section of the web page (below), pasting it into a text editor, and saving the file with a .csv extension)

I’ve got the writing variables to a csv file and the header row output variable ($output1) figured out.

I can’t seem to figure out a way to create the $output3 variable from the multi record report output.

Hope someone can help.

Thanks,

Jerry Kornbluth

The basic setup for the output on the page (without the search query form code) is:

<!--************************************
Start of Visibility Variables
*************************************-->
<?php if (isset($_POST['location_visible'])) {
@$location_visible = 1;

} else {

@$location_visible = 0;
}
?>
<?php if (isset($_POST['publisher_visible'])) {
@$publisher_visible = 1;

} else {

@$publisher_visible = 0;
}
?>
<?php if (isset($_POST['publication_date_visible'])) {
@$publication_date_visible = 1;

} else {

@$publication_date_visible = 0;
}
?>
<?php if (isset($_POST['purchase_date'])) {
@$purchase_date_visible = 1;

} else {

@$purchase_date_visible = 0;
}
?>

<!--************************************
End of Visibility Variables
*************************************-->

<!--************************************
Header Row Start
*************************************-->

<?php @$var0 = 'Title,'; @$var1=''; @$var2=''; @$var3=''; @$var4='';?>

Title,
<?php if(($location_visible == 1)):?>
<?php @$var1 = "Location," ?>
<?php echo "Location," ?><?php endif ?>

<?php if(($publisher_visible == 1)):?>
<?php @$var2 = "Publisher," ?>
<?php echo "Publisher," ?>
<?php endif ?>

<?php if(($publication_date_visible == 1)):?>
<?php @$var3 = "Publication Date," ?><?php endif ?>

<?php if(($purchase_date_visible == 1)):?>
<?php $var4 = "Purchase Date," ?>
<?php echo "Purchase Date," ?><?php endif ?>

<?php if(@$_REQUEST['save']):?>
<br />
<?php endif ?>
<?php $output1 = $var0 .$var1 .$var2 .$var3 .$var4 ;
$output2 = preg_replace("/<br\W*?\/>/", "\n", $output1);
<!--************************************
Header Row End
*************************************-->
<!--************************************
Report Output Start
*************************************-->
<?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 ?>

<?php $output3 = ????; // Can’t figure out how to create this variable from the report output
$output4 = preg_replace("/<br\W*?\/>/", "\n", $output3);

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

<!--************************************
Save the csv file Begin
*************************************-->
<?php
$myfile = fopen("newfile.csv", "w") or die("Unable to open file!");
$txt = $output2;
fwrite($myfile, $txt);
$txt = $output4;
fwrite($myfile, $txt);
fclose($myfile);
?>

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

The final web page output for the above is something like:

Title, Location, Writer 1,Writer 2,
600 Black Spots, B-12, David A Carter, ,
A l'abordage ! : La grande aventure du bateau pirate, C-14, Dario Cestaro, Paul Thiès,
A Pop Up Book of European Cities, A-12, Graham Brown, Sandy Ransford,
A POP-UP BOOK OF NURSERY RYHMES, D-24, , ,
A POP-UP RAILROAD BOOK TRAINS, A-12, Robert Crowther, ,

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

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