Uploading a spreadsheet into a database

Although PHP does not have any direct capability to read a specific spreadsheet format (that is, XLSX, ODS, and so on), it does have the ability to read (CSV Comma Separated Values) files. Accordingly, in order to process customer spreadsheets, you will need to either ask them to furnish their files in CSV format, or you will need to perform the conversion yourself.

Getting ready...

When uploading a spreadsheet (that is, a CSV file) into a database, there are three major considerations:

Iterating through a (potentially) massive file
Extracting each spreadsheet row into a PHP array
Inserting the PHP array into the database

Massive file iteration will be handled using the preceding recipe. We will use the fgetcsv() function to convert a CSV row into a PHP array. Finally, we will use the (PDO PHP Data Objects) class to make a database connection and perform the insert.

How to do it...

First, we define a Application\Database\Connection class that creates a PDO instance based on a set of parameters supplied to the constructor:

<?php
  namespace Application\Database;

  use Exception;
  use PDO;

  class Connection
  { 
    const ERROR_UNABLE = 'ERROR: Unable to create database connection';    
    public $pdo;

    public function __construct(array $config)
    {
      if (!isset($config['driver'])) {
        $message = __METHOD__ . ' : ' . self::ERROR_UNABLE . PHP_EOL;
        throw new Exception($message);
    }
    $dsn = $config['driver'] 
    . ':host=' . $config['host'] 
    . ';dbname=' . $config['dbname'];
    try {
      $this->pdo = new PDO($dsn, 
      $config['user'], 
      $config['password'], 
      [PDO::ATTR_ERRMODE => $config['errmode']]);
    } catch (PDOException $e) {
      error_log($e->getMessage());
    }
  }

}

We then incorporate an instance of Application\Iterator\LargeFile. We add a new method to this class that is designed to iterate through CSV files:

protected function fileIteratorCsv()
{
  $count = 0;
  while (!$this->file->eof()) {
    yield $this->file->fgetcsv();
    $count++;
  }
  return $count;        
}

We also need to add Csv to the list of allowed iterator methods:

  const ERROR_UNABLE = 'ERROR: Unable to open file';
  const ERROR_TYPE   = 'ERROR: Type must be "ByLength", "ByLine" or "Csv"';
     
  protected $file;
  protected $allowedTypes = ['ByLine', 'ByLength', 'Csv'];

How it works...

First we define a config file,/path/to/source/config/db.config.php, that contains database connection parameters:

<?php
return [
  'driver'   => 'mysql',
  'host'     => 'localhost',
  'dbname'   => 'php7cookbook',
  'user'     => 'cook',
  'password' => 'book',
  'errmode'  => PDO::ERRMODE_EXCEPTION,
];

Next, we take advantage of the autoloading class defined in Chapter 1, Building a Foundation, to obtain an instance of Application\Database\Connection and Application\Iterator\LargeFile, defining a calling program, chap_02_uploading_csv_to_database.php:

define('DB_CONFIG_FILE', '/../data/config/db.config.php');
define('CSV_FILE', '/../data/files/prospects.csv');
require __DIR__ . '/../../Application/Autoload/Loader.php';
Application\Autoload\Loader::init(__DIR__ . '/..');

After that, we set up a try {...} catch () {...} block, which catches Throwable. This allows us to catch both exceptions and errors:

try {
  // code goes here  
} catch (Throwable $e) {
  echo $e->getMessage();
}

Inside the try {...} catch () {...} block we get an instance of the connection and large file iterator classes:

$connection = new Application\Database\Connection(
include __DIR__ . DB_CONFIG_FILE);
$iterator  = (new Application\Iterator\LargeFile(__DIR__ . CSV_FILE))
->getIterator('Csv');

We then take advantage of the PDO prepare/execute functionality. The SQL for the prepared statement uses ? to represent values that are supplied in a loop:

$sql = 'INSERT INTO `prospects` '
  . '(`id`,`first_name`,`last_name`,`address`,`city`,`state_province`,'
  . '`postal_code`,`phone`,`country`,`email`,`status`,`budget`,`last_updated`) '
  . ' VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)';
$statement = $connection->pdo->prepare($sql);

We then use foreach() to loop through the file iterator. Each yield statement produces an array of values that represents a row in the database. We can then use these values with PDOStatement::execute() to execute the prepared statement, inserting the row of values into the database:

foreach ($iterator as $row) {
  echo implode(',', $row) . PHP_EOL;
  $statement->execute($row);
}

You can then examine the database to verify that the data was successfully inserted.

Previous Chapter

Iterating through a massive file

Next Chapter

Recursive directory iterator

Table of Contents for PHP 7: Real World Application Development

Uploading a spreadsheet into a database

Getting ready...

How to do it...

How it works...

Table of Contents for
PHP 7: Real World Application Development