Posted By

kidmizere on 01/09/11


Tagged

mysql php csv excel


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

sebikovacs


Export MYSQL to EXCEL / CSV via PHP


 / Published in: PHP
 

Modify includes to connect and select to provide correct result set

  1. <?php
  2. include 'config.php';
  3. include 'opendb.php';
  4.  
  5. $select = "SELECT * from table_name";
  6.  
  7. $export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );
  8.  
  9. $fields = mysql_num_fields ( $export );
  10.  
  11. for ( $i = 0; $i < $fields; $i++ )
  12. {
  13. $header .= mysql_field_name( $export , $i ) . "\t";
  14. }
  15.  
  16. while( $row = mysql_fetch_row( $export ) )
  17. {
  18. $line = '';
  19. foreach( $row as $value )
  20. {
  21. if ( ( !isset( $value ) ) || ( $value == "" ) )
  22. {
  23. $value = "\t";
  24. }
  25. else
  26. {
  27. $value = str_replace( '"' , '""' , $value );
  28. $value = '"' . $value . '"' . "\t";
  29. }
  30. $line .= $value;
  31. }
  32. $data .= trim( $line ) . "\n";
  33. }
  34. $data = str_replace( "\r" , "" , $data );
  35.  
  36. if ( $data == "" )
  37. {
  38. $data = "\n(0) Records Found!\n";
  39. }
  40.  
  41. header("Content-type: application/octet-stream");
  42. header("Content-Disposition: attachment; filename=your_file_name.xls");
  43. header("Pragma: no-cache");
  44. header("Expires: 0");
  45. print "$header\n$data";
  46.  
  47. ?>

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: sebikovacs on March 21, 2011

Hello, thanks for posting this, helped me out a lot. I just had to make two small changes in your script to run smooth in my environment (tested in WAMP on WinXP and MS Office 2003): 1. just after the includes I initialized the $header and the $data variables like $header = "" and $data = "" 2. on line 23 I had to put an empty space before the \t to force Excel leave the cell empty. so the value looks like this " \t"

Thanks again and good luck, Cheers

You need to login to post a comment.