mysqlからカラム名も取得してfputcsvによる帳票出力

### データ作成
create database universe;
use universe;
create table planets(
id int primary key auto_increment,
name varchar(255),
diameter float,
mass float
);
describe planets;
insert into planets(name, diameter, mass) values (‘Sun’,’110′,’330000′);
insert into planets(name, diameter, mass) values (‘Mercury’,’0.38′,’0.06′);
insert into planets(name, diameter, mass) values (‘Venus’,’0.95′,’0.82′);
insert into planets(name, diameter, mass) values (‘Earth’,’1′,’1′);
insert into planets(name, diameter, mass) values (‘Mars’,’0.53′,’0.11′);
insert into planets(name, diameter, mass) values (‘Jupiter’,’11’,’320′);
insert into planets(name, diameter, mass) values (‘Saturn’,’9.5′,’95’);
insert into planets(name, diameter, mass) values (‘Uranus’,’4′,’15’);
insert into planets(name, diameter, mass) values (‘Neptune’,’3.9′,’17’);
select * from planets;

### データ出力
var_dumpでテストしてから、出力します。

try {
$dbh = new PDO('mysql:host=localhost;dbname=universe;charset=utf8','root','hogehoge', array(PDO::ATTR_EMULATE_PREPARES => false));
} catch(PDOException $e){
    exit('データベース接続失敗。'.$e->getMessage());
}

$export_sql = "select * from planets";

// $stmt = $dbh->query($export_sql);
// $row = $stmt->fetchAll(PDO::FETCH_ASSOC);
// var_dump($row);

$file_path = "csv/planets.csv";
$export_csv_title = ["id","name","diameter","mass"];

foreach($export_csv_title as $key => $val){
	$export_header[] = mb_convert_encoding($val, 'SJIS-win', 'UTF-8');
	}

	if(touch($file_path)){
		$file = new SplFileObject($file_path, "w");

		$file->fputcsv($export_header);
		$stmt = $dbh->query($export_sql);

		while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
			$file->fputcsv($row);
		}
		$dbh = null;

	}

echo "done";

### カラム名も取得したい場合
show columnsとして配列を取得して、その中からFieldを取り出す

$export_column = "show columns from planets";
$stmt = $dbh->query($export_column);
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
	$field[] = $row["Field"];
}

つなげると

try {
$dbh = new PDO('mysql:host=localhost;dbname=universe;charset=utf8','root','hogehoge', array(PDO::ATTR_EMULATE_PREPARES => false));
} catch(PDOException $e){
    exit('データベース接続失敗。'.$e->getMessage());
}

$export_column = "show columns from planets";
$stmt = $dbh->query($export_column);
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
	$field[] = $row["Field"];
}

$export_sql = "select * from planets";
$file_path = "csv/planets.csv";
$export_csv_title = $field;

foreach($export_csv_title as $key => $val){
	$export_header[] = mb_convert_encoding($val, 'SJIS-win', 'UTF-8');
	}

	if(touch($file_path)){
		$file = new SplFileObject($file_path, "w");

		$file->fputcsv($export_header);
		$stmt = $dbh->query($export_sql);

		while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
			$file->fputcsv($row);
		}
		$dbh = null;

	}

echo "done";

カラム名だけ取得のニーズはあるので、その命令文を作っても良いように思ったが、、、
MySQL8.0のGithubレポを見ると軽々しく言えんな。。
https://github.com/mysql/mysql-server/blob/8.0/sql/sql_select.cc