[PHP×PostgreSQL]DBのレコードをCSV出力する

PHPで、DBから取り出したレコードをCSV出力するサンプルコードです。

## 環境
* OS: Amazon Linux2
* PHP: 7.4.11
* PostgreSQL: 11.5

※今回は、[Amazon Linux2でLNPP環境を構築する | Qiita](https://qiita.com/C_HERO/items/7563d8e7fcbe63754e90)で構築した、LNPP環境を使用しています。

## ディレクトリ構成と処理概要

```
/product/share
     |
    ccc
     |----dbCsv.php → 処理プログラム
     |
     |----config.ini → 設定ファイル（PostgreSQLへの接続情報を記載）
     |      
     | 
     |----csv                   
            |----data_[yyyymm].csv → 出力CSV（yyyymm形式：年月ごとに出力, 文字コード：SJIS）              
                      :
                      :
```
DBから、前月1ヶ月間のデータ(アンケート結果)を取得し、文字コード：SJISでcsv出力する内容になります。

## テーブルデータの準備
まず、PostgreSQLにログインし、スキーマとテーブルを作成します。

```sql:
-- ホスト: 127.0.0.1, ポート番号: 5432のデータベース: postgresにロール名: postgresでログイン
psql -h 127.0.0.1 -p 5432 -U postgres -d postgres
-- スキーマ: myschemaを作成
create schema myschema;

-- スキーマ: myschemaに会員情報テーブル: membersを作成
create table myschema.members (
  member_id int not null
  , name varchar(255) not null
  , sex char(1) not null
  , age smallint not null
  , created_by varchar(255)
  , created_at date
  , update_by varchar(255)
  , update_at date
  , constraint members_PKC primary key (member_id)
) ;

-- スキーマ: myschemaにアンケート結果テーブル: questionnaire_resultsを作成
create table myschema.questionnaire_results (
  member_id int
  , questionnaire_id bigint
  , choice1 varchar(255)
  , choice2 varchar(255)
  , choice3 varchar(255)
  , created_by varchar(255)
  , created_at date
  , updated_by varchar(255)
  , updated_at date
  , constraint questionnaire_results_PKC primary key (member_id,questionnaire_id)
  , foreign key (member_id) references myschema.members(member_id)
) ;
```

テーブルの作成まで、完了したら、対象のスキーマに、対象のテーブルが存在しているか、確認しておきましょう。

```sql:
-- スキーマへのパスを変更
SET search_path TO myschema;
-- 現在のスキーマを確認
select current_schema;
-- 現在のスキーマに存在するテーブル一覧を表示
\dt;
```

作成したテーブルにデータを投入します。
アンケート結果テーブル: questionnaire_resultsからは、先月1ヶ月分（本記事の執筆が2021年2月なので、2021年1月分）のデータを取得するため、更新日時:updated_atに、境界値を意識した値を入れます。

```sql:
-- 会員情報テーブル: membersにデータを投入
insert into myschema.members values (1,'相川健太','m',21,'webuser','2020/12/28 12:03:45','webuser','2020/12/28 12:03:45');
insert into myschema.members values (2,'井上和子','f',18,'webuser','2020/12/30 11:43:54','webuser','2020/12/30 11:43:54');
insert into myschema.members values (3,'宇野拓人','m',32,'webuser','2021/01/09 13:26:11','webuser','2021/01/09 13:26:11');
insert into myschema.members values (4,'榎本翼','m',28,'webuser','2021/01/10 09:33:57','webuser','2021/01/10 09:33:57');
insert into myschema.members values (5,'恩田栞','f',26,'webuser','2021/01/10 16:54:05','webuser','2021/01/10 16:54:05');

-- アンケート結果テーブル: questionnaire_resultsにデータを投入
insert into myschema.questionnaire_results values (2,1001,'伊達巻','田作り','昆布巻き','webuser','2020/12/31 23:59:59','webuser','2020/12/31 23:59:59');
insert into myschema.questionnaire_results values (1,1001,'お雑煮','数の子',null,'webuser','2021/01/01 00:00:00','webuser','2021/01/01 00:00:00');
insert into myschema.questionnaire_results values (5,1001,'栗きんとん','伊達巻','黒豆','webuser','2021/01/15 16:37:42','webuser','2021/01/15 16:37:42');
insert into myschema.questionnaire_results values (3,1001,'海老','お雑煮','黒豆','webuser','2021/01/31 23:59:59','webuser','2021/01/31 23:59:59');
insert into myschema.questionnaire_results values (4,1001,'田作り','昆布巻き','伊達巻','webuser','2021/02/01 00:00:00','webuser','2021/02/01 00:00:00');
```

## コードと解説

まず、設定ファイル、config.iniです。
こちらには、DBの接続設定の記載をしています。

```:config.ini
[db]
;ホスト
host = '127.0.0.1';
;ポート番号
port = '5432';
;DB名
name = 'postgres';
;ユーザー名
user = 'postgres';
;パスワード
pass = 'postgres';
```

処理本体です。
サンプルなので、読みやすさ重視で、DB周りの処理もDaoに分割せず、同じファイルに書いています。

```php:dbCsv.php
<?php
// 設定ファイルの読み込み
$conf = parse_ini_file('config.ini', true);
// 先月初日
$last_month_start = date('Y-m-d H:i:s', mktime(0, 0, 0, date('m')-1, 1, date('Y'))); 
// 先月末日
$last_month_end = date('Y-m-d H:i:s', mktime(23, 59, 59, date('m'), 0, date('Y'))); 
// 先月（年月のみ）
$last_month = date('Ym', mktime(0, 0, 0, date('m'), 0, date('Y'))); 
// csvファイル名
$filename = '/product/share/ccc/csv/data_'.$last_month.'.csv';

try{

    // csvファイルが存在しない(処理未実施の)場合のみ、処理を実施
    if(!file_exists($filename)){
        $dbh = new PDO('pgsql:host='.$conf['db']['host'].';port='.$conf['db']['port'].';dbname='.$conf['db']['name'].';user='.$conf['db']['user'].';password='.$conf['db']['pass']);
        // 先月1ヶ月分のアンケート結果を取得
        $stmt = $dbh->prepare("SELECT m.member_id as member_id, m.sex as sex, m.age as age, q.choice1 as choice1, q.choice2 as choice2, q.choice3 as choice3 
                                FROM myschema.members m INNER JOIN myschema.questionnaire_results q 
                                ON m.member_id = q.member_id 
                                WHERE q.updated_at BETWEEN :last_month_start AND :last_month_end");
        
        $stmt->bindValue(':last_month_start', $last_month_start);
        $stmt->bindValue(':last_month_end', $last_month_end);
        $stmt->execute();
        $result = $stmt->fetchAll();
        // csvヘッダ
        $csv = 'member_id,sex,age,choice1,choice2,choice3'.PHP_EOL;
        // csvボディ
        foreach($result as $row){
            $csv .= $row['member_id'].','.$row['sex'].','.$row['age'].','.$row['choice1'].','.$row['choice2'].','.$row['choice3'].PHP_EOL;
        }
        // csv出力（SJIS）
        $fp = fopen($filename, 'w');
        fwrite($fp, mb_convert_encoding($csv,"SJIS"));
        fclose($fp);
    }else{
        echo "すでにファイルが存在しています\n";
    }

}catch(PDOException $e){
    echo "接続に失敗しました\n";
    echo $e;
}finally{
    // 接続をクローズ
    $dbh = null;
}
```

(1)月初と月末の取得
[PHPで月末日を取得する | Qiita](https://qiita.com/akase244/items/e6c4835648a4e9a8678f)がとても参考になりました。
2月のような短い月でも想定通りの結果となりました。

(2) PDOによるPostgreSQL接続
[PDO_PGSQL DSN | PHPマニュアル](https://www.php.net/manual/ja/ref.pdo-pgsql.connection.php)を参考に記述します。

(3) SQLの実行
今回は、値のバインドを行うので、[query](https://www.php.net/manual/ja/pdo.query.php)ではなく、[prepare](https://www.php.net/manual/ja/pdo.prepare.php)→値のバインド→executeの流れで処理を実行します。
値のバインドを行うには、[bindValue](https://www.php.net/manual/en/pdostatement.bindvalue)と、[bindParam](https://www.php.net/manual/en/pdostatement.bindparam)の2種類の方法がありますが、エラーがある場合にその場で判定してくれるなど、メリットが大きいbindValueを用いました。
詳しくは、
[bindParam()とbindValue()の違い | Qiita](https://qiita.com/_dozen_/items/f6239cf1bdab6f8b0026)
辺りをご参照ください。

(4) 実行結果の取得
今回は、複数行を取得するので、先頭の1行のみ取得する[fetch](https://www.php.net/manual/ja/pdostatement.fetch.php)ではなく、[fetchAll](https://www.php.net/manual/ja/pdostatement.fetchall.php)を使います。

(5) CSV出力
PHPには、[fputcsv](https://www.php.net/manual/ja/function.fputcsv.php)という行を CSV 形式にフォーマットし、ファイルポインタに書き込んでくれる関数があるのですが、fetchAllで取得したレコードは、例えば以下のように、値に重複が出てしまいます。

```php:fetchAllで取得したレコード（一部抜粋）
array(12) {
  ["member_id"]=>
  int(1)
  [0]=>
  int(1)
  ["sex"]=>
  string(1) "m"
  [1]=>
  string(1) "m"
  ["age"]=>
  int(21)
  [2]=>
  int(21)
  ["choice1"]=>
  string(9) "お雑煮"
  [3]=>
  string(9) "お雑煮"
  ["choice2"]=>
  string(9) "数の子"
  [4]=>
  string(9) "数の子"
  ["choice3"]=>
  NULL
  [5]=>
  NULL
}
```

そのため、今回は必要な値だけ別途変数に詰め込んで、その後出力することにしました。
PHP_EOLは、改行を表していて、環境に応じて、自動で改行コードを切り替えてくれる定数です。
今回は、Linux環境なので、改行コードは、LFとなります。
[fopen](https://www.php.net/manual/ja/function.fopen)を使って対象のファイルを開き、[fwrite](https://www.php.net/manual/ja/function.fwrite)を使って書き込み処理を行い、[fclose](https://www.php.net/manual/ja/function.fclose)で、ファイルを閉じています。
SJIS形式にしたいのですが、設定の関係上、そのままだと、UTF-8で保存されてしまうため、[mb_convert_encoding](https://www.php.net/manual/en/function.mb-convert-encoding)を使って変換しています。

## 終わりに
何気に、前月の範囲指定がいちばん難しかったです。
日付や範囲指定は永遠の課題だなぁ・・。

## 参考

### テーブルデータの準備
* [PostgreSQLへの接続と切断 | DBOnline](https://www.dbonline.jp/postgresql/connect/index2.html)
* [PostgreSQLの基本的なコマンド | Qiita](https://qiita.com/H-A-L/items/fe8cb0e0ee0041ff3ceb)

### コードと解説
* [【PHP】PHP_EOL | Qiita](https://qiita.com/kazu56/items/bc77582313918fe2a3b1)