PHP and MySQL - Exporting Data

Sometimes it can be useful to export data from a database, so that it can be analysed, or, to import in to another computer system. CSV, or Comma Separated Value files, are one such file format that allows for both of these scenarios.

Below is an example of how PHP can be used to export data to a CSV file called ‘personexport.csv’, from a MySQL database table called ‘person’, which was used in the examples for selecting, inserting, updating, deleting and importing data.

Firstly, a connection to the database is established and a query is executed to extract the data from it. A check is made to see if any data has been returned and if there has been, the name of the CSV file is defined. The file is then opened for writing, the column headers are extracted from the returned data and subsequently written out to the CSV file. This is followed by the rows of data being added one by one. Finally, confirmation of a successful export is provided. A ‘try-catch’ block is also used to catch any errors that may occur.

<?php

    // Connect to the database.
    require_once('database-connect.php');

   try
   {
      
      // Prepare the query.
      $results = $connect->prepare("SELECT id, firstname, lastname, title, dob
                                    FROM person
                                    ORDER BY id");
                                    
      // Execute the query.
      $results->execute();
      
      // Assign the results to a variable.
      $people = $results->fetchAll(PDO::FETCH_ASSOC);
      
      // Check to see if any records have been returned.
      if (!$people)
      {
         
         // Display a message if no records exist.
         echo "There are no person records to export.";
         
      }
      else
      {
         
         // File name.
         $fileName = 'personexport.csv';
         
         // Open the CSV file.
         $file = fopen($fileName,"w");
         
         // Get the column names.
         $columnNames = array();
         $firstRow = $people[0];
         foreach($firstRow as $colName => $val){
            $columnNames[] = $colName;
         }
         
         // Add the column headers to the CSV file.
         fputcsv($file, $columnNames);
         
         // Add the rows of data to the CSV file.
         foreach ($people as $person)
         {
            fputcsv($file, $person);
         }
         
         // Close the CSV file.
         fclose($file);
         
         // Message stating export successful.
         echo "Data export successful.";
         
      } 
      
   } catch(Exception $e) {
      
      // Confirm error eporting data.
      echo "Data export unsuccessful.";
      exit;
      
   }

?>

The CSV file produced contains the following data. It should be noted that PHP only includes data in quotes where it is absolutely necessary, for example, where there is a space in a piece of text.

id,firstname,lastname,title,dob
1,Bob,Smith,Mr,1980-01-20
3,Fred,Bloggs,Mr,1975-05-07
4,Alan,White,Mr,1989-03-20
5,Fiona,Bloggs,Mrs,1985-05-19
6,Zoe,Davis,Miss,1979-07-11
7,Tom,Ingram,Mr,1971-10-04
8,Karen,Thomas,Mrs,1969-03-08
9,Samantha,Yates,Miss,1995-08-27