webdata
DX推進をサポートする技術者向け情報提供サイト

初心者向けPHP・データベース入門

TOP >初心者向けPHP・データベース入門 >3.3 日報システム作成(一覧表作成)

【PHP入門】日報システム作成(一覧表作成)

 2023-06-25 (更新日:2024-05-19)

<学習する内容>

 日報の一覧表の表示画面を作成します。

1)日報一覧作成


下記のサンプルプログラムをコピーもしくはダウンロードし指定のフォルダーに配置してください。
ファイル名:dailyreport_list.php
配置先:c:\xampp\htdocs\
配置先URL:http://localhost/dailyreport_list.php

サンプルプログラム名:dailyreport_list.php
						<?php

						//DBへの接続
						include('/xampp/data/conn.php');

						//POSTの受信
							$fnc = filter_input(INPUT_POST, 'fnc');
							$select_id = filter_input(INPUT_POST, 'select_id');
							$select_sectionCode = filter_input(INPUT_POST, 'select_sectionCode');
							$select_staffCode = filter_input(INPUT_POST, 'select_staffCode');
							$select_class = filter_input(INPUT_POST, 'select_class');
							$select_workDate = filter_input(INPUT_POST, 'select_workDate');
							$select_comment = filter_input(INPUT_POST, 'select_comment');
							$select_customerName = filter_input(INPUT_POST, 'select_customerName');
								$select_customerName = mb_convert_kana($select_customerName,'A');			//全角化

						//変数リセット
							$OPT_DATA = null;
							$opt_staff = null;
							$class_display = null;
							$opt_section = null;
							$workDate = null;
							$opt_date = null;
						
						//変数表示変換
							if($select_class == 1){
								$select_class_display = '得意先訪問';
							}
							if($select_class == 2){
								$select_class_display = '社内作業';
							}
	
						//部署option
						$sql = "SELECT sectionCode
										,sectionName 
								FROM Msection;";
						$res = mysqli_query($conn,$sql) or die("error$sql");
							$opt_section = '<option style="color:#2E2E2E;" value="">解除';
						while($row = mysqli_fetch_array($res)){
							$sectionCode_opt = $row["sectionCode"];
							$sectionName_opt = $row["sectionName"];
							$opt_section .= '<option style="color:#2E2E2E;" value="'.$sectionCode_opt.'">'.$sectionName_opt;
						}
						//部署検索
						if(empty($select_sectionCode)){
							$clr_section = 'color:#c0c0c0;';
							$select_sectionName = '部署検索';
							$where_and_section = null;
						}else{
							$clr_section = null;
							$where_and_section = "and a.sectionCode = $select_sectionCode";
							$sql = "SELECT sectionCode
											,sectionName 
									FROM Msection 
									WHERE sectionCode = $select_sectionCode;";
							$res = mysqli_query($conn,$sql) or die("error $sql");
							$row = mysqli_fetch_array($res);
								$select_sectionName = $row["sectionName"];
						}
						//担当option
						if(strlen($select_sectionCode)){
							$sql = "SELECT staffCode
											,staffName 
									FROM Mstaff
									WHERE sectionCode = $select_sectionCode;";
							$res = mysqli_query($conn,$sql) or die("error $sql");
								$opt_staff = '<option style="color:#2E2E2E;" value="">解除';
							while($row = mysqli_fetch_array($res)){
								$staffCode_opt = $row["staffCode"];
								$staffName_opt = $row["staffName"];
								$opt_staff .= '<option style="color:#2E2E2E;" value="'.$staffCode_opt.'">'.$staffName_opt;
							}
						}
						//担当者名検索
						if(empty($select_staffCode)){
							$clr_staff = 'color:#c0c0c0;';
							$select_staffName = '担当検索';
							$where_and_staff = null;
						}else{
							$clr_staff = null;
							$where_and_staff = "and a.staffCode = $select_staffCode";
							$sql = "SELECT staffCode
											,staffName 
									FROM Mstaff 
									WHERE staffCode = $select_staffCode;";
							$res = mysqli_query($conn,$sql) or die("error $sql");
							$row = mysqli_fetch_array($res);
								$select_staffName = $row["staffName"];
						}
						//実施日option
						$day0 = date("Y-m-d");
						$cnt = 60;		  //60日前まで表示
							$opt_date = '<option style="color:#2E2E2E;" value="">解除';
						for( $i = 0 ; $i <= $cnt ; $i++ ){
							$day = date("Y-m-d",strtotime("-$i day",strtotime($day0)));
							$opt_date .= '<option style="color:#2E2E2E;" value="'.$day.'">'.$day;
						}
						//実施日検索
						if(empty($select_workDate)){
							$select_workDate_display = '実施日検索';
							$clr_workDate = 'color:#c0c0c0;';
							$where_and_work = null;
						}else{
							$clr_workDate = null;
							$where_and_work = "and a.workDate = '$select_workDate'";
							$select_workDate_display = date("Y-m-d",strtotime($select_workDate));
						}
						//分類検索
						if(empty($select_class)){
							$select_class_display = '分類検索';
							$clr_class = 'color:#c0c0c0;';
							$where_and_class = null;
						}else{
							$clr_class = null;
							$where_and_class = "and a.class = $select_class";
							if($select_class == 1){
								$select_class_display = '得意先訪問';
							}
							if($select_class == 2){
								$select_class_display = '社内作業';
							}
						}
						//得意先検索
							$select_customerName = filter_input(INPUT_POST, 'select_customerName');
							$select_customerName = mb_convert_kana($select_customerName,'AK');	  //文字の全角化
						if(empty($select_customerName)){
							$clr_customer = "color:#c0c0c0;";
							$where_and_customer = null;
						}else{
							$clr_customer = null;
							$where_and_customer = "WHERE d.customerName like '%$select_customerName%'";
						}
						
						//$_POSTデータの保持
						$OPT_DATA .= '<input type="hidden" name="select_sectionCode" value="'.$select_sectionCode.'">';
						$OPT_DATA .= '<input type="hidden" name="select_staffCode" value="'.$select_staffCode.'">';
						$OPT_DATA .= '<input type="hidden" name="select_class" value="'.$select_class.'">';
						$OPT_DATA .= '<input type="hidden" name="select_workDate" value="'.$select_workDate.'">';
						$OPT_DATA .= '<input type="hidden" name="select_customerName" value="'.$select_customerName.'">';
						
						//ページ対応
							$page = filter_input(INPUT_POST, 'page');	//ページ指定
							$add = filter_input(INPUT_POST, 'add');		//ページを戻るか進めるか
							if(empty($page)){
								$page = 0;
								$offset = 0;
							}
							if($add == 1){				//ページを進める
								$page = $page + 1;
							}
							if($add == -1){				//ページを戻す
								$page = $page - 1;
							}
							$offset = $page * 5;		//5行のデータを表示 20にすると20行表示
							
						//合計件数の集計
						$sql = "SELECT a.id
										from dailyreport AS a
									left join Msection AS b 
										ON a.sectionCode = b.sectionCode
									left join Mstaff AS c 
										ON a.staffCode = c.staffCode
									left join Mcustomer AS d 
										ON a.customerCode = d.customerCode
									WHERE a.id > 0
									$where_and_section
									$where_and_staff
									$where_and_customer
									$where_and_class
									$where_and_work
									ORDER BY workDate DESC
									;";
						$res = mysqli_query($conn,$sql) or die("er select $sql");
						$cnt = mysqli_affected_rows($conn);
						$cnt_all = mysqli_num_rows($res);
							$cnt = ceil($cnt_all/5);

							$page_add1 = $page + 1;		//表示させるページ
						
						//HTML本文
						echo <<<EOD
						<!DOCTYPE html>
						<html lang="ja">
						  
						<head>
						<meta charset="utf-8">
						<link rel="stylesheet" href="/style.css" type="text/css">
						<title>日報一覧</title>
						</head>
						  
						<body>
						日報一覧<br>
						<table  style="font-size:12px;" >
							<tr style="height:24px;" align="center">
							<form method="POST" action="{$_SERVER['PHP_SELF']}">
									{$OPT_DATA}
								<td width="70">
									<select name="select_sectionCode" onchange="submit(this.form)" style="width:100px;height:26px;{$clr_section}">
										<option value="{$select_sectionCode}" selected>{$select_sectionName}
										{$opt_section}
									</select>
								</td>
							</form>
							<form method="POST" action="{$_SERVER['PHP_SELF']}">
									{$OPT_DATA}
								<td width="70">
									<select name="select_staffCode" onchange="submit(this.form)" style="width:100px;height:26px;{$clr_staff}">
										<option value="{$select_staffCode}" selected>{$select_staffName}
										{$opt_staff}
									</select>
								</td>
							</form>
							<form method="POST" action="{$_SERVER['PHP_SELF']}">
									{$OPT_DATA}
								<td width="70">
									<select name="select_class" onchange="submit(this.form)" style="width:100px;height:26px;{$clr_class}">
										<option value="{$select_class}" selected>{$select_class_display}
										<option style="color:#2E2E2E;" value="1">得意先訪問
										<option style="color:#2E2E2E;" value="2">社内作業
										<option style="color:#2E2E2E;" value="">解除
									</select>
								</td>
							</form>
							<form method="POST" action="{$_SERVER['PHP_SELF']}">
									{$OPT_DATA}
								<td width="70">
									<select name="select_workDate" onchange="submit(this.form)" style="width:100px;height:26px;{$clr_workDate}">
										<option value="{$select_workDate}" selected>{$select_workDate_display}
										{$opt_date}
									</select>
								</td>
							</form>
							<form method="POST" action="{$_SERVER['PHP_SELF']}">
									{$OPT_DATA}
								<td width="200px">
									<input type="text" name="select_customerName" style="width:200px;height:20px;"
										placeholder="得意先名検索" onfocus="this.placeholder=''" onblur="this.placeholder='得意先名検索'"
										value="{$select_customerName}">
								</td>
								<td width="60px">
									<input type="submit" value="検索" style="width:100%;">
								</td>
							</form>
							<form method="POST" action="{$_SERVER['PHP_SELF']}">
								<td width="60px">
									<input type="submit" value="クリア" style="width:60px;">
								</td>
								<td width="37">
						EOD;
							if($page > 0){			//戻るページがあれば下記を表示
						echo <<<EOD
							<form method="POST" action="{$_SERVER['PHP_SELF']}">
								<td width="25px">
									{$OPT_DATA}
									<input type="hidden" name="add" value="-1">
									<input type="hidden" name="page" value="{$page}">
									<input type="submit" value="<" style="width:25px;">
								</td>
							</form>
						EOD;
							}else{
						echo <<<EOD
								<td width="25px"></td>
						EOD;
							}
							if($page_add1 < $cnt){	//進めるページがあれば下記を表示
						echo <<<EOD
							<form method="POST" action="{$_SERVER['PHP_SELF']}">
								<td width="25px">
									{$OPT_DATA}
									<input type="hidden" name="add" value="1">
									<input type="hidden" name="page" value="{$page}">
									<input type="submit" value=">" style="width:25px;">
								</td>
							</form>
						EOD;
							}else{
						echo <<<EOD
								<td width="25px"></td>
						EOD;
							}
						echo <<<EOD
								<td width="150" align="right"> {$page_add1} / {$cnt}ページ {$cnt_all}件<td>
							   </tr>
						</table>
						<table bgcolor="#a9a9a9" cellspacing="1px" style="font-size:12px;" >
							<tr bgcolor="#D3D3D3" style="height:24px;" align="center">
								<td width="100">部署</td>
								<td width="90">担当者</td>
								<td width="90">実施日</td>
								<td width="250">得意先</td>
								<td width="400">内容(先頭50文字)</td>
								<td width="60">修正</td>
							</tr>
						EOD;
							$sql = "SELECT a.id
											,b.sectionName
											,c.staffName
											,d.customerName
											,a.workDate
											,a.class
											,a.comment
										from dailyreport AS a
									left join Msection AS b 
										ON a.sectionCode = b.sectionCode
									left join Mstaff AS c 
										ON a.staffCode = c.staffCode
									left join Mcustomer AS d 
										ON a.customerCode = d.customerCode
									WHERE a.id > 0
									$where_and_section
									$where_and_staff
									$where_and_customer
									$where_and_class
									$where_and_work
									ORDER BY workDate DESC
									LIMIT 5 OFFSET $offset
									;";
							$res = mysqli_query($conn,$sql) or die("er select $sql");
							while($row = mysqli_fetch_array($res)){
									$id = $row["id"];
									$sectionName = $row["sectionName"];
									$staffName = $row["staffName"];
									$workDate = $row["workDate"];
									$workDate = date("Y/n/j",strtotime($workDate));
									$class = $row["class"];
									$customerName = $row["customerName"];
									if($class == 2){
										$customerName = '社内作業';
									}
									$comment = $row["comment"];
										$comment = substr($comment,0,50);
						echo <<<EOD
							<tr bgcolor="white" style="height:24px;" align="center">
								<td>{$sectionName}</td>
								<td>{$staffName}</td>
								<td>{$workDate}</td>
								<td align="left">{$customerName}</td>
								<td align="left">{$comment}</td>
							<form method="POST" action="dailyreport_input.php">
									{$OPT_DATA}
									<input type="hidden" name="set_id" value="{$id}">
								<td>
									<input type="submit" style="width:60px;" value="修正">
								</td>
							</form>
							</tr>
						EOD;
							}
					
						echo <<<EOD
						</table>
						</body>
						</html>
						EOD;
						
						?>
						
<解説>
38行目等
<option>タグに style="color:#2E2E2E;"で文字の色(少し薄めの黒)が設定されてます。 これは検索項目名を薄めのグレーで表示しており、このstyleの設定がないとプルダウンに 表示される文字も薄めのグレーで表示されてしまうための対処です。

354行目
action="dailyreport_input.php"が入力画面に遷移する設定にしてます。これにより一覧表より 明細の確認や修正が可能となります。