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

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!

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
*************************************-->

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

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,

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