PHP code to write to a CSV file from MySQL query

By: Jamie  

Utility function to output a mysql query to csv with the option to write to file or send back to the browser as a csv attachment.

<?php
    function query_to_csv($db_conn, $query, $filename, $attachment = false, $headers = true) {
        
        if($attachment) {
            // send response headers to the browser
            header( 'Content-Type: text/csv' );
            header( 'Content-Disposition: attachment;filename='.$filename);
            $fp = fopen('php://output', 'w');
        } else {
            $fp = fopen($filename, 'w');
        }
        
        $result = mysql_query($query, $db_conn) or die( mysql_error( $db_conn ) );
        
        if($headers) {
            // output header row (if at least one row exists)
            $row = mysql_fetch_assoc($result);
            if($row) {
                fputcsv($fp, array_keys($row));
                // reset pointer back to beginning
                mysql_data_seek($result, 0);
            }
        }
        
        while($row = mysql_fetch_assoc($result)) {
            fputcsv($fp, $row);
        }
        
        fclose($fp);
    }

    // Using the function
    $sql = "SELECT * FROM table";
    // $db_conn should be a valid db handle

    // output as an attachment
    query_to_csv($db_conn, $sql, "test.csv", true);

    // output to file system
    query_to_csv($db_conn, $sql, "test.csv", false);
?>



Archived Comments

1. Ohyeahohyea
View Tutorial          By: Fre at 2017-01-25 11:37:08


Most Viewed Articles (in PHP )

The Object (compound) Type in PHP

PHP ./configure RESULTING IN [email protected]_2_2_3_... AND UNRESOLVED REFERENCES WITH ORACLE OCI8

Convert IP address to integer and back to IP address in PHP

PHP 5.1.4 INSTALLATION on Solaris 9 (Sparc)

Building PHP 5.x with Apache2 on SuSE Professional 9.1/9.2

Installing PHP 5.x with Apache 2.x on HP UX 11i and configuring PHP 5.x with Oracle 9i

Cannot load /usr/local/apache/libexec/libphp4.so into server: ld.so.1:......

Setting up PHP in Windows 2003 Server IIS7, and WinXP 64

error: "Service Unavailable" after installing PHP to a Windows XP x64 Pro

Running different websites on different versions of PHP in Windows 2003 & IIS6 platform

Function to return number of digits of an integer in PHP

Function to sort array by elements and count of element in PHP

Function to force strict boolean values in PHP

Function to convert strings to strict booleans in PHP

Floating point precision in PHP

Latest Articles (in PHP)

Comment on this tutorial