Solved

Save table as csv in PHP

Posted on 2007-03-20
34
689 Views
Last Modified: 2008-01-09
Why does this not prompt to download a file instead of just echoing to screen?
I have tried various header()
I tired print $csvoutput as well.

$csv_output = '"column 1","column2"';
$csv_output .= "\015\012";
$result = mysql_query("select * from results");

  while($row = mysql_fetch_array($result)) {
    $csv_output .= '"'.$row[firstname].'","'.$row[email].'"';
    $csv_output .= "\015\012";
  }

  //You cannot have the breaks in the same feed as the content.
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=yourfilename.csv");
header("Pragma: no-cache");
header("Expires: 0");
 echo $csv_output;
 // exit;
0
Comment
Question by:livegirllove
  • 16
  • 11
  • 7
34 Comments
 
LVL 19

Expert Comment

by:Michael701
ID: 18762008
try adding the length line of code, here's a snippet from code I've used.

     default:
          //Handle All Other Files
          $ContentType = 'application/octet-stream';
}
$strFileSize = filesize($strFilePath);

header("Content-Type: $ContentType);
header("Content-Length: $strFileSize");
header("Content-Disposition:attachment; filename=$strFileName");

echo $fileContents;
0
 
LVL 1

Author Comment

by:livegirllove
ID: 18762025
No Difference.  Just prints to screen.  IE7 and Firefox2.

$strFileSize = filesize($csv_output);
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=yourfilename.csv");
header("Pragma: no-cache");
header("Expires: 0");
header("Content-Length: $strFileSize");
 print $csv_output;
  exit;

0
 
LVL 19

Expert Comment

by:Michael701
ID: 18762053
use the string length since you have it in a local variable, sorry, i was reading data from a file.

$strFileSize = strlen($csv_output);
0
 
LVL 9

Expert Comment

by:richdiesal
ID: 18762064
I encountered problems getting IE to recognize a download while Firefox was parsing correctly.  These work for me to download Word DOCs, but should work for you too:

$strFileSize = filesize($csv_output);
header("Content-type: application/octet-stream");
header('Content-Disposition: attachment; filename="yourfilename.csv"');
header('Pragma: private');
header('Expires: 0');
header('Cache-control: private, must-revalidate');
header('Content-Transfer-Encoding: binary');
print $csv_output;
exit;
0
 
LVL 1

Author Comment

by:livegirllove
ID: 18762066
no change.
Is it possible that it being in a protected directory has something to do with it?  
0
 
LVL 1

Author Comment

by:livegirllove
ID: 18762068
oh wait didnt see the last post.
0
 
LVL 1

Author Comment

by:livegirllove
ID: 18762074
Well after the last post still no dialog or save.

$csv_output = '"column 1","column2"';
$csv_output .= "\015\012";
$result = mysql_query("select * from results");

  while($row = mysql_fetch_array($result)) {
    $csv_output .= '"'.$row[firstname].'","'.$row[email].'"';
    $csv_output .= "\015\012";
  }

  //You cannot have the breaks in the same feed as the content.
$strFileSize = strlen($csv_output);
header("Content-type: application/octet-stream");
header('Content-Disposition: attachment; filename="yourfilename.csv"');
header('Pragma: private');
header('Expires: 0');
header('Cache-control: private, must-revalidate');
header('Content-Transfer-Encoding: binary');
 print $csv_output;
  exit;
0
 
LVL 9

Expert Comment

by:richdiesal
ID: 18762082
Try changing your content-type line to:
header("Content-type: application/vnd.ms-excel");
0
 
LVL 9

Expert Comment

by:richdiesal
ID: 18762084
AND if that doesn't work, try
header("Content-type: application/force-download");
0
 
LVL 19

Expert Comment

by:Michael701
ID: 18762089
you missed the content length header

header("Content-Length: $strFileSize");
0
 
LVL 1

Author Comment

by:livegirllove
ID: 18762097
crazy.  Same problem.

Im going to try from a different computer in a different network just in case my isa server is blocking something.

$csv_output = '"column 1","column2"';
$csv_output .= "\015\012";
$result = mysql_query("select * from results");

  while($row = mysql_fetch_array($result)) {
    $csv_output .= '"'.$row[firstname].'","'.$row[email].'"';
    $csv_output .= "\015\012";
  }

  //You cannot have the breaks in the same feed as the content.
$strFileSize = strlen($csv_output);
header("Content-type: Content-type: application/force-download");
header('Content-Disposition: attachment; filename="yourfilename.csv"');
header('Pragma: private');
header('Expires: 0');
header('Cache-control: private, must-revalidate');
header('Content-Transfer-Encoding: binary');
header("Content-Length: $strFileSize");
 echo $csv_output;
  exit;
0
 
LVL 19

Expert Comment

by:Michael701
ID: 18762104
also, just because, try it without the extra quotes around the filename, and a filename without spaces.

header('Content-Disposition: attachment; filename=yourfilename.csv');
0
 
LVL 9

Expert Comment

by:richdiesal
ID: 18762106
Your question got me concerned enough - I started up Apache in development mode on my local machine, pasted your code, created a new CSV using "$csv_output = "1,2,3\n4,5,6\n7,8,9";, and tested it in both IE and Firefox - both worked!

Perhaps the CSV output is corrupt in some way?  Or headers are being blocked?
0
 
LVL 1

Author Comment

by:livegirllove
ID: 18762141
Below code returns 1,2,3 4,5,6 7,8,9

How would headers be blocked?  PHP is NOT in safe mode if that means anything.

$csv_output = "1,2,3\n4,5,6\n7,8,9";
$strFileSize = strlen($csv_output);
header("Content-type: Content-type: application/force-download");
header('Content-Disposition: attachment; filename=yourfilename.csv');
header('Pragma: private');
header('Expires: 0');
header('Cache-control: private, must-revalidate');
header('Content-Transfer-Encoding: binary');
header("Content-Length: $strFileSize");
 echo $csv_output;
  exit;
0
 
LVL 9

Expert Comment

by:richdiesal
ID: 18762149
What web browser are you testing this in?
0
 
LVL 1

Author Comment

by:livegirllove
ID: 18762154
IE7 and Firefox 2.0.0.3
0
 
LVL 9

Expert Comment

by:richdiesal
ID: 18762159
Same as me... now I'm just royally confused!

Let's try something else.

At the top of the file, enter:
header("Location: http://www.google.com");

If Google doesn't appear when you test the file, that means headers aren't being sent.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 19

Expert Comment

by:Michael701
ID: 18762161
ok, i posted that exact code on my go daddy hosted site and it prompts 'save as' dialog.

http://www.redboxcode.com/test.php

you try it, if still not prompting then it must be something in your local browser.

if it prompts, then it must be something in the php info settings.

michael
0
 
LVL 1

Author Comment

by:livegirllove
ID: 18762163
google does not appear.

Hmm so why arent headers being sent?
0
 
LVL 1

Author Comment

by:livegirllove
ID: 18762164
and your page prompts the #$@^$% dialog.

So what needs to change in the ini so i can ssh in and change it.
0
 
LVL 9

Expert Comment

by:richdiesal
ID: 18762187
Frankly, this is by no means a normal problem.  It sounds like PHP and your webserver aren't interfacing correctly.  From Googling around a bit, it seems like it can occur in Apache when PHP is configured as CGI instead of as a Module...  but that might or might not be relevant.  What webserver are you using?  Apache?
0
 
LVL 19

Accepted Solution

by:
Michael701 earned 250 total points
ID: 18762193
here's the data from the config setion of phpinfo(), i don't see anything about headers in the other sections.

PHP Version 4.3.11

System       Linux linhost110.mesa1.secureserver.net 2.4.21-27.ELsmp #1 SMP Wed Dec 1 21:59:02 EST 2004 i686
Build Date       Jan 11 2007 15:02:51
Configure Command       './configure' '--with-cgi' '--enable-fastcgi' '--with-config-file-path=/web/conf' '--with-gd' '--with-xml' '--with-gettext' '--with-zlib-dir=/usr/src/zlib' '--disable-posix' '--with-jpeg-dir=../jpeg-6b' '--enable-gd-native-ttf' '--enable-ftp' '--with-freetype-dir=/usr' '--with-freetype' '--with-sybase=/usr/src/freetds' '--with-curl=/usr/bin/curl' '--with-dom' '--enable-calendar' '--enable-soap' '--enable-bcmath' '--with-zip' '--with-openssl' '--with-mcrypt' '--with-mysql=/usr/local/mysql-5.0'
Server API       CGI/FastCGI
Virtual Directory Support       disabled
Configuration File (php.ini) Path       /web/conf/php.ini
PHP API       20020918
PHP Extension       20020429
Zend Extension       20021010
Debug Build       no
Thread Safety       disabled
Registered PHP Streams       php, http, ftp, https, ftps, compress.zlib
0
 
LVL 1

Author Comment

by:livegirllove
ID: 18762203
Apache/2.0.52 (CentOS)
PHP 4.3.9-3.22.3
plesk 8.1
0
 
LVL 1

Author Comment

by:livegirllove
ID: 18762207
'./configure' '--build=i686-redhat-linux-gnu' '--host=i686-redhat-linux-gnu' '--target=i386-redhat-linux-gnu' '--program-prefix=' '--prefix=/usr' '--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--includedir=/usr/include' '--libdir=/usr/lib' '--libexecdir=/usr/libexec' '--localstatedir=/var' '--sharedstatedir=/usr/com' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--cache-file=../config.cache' '--with-config-file-path=/etc' '--with-config-file-scan-dir=/etc/php.d' '--enable-force-cgi-redirect' '--disable-debug' '--enable-pic' '--disable-rpath' '--enable-inline-optimization' '--with-bz2' '--with-db4=/usr' '--with-curl' '--with-exec-dir=/usr/bin' '--with-freetype-dir=/usr' '--with-png-dir=/usr' '--with-gd=shared' '--enable-gd-native-ttf' '--without-gdbm' '--with-gettext' '--with-ncurses=shared' '--with-gmp' '--with-iconv' '--with-jpeg-dir=/usr' '--with-openssl' '--with-png' '--with-pspell' '--with-xml' '--with-expat-dir=/usr' '--with-dom=shared,/usr' '--with-dom-xslt=/usr' '--with-dom-exslt=/usr' '--with-xmlrpc=shared' '--with-pcre-regex=/usr' '--with-zlib' '--with-layout=GNU' '--enable-bcmath' '--enable-exif' '--enable-ftp' '--enable-magic-quotes' '--enable-sockets' '--enable-sysvsem' '--enable-sysvshm' '--enable-track-vars' '--enable-trans-sid' '--enable-yp' '--enable-wddx' '--with-pear=/usr/share/pear' '--with-imap=shared' '--with-imap-ssl' '--with-kerberos' '--with-ldap=shared' '--with-mysql=shared,/usr' '--with-pgsql=shared' '--with-snmp=shared,/usr' '--with-snmp=shared' '--enable-ucd-snmp-hack' '--with-unixODBC=shared,/usr' '--enable-memory-limit' '--enable-shmop' '--enable-calendar' '--enable-dbx' '--enable-dio' '--enable-mbstring=shared' '--enable-mbstr-enc-trans' '--enable-mbregex' '--with-mime-magic=/usr/share/file/magic.mime' '--with-apxs2=/usr/sbin/apxs'
0
 
LVL 9

Expert Comment

by:richdiesal
ID: 18762214
You're absolutely sure that no output is being sent to the page before the headers are?

And have you checked your Apache logs to make sure that it's not intercepting a malformed header somewhere?
0
 
LVL 1

Author Comment

by:livegirllove
ID: 18762225
no and no

ill check that.

0
 
LVL 9

Expert Comment

by:richdiesal
ID: 18762230
Headers cannot be sent after ANY output has been sent to the page itself.  Even if you echo ' '; before sending headers, those headers won't be sent.

As for the Apache logs, that should tell you if PHP is sending a header statement, Apache is receiving it, discarding it, and displaying the remainder of page how it wants.
0
 
LVL 19

Expert Comment

by:Michael701
ID: 18762234
in your phpinfo what do you have for

display_errors      On

it's possible errors are not being displayed and you might have output before the headers

0
 
LVL 1

Author Comment

by:livegirllove
ID: 18762238
PHP Warning: Cannot modify header information - headers already sent by (output started at /var/www/vhosts/soloenterprises.org/httpdocs/sampleagency/getcsv_clients.php:2) in /var/www/vhosts/soloenterprises.org/httpdocs/sampleagency/getcsv_clients.php on line 22

lots of them.  LOL

so lets start from scratch and try
0
 
LVL 1

Author Comment

by:livegirllove
ID: 18762240
<?
header("Location: http://www.google.com");
?>

works!!

so...

It must be something in my DB.php file.
0
 
LVL 9

Assisted Solution

by:richdiesal
richdiesal earned 250 total points
ID: 18762245
That should fix your problem then.  Headers MUST be sent before anything else.  It might be easier to link to an external download.php page with nothing but the CSV download.

And PSHEW!  That was close.  I was about to have you bust out a Telnet client.
0
 
LVL 1

Author Comment

by:livegirllove
ID: 18762252
i tinkering now and will update.  im on a roll now lol.  tx
0
 
LVL 1

Author Comment

by:livegirllove
ID: 18762257
works!!

Thanks for the help!!
0
 
LVL 9

Expert Comment

by:richdiesal
ID: 18762260
Glad to help!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now