Download table from mysql in .xls without Popup in Php

For download table from myslq into .xls form without asking have two basic steps:

  1. create connection to the database
  2. create index file to get the data from database, execute code and get desired output ( .xls file )

1.  connection to Database in connection.php

<?php $server="localhost"; 
$dbusername="root"; $dbPassword=""; 
$dbname="Database Name"; 
$connection_error= "your database is not connected";
if(!mysql_connect($server,$dbusername,$dbPassword) || !mysql_select_db($dbname)) { die($connection_error); }
?>

 

2. Then in index.php add this code

<?php // connection connection file to index.php 
include ("connection.php");
$setCounter = 0; $setData=""; 
$setExcelName = "download_excal_file";
// sql query which brings your table data
$setSql = "SELECT * FROM `tablename` WHERE 1";
// executing select query
$setRec = mysql_query($setSql);
// counting number of rows in the result of select query 
$setCounter = mysql_num_fields($setRec); $setMainHeader="";
// creating header for .xls file
for ($i = 0; $i &lt; $setCounter; $i++)
{
$setMainHeader .= mysql_field_name($setRec, $i)."\t"; 
}
$setMainHeader.="\n"; 
while($rec = mysql_fetch_row($setRec))
{ $rowLine = ''; 
foreach($rec as $value)
{
if(!isset($value) || $value == "")
{ 
$value = "\t"; 
}
else {
//It escape all the special charactor, quotes from the data. 
$value = strip_tags(str_replace('"', '""', $value));
$value = '"' . $value . '"' . "\t";
}
$rowLine .= $value;
}
$setData .= trim($rowLine)."\n";
}
$setData = str_replace("\r", "", $setData);
if ($setData == "") 
{ 
$setData = "\nno matching records found\n";
}
$setCounter = mysql_num_fields($setRec);
$myfile = fopen($setExcelName."_Reoprt.xls", "w") or die("Unable to open file!");
$txt = $setMainHeader. $setData;
fwrite($myfile, $txt);
?>

after adding this code in index file just load the file to the browser and check the root folder of your code you will get a xls file created with file name ending with “_Report.xls”

NOTE: You can give selected fields name if you want to get only some selected column instead of all the fields of table.