how to export mysql database to excel (PHP)

Question

how can I export my mysql database to XLS with PHP?

Answer 1

<?php

function export_excel_csv()

{

    $conn = mysql_connect("localhost","root","");

    $db = mysql_select_db("database",$conn);

    

    $sql = "SELECT * FROM table";

    $rec = mysql_query($sql) or die (mysql_error());

    

    $num_fields = mysql_num_fields($rec);

    

    for($i = 0; $i < $num_fields; $i++ )

    {

        $header .= mysql_field_name($rec,$i)."\t";

    }

    

    while($row = mysql_fetch_row($rec))

    {

        $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 = "\n No Record Found!\n";                        

    }

    

    header("Content-type: application/octet-stream");

    header("Content-Disposition: attachment; filename=reports.xls");

    header("Pragma: no-cache");

    header("Expires: 0");

echo chr(255).chr(254).iconv("UTF-8", "UTF-16LE//IGNORE", $data); //special charachter (turkish vs..)

    print "$header\n$data";

}

?>