How to Export MySQL Data in CSV/Excel File using PHP/MySQL

This tutorial is a continuation of our last topic called “How to Import CSV/Excel File into MySQL Database using PHP”. But this time, we will be focusing on how to Export MySQL Data into Excel file. To do this application open our last project called “excel”. At this time, we will modify the index file. To do this, open the “index.php” and add the following code above the table tag. And here’s the code:
  1. <form action="export.php" method="post" name="export_excel">
  2.  
  3. <div class="control-group">
  4. <div class="controls">
  5. <button type="submit" id="export" name="export" class="btn btn-primary button-loading" data-loading-text="Loading...">Export MySQL Data to CSV/Excel File</button>
  6. </div>
  7. </div>
  8. </form>
The code above will simply add a single button wherein, if this button is clicked it will automatically export the MySQL Database into Excel file. And here’s how it looks like. x1 But before the export will be successful,l we need to create another PHP file called “export.php” and here’s the code of this file.
  1. <?php
  2.  
  3.  
  4. include 'db.php';
  5. $SQL = "SELECT * from subject";
  6. $header = '';
  7. $result ='';
  8. $exportData = mysql_query ($SQL ) or die ( "Sql error : " . mysql_error( ) );
  9.  
  10. $fields = mysql_num_fields ( $exportData );
  11.  
  12. for ( $i = 0; $i < $fields; $i++ )
  13. {
  14. $header .= mysql_field_name( $exportData , $i ) . "\t";
  15. }
  16.  
  17. while( $row = mysql_fetch_row( $exportData ) )
  18. {
  19. $line = '';
  20. foreach( $row as $value )
  21. {
  22. if ( ( !isset( $value ) ) || ( $value == "" ) )
  23. {
  24. $value = "\t";
  25. }
  26. else
  27. {
  28. $value = str_replace( '"' , '""' , $value );
  29. $value = '"' . $value . '"' . "\t";
  30. }
  31. $line .= $value;
  32. }
  33. $result .= trim( $line ) . "\n";
  34. }
  35. $result = str_replace( "\r" , "" , $result );
  36.  
  37. if ( $result == "" )
  38. {
  39. $result = "\nNo Record(s) Found!\n";
  40. }
  41.  
  42. header("Content-type: application/octet-stream");
  43. header("Content-Disposition: attachment; filename=export.xls");
  44. header("Pragma: no-cache");
  45. header("Expires: 0");
  46. print "$header\n$result";
  47.  
  48. ?>
At this time, we can test the code above. And here’s the steps to do this. Step 1. Run the application and it should be observed that the “Export MySQL Data to CSV/Excel File” button is available. Like as shown below. x1
Step 2. Click the ”Export MySQL Data to CSV/Excel File” button. x2
Step 3. You can observe it below your browser looks like as shown below. x3
Step 4. Open the file and it looks the same from your MySQL Database file. x4 We’re done. And you can modify the codes that fit your requirements. If you want to see some new Source Code or Application and Tutorials Just click here.

Comments

Submitted bynaemz (not verified)on Tue, 04/19/2016 - 08:37

how can i remove header from excel from inserting the data of the header into the excel table database...
Submitted byBhavendra (not verified)on Wed, 09/28/2016 - 15:44

when i use this codes for my website it download all the page content including table data
but i export only table data.

Submitted byMykology (not verified)on Tue, 03/07/2017 - 06:44

Thanks for your code, It is really helpful.
Submitted bycolor (not verified)on Sat, 08/05/2017 - 11:54

I can not export the language "utf-8"

Add new comment