How to export a PHP recordset to excel using a button

[dreamaz]

Hi,

I have a table that's displayed with data using PHP/MySQL. I wanted to add a button on the bottom of the table like 'EXPORT'

On clicking i would like the contents of the table exported into an EXCEL formatted file which I can then email out. Please let me know how i can do this - any help is appreciated.

Thanks,

dR

 

[jpadie]

$result = mysql("Select * from table") 
            or die (mysql_error());
$dl = "";
while ($row=mysql_fetch_assoc($result)):
  $tmp = "'" . implode ("','", $row) . "'\r\n";
  $dl .=$tmp;
endwhile;
$size = strlen($dl);
header("Content-Length: " . $size);
header("Content-Type: application/force-download");
header('Content-Disposition: attachment; filename="exportedtable.csv"');
header("Content-Transfer-Encoding: binary");
echo $dl;
exit;

 

[dreamaz]

jpadie worked like a charm!.. Just gotta make some minor modifications, otherwise - thanks a lot!

 

[jpadie]

pleasure.

i haven't not tested the output. you might need to escape certain characters for escel's benefit. if you want to have the field names too:

while ($row=mysql_fetch_assoc($result)):
  if (!isset($flag)):
      foreach ($row as $key=>$val):
          $dl .= "'".$key."'";
      endforeach;
      $dl .= "\r\n";
      $flag = true;
  endif;
  $tmp = "'" . implode ("','", $row) . "'\r\n";
  $dl .=$tmp;
endwhile;

 

[dreamaz]

I ran into a minor problem (hope its minor). I decided to narrow down the output based on URL Parameter, but now either everything or nothing shows up in the excel file.

Created a basic page that has a link to the php page that generates the excel page.

http://site/report2.php?OwnerGroup=GROUP1

I have a field in that table called Product Type and wanted only the records which are memebers of group1.

When i click on that link i get an empty excel sheet. When i take out the URL completely i get everything in the table

 

[jpadie]

if (isset($_GET['OwnerGroup'])):
 $og = trim($_GET['OwnerGroup']);
 if (get_magic_quotes_gpc()):
   $og = " where " . mysql_escape_string(stripslashes($og));
 else:
   $og = " where " . mysql_escape_string($og);
 endif;
endif;

$result = mysql("Select * from table $og")
            or die (mysql_error());
$dl = "";
while ($row=mysql_fetch_assoc($result)):
  if (!isset($flag)):
      foreach ($row as $key=>$val):
          $dl .= "'".$key."'";
      endforeach;
      $dl .= "\r\n";
      $flag = true;
  endif;
  $tmp = "'" . implode ("','", $row) . "'\r\n";
  $dl .=$tmp;
endwhile;
$size = strlen($dl);
header("Content-Length: " . $size);
header("Content-Type: application/force-download");
header('Content-Disposition: attachment; filename="exportedtable.csv"');
header("Content-Transfer-Encoding: binary");
echo $dl;
exit;

 

[jpadie]

oops. that won't work...

change

 if (get_magic_quotes_gpc()):
   $og = " where " . mysql_escape_string(stripslashes($og));
 else:
   $og = " where " . mysql_escape_string($og);
 endif;

for

 if (get_magic_quotes_gpc()):
   $og = " where ProductType ='" . mysql_escape_string(stripslashes($og))."'";
 else:
   $og = " where ProductType = '" . mysql_escape_string($og)."'";
 endif;

 

[dreamaz]

Tested it out and works great.

 

[BlindPete]

Just a thought but if you want to format your data, Excel converts an HTML table quite nicely too.

If you wanted to format your data, encapsulate it in an HTML table and change this to:
header('Content-Disposition: attachment; filename="exportedtable.xls"');

You'll find excel converts it seemlessly. Open Office also converts it.

If your intersted I'll post a more comeplete example.

-Pete

 

[jpadie]

i guess this would fit blindpete's bill as a replacement block of code:

while ($row=mysql_fetch_assoc($result)):
if (!isset($flag)):
      foreach ($row as $key=>$val):
          $dl .= "<td>".$key."</td>";
      endforeach;
      $dl = "<table><tr>$dl</tr>";
      $flag = true;
  endif;
  $tmp = "<tr><td>" . implode ("</td><td>", $row) . "</td></tr>";
  $dl .=$tmp;
endwhile;
$dl .= "</table>";

 

[BlindPete]

Yes!

It totally depends on what the customer ultimately needs. Sometimes the data is what is needed and csv fits that perfectly. I would only go the encapsulated table route if what you are producing is more of a report and not just raw data.

So you can format cell heights, widths, borders, colors, fonts, text styles etc. and make the data... pretty ;-)

It is just a question of what the client needs from the export.

-Pete