How to Create and Download CSV File Using PHP

In this quick article, I am going to show you how to create and download a CSV file using PHP scripts. One of my previous blog called “How to Read / Write CSV File using PHP” also very helpful to you.

As you may know, CSV stands for Comma Separated Values and it is one of the most popular methods for transferring tabular data (i.e, spreadsheet) between one system to another, Also most of the applications want to export data as a CSV file.

For instance, we have a database table like below,

CREATE TABLE user_details (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50)
)

Thus we can get the table data by using the following PHP script,

<?php
$conn = new mysqli('localhost', 'root', 'welcome', 'my_database');

// check connection
if ($conn->connect_error) {
    die('Connection failed: ' . $conn->connect_error);
} 

// fetch data from table named user
$sql = "SELECT user_id, firstname, lastname, email FROM user";
$result = $conn->query($sql);

if ($result->num_rows > 0) {

    // declare empty array
    $data = [];

    // CSV file's column header
    $data[] = ['user_id', 'firstname', 'lastname', 'email'];
    
    // output data of each row
    while($row = $result->fetch_assoc()) {
        $data[] = [$row['user_id'], $row['firstname'], $row['lastname'], $row['email']];
    }
}

// close the connection
$conn->close();

Then we can use the php function fputcsv() to write the data on the CSV file.

// name of the CSV file which is going to download
$fileName = 'file.csv';

// set the Content-Type and Content-Disposition headers
header('Content-Type: application/excel');
header('Content-Disposition: attachment; filename="' . $fileName . '"');

// open up a PHP output stream
$fp = fopen('php://output', 'w');
foreach ($data as $row) {
    fputcsv($fp, $row);
}

// close the file handle
fclose($fp);

Finally, here is the complete code to creating downloadable CSV file using PHP file,

<?php
/**
 * create connection
 *
 * here,
 * - localhost -> server name
 * - root -> username
 * - welcome -> password
 * - my_database -> database name
 */
$conn = new mysqli('localhost', 'root', 'welcome', 'my_database');

// check connection
if ($conn->connect_error) {
    die('Connection failed: ' . $conn->connect_error);
}

// fetch data from table named user
$sql = "SELECT user_id, firstname, lastname, email FROM user";
$result = $conn->query($sql);

if ($result->num_rows > 0) {

    // declare empty array
    $data = [];

    // CSV file's column header
    $data[] = ['user_id', 'firstname', 'lastname', 'email'];

    // output data of each row
    while($row = $result->fetch_assoc()) {
        $data[] = [$row['user_id'], $row['firstname'], $row['lastname'], $row['email']];
    }

    // name of the CSV file which is going to download
    $fileName = 'file.csv';

    // set the Content-Type and Content-Disposition headers
    header('Content-Type: application/excel');
    header('Content-Disposition: attachment; filename="' . $fileName . '"');

    // open up a PHP output stream
    $fp = fopen('php://output', 'w');

    foreach ($data as $row) {
        fputcsv($fp, $row);
    }

    // close the file handle
    fclose($fp);
}

// close the connection
$conn->close();

Hope this helps.

Leave a Reply

Your email address will not be published. Required fields are marked *