PHP / MYSQL -> CSV file

[ramsfield]

I am trying to create a comma seperated value (CSV) file based on a mysql query. I want a user to click on a link, then they would be presented with the pop-up open document / save as dialog box.

I have tried a few approaches but have not had any success.

Any help or direction would be appreciated

Here is one of the things I have tried...this only displays the result in the browser window.

<?
$filename = "test";
$ext = "txt"; // file extension
$mime_type = (PMA_USR_BROWSER_AGENT == 'IE' || PMA_USR_BROWSER_AGENT == 'OPERA')
? 'application/octetstream'
: 'application/octet-stream';
header('Content-Type: ' . $mime_type);
header('Content-Disposition: inline; filename="' . $filename . '.' . $ext . '"');
header("Content-Transfer-Encoding: binary");
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
print "Success";
?>
<?php 
$hostname_hr_jobs = "10.1.0.65";
$database_hr_jobs = "hr_jobs";
$username_hr_jobs = "user";
$password_hr_jobs = "password";
$hr_jobs = mysql_pconnect($hostname_hr_jobs, $username_hr_jobs, $password_hr_jobs) or trigger_error(mysql_error(),E_USER_ERROR); 

mysql_select_db($database_hr_jobs, $hr_jobs);
$query_export_file = "SELECT * FROM job_postings";
$export_file = mysql_query($query_export_file, $hr_jobs) or die(mysql_error());
$row_export_file = mysql_fetch_assoc($export_file);
$totalRows_export_file = mysql_num_rows($export_file);
print "<b>"."job_title"."</b><br>";
do {
print $row_export_file['job_title']."\n";
} while ($row_export_file = mysql_fetch_assoc($export_file));

?>

[AstroTeg]

I'm just guessing, but the content disposition may need tweaking. Here's what I use for my CSV export (its built to handle CSV and Excel files):

			header('Content-type: application/' . (($format == 'csv') ? 'octet-stream' : 'vnd.ms-excel'));
			header('Content-Disposition: attachment; filename="filename"');

Just a comment about this snippet of code:

$row_export_file = mysql_fetch_assoc($export_file);
$totalRows_export_file = mysql_num_rows($export_file);
print "<b>"."job_title"."</b><br>";
do {
print $row_export_file['job_title']."\n";
} while ($row_export_file = mysql_fetch_assoc($export_file)); 

You're using a do-while loop here. You might want to toss in some error checking to make sure mysql_fetch_assoc() was successful. Otherwise, it'll plow into your do-while loop and make a small mess.

You might try something like this:

while($row_export_file = mysql_fetch_assoc($export_file))
{
  print $row_export_file['job_title']."\n";
}  // end 

Here, if mysql_fetch_assoc() bombs (even if there's no records) then it just passes right through the while loop without errors. Just a thought...

[ramsfield]

AstroTeg - thanks for the info and the help. This works with one exception, some of my DB fields contain text with commas.

How can I make this so that if I have commas in my DB field, they do not screw up the download.

Is a different format more acceptable, such as tab delimated

Here is my current code...

<?
$filename = "hr_jobs.csv";
// file extension $ext = "csv"; 
$mime_type = (PMA_USR_BROWSER_AGENT == 'IE' || PMA_USR_BROWSER_AGENT == 'OPERA')
? 'application/octetstream'
: 'application/octet-stream';
header('Content-Type: ' . $mime_type);
header('Content-type: application/' . (($format == 'csv') ? 'octet-stream' : 'vnd.ms-excel')); 
header('Content-Disposition: attachment; filename="' . $filename . ' "');
//header('Content-Disposition: inline; filename="' . $filename . '.' . $ext . '"');
header("Content-Transfer-Encoding: binary");
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
?>
<?php 
$hostname_hr_jobs = "10.1.0.65";
$database_hr_jobs = "hr_jobs";
$username_hr_jobs = "user";
$password_hr_jobs = "password";
$hr_jobs = mysql_pconnect($hostname_hr_jobs, $username_hr_jobs, $password_hr_jobs) or trigger_error(mysql_error(),E_USER_ERROR); 

mysql_select_db($database_hr_jobs, $hr_jobs);

$query_export_file = "SELECT * FROM job_postings";
$export_file = mysql_query($query_export_file, $hr_jobs) or die(mysql_error());
//$row_export_file = mysql_fetch_assoc($export_file);
$totalRows_export_file = mysql_num_rows($export_file);

$fp = fopen("hr_jobs.csv", "w"); #open for write
print "job_title"."\n"; 
while($row_export_file = mysql_fetch_assoc($export_file)) 
{ 
  print $row_export_file['job_title']."\n"; 
}
/* no error checking
while ($row = mysql_fetch_row($export_file)) {
fwrite ($fp, join(',' , $row) . "\n");
} 
End no error checking*/

fclose ($fp);
?>

[AstroTeg]

You'll want to check out the specs on CSV files. You'll find that for text strings, they need to be enclosed in double quotes. But then what happens if you have a double quote in your text string? Yup, it gets a little messy, but its not too bad. I'd tell you how to do it, but its been a while and I don't want to give you incorrect information. But google will tell you...

You might also look out for carriage returns. Don't know if you'll encounter any. Some programs don't mind carriage returns (most Microsoft Office apps), but others may complain (like Act).

Also, watch out for Excel if you're using Excel for any of this.

Excel has the most botched up CSV import functionality I have ever seen.

The rules say if you have a text string wrapped in quotes, its to be taken literally. Example:

"00123"

You would expect Excel to import this as 00123 but instead imports it at 123. When is this a problem? Zip codes. This is a huge problem with zip codes. So I figure I warn you about that.

Beyond that, that's about all the hang ups you should expect.