How to Export MySQL Data into CSV or Excel Format

Writing this post after so many days due to busy schedule. A popular post on "How to Import CSV file Data into Mysql Using PHP" has already been written.

Now we'll learn totally opposite procedure of that tutorial.In this tutorial you will see how to export MySQL Data into CSV or Excel Format.Its just a single script and very easy to understand.

Logic of Script

• Establish Connection
• Select your Database
• Fetching Data from the table
• Get total no. of the fields using mysql_num_fields
• Get the names of the fields through the loop using mysql_field_name
• The header() function sends a raw HTTP header to a client.To know more about the options please refer below links

 http://www.php.net/manual/en/function.header.php

 http://www.w3schools.com/Php/func_http_header.asp

index.php

<?php

//database connection details
$connect = mysql_connect('localhost','root','');

//your database name
mysql_select_db('test',$connect);

//selecting records from the table
$query = "SELECT * FROM csvtbl limit 50";
$header = '';
$data ='';

$export = mysql_query ($query ) or die ( "Sql error : " . mysql_error( ) );
$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
    $header .= mysql_field_name( $export , $i ) . "\t";
}

while( $row = mysql_fetch_row( $export ) )
{
    $line = '';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $value == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
}
$data = str_replace( "\r" , "" , $data );

if ( $data == "" )
{
    $data = "\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$data";

?>

Thats it..!! and so simple tutorial ends here :) 

I hope you will enjoy it and find it easy to understand and make use of it. For any issues or Queries please just left comments.