How to Export a My SQL Data to CSV or Excel Format

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

Script File 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.