CSVアップロードからのMySQLへのデータ挿入

# **前書き**

まだ **[「ファイルアップロードの例外処理はこれぐらいしないと気が済まない」](http://qiita.com/mpyw/items/939964377766a54d4682)** や  **[「PHPでデータベースに接続するときのまとめ」](http://qiita.com/mpyw/items/b00b72c5c95aac573b71)** をご覧になっていない方は先にそちらからどうぞ。

…もうこのシリーズ何番煎じだよって感じになってきましたが、気にせず書きますｗｗｗ

# **実装のポイント**

## **ファイルアップロード段階でエラーが発生した場合にも適切に対応する**

このシリーズ共通の目標です。

## **CSVファイルのMIMEタイプは判定できない**

実は **[finfo::file](http://www.php.net/manual/ja/function.finfo-file.php)** メソッドを使って判定しようとすると `text/csv` とはならず、どう頑張っても **`text/plain`** にしかなりません。というわけでこの方法は断念せざるを得ません。これ以降、 **文字コードの厳密な判定** や **CSVとして正しく読み取れたかどうか** などのチェックを交えることによって、ここで実現出来なかった判定に代えようと試みることにします。

## **アップロードされたファイルの文字コードを確実にUTF-8に変換する**

CSVファイルと言えども、いろいろな環境で作成されたものがあるでしょう。そういった **文字コードがよく分からないもの** を全て無難に扱えるUTF-8に変換するためには、 **[mb_convert_encoding](http://www.php.net/manual/ja/function.mb-convert-encoding.php)** を利用する際、php.iniの設定に依存する自動判定の `auto` は使用せずに

```text:日本語ファイルの文字コードを正確に判定できる順番
ASCII,JIS,UTF-8,CP51932,SJIS-win
```

と明示するほうが望ましいです。更に、 **[mb_convert_encoding](http://www.php.net/manual/ja/function.mb-convert-encoding.php)** をすぐには使わず、 _\$strict_ を `true` に指定した **[mb_detect_encoding](http://www.php.net/manual/ja/function.mb-detect-encoding.php)** を併用することで正確性を最大限に引き上げられます。

## **トランザクション処理を行う**

途中でエラーが発生したとき、後から前に行った処理を取り消しできるように **トランザクション処理** を行うべきでしょう。

```php:トランザクション処理のひな形
$pdo->beginTransaction(); // トランザクション処理を開始する
try {
    /* ここで目的の処理を行う */
    $pdo->commit(); // 行った処理をすべて反映する
} catch (Exception $e) {
    $pdo->rollBack(); // 行った処理をすべて取り消しする
    throw $e; // 外側に例外を引き継ぐ
}
```

## **[fgetcsv](http://www.php.net/manual/ja/function.fgetcsv.php) 関数を正しく使う** 

以下のようにファイル終端に到達して `false` が返されるまでの間ループを続けさせます。

```php
while ($row = fgetcsv($fp)) {
    /* ここで各行に対する処理を行う */
}
```

### **ロケールを設定する**

PHP5では **[setlocale](http://www.php.net/manual/ja/function.setlocale.php)** 関数を用いて以下を実行しておかないと文字化けが発生するリスクがあります。

```php
setlocale(LC_ALL, 'ja_JP.UTF-8');
```

### **途中の空行を検知する**

最終行以外の空行は `array(null)` として取り出されるので、これが見つかった場合はその行はスキップさせるべきでしょう。

```php
if ($row === array(null)) {
    // 空行はスキップ
    continue;
}
```

但し、PHP5.3.7より古いバージョンには **バグ** があります。

| | ～ 5.2.9 | 5.2.10 ～ 5.3.6 | 5.3.7 ～ |
|:---:|:--------:|:---------------:|:--------:|
| `""`<br />(空文字列) | `array("")` | `array(null)` | `array(null)` |
| `" "`<br />(半角スペース) | `array("")` | `array(null)` | `array(" ")` |

### **エラーとファイルの終端を区別して検知する**

**[fgetcsv](http://www.php.net/manual/ja/function.fgetcsv.php)** 関数が配列以外の値を返す条件は以下のようになります。

![ss (2014-04-26 at 10.55.39).png](https://qiita-image-store.s3.amazonaws.com/0/25060/109a48a6-06f9-6c4b-4c2c-5bc23d797d06.png)

これらは **[feof](http://www.php.net/manual/ja/function.feof.php)** 関数によって区別することが出来ます。

```php
if (!feof($fp)) {
    // ファイルポインタが終端に達していなければエラー
    throw new RuntimeException('CSV parsing error');
}
```

### **カラム数の整合性をチェックする**

ここまでのチェックを行った後、仕上げに期待するカラム数と配列の要素数が一致するかどうかを調べます。

```php:期待するカラム数が4の場合
if (count($row) !== 4) {
    // カラム数が異なる無効なフォーマット
    throw new RuntimeException('Invalid column detected');
}
```

## **SQLモードを `TRADITIONAL` に設定する**

こうすることで、不適切なデータが強引に適切な形に変換されて格納されようとしたときに、そうする代わりにSQLエラーを発生させてくれます。PDOのエラーモードを **`PDO::ERRMODE_EXCEPTION`** に設定しておくと、発生したSQLエラーが更にPDOExceptionとしてスローされるので、PDOとの相性は抜群とも言えるでしょう。

```sql
SET SESSION sql_mode='TRADITIONAL'
```

# **ソースコード**

DSN、テーブル名、カラム数といった情報は適宜ご自分の環境に合わせて修正してください。

```html+php
<?php

/* HTML特殊文字をエスケープする関数 */
function h($str) {
    return htmlspecialchars($str, ENT_QUOTES, 'UTF-8');
}

// パラメータを正しい構造で受け取った時のみ実行
if (isset($_FILES['upfile']['error']) && is_int($_FILES['upfile']['error'])) {
    
    try {
        
        /* ファイルアップロードエラーチェック */
        switch ($_FILES['upfile']['error']) {
            case UPLOAD_ERR_OK:
                // エラー無し
                break;
            case UPLOAD_ERR_NO_FILE:
                // ファイル未選択
                throw new RuntimeException('File is not selected');
            case UPLOAD_ERR_INI_SIZE:
            case UPLOAD_ERR_FORM_SIZE:
                // 許可サイズを超過
                throw new RuntimeException('File is too large');
            default:
                throw new RuntimeException('Unknown error');
        }
        
        $tmp_name = $_FILES['upfile']['tmp_name'];
        $detect_order = 'ASCII,JIS,UTF-8,CP51932,SJIS-win';
        setlocale(LC_ALL, 'ja_JP.UTF-8');
        
        /* 文字コードを変換してファイルを置換 */
        $buffer = file_get_contents($tmp_name);
        if (!$encoding = mb_detect_encoding($buffer, $detect_order, true)) {
            // 文字コードの自動判定に失敗
            unset($buffer);
            throw new RuntimeException('Character set detection failed');
        }
        file_put_contents($tmp_name, mb_convert_encoding($buffer, 'UTF-8', $encoding));
        unset($buffer);
        
        /* データベースに接続 */
        $pdo = new PDO(
            'mysql:dbname=test_db;host=localhost;charset=utf8',
            'root',
            '',
            array(
                // カラム型に合わない値がINSERTされようとしたときSQLエラーとする
                PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION sql_mode='TRADITIONAL'",
                // SQLエラー発生時にPDOExceptionをスローさせる
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                // プリペアドステートメントのエミュレーションを無効化する
                PDO::ATTR_EMULATE_PREPARES => false,
            )
        );
        $stmt = $pdo->prepare('INSERT INTO test_table VALUES (?, ?, ?, ?)');
             
        /* トランザクション処理 */
        $pdo->beginTransaction();
        try {
            $fp = fopen($tmp_name, 'rb');
            while ($row = fgetcsv($fp)) {
                if ($row === array(null)) {
                    // 空行はスキップ
                    continue;
                }
                if (count($row) !== 4) {
                    // カラム数が異なる無効なフォーマット
                    throw new RuntimeException('Invalid column detected');
                }
                $executed = $stmt->execute($row);
            }
            if (!feof($fp)) {
                // ファイルポインタが終端に達していなければエラー
                throw new RuntimeException('CSV parsing error');
            }
            fclose($fp);
            $pdo->commit();
        } catch (Exception $e) {
            fclose($fp);
            $pdo->rollBack();
            throw $e;
        }
        
        /* 結果メッセージをセット */
        if (isset($executed)) {
            // 1回以上実行された
            $msg = array('green', 'Import successful');
        } else {
            // 1回も実行されなかった
            $msg = array('black', 'There were nothing to import');
        }
        
    } catch (Exception $e) {
        
        /* エラーメッセージをセット */
        $msg = array('red', $e->getMessage());
        
    }

}

// XHTMLとしてブラウザに認識させる
// (IE8以下はサポート対象外ｗ)
header('Content-Type: application/xhtml+xml; charset=utf-8');

?>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <title>CSV to MySQL importation test</title>
</head>
<body>
<?php if (isset($msg)): ?>
  <fieldset>
    <legend>Result</legend>
    <span style="color:<?=h($msg[0])?>;"><?=h($msg[1])?></span>
  </fieldset>
<?php endif; ?>
  <form enctype="multipart/form-data" method="post" action="">
    <fieldset>
      <legend>Select File</legend>
      Filename(CSV is only supported): <input type="file" name="upfile" /><br />
      <input type="submit" value="Upload" />
    </fieldset>
  </form>
</body>
</html>