PHP: Create CSV file from MySQL.

This is a guide on how to create a CSV file from MySQL. In this tutorial, we will:

  1. Connect to MySQL using the PDO object.
  2. Select rows from our MySQL table.
  3. Add the rows to a CSV file.
  4. Force the user’s browser to download the CSV file in question.

Let’s jump right into the following PHP code snippet:

<?php

//Our MySQL connection details.
$host = 'localhost';
$user = 'root';
$password = '';
$database = 'test';

//Connect to MySQL using PDO.
$pdo = new PDO("mysql:host=$host;dbname=$database", $user, $password);

//Create our SQL query.
$sql = "SELECT * FROM my_table_name LIMIT 20";

//Prepare our SQL query.
$statement = $pdo->prepare($sql);

//Executre our SQL query.
$statement->execute();

//Fetch all of the rows from our MySQL table.
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);

//Get the column names.
$columnNames = array();
if(!empty($rows)){
    //We only need to loop through the first row of our result
    //in order to collate the column names.
    $firstRow = $rows[0];
    foreach($firstRow as $colName => $val){
        $columnNames[] = $colName;
    }
}

//Setup the filename that our CSV will have when it is downloaded.
$fileName = 'mysql-export.csv';

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

//Open up a file pointer
$fp = fopen('php://output', 'w');

//Start off by writing the column names to the file.
fputcsv($fp, $columnNames);

//Then, loop through the rows and write them to the CSV file.
foreach ($rows as $row) {
    fputcsv($fp, $row);
}

//Close the file pointer.
fclose($fp);

A drill-down of the code above:

  1. We connected to our MySQL database using the PDO object.
  2. We selected our rows. Note that you need to be careful about the number of rows that you are returning in this script, as you may run into memory errors if the result set is too large. Hence the reason I used a LIMIT clause in my query.
  3. We prepared our query, executed it and fetched the results. Note that we used PDO::FETCH_ASSOC in order to retrieve an associative array. This is important if you want to place the column names as headers at the top of the CSV file.
  4. We looped through the first row that was returned from MySQL in order to collect the column names. We placed these column names in a PHP array called $columnNames.
  5. We declared what name our file will have when it is downloaded by the user’s browser.
  6. We set the Content-Type and the Content-Disposition in order to force the browser to download the CSV data as a file.
  7. We opened up a file pointer, using the php://output IO stream.
  8. We added the column names to the CSV file. We did this before we added our result set because we want them to appear as headers at the top of the file.
  9. We loop through the rows that were returned by MySQL while adding them to our file using the fputcsv function.
  10. Finally, we close the file pointer using fclose.

Hopefully, you found this tutorial to be useful.