Query results to PHPExcel?

Topics: Developer Forum, Project Management Forum, User Forum
Jul 3, 2008 at 6:03 PM
Hello all,

I am trying to use PHPExcel to export the results of a query to Excel.  Is this possible?

Here is the query and my attempt to get its output:

$sql = "SELECT * FROM brev_pending_summary_detail WHERE name = '$name_code'";
 if (!empty($case_age)) {
  $sql.=" AND case_age_group = '$case_age'";
 }
 if (!empty($case_cat)) {
  $sql.=" AND case_category = '$case_cat'";
 }
 if (!empty($case_status)) {
  $sql.=" AND case_status = '$case_status'";
 } // Start our query of the database
$query = ifx_query($sql, $connect_id);
$row = ifx_fetch_row($query);

echo date('H:i:s') . " Add some data<br />\n";
if(!empty($query)) {
while ($row = ifx_fetch_row($query)) {
 $stat_year = $row['stat_year'];
 $stat_month = $row['stat_month'];
 $name = $row['name'];
 $case_number = $row['case_number'];
 echo "This ".$stat_year." ".$stat_month." ".$j_name." ".$case_number."<br />\n";
 $objPHPExcel->getActiveSheet()->setCellValue('A', $stat_year);
 $objPHPExcel->getActiveSheet()->setCellValue('B', $stat_month);
 $objPHPExcel->getActiveSheet()->setCellValue('C', $name);
 $objPHPExcel->getActiveSheet()->setCellValue('D', $case_number);
 }
}

Whenever I execute my query and it saves the Excel file, instead of getting a row for each result of my query, I get a spreadsheet with 65,535 empty rows and row 65,536 contains the very last result row from my query.  How do I get it to print out correctly?

Thanks.

Jul 3, 2008 at 7:35 PM
Okay, I figured it out! However, I am now constantly getting an error of "PHP Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 936 bytes) on line 689"

Is there a way to continuously write to the file and avoid getting this error?

Here is the working code:

$sql = "SELECT * FROM brev_pending_summary_detail WHERE name = '$name_code'";
 if (!empty($case_age)) {
  $sql.=" AND case_age_group = '$case_age'";
 }
 if (!empty($case_cat)) {
  $sql.=" AND case_category = '$case_cat'";
 }
 if (!empty($case_status)) {
  $sql.=" AND case_status = '$case_status'";
 } // Start our query of the database
$query = ifx_query($sql, $connect_id);
$row = ifx_fetch_row($query);
$results = array();

echo date('H:i:s') . " Add some data<br />\n";

for ($i = 3, $j = 0; $i <= $count; $i++, $j++) {
 $results[$j] = ifx_fetch_row($query);

 $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $results[$j]['stat_year']);
 $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $results[$j]['stat_month']);
 $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $results[$j]['name']);
 $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $results[$j]['case_age_group']);
 }

Coordinator
Jul 3, 2008 at 7:41 PM
You'll have to increase the memory limit, I'm affraid... Is it really necessary to keep track of all results in the $results array? Here's a possible optimization by simply fetching data into $results = ifx_fetch_row() and copying it to PHPExcel.    
Jul 3, 2008 at 7:52 PM
I just need to output all of the data into PHPExcel. I was getting the PHP Fatal error: Allowed memory size of 16777216 bytes exhausted error when trying to just output 130 rows with 12 columns.

So are you saying to replace $results = array(); with $results = ifx_fetch_row() and run it just like that?

Like this code:

$sql = "SELECT * FROM brev_pending_summary_detail WHERE name = '$name_code'";
 if (!empty($case_age)) {
  $sql.=" AND case_age_group = '$case_age'";
 }
 if (!empty($case_cat)) {
  $sql.=" AND case_category = '$case_cat'";
 }
 if (!empty($case_status)) {
  $sql.=" AND case_status = '$case_status'";
 } // Start our query of the database
$query = ifx_query($sql, $connect_id);
$row = ifx_fetch_row($query);

$results = ifx_fetch_row();

echo date('H:i:s') . " Add some data<br />\n";

for ($i = 3, $j = 0; $i <= $count; $i++, $j++) {
 $results[$j] = ifx_fetch_row($query);

 $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $results[$j]['stat_year']);
 $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $results[$j]['stat_month']);
 $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $results[$j]['name']);
 $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $results[$j]['case_age_group']);
 }

Jul 3, 2008 at 8:08 PM
maartenba,

I bumped the max memory size from 16MB to 256MB and the max timeout form 30 to 60.  Trying to export a file with 3600 rows still times out.

Is there something different I can do to make the code more efficient and less memory intensive?
Jul 3, 2008 at 8:28 PM
Edited Jul 3, 2008 at 8:37 PM
.