### データ作成
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
