Export php-mysql records into excel sheet

To export php-mysql records into excel sheet and download it follow these simple steps.

1. Make database connection.
2. Read the headers (fieldname of your table) and store in a variable.
3. Read records one by one and store it in another variable.
4. Set the file name to be downloaded.
5. Set appropriate http header types to write data on excel sheet and download it.

The code is given here.

 <?php
 ob_start();
 mysql_connect('localhost','username','password');
 mysql_select_db('dbname');
 $sql = "SELECT * from tablename";
 $res = mysql_query($sql) or die();
 $count = mysql_num_fields($res);

 // fetch table header from database
 $header = '';
 for ($i = 0; $i < $count; $i++){
    $header .= mysql_field_name($res, $i)."\t";
    }

 // fetch data each row, store on tabular row data
 while($row = mysql_fetch_row($res)){
   $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);
  }
 
 $name=date('d-m-y').'-list.xls';
 header("Content-type:application/vnd.ms-excel;name='excel'");
 header("Content-Disposition: attachment; filename=$name");
 header("Pragma: no-cache");
 header("Expires: 0");

 // Output data
 echo $header."\n\n".$data;
 ?>