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:

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.

Comments

comments