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

Submitted by joken on Wednesday, April 23, 2014 - 10:16.

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:

<form action="export.php" method="post" name="export_excel">
 
                        <div class="control-group">
                                <div class="controls">
                                        <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>
                                </div>
                        </div>
                </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.

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.

<?php
 
 
include 'db.php';
$SQL = "SELECT  * from subject";
$header = '';
$result ='';
$exportData = mysql_query ($SQL ) or die ( "Sql error : " . mysql_error( ) );
 
$fields = mysql_num_fields ( $exportData );
 
for ( $i = 0; $i < $fields; $i++ )
{
    $header .= mysql_field_name( $exportData , $i ) . "\t";
}
 
while( $row = mysql_fetch_row( $exportData ) )
{
    $line = '';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $value == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $result .= trim( $line ) . "\n";
}
$result = str_replace( "\r" , "" , $result );
 
if ( $result == "" )
{
    $result = "\nNo Record(s) Found!\n";                        
}
 
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=export.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$result";
 
?>

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.

Step 2. Click the ”Export MySQL Data to CSV/Excel File” button.

Step 3. You can observe it below your browser looks like as shown below.

Step 4. Open the file and it looks the same from your MySQL Database file.

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.