Programming Tutorials

PHP code to write to a CSV file from MySQL query

By: Jamie in PHP Tutorials on 2014-10-06  

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);
?>





Add Comment

* Required information
1000

Comments

No comments yet. Be the first!

Most Viewed Articles (in PHP )

PHP code to write to a CSV file from MySQL query

Different versions of PHP - History and evolution of PHP

PHP code to import from CSV file to MySQL

Send push notifications using Expo tokens in PHP

Encrypting files using GnuPG (GPG) via PHP

PHP Warning: Unknown(): Unable to load dynamic library '/usr/local/php4/lib/php/extensions/no-debug ......

A Basic Example using PHP in AWS (Amazon Web Services)

Decrypting files using GnuPG (GPG) via PHP

Parent: child process exited with status 3221225477 -- Restarting

Error: Length parameter must be greater than 0

Password must include both numeric and alphabetic characters - Magento

Convert a hex string into a 32-bit IEEE 754 float number in PHP

Convert XML to CSV in PHP

PHP file upload prompts authentication for anonymous users

Count occurrences of a character in a String in PHP

Latest Articles (in PHP)