Exporting MySql Table into Excel Format

[Bile]

--Hi DaniWeb.
I have been doying bulk data Uploads from Excel Sheet to MySql data base and hence thought that I may need the reverse of this process and so Export MySql Tables into Excel Format for Backup purposes (I also will be happy for a better suggestion for a better data backup solution if is any from this like saving them in .sql readable format and ready for upload Programatically as the way is there if I go to Php Myadmin).
For now I just wanted to have something of the sort as I alredy have a mechanism for Upload with Excel.
I serached throught the Internet and come accross this following code in a Mysql extension:

<?php
/***** DATABASE CONNECTIONS *****/
$DB_Server = "localhost"; 
$DB_Username = "username"; 
$DB_Password = "password"; 
$DB_DBName = "Databasename";
$DB_TBLName = "Tablename"; 
/* THE FOLLOWING WILL BE THE NAME FOR YOUR EXCEL FILE */
$MySQL_Excel = "Export_Excel_".date("Y-m-d").".xls";

/***** DO NOT EDIT BELOW LINES *****/

$Sql = "Select * from ".$DB_TBLName."";
$Connector = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Failed to connect to MySQL:<br />" . mysql_error() . "<br />" . mysql_errno());

$Db = @mysql_select_db($DB_DBName, $Connector) or die("Failed to select database:<br />" . mysql_error(). "<br />" . mysql_errno());

$Result = @mysql_query($Sql,$Connector) or die("Failed to execute query:<br />" . mysql_error(). "<br />" . mysql_errno());

/* HEADER INFORMATION SETTINGS FOR AN EXCEL FILE */
header("Content-Type: application/xls");
header("Content-Disposition: attachment; filename=".$MySQL_Excel."");
header("Pragma: no-cache");
header("Expires: 0");

/***** START OF FORMATTING FOR EXCEL *****/

/* DEFINE SEPARATOR (DEFINES COLUMNS IN EXCEL &AMP; TABS IN WORD) */
$Separator = "\t"; 

/* START OF PRINTING COLUMN NAMES AS NAMES OF MYSQL FIELDS */
for ($iColumns = 0; $iColumns<mysql_num_fields($Result); $iColumns++) 
{
  echo mysql_field_name($Result, $iColumns) . "\t";
}
print("\n");
/* END OF PRINTING COLUMN NAMES */

/* START WHILE LOOP TO GET DATA */
while($Row = mysql_fetch_row($Result))
{
  $Schema_Isertor = "";

  for($iRow=0; $iRow<mysql_num_fields($Result); $iRow++)
  {
    if(!isset($Row[$iRow])) 
    {
      $Schema_Isertor .= "NULL".$Separator;
    }
    elseif ($Row[$iRow] != "") 
    {
      $Schema_Isertor .= $Row[$iRow]."".$Separator;
    }
    else 
    {
      $Schema_Isertor .= "".$Separator;
    }
  }
  $Schema_Isertor = str_replace($Separator."$", "", $Schema_Isertor);
  $Schema_Isertor = preg_replace("/\r\n|\n\r|\n|\r/", " ", $Schema_Isertor);
  $Schema_Isertor .= "\t";
  print(trim($Schema_Isertor));
  print "\n";
}
?>

It works fine in its own way of old extension but My Server doesnt as its an upgrade for the old extension.
And so I started trying changing it to suit with the OOP style for MySqli,y changing up some lines it somehow worked but NOT so perfectly as I wanted as I cant get the names of My MySql tables Column names as it is in the code I got.
Here is My edited code:

