Need help in creating a single variable from a multi record search report output

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

By gkornbluth - January 19, 2020 - edited: January 20, 2020

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

By gkornbluth - January 20, 2020

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

By daniel - January 20, 2020

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
Technical Lead
interactivetools.com

By gkornbluth - January 20, 2020 - edited: January 20, 2020

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

By daniel - January 20, 2020

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
Technical Lead
interactivetools.com

By gkornbluth - January 20, 2020 - edited: January 21, 2020

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