How to export MySql data results output to MS Excel file

Need to have link on the output results page to export/save data to a file

[hugotobi]

How do I add a link at the bottom [or top, for that matter] of my data output page so that a user may export/save the results to a MS Excel [CSV] file?

[Birdman]

Hello,

Here is a basic example of outputting a csv file. Just use the basic structure and work your data in using a loop or something similar. Don't forget the newlines(\n).

<?php
$filename = 'whatever.csv';

$csv_output = ' "ID", "NAME", "EMAIL" \n';

$csv_output .= ' "1", "John Doe", "[e-mail]" \n';

$csv_output .= ' "2", "Jane Doe", "[e-mail]" \n';

header("Content-type: application/x-msexcel");
header("Content-disposition: attachment; filename=".$filename);
header("Pragma: no-cache");
header("Expires: 0");

print $csv_output;
exit;
?>

[hugotobi]

hmmm. maybe I was not clear as to what I am trying to achieve. Data from MySQL database is searched/sorted via a PHP script and the data is then output as a tabulated page. I would like for my customers/users to then be able to "download" or "save" the resulting data to a CSV file. when I inserted your script expample [with necessary modifications] to my script, I get various errors depending on where in the script I insert this code. Mostly that error is similar to:

"Warning: Cannot modify header information - headers already sent by (output started at /home3/user/my-domain/my_directory/my_file.php:7) in /.......php on line 76"

[Birdman]

You should probably put the cvs script in it's own file. Then, use a form with hidden variables or a query string to tell the script what data to output.

<form action="csv.php" method="post">
<input type="hidden" name="id" value="<?=$idVariable?>" />
<input type="submit" value="Download" />
</form>

Or...

<a href="csv.php?id=<?=$idVariable?>">Download</a>

Use one of the above methods in your existing page then, in the csv script, build your mysql_query() with the variable, $_POST['id'] or $_GET['id'].

Be sure your variables are safe [us4.php.net] before sending them to mysql.

[hugotobi]

Birdman,
could not figure out the above.
But I did find a script at a php site and it works... somewhat. The script sends the data to an Excel file for download, but for some reason the file NAME each time comes up as a random file name with out any .xls extension! How do I fix this problem? Appreciate all help. Here is the script:

-------------------
<?php
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=ajareport.xls>");
header("Pragma: no-cache");
header("Expires: 0");
include("dbconnect.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die("Unable to select database");
$select = "SELECT * FROM aja_myfile";
$export = mysql_query($select);
$count = mysql_num_fields($export);
for ($i = 0; $i < $count; $i++) {
$header .= mysql_field_name($export, $i)."\t";
}
while($row = mysql_fetch_row($export)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r", "", $data);
if ($data == "") {
$data = "\n(0) Records Found!\n";
}
print "$header\n$data";
?>
---------------------

[hugotobi]

AH! Found the error and it works great now.

ther error was, that

.... filename = ordersrpt.xls>"

should read

.... filename = ordersrpt.xls"

it had a ">" by mistake!