Export mysql table to CSV using PHP - header issue

Question

Possible Duplicate:
Headers already sent by PHP

PHP script which is converting mysql table to .CSV file is giving errors like cannot modify header information and not writing the data to CSV file:

Below are the errors:

Warning: Cannot modify header information - headers already sent by (output started at /home/public_html/amgtst/export-tst.php:3) in /home/public_html/amgtst/export-tst.php on line 30

Warning: Cannot modify header information - headers already sent by (output started at /home/public_html/amgtst/export-tst.php:3) in /home/public_html/amgtst/export-tst.php on line 31

Warning: Cannot modify header information - headers already sent by (output started at /home/public_html/amgtst/export-tst.php:3) in /home/public_html/amgtst/export-tst.php on line 32

Warning: Cannot modify header information - headers already sent by (output started at /home/public_html/amgtst/export-tst.php:3) in /home/public_html/amgtst/export-tst.php on line 33

Area Area DC_No Area DC_No Product_type Area DC_No Product_type DC_date Area DC_No Product_type DC_date Ac_code Area DC_No Product_type DC_date Ac_code PO_No Area DC_No Product_type DC_date Ac_code PO_No PO_date Area DC_No Product_type DC_date Ac_code PO_No PO_date Inv_No Area DC_No Product_type DC_date Ac_code PO_No PO_date Inv_No CNo_Qty Area DC_No Product_type DC_date Ac_code PO_No PO_date Inv_No CNo_Qty Deleted Area DC_No Product_type DC_date Ac_code PO_No PO_date Inv_No CNo_Qty Deleted Vehicle_no Area DC_No Product_type DC_date Ac_code PO_No PO_date Inv_No CNo_Qty Deleted Vehicle_no "Jayanagar" "100" "Gas" "2012-11-30" "2" "2012-11-30" "11" "KA123-333" "Jayanagar" "104" "Gas" "2012-12-03" "3" "2012-12-03" "14" "Jayanagar" "101" "Gas" "2012-12-03" "3" "2012-12-03" "13" "Jayanagar" "105" "Gas" "2012-12-03" "3" "2012-12-03" "17" "Jayanagar" "106" "Gas" "2012-12-03" "3" "2012-12-03" "16" "Jayanagar" "107" "Gas" "2012-12-03" "3" "2012-12-03" "KA576" "Jayanagar" "108" "Gas" "2012-12-03" "2" "2012-12-03" "25" "KA01P213" "Jayanagar" "111" "Gas" "2012-12-04" "2" "2012-12-04" "27" "Jayanagar" "125" "Gas" "2012-12-04" "3" "2012-12-04" "12" "Jayanagar" "116" "Gas" "2012-12-06" "2" "2012-12-06" "Jayanagar" "117" "Gas" "2012-12-06" "2" "2012-12-06" "19" "Jayanagar" "118" "Gas" "2012-12-06" "2" "2012-12-06" "20" "Jayanagar" "119" "Gas" "2012-12-06" "2" "2012-12-06" "21" "Jayanagar" "130" "Gas" "2012-12-06" "3" "2012-12-06" "22" "KA-01-A4564" "Jayanagar" "131" "Gas" "2012-12-08" "2" "2012-12-08" "23" "KA01-23212" "Jayanagar" "132" "Gas" "2012-12-08" "2" "PIA-234234-ERES" "2012-12-08" "24" "KA-10-23232" "Jayanagar" "133" "Gas" "2012-12-08" "2" "verbal" "2012-12-08" "26" "ka91901212"

My php script:

    <?php
    include 'dbconnect.php';

    echo "Exporting file - process"."<br><br>";


        header("Content-type: application/csv");
        header("Content-Disposition: attachment; filename=download.csv");
        header("Pragma: no-cache");
        header("Expires: 0");



    $query = "SELECT * FROM DCHDR";

    $export = mysql_query ($query ) or die ( "Sql error : " . mysql_error( ) );

    $fields = mysql_num_fields ( $export );

    for ( $i = 0; $i < $fields; $i++ )
    {
        $header .= mysql_field_name( $export , $i ) . "\t";
        
        echo $header;
    }

    while( $row = mysql_fetch_row( $export ) )
    {
        $line = '';
        foreach( $row as $value )
        {                                            
            if ( ( !isset( $value ) ) || ( $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";

    exit();

    php?>

score 1 · Accepted Answer · answered Dec 12 '12 at 11:43

1

In this line

echo "Exporting file - process"."<br><br>";

you make some output, while a few lines later you try several header() calls.

Citing from the PHP docu:

Remember that header() must be called before any actual output is sent, either by normal HTML tags, blank lines in a file, or from PHP.

So do any header calls, before you create send some output to the client (with echo or similar)!

Aside from that you could use the native fputcsv() to create the CSV file. Maybe in combination with tmpfile(), if you want to send the output to the client.

answered Dec 12 '12 at 11:43

Sirko

72,589
19
149
183

I have removed the echo lines and now it is working, but header is getting repeated – user1114409 Dec 12 '12 at 11:51
1

@user1114409 You output `$header` on multiple occasions in your script. Remove, e.g., the `echo $header;` in your first loop. – Sirko Dec 12 '12 at 11:56

score 0 · Answer 2 · answered Dec 12 '12 at 11:41

0

use ob_start(); function on the top of the file and remove all echos expect the last one

answered Dec 12 '12 at 11:41

user1259132

320
2
3
11

score 0 · Answer 3 · answered Dec 12 '12 at 11:42

0

remove

echo "Exporting file - process"."<br><br>";

answered Dec 12 '12 at 11:42

user7282

5,106
9
41
72

score 0 · Answer 4 · edited May 23 '17 at 12:21

(edit: check out this question)

You need to use ob_start(); and then ob_end_flush() as it looks like you're trying to set the headers after something has already been sent to the browser.

Try this:

<?php

ob_start();

include 'dbconnect.php';

echo "Exporting file - process"."<br><br>";


    header("Content-type: application/csv");
    header("Content-Disposition: attachment; filename=download.csv");
    header("Pragma: no-cache");
    header("Expires: 0");



$query = "SELECT * FROM DCHDR";

$export = mysql_query ($query ) or die ( "Sql error : " . mysql_error( ) );

$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
    $header .= mysql_field_name( $export , $i ) . "\t";

    echo $header;
}

while( $row = mysql_fetch_row( $export ) )
{
    $line = '';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $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";

ob_end_flush();

exit();

php?>

Also you might want to use php's built in CSV function, fputcsv

score 0 · Answer 5 · answered Dec 12 '12 at 11:43

0

You cannot sent any data before headers information, so line

echo "Exporting file - process"."<br><br>";

has to be removed.

answered Dec 12 '12 at 11:43

MarcinJuraszek

124,003
15
196
263

Export mysql table to CSV using PHP - header issue

5 Answers5