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

前書き

まだ 「ファイルアップロードの例外処理はこれぐらいしないと気が済まない」 や 「PHPでデータベースに接続するときのまとめ」 をご覧になっていない方は先にそちらからどうぞ。

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

実装のポイント

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

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

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

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

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

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

日本語ファイルの文字コードを正確に判定できる順番

ASCII,JIS,UTF-8,CP51932,SJIS-win

と明示するほうが望ましいです。更に、 mb_convert_encoding をすぐには使わず、 \$strict を true に指定した mb_detect_encoding を併用することで正確性を最大限に引き上げられます。

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

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

トランザクション処理のひな形

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

fgetcsv 関数を正しく使う

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

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

ロケールを設定する

PHP5では setlocale 関数を用いて以下を実行しておかないと文字化けが発生するリスクがあります。

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

途中の空行を検知する

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

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

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

	～ 5.2.9	5.2.10 ～ 5.3.6	5.3.7 ～
"" (空文字列)	array("")	array(null)	array(null)
" " (半角スペース)	array("")	array(null)	array(" ")

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

fgetcsv 関数が配列以外の値を返す条件は以下のようになります。

これらは feof 関数によって区別することが出来ます。

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

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

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

期待するカラム数が4の場合

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

SQLモードを TRADITIONAL に設定する

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

SET SESSION sql_mode='TRADITIONAL'

ソースコード

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

<?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>