-2

I am using this code to fetch my data from DB and download xls file but it not writing my actual data into the sheet. I think it doesn't work. Should I add something more to my code?

 <?php 
include 'functions.php';
 ob_start();

?>
<?php
if($_POST['types'] == 'name') {  

    $query = "SELECT * FROM visitor_detail WHERE name='".$_POST['filter']."' ORDER BY id DESC ";  
}  
elseif($_POST['types'] == 'mobile') {  

    $query = "SELECT * FROM visitor_detail WHERE mobile='".$_POST['filter']."' ORDER BY id DESC ";  
} 
elseif($_POST['types'] == 'OccasionType') {  

    $query = "SELECT * FROM visitor_detail WHERE OccasionType='".$_POST['filter']."' ORDER BY id DESC ";  
} 

elseif($_POST['types'] == 'InquiryDate') {  
    $query="SELECT * FROM visitor_detail WHERE TodayDate between '".$_POST['From']."' and '".$_POST['TO']."' ORDER BY id DESC " ;  
}

 elseif($_REQUEST['types'] == 'OccasionDate') {  
    $query="SELECT * FROM visitor_detail WHERE date between '".$_POST['From']."' and '".$_POST['To']."' ORDER BY id DESC ";  
                            }
else {  
    $query = "SELECT * FROM visitor_detail ORDER BY id DESC ";  
    } 

$result = mysql_query($query); 

$sep = "\t"; //tabbed character 
$fp = fopen('database.xls', "w"); 
$schema_insert = ""; 
$schema_insert_rows = ""; 
//start of printing column names as names of MySQL fields

//start of adding column names as names of MySQL fields 
for ($i = 1; $i < mysql_num_fields($result); $i++) 
{ 
$schema_insert_rows.=mysql_field_name($result,$i) . "\t"; 
} 
$schema_insert_rows.="\n"; 
echo $schema_insert_rows; 
fwrite($fp, $schema_insert_rows); 
//end of adding column names

while($row = mysql_fetch_row($result))
{
$schema_insert = ""; 
for($j=1; $j<mysql_num_fields($result);$j++) 
{ 
if(!isset($row[$j])) 
$schema_insert .= "NULL".$sep; 
elseif ($row[$j] != "") 
$schema_insert .= strip_tags("$row[$j]").$sep;
else 
$schema_insert .= "".$sep; 
} 
$schema_insert = str_replace($sep."$", "", $schema_insert); 

//this corrects output in excel when table fields contain \n or \r 
//these two characters are now replaced with a space 

$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert); 
$schema_insert .= "\n"; 
//$schema_insert = (trim($schema_insert)); 
//print $schema_insert .= "\n"; 
//print "\n";

fwrite($fp,$schema_insert); }

fclose($fp);

header('Content-Type: application/xls');
header('Content-Disposition: attachment; filename=my.xls');
header('Pragma: no-cache');

?>
Lundin
  • 195,001
  • 40
  • 254
  • 396
Meet Vora
  • 1
  • 1
  • 5
  • You're writing to a file, but not doing anything with that file. Simply writing a file on the server doesn't automagically display that file to the client browser – Mark Baker Mar 15 '13 at 10:11
  • @MarK Baker then what should i do to fetch data...in to the correct formate??? – Meet Vora Mar 15 '13 at 10:15
  • Well an obvious answer would be to readfile() your 'database.xls' file after you've sent the headers; but there's a flaw here if two clients both request a download at the same time – Mark Baker Mar 15 '13 at 10:17
  • Check this one: [how can i write data into an excel using php](http://stackoverflow.com/questions/3968973/how-can-i-write-data-into-an-excel-using-php) – Martina Mar 15 '13 at 10:19
  • Either use a tempfile that's unique to each request - http://php.net/manual/en/function.tempnam.php may help - so that each request isn't overwriting the other's data – Mark Baker Mar 15 '13 at 10:19
  • 1
    Or why use a file in the first place? Why not simply send each line of the output directly to the client's browser (send headers first); especially as you're not using fputcsv() or anything like that which would make your life easier... and even if you were, you could write to php://output rather than a disk file – Mark Baker Mar 15 '13 at 10:21
  • You also need to fclose($fp); after first fwrite as well. – Dead Man Mar 15 '13 at 10:27
  • @Mark Baker..yes you were right...i add readfile() and its working correctly – Meet Vora Mar 15 '13 at 10:33

2 Answers2

1

Two misconceptions:

  1. Perhaps the feature is called "Download an Excel file" but that doesn't imply that you have to create a real file.

  2. PHP does not magically send to the browser all the files you create server-side.

Get rid of all the file related code and simply change this:

fwrite($fp,$schema_insert);

... into this:

echo $schema_insert;

Also, move these lines before the first echo:

header('Content-Type: application/xls');
header('Content-Disposition: attachment; filename=my.xls');
header('Pragma: no-cache');

Edit: Despite the several mentions to XLS format, code in question basically creates a CSV file and sends it with fake HTTP headers to trick Excel into opening it. As Mark points out, PHP has a builtin function to create CSV files so you don't have to write the code yourself. This function expects a file pointer but, again, there's no need to actually create a file: you can call fopen() with php://output as file name and everything will be sent to the browser automatically.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
  • And don't forget it's worth defining php://output as the filename rather than database.xls, for those cases where multiple requests are being made concurrently – Mark Baker Mar 15 '13 at 10:45
  • @MarkBaker - I think you misread my answer. What I basically say is that there doesn't need to be a file at all. – Álvaro González Mar 15 '13 at 10:46
  • 2
    True, my mistake - although using a filename of php://output can allow OP to use fputcsv() instead of "rolling his own" – Mark Baker Mar 15 '13 at 10:49
0

Maybe the server can not allow you write the file. You can only query the data from database and format it to tables, and use header function to download it.

formatcc
  • 1
  • Can you bother providing download code along with this story? – Dead Man Mar 15 '13 at 10:21
  • You can use 'echo' function to form a table, and after this, use `code`header('Cache-Control: no-cache, must-revalidate'); header('Content-type: application/vnd.ms-excel'); header('Content-Disposition: filename=test.xls'); – formatcc Mar 15 '13 at 10:25
  • no actually it allows me to write in to the file ...because firsr fwrite() works ..thats why i am getting column name..but the second fwrite() not working actually...i dnt knw why – Meet Vora Mar 15 '13 at 10:26
  • @DeadMan Thank you, i am a fresh. – formatcc Mar 15 '13 at 10:31