PHP and MySQL - Importing Data

In order to import data into a MySQL database, it must be in a suitable format. CSV files are one such format. A CSV file, or Comma Separated Values file, is a delimited file that uses commas to separate values. They store tabular data in plain text. The first row in a CSV file often contains headers for each column of data. Each row in the file thereafter is a record of related data. In the example contents of a CSV file below, each row contains information relating to a person, including their first name, last name, title and date of birth in the format YYYY-MM-DD (four digit year, two digit month and two digit day).

"firstname","lastname","title","dob"
"Zoe","Davis","Miss","1979-07-11"
"Tom","Ingram","Mr","1971-10-04"
"Karen","Thomas","Mrs","1969-03-08"
"Samantha","Yates","Miss","1995-08-27"

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

Firstly, the file path is set and a check is made to see if the CSV file actually exists. If it does, a connection to the database is established and the CSV file is assigned to a variable. The headers are removed and the rows of data are then processed one by one. An SQL ‘Insert‘ statement is constructed, incorporating the values from the row in the CSV file variable. This is used to insert the data into the database. Finally, feedback is given as to the number of records added to the database.

<?php

   // CSV file.
   $filePath = "personimport.csv";
   
   // Record count.
   $recordCount = 0;
   
   // Check if CSV file exists.
   if (file_exists($filePath))
   {
   
      // Connect to the database.
      require_once('database-connect.php');

      // Assign the CSV file to a variable.
      $csv = array_map('str_getcsv', file($filePath));
      
      // Remove the header row.
      array_shift($csv);
      
      // Process the contents of the CSV file.
      foreach ($csv as $person)
      {
         
         // Construct the insert statement.
         $query = "INSERT INTO person ";
         $query .= "(firstname, lastname, title, dob) ";
         $query .= "VALUES (?, ?, ?, ?)";
         
         try
         {
         
            // Prepare the query.
            $results = $connect->prepare($query);
            
            // Bind the parameters.
            $results->bindParam(1, $person[0]);
            $results->bindParam(2, $person[1]);
            $results->bindParam(3, $person[2]);
            $results->bindParam(4, $person[3]);
            
            // Execute the query.
            $results->execute();
            
            // Add one to the record count.
            $recordCount += 1;
         
         } catch(Exception $e) {
      
            // If query fails, display an error and exit.
            echo "Error adding person information.";
            exit;
      
         }
         
      }
      
      // Provide feedback on the number of records imported.
      if ($recordCount == 0)
      {
         
         echo "No person records have been imported.";
         
      }
      else if ($recordCount == 1)
      {
         
         echo $recordCount . " person record has been imported.";
         
      }
      else
      {
         
         echo $recordCount . " person records have been imported.";
         
      }
   
   }
   else
   {
      
      // Message stating CSV file could not be located.
      echo "Could not locate the CSV file.";
      
   }
   
?>

The contents of the 'person' table now looks like this.

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