phpでcsvをダウンロードする。
//フォームindex.html <form action="dl.php" method="post"> <input name="id" type="hidden" value="1" /> <p><input name="dlbtn_ys" type="submit" value="CSVダウンロード" /></p> </form>
<?php //dl.php //DB接続情報 $dsn = 'mysql:host=localhost;dbname=DBの名前'; $id = 'xxxxxxx'; $pw = 'pass'; //画面からパラメータ取得 //$val = filter_input(INPUT_POST, "busyo_cd"); //$val=filter_input(INPUT_POST,'busyo_cd',FILTER_VALIDATE_INT,["flags"=>FILTER_REQUIRE_ARRAY]); //$val4=filter_input(INPUT_POST,'busyo_cd4',FILTER_VALIDATE_INT,["flags"=>FILTER_REQUIRE_ARRAY]); if (isset($_POST["dlbtn_ys"])) { try { //DB検索処理 $pdo = new PDO($dsn, $id, $pw, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)); $sql = "SELECT emp_name1, mc_area_remarks, mc_belong_remarks, emp_building_name1, emp_building_name2, emp_total,emp_rent,emp_fee, emp_transfer14, emp_transfer15, emp_transfer16, emp_transfer17, emp_transfer18 FROM tb_user_tr LEFT JOIN tb_user_emp ON tb_user_tr.tr_e_id = tb_user_emp.emp_id LEFT JOIN tb_user_tr_title ON tb_user_tr.tr_flg1_id = tb_user_tr_title.title_flg1_id LEFT JOIN tb_mc_area ON (tb_user_tr.tr_k=tb_mc_area.mc_area_id) LEFT JOIN tb_mc_belong ON (tb_user_tr.tr_aff=tb_mc_belong.mc_belong_id) where 1 "; $data=[]; $a=filter_input(INPUT_POST,'a',FILTER_VALIDATE_INT); $d=filter_input(INPUT_POST,'d',FILTER_VALIDATE_INT); $e=filter_input(INPUT_POST,'e',FILTER_VALIDATE_INT); if(!is_null($a)){ $sql.=" AND user_emp_a_id = ? "; $data[]=$a; } if(!is_null($d)){ $sql.=" AND tr_flg1 = ? "; $data[]=$d; } if(!is_null($e)){ $sql.=" AND tr_flg1_id = ? "; $data[]=$e; } /* if(!is_null($f)){ $sql.=" OR (emp_name2 LIKE ? ))"; $data[]="%".$e."%"; } if(!is_null($g)){ $sql.=" and ? < concat((emp_cont1),lpad((emp_cont2), 2, '0'), lpad((emp_cont3), 2, '0'))"; $g= str_replace('/', '', $g); $data[]=$g; } if(!is_null($h)){ $sql.=" and concat((emp_cont1),lpad((emp_cont2), 2, '0'), lpad((emp_cont3), 2, '0')) < ? "; $h= str_replace('/', '', $h); $data[]=$h; } */ $sql.=" order by tr_id DESC "; // WHERE su1_id = :busyocd "; //print_r($data); //print $sql; $stmt = $pdo->prepare($sql); //$stmt->bindParam(':busyocd', $val, PDO::PARAM_STR);https://teratail.com/questions/76415 $stmt->execute($data); //var_dump($stmt->errorCode()); //CSV文字列生成 $csvstr = "社員名,エリア,所属,物件名・号室,賃料,管理費,賃料口座名義人,銀行名,支店名,普通当座,口座番号 "; while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $csvstr .= $row['emp_name1'] . ",";//社員名 $csvstr .= $row['mc_area_remarks'] . ",";//エリア $csvstr .= $row['mc_belong_remarks'] . ",";//所属 $csvstr .= $row['emp_building_name1'] .$row['emp_building_name2'] . ","; $csvstr .= $row['emp_rent'] . ",";//賃料+管理費 $csvstr .= $row['emp_fee'] . ",";//賃料+管理費 $csvstr .= $row['emp_transfer18'] . ",";//賃料口座名義人 $csvstr .= $row['emp_transfer14'] . ",";//銀行名 $csvstr .= $row['emp_transfer15'] . ",";//支店名 $et16= $row['emp_transfer16'];//普通or当座 if($et16=="1"){ $et6="普通"; } elseif($et16=="2"){ $et6="当座"; } $csvstr .= $et6 . ",";//普通or当座 $csvstr .= $row['emp_transfer17'] . " ";//口座番号 } //CSV出力 $dtStr = date("Y-m-dH:i:s") . "-" . substr(explode(".", (microtime(true) . ""))[1], 0, 3); $fileNm = "{$dtStr}.csv"; header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename='.$fileNm); echo mb_convert_encoding($csvstr, "SJIS-win", "UTF-8"); //Shift-JISに変換したい場合のみ exit(); }catch(ErrorException $ex){ print('ErrorException:' . $ex->getMessage()); }catch(PDOException $ex){ print('PDOException:' . $ex->getMessage()); } }