<?php

    function Database_Connect()
        {
            $Host="localhost";
            $Username="username";
            $Password="password";
            $dbName="Databasename";
            $Error_Message="";

            @$Connector=new mysqli($Host,$Username,$Password,$dbName);

            if(mysqli_connect_errno())
            {
            $Error_Message="<br/><br/><div class='alert alert-danger'><i class='icon-remove-circle'></i>Sorry System '".$dbName."' Database Connection Failed to Initiate on this: ".$Host." Server, Username: ".$Username.".Please try again Later</div>";
            die($Error_Message);
            exit();
            }
            else
            {
            return $Connector;
            }

        }

    function MySql_Excel_Exporter()
        {
        $Excel_File = "Export_Excel_".date("Y-m-d").".xls";
        $Separator="\t";

        /* Header info settings */
        header("Content-Type: application/xls");
        header("Content-Disposition: attachment; filename=".$Excel_File."");
        header("Pragma: no-cache");
        header("Expires: 0");

        $MySqli=Database_Connect();

        $Sql="SELECT Column1,Column2,Column3 FROM MyTable";
        $Query=$MySqli->prepare($Sql);
        $Query->execute();
        $Query->bind_result($Column1,$Column2,$Column3);


        /* THE FOLLOWING LINES DOESNT WORK FOR ME AND I CANT GET THE file_name() 
        TO WORK PLAYED WITH ITS ALTERNATIVE IN MYSQLI THAT GIVES AN ARRAY OF INFORMATION 
        FOR THE FIRLD BUT STILL COULDNT GET IT TO WORK AND SO THE COLUMN NAMES OF MY TABLE 
        WONT SHOW UP SO I NEED HELP HERE
        */

        /* Start of printing column names as names of MySQL fields /
        for ($i = 0; $i<$MySqli->field_count; $i++) 
        {
          echo $Query->field_name()."\t";

        }
        print("\n");
        /* End of printing column names */



        while($Fetch=$Query->fetch())
            {           
              $Schema_Insert = "";
              /* HERE I ACCEPT IF THERE IS A BETTER WAY OF JUST GETTING THE VALUES IN A FORM OF DYNAMIC WAY
              OF REFFERING COLUMS AS IN THE ORIGINAL CODE JUST USED LOOP TO GET COLUMNS,
              BUT AS I FDIDNT KNOW HOW TO DO THAT AND SO I USED WHAT I KNOW THE bind_result()
              AND JUST ASSIGN THEM TO $Schema_Insert FOR EXCEL FORMATING
              */
              $Schema_Insert =$Column1."".$Separator."".$Column2."".$Separator."".$Column3."".$Separator."";
              $Schema_Insert = str_replace($Separator."$", "", $Schema_Insert);
              $Schema_Insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $Schema_Insert);
              $Schema_Insert .= "\t";
              print(trim($Schema_Insert));
              print "\n";

            }
        $Query->close();
        }

        MySql_Excel_Exporter();
?>

Thats all,and thanks in advance.

[paul-lucas]

I'm applying now this javascript. I hope this one is right for your case.

<button class="excel-export" id="btnExport">Export to Excel</button>
<script type="text/javascript">
$("#btnExport").click(function(e) {
window.open('data:application/vnd.ms-excel,' + 
encodeURIComponent($('#dvData').html()));
e.preventDefault();
});
</script>
<div id="dvData">  
    <table>
    </table>
</div>

[Bile]

--Thanks Paul but what I can see at glance is that Your JS code converts html table into excel right?
But what I needed is to directly read data and export them from within MySql trough a Query and so get its Fields names form MySql Table and all its records to Excel file.
Did I make Myself clear there?
And Please just copy and paste the codes writen in an old MySql Extension I provided and run them against Your Database table U'll get a Picture of what I want and Try the Modified One I provided in MySqli Extension so that You can See the difference through the MySqli Extension I can read the Table Fileds Names and Show them in My Excel File.
And form there please help Me how to go through that,thanks for Your reply.

[mirudu]

I need the code for column and row span table convert into excel formate

[Ray Paseur]

This is an old question, but there is a straightforward solution You can fetch the rows from the query results set and write them to an intermediate file with PHP fputcvsv(). Almost all Excel installations automatically associate the .CSV file sufix with Excel. Just fire up Excel and open the CSV file.

That said, these old code examples need to be updated to use a more modern MySQL connection. An article here describes the process.
https://www.experts-exchange.com/articles/11177/Why-PHP-Removed-Support-for-the-MySQL-API.html

[juniperoites]

for more infomation you can visit this https://www.max2colors.com/blog/267/export-mysqli-to-excel-in-php-with-code