Generate excel from SQL using PHP

[mana_panigrahi]

Hi All,
Can anybody share PHP code to generate excel file by querying MySQL database.

Thanks in advance..

[rupeshpradhan]

You could use PHPMyAdmin to save the output of your SQL queries directly as MS Excel files.

If you want to write a PHP script to do that, the simplest way is to make a '.csv' file. You want a tip on this, make a post

[chrishea]

It's actually quite easy to save the output as Excel (or Word or ...) but first you have to produce a table on the screen in the format that you want to see in Excel. You can go to my site and download the Desktop Write utility that will save it for you (link below):

Download Desktop Write

[mana_panigrahi]

Yes, I need a tip on same....If any code please share...

[avocado_juice]

you may try to use this code

<?php
{
require_once "prerequisite.php";
$sql2 = mysql_query("SELECT * FROM student_registration ORDER BY stu_ID");
$count   = mysql_num_fields($sql2);
$header = '';
$data = '';

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

while($row = mysql_fetch_assoc($sql2)) {
  $line = '';
  foreach($row as $key => $value) {

    if ($key === 'date_of_reg') {
      $value  = '"' . date('d/m/Y', $value) . '"';
    } else {
      $value  = '"' . $value . '"';
    }

    if ($line === '') {
      $line  = $value;
    } else {
      $line .= "\t" . $value;
    }
  }

  $data .= $line . "\n";
}

if ($data == "") 
{
    $data = "There is no record inside the table.";
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=student_excel.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo $header."\n".$data; 

}
?>