Apr 9, 2014

Ketan Patel

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.

How to Export MySQL Data into CSV 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


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.  

Ketan Patel

About Ketan Patel -

I have developed a wide range of websites using CorePHP, Opencart, CakePHP and CodeIgniter including sites for startup companies and small businesses. Apart from my blogging life, I like to read Novels, Listening music and Net surfing.

Subscribe to this Blog via Email :