PHP Mysql to excel export

[renfley]

Hey guys im trying to download an excel sheet from the db and it keeps displaying the entire content on page and would like to just download but it aint working. 

$DB_Server = "localhost";
$DB_Username = "root";
$DB_Password = "";
$DB_DBName = "test";
$DB_TBLName = "tickets";
$filename = "MasterBackup";

//create MySQL connection
$sql = "Select * from $DB_TBLName";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error() . "<br>" . mysql_errno());
//execute query
$result = @mysql_query($sql, $Connect) or die("Couldn't execute query:<br>" . mysql_error() . "<br>" . mysql_errno());

//create query to select as data from your table
$select = "SELECT * FROM $DB_TBLName";

//run mysql query and then count number of fields
$export = mysql_query ( $select ) 
       or die ( "Sql error : " . mysql_error( ) );
$fields = mysql_num_fields ( $export );

//create csv header row, to contain table headers 
//with database field names
for ( $i = 0; $i < $fields; $i++ ) {
	$header .= mysql_field_name( $export , $i ) . ",";
}

//this is where most of the work is done. 
//Loop through the query results, and create 
//a row for each
while( $row = mysql_fetch_row( $export ) ) {
	$line = '';
	//for each field in the row
	foreach( $row as $value ) {
		//if null, create blank field
		if ( ( !isset( $value ) ) || ( $value == "" ) ){
			$value = ",";
		}
		//else, assign field value to our data
		else {
			$value = str_replace( '"' , '""' , $value );
			$value = '"' . $value . '"' . ",";
		}
		//add this field value to our row
		$line .= $value;
	}
	//trim whitespace from each row
	$data .= trim( $line ) . "\n";
}
//remove all carriage returns from the data
$data = str_replace( "\r" , "" , $data );


//create a file and send to browser for user to download
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$file_name.".csv");
print "$header\n$data";

Any help would be awesome

 

[kicken]

You should only have one Content-Disposition header, and it should be in the format of:

header('Content-disposition: attachment; filename='.$file_name.'.csv');

You should also look into using fputcsv, it'd simplify your CSV generation and ensure things are generated accurately.

 

 

On a different note, you should use text/csv for the content type, since that is what you are actually serving. In my experience the file is still downloaded and handled by excel with that mime type, and it accurately represents what the page serves.

[ginerjm]

Are you saying you want to create an excel sheet? By displaying comma-separated text on a page with those headers (of which I know nothing about)? Can one do that?

 

Have you looked closely at the data that you generated to ensure it is correctly formatted. Did you remove the trailing comma from the header row?

 

Looking to learn from this exercise of yours. Of course, if this is a sometimes-exercise, can't you just save the file on the server in an accessible folder and then use an ftp client to bring it to your pc and manually import into excel?

[renfley]

Thanks ive made the chage but issue is still happening.. no file is being downloaded. 

[hyster]

in the url is it showing as filename.php or filename.csv  ?

[renfley]

no it isnt showing any filename