PHPで、DBから取り出したレコードをCSV出力するサンプルコードです。
環境
- OS: Amazon Linux2
- PHP: 7.4.11
- PostgreSQL: 11.5
※今回は、Amazon Linux2でLNPP環境を構築する | Qiitaで構築した、LNPP環境を使用しています。
ディレクトリ構成と処理概要
/product/share
|
ccc
|----dbCsv.php → 処理プログラム
|
|----config.ini → 設定ファイル（PostgreSQLへの接続情報を記載）
|
|
|----csv
|----data_[yyyymm].csv → 出力CSV（yyyymm形式：年月ごとに出力, 文字コード：SJIS）
:
:
DBから、前月1ヶ月間のデータ(アンケート結果)を取得し、文字コード：SJISでcsv出力する内容になります。
テーブルデータの準備
まず、PostgreSQLにログインし、スキーマとテーブルを作成します。
-- ホスト: 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)
) ;
テーブルの作成まで、完了したら、対象のスキーマに、対象のテーブルが存在しているか、確認しておきましょう。
-- スキーマへのパスを変更
SET search_path TO myschema;
-- 現在のスキーマを確認
select current_schema;
-- 現在のスキーマに存在するテーブル一覧を表示
\dt;
作成したテーブルにデータを投入します。
アンケート結果テーブル: questionnaire_resultsからは、先月1ヶ月分（本記事の執筆が2021年2月なので、2021年1月分）のデータを取得するため、更新日時:updated_atに、境界値を意識した値を入れます。
-- 会員情報テーブル: 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の接続設定の記載をしています。
[db]
;ホスト
host = '127.0.0.1';
;ポート番号
port = '5432';
;DB名
name = 'postgres';
;ユーザー名
user = 'postgres';
;パスワード
pass = 'postgres';
処理本体です。
サンプルなので、読みやすさ重視で、DB周りの処理もDaoに分割せず、同じファイルに書いています。
<?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がとても参考になりました。
2月のような短い月でも想定通りの結果となりました。
(2) PDOによるPostgreSQL接続
PDO_PGSQL DSN | PHPマニュアルを参考に記述します。
(3) SQLの実行
今回は、値のバインドを行うので、queryではなく、prepare→値のバインド→executeの流れで処理を実行します。
値のバインドを行うには、bindValueと、bindParamの2種類の方法がありますが、エラーがある場合にその場で判定してくれるなど、メリットが大きいbindValueを用いました。
詳しくは、
bindParam()とbindValue()の違い | Qiita
辺りをご参照ください。
(4) 実行結果の取得
今回は、複数行を取得するので、先頭の1行のみ取得するfetchではなく、fetchAllを使います。
(5) CSV出力
PHPには、fputcsvという行を CSV 形式にフォーマットし、ファイルポインタに書き込んでくれる関数があるのですが、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を使って対象のファイルを開き、fwriteを使って書き込み処理を行い、fcloseで、ファイルを閉じています。
SJIS形式にしたいのですが、設定の関係上、そのままだと、UTF-8で保存されてしまうため、mb_convert_encodingを使って変換しています。
終わりに
何気に、前月の範囲指定がいちばん難しかったです。
日付や範囲指定は永遠の課題だなぁ・・。