Create and store xls files

[hiprakhar]

Hi,

 

I have a database and I need to generate xls file selecting only few columns. The following should happen on click of a trigger like button/link:

 

1) 6 columns from mysql database table are selected. The first row of xls is the field name which is NOT the same as mysql table column name (xls columns are explicitly defined)

2) This file is generated and saved inside filesystem on remote server.

3) Link to this file is displayed which can be downloaded from server.

 

Please help how to do this. I have the code for creating csv files but that doesnt save the file on server, just shoots it to browser to download.

 

function createCSV()
{
        include("dbconnect.php");	
$query = "SELECT `EmailAddress` ,
`Name` ,
`FirstName` ,
`LastName` 

FROM directtable;";
$rsSearchResults = mysql_query($query) or die(mysql_error());
$out = '';
$fields = mysql_list_fields($dbDatabase,'directtable');
$columns = mysql_num_fields($fields);

// Put the name of all fields
for ($i = 0; $i < $columns; $i++) 
{
	$l=mysql_field_name($fields, $i);
	$out .= '"'.$l.'",';
}
$out .="\n";

// Add all values in the table
while ($l = mysql_fetch_array($rsSearchResults)) 
{
	for ($i = 0; $i < $columns; $i++) 
	{
		$out .='"'.$l["$i"].'",';
	}
	$out .="\n";
}
// Output to browser with appropriate mime type, you choose 
header("Content-type: text/x-csv");
//header("Content-type: text/csv");
//header("Content-type: application/csv");
$now=date("d-m-Y,H:i:s", time());
header("Content-Disposition: attachment; filename=emails_".$now.".csv");
echo $out;	

}

[taquitosensei]

Write it to a file instead of ouputting the headers.

function createCSV()
{
$now=date("d-m-Y,H:i:s", time());
$fh=fopen("emails_".$now.".csv","a"); 
include("dbconnect.php");
$query = "SELECT `EmailAddress` ,
`Name` ,
`FirstName` ,
`LastName` 
FROM directtable;";
$rsSearchResults = mysql_query($query) or die(mysql_error());
$fields = mysql_list_fields($dbDatabase,'directtable');
$columns = mysql_num_fields($fields);
// Put the name of all fields
for ($i = 0; $i < $columns; $i++) 
{
$l=mysql_field_name($fields, $i);
fwrite($fh, '"'.$l.'",');
}
fwrite($fh,"\n"); 
// Add all values in the table
while ($l = mysql_fetch_array($rsSearchResults)) 
{
for ($i = 0; $i < $columns; $i++) 
{
fwrite($fh,'"'.$l["$i"].'",');
}
fwrite($fh,"\n");
}
// Output to browser with appropriate mime type, you choose 
//header("Content-type: text/x-csv");
//header("Content-type: text/csv");
//header("Content-type: application/csv");
//$now=date("d-m-Y,H:i:s", time());
//header("Content-Disposition: attachment; filename=emails_".$now.".csv");
//echo $out;
fclose($fh);
}