postgresql query result to csv prints current page's html code also

Question

I am trying to output the results of a PostgreSQL query to CSV format using PHP.

On the main page is a link that sends the SQL statements as a string to another function in another PHP class, which in turn takes the SQL and executes the query using pg_query() and return the result sets.

My problem is that when I open the CSV file, all the results of my query are there, but at the end of the file I see the HTML code from the page that sent the query.

I looked at several StackOverflow posts, but to no avail.

Here is my code:

Main class:

$o .= '<p>You can convert the result set to CSV format to be opened in Excel.</p>';

$link = array('op1' => 'PatternExport', 'op2' => 'outputToCSV', 'id' => $pattern_id, 'data' => $pattern_SQL);

$o .= '<a href="' . $this->oQS->buildEncryptedURL($link) . '">Download Query Results as CSV File</a>';

Receiving class:

function outputToCSV()
{ 
    $ptid = $this->oQS->getValue('id');
    $sql = $this->oQS->getValue('data');

    $href = $this->oQS->buildEncryptedURL(array('op1'=>'PatternManager', 'op2'=>'listPatterns'),'/aatsc/index.php');
    $result = pg_query($sql);

    // filename for download 
    $filename = "query_results_" . date('Ymd') . "_" . $ptid . ".csv"; 

    $output = fopen('php://temp/maxmemory:' . (12*1024*1024), 'rw+');
    foreach(pg_fetch_assoc($result,0) AS $field=>$value)
    {
        $output .= '' . $field . ',';
    }
    $output = rtrim($output,',') . "\n";


    for($i=0;$i<pg_num_rows($result);$i++)
    {
        foreach(pg_fetch_assoc($result,$i) AS $field=>$value) 
            $output .= '' . $value . ',';
        $output = rtrim($output,',') . "\n";
    }
    header("Content-Type: application/vnd.ms-excel;"); 
    header("Content-Disposition: attachment; filename=\"$filename\";");
    header("Pragma: no-cache");
    print($output);
    //$href = $this->oQS->buildEncryptedURL(array('op1'=>'PatternManager', 'op2'=>'listPatterns'),'/aatsc/index.php');
    //header("Location: $href");  
}

Could you tell me whether I am using the right approach to export query results to CSV, and what in my code is causing the whole HTML code to be streamed?

Thanks

And what happens after the outputToCSV() function call? If there's html code after that, it will end up in the file. In this case, insert exit(0); after the function call. — Daniel Vérité
– Daniel Vérité, Commented May 23, 2013 at 19:54

Ryan M · Accepted Answer · 2024-05-10 22:40:07Z

1

You're generating $output twice, if you remove the foreach loop & fopen line, it should work.

edited May 10, 2024 at 22:40

Ryan M♦

20.6k35 gold badges75 silver badges85 bronze badges

answered May 23, 2013 at 15:19

user1977661

2631 gold badge3 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hd1 · Accepted Answer · 2013-05-23 15:16:06Z

1

You merely just need to issue the SQL COPY statement as follows:

COPY (select * from tbl) to stdout with csv header

resulting in:

col1,col2,col3
2013-05-22 07:28:59.732,192.168.1.67,3

answered May 23, 2013 at 15:16

hd1

34.9k5 gold badges83 silver badges95 bronze badges

1 Comment

Daniel Vérité Over a year ago

That would work with the psql program but not with php. There is pg_copy_to in php but it doesn't do CSV so not really useful in the context of this question

Collectives™ on Stack Overflow

postgresql query result to csv prints current page's html code also

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related