webdata
DX推進をサポートする技術者向け情報提供サイト
　お問い合わせ　 プロフィール

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

＞TOP　＞初心者向けPHP・データベース入門　＞3.1 マスターの作成

【PHP入門】マスターの作成

　2023-06-10　（更新日:2023-06-10）

＜学習する内容＞

　日報で利用する部署・担当者マスターの作成によりリレーションを理解する

１）マスターの設計

訪問件数や履歴、商談の流れをスムーズに把握するためにも日報には部署検索、担当者検索が必須となります。 そのためのマスターを作成します。
部署マスター　テーブル名：Msection
項目 カラム名 データ型 長さ 備考
部署コード sectionCode smallint 4 primary key
部署名 sectionName varchar 20
　primary key　　 ：プライマリーキー（主キー）
＜テーブルの作成＞
MariaDB [(sales)]> create table Msection ( sectionCode smallint(4) primary key,sectionName varchar(20));

担当者マスター　テーブル名：Mstaff
項目 カラム名 データ型 長さ 備考
担当者コード staffCode smallint 4 primary key
部署コード sectionCode smallint 4 部署マスターとリレーション
姓 lastName varchar 10
名 firstName varchar 10
担当者名 lastName varchar 20
＜テーブルの作成＞
MariaDB [(sales)]> create table Mstaff ( staffCode smallint(4) primary key,sectionCode smallint(4)
,lastName varchar(20),firstName varchar(10),staffName varchar(20));

２）部署マスター登録画面の作成

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

サンプルプログラム名：section_input.php 
						<?php

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

						//POSTの受信
							$fnc = filter_input(INPUT_POST, 'fnc');
							$set_sectionCode = filter_input(INPUT_POST, 'set_sectionCode');
							$set_sectionName = filter_input(INPUT_POST, 'set_sectionName');
						
						//変数リセット
							$mess = null;
							
						//データの登録
						if($fnc == 1){
							if(!empty($set_sectionCode) and !empty($set_sectionName)){			//データが入力されていればSQLを実行
								$sql = "INSERT INTO Msection
												(
													sectionCode
													,sectionName
												) VALUES (
													$set_sectionCode
													,'$set_sectionName'
												) ON DUPLICATE KEY UPDATE
													sectionName = '$set_sectionName'
												;";
								mysqli_query($conn,$sql) or die ("error $sql");
								$mess = '新規登録もしくは修正されました。';
							}else{
								if(!empty($set_sectionCode)){
									$mess = '部署コードを入力してください。';
								}
								if(!empty($set_sectionName)){
									$mess = '部署名を入力してください。';
								}
							}
						}
						
						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>
						部署一覧表　{$mess}<br>
						<table bgcolor="#a9a9a9" cellspacing="1px" style="font-size:12px;" >
							<tr bgcolor="#D3D3D3" style="height:24px;" align="center">
								<td width="90">部署コード</td>
								<td width="200">部署名</td>
								<td width="60">登録</td>
							</tr>
							<tr bgcolor="white" style="height:24px;" align="left">
							<form method="POST" action="{$_SERVER['PHP_SELF']}">
									<input type="hidden" name="fnc" value="1">
								<td>
									<input type="text" name="set_sectionCode" style="width:90px;">
								</td>
								<td>
									<input type="text" name="set_sectionName" style="width:200px;">
								</td>
								<td>
									<input type="submit" value="新規" style="width:60px;">
								</td>
							</form>
							</tr>
						EOD;
						
						//DBからデータを抽出
							$sql = "SELECT sectionCode
											,sectionName
									FROM Msection;";
							$res = mysqli_query($conn,$sql) or die("エラー $sql");
							while($row = mysqli_fetch_array($res)){
								$sectionCode = $row["sectionCode"];
								$sectionName = $row["sectionName"];
								
						echo <<<EOD
							<tr bgcolor="white" style="height:24px;" align="left">
							<form method="POST" action="{$_SERVER['PHP_SELF']}">
									<input type="hidden" name="fnc" value="1">
									<input type="hidden" name="set_sectionCode" value="{$sectionCode}">
								<td width="100">
									{$sectionCode}
								</td>
								<td>
									<input type="text" name="set_sectionName" style="width:198px;" value="{$sectionName}">
								</td>
								<td>
									<input type="submit" value="修正" style="width:60px;">
								</td>
							</form>
							</tr>
						EOD;
							}
						
						echo <<<EOD
							</table>
						</body>
						
						</html>
						EOD;

						?>

３）担当マスターの作成とリレーション

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

サンプルプログラム名：staff_input.php 
						<?php

						//DBへの接続
						    include('/xampp/data/conn.php');
						//POSTの受信
							$fnc = filter_input(INPUT_POST, 'fnc');
							$set_staffCode = filter_input(INPUT_POST, 'set_staffCode');
							$set_sectionCode = filter_input(INPUT_POST, 'set_sectionCode');
							$set_lastName = filter_input(INPUT_POST, 'set_lastName');
							$set_firstName = filter_input(INPUT_POST, 'set_firstName');

						//変数リセット
							$mess = null;
						
						//データの登録
						if($fnc == 1){
							if(!empty($set_sectionCode) and !empty($set_staffCode) and !empty($set_lastName) and !empty($set_firstName)){			//データが入力されていればSQLを実行
									$set_staffName = $set_lastName."　".$set_firstName;
								$sql = "INSERT INTO Mstaff
												(
													staffCode
													,sectionCode
													,lastName
													,firstName
													,staffName
												) VALUES (
													$set_staffCode
													,$set_sectionCode
													,'$set_lastName'
													,'$set_firstName'
													,'$set_staffName'
												) ON DUPLICATE KEY UPDATE
													sectionCode = $set_sectionCode
													,lastName = '$set_lastName'
													,firstName = '$set_firstName'
													,staffName = '$set_staffName'
												;";
								mysqli_query($conn,$sql) or die ("error $sql");
								$mess = '新規登録もしくは修正されました。';
							}else{
								$mess = '各情報を入力してください。';
							}
						}
						
						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>
						営業担当者一覧表　{$mess}<br>
						<table bgcolor="#a9a9a9" cellspacing="1px" style="font-size:12px;" >
							<tr bgcolor="#D3D3D3" style="height:24px;" align="center">
								<td width="200">部署名</td>
								<td width="90">担当者コード</td>
								<td width="100">姓</td>
								<td width="100">名</td>
								<td width="150">担当者名</td>
								<td width="60">登録</td>
							</tr>
							<tr bgcolor="white" style="height:24px;" align="left">
							<form method="POST" action="{$_SERVER['PHP_SELF']}">
									<input type="hidden" name="fnc" value="1">
								<td>
									<select name="set_sectionCode" style="width:200px;">
										<option value="" selected>
										<option value="101">営業1課
										<option value="102">営業2課
										<option value="103">営業3課
									</select>
								</td>
								<td>
									<input type="text" name="set_staffCode" style="width:90px;">
								</td>
								<td>
									<input type="text" name="set_lastName" style="width:100px;">
								</td>
								<td>
									<input type="text" name="set_firstName" style="width:100px;">
								</td>
								<td></td>
								<td>
									<input type="submit" value="新規" style="width:60px;">
								</td>
							</form>
							</tr>
						EOD;
						
						//DBからデータを抽出
							$sql = "SELECT a.sectionCode
											,a.staffCode
											,a.lastName
											,a.firstName
											,a.staffName
											,b.sectionName
									FROM Mstaff as a
									LEFT JOIN Msection as b 
										ON a.sectionCode = b.sectionCode;";
							$res = mysqli_query($conn,$sql) or die("エラー $sql");
							while($row = mysqli_fetch_array($res)){
								$sectionCode = $row["sectionCode"];
								$staffCode = $row["staffCode"];
								$staffName = $row["staffName"];
								$lastName = $row["lastName"];
								$firstName = $row["firstName"];
								$sectionName = $row["sectionName"];
								
						echo <<<EOD
							<tr bgcolor="white" style="height:24px;" align="left">
							<form method="POST" action="{$_SERVER['PHP_SELF']}">
									<input type="hidden" name="fnc" value="1">
									<input type="hidden" name="set_staffCode" value="{$staffCode}">
								<td>
									<select name="set_sectionCode" style="width:200px;">
										<option value="{$sectionCode}" selected>{$sectionName}
										<option value="101">営業1課
										<option value="102">営業2課
										<option value="103">営業3課
									</select>
								</td>
								<td>
									{$staffCode}
								</td>
								<td>
									<input type="text" name="set_lastName" style="width:100px;" value="{$lastName}">
								</td>
								<td>
									<input type="text" name="set_firstName" style="width:100px;" value="{$firstName}">
								</td>
								<td>{$staffName}</td>
								<td>
									<input type="submit" value="修正" style="width:60px;">
								</td>
							</form>
							</tr>
						EOD;
							}
						
						echo <<<EOD
							</table>
						</body>
						
						</html>
						EOD;

						?>

＜解説＞ 95行～103行目のSQL文
これがリレーションのSQLとなります。

SELECT a.sectionCode
　　　　 ,a.staffCode
　　　　 ,a.lastName
　　　　 ,a.firstName
　　　　 ,a.staffName
　　　　 ,b.sectionName
FROM Mstaff as a
LEFR JOIN Msection as b
ON a.sectionCode = b.sectionCode;

Mstaff　に　a というエイリアス（仮称・通称）を設定しています。
Msection　に　b というエイリアス（仮称・通称）を設定しています。
それぞれのテーブルのカラム a の sectionCode と b の sectionCode にて left joinでリレーションを
張るSQL文となっています。

70行～75行のhtmlのselect文
<select name="set_sectionCode" style="width:200px;">
　　<option value="" selected>
　　<option value="101">営業1課
　　<option value="102">営業2課
　　<option value="103">営業3課
</select>
これは、記述されているoptionタグをプルダウンで選択できる記述です。ブラウザ上で営業2課を選択するとPOST変数でset_sectionCodeが102で送信されます。
今回直接営業部署を記載してますが、部署が増えたりするとプログラムを書き直す必要があります。SQLを使って自動生成方法を下記に記します。
$sql = "SELECT sectionCode ,sectionName FROM Msection;";
$res = mysqli_query($conn,$sql) or die("error $sql");
while($row = mysqli_fetch_array($res)){
　　$sectionCode = $row["sectionCode"];
　　$sectionName = $row["sectionName"];
　　$opt_section_tmp = '<option value="".$sectionCode.'">'.$sectionName;
　　$opt_section = $opt_section_tmp.$opt_section;　　//※$変数1と\変数2は「.」ドットでつなげます。
}
これにより
<select name="set_sectionCode" style="width:200px;">
　　<option value="" selected>
　　{$opt_section}
</select>
と{$opt_section}のみの記述で部署の増減かかわらずプログラムの記述を変えることなく対応できます。
※$opt_section_tmp = '<option value="".$sectionCode.'">'.$sectionName;
　$opt_section = $opt_section_tmp.$opt_section;
　記述ですが下記のように1行で書き替えることもできます。
　$opt_section .= '<option value="".$sectionCode.'">'.$sectionName;
　.= この表記で$opt_sectionにwhileの間 '<option value="'.$sectionCode.'">'.$sectionName; をつなげて行きます。

これらを反映させたサンプルプログラム(staff_input.php)は下記となります。
						<?php

						//DBへの接続
						    include('/xampp/data/conn.php');
						//POSTの受信
							$fnc = filter_input(INPUT_POST, 'fnc');
							$set_staffCode = filter_input(INPUT_POST, 'set_staffCode');
							$set_sectionCode = filter_input(INPUT_POST, 'set_sectionCode');
							$set_lastName = filter_input(INPUT_POST, 'set_lastName');
							$set_firstName = filter_input(INPUT_POST, 'set_firstName');

						//変数リセット
							$mess = null;
							$opt_section = null;
						//データの登録
						if($fnc == 1){
							if(!empty($set_sectionCode) and !empty($set_staffCode) and !empty($set_lastName) and !empty($set_firstName)){			//データが入力されていればSQLを実行
									$set_staffName = $set_lastName."　".$set_firstName;
									$set_sectionCode = mb_convert_kana($set_sectionCode, 'a');		//半角化
									$set_staffCode = mb_convert_kana($set_staffCode, 'a');			//半角化
								$sql = "INSERT INTO Mstaff
												(
													staffCode
													,sectionCode
													,lastName
													,firstName
													,staffName
												) VALUES (
													$set_staffCode
													,$set_sectionCode
													,'$set_lastName'
													,'$set_firstName'
													,'$set_staffName'
												) ON DUPLICATE KEY UPDATE
													sectionCode = $set_sectionCode
													,lastName = '$set_lastName'
													,firstName = '$set_firstName'
													,staffName = '$set_staffName'
												;";
								mysqli_query($conn,$sql) or die ("error $sql");
								$mess = '登録修正されました。';
							}else{
								$mess = '各情報を入力してください。';
							}
						}
						
						//部署optionの生成$opt_section
						$sql = "SELECT sectionCode
										,sectionName
								FROM Msection;";
						$res = mysqli_query($conn,$sql) or die("error $sql");
						while($row = mysqli_fetch_array($res)){
							$sectionCode = $row["sectionCode"];
							$sectionName = $row["sectionName"];
							$opt_section .= '<option value="'.$sectionCode.'">'.$sectionName;
						}
						
						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>
						営業担当者一覧表　{$mess}<br>
						<table bgcolor="#a9a9a9" cellspacing="1px" style="font-size:12px;" >
							<tr bgcolor="#D3D3D3" style="height:24px;" align="center">
								<td width="200">部署名</td>
								<td width="90">担当者コード</td>
								<td width="100">姓</td>
								<td width="100">名</td>
								<td width="150">担当者名</td>
								<td width="60">登録</td>
							</tr>
							<tr bgcolor="white" style="height:24px;" align="left">
							<form method="POST" action="{$_SERVER['PHP_SELF']}">
									<input type="hidden" name="fnc" value="1">
								<td>
									<select name="set_sectionCode" style="width:200px;">
										<option value="" selected>
										{$opt_section}
									</select>
								</td>
								<td>
									<input type="text" name="set_staffCode" style="width:90px;">
								</td>
								<td>
									<input type="text" name="set_lastName" style="width:100px;">
								</td>
								<td>
									<input type="text" name="set_firstName" style="width:100px;">
								</td>
								<td></td>
								<td>
									<input type="submit" value="新規" style="width:60px;">
								</td>
							</form>
							</tr>
						EOD;
						
						//DBからデータを抽出
							$sql = "select a.sectionCode,
											a.staffCode,
											a.lastName,
											a.firstName,
											a.staffName,
											b.sectionName
									FROM Mstaff as a
									LEFT JOIN Msection as b 
										ON a.sectionCode = b.sectionCode;";
							$res = mysqli_query($conn,$sql) or die("エラー $sql");
							while($row = mysqli_fetch_array($res)){
								$sectionCode = $row["sectionCode"];
								$staffCode = $row["staffCode"];
								$staffName = $row["staffName"];
								$lastName = $row["lastName"];
								$firstName = $row["firstName"];
								$sectionName = $row["sectionName"];
								
						echo <<<EOD
							<tr bgcolor="white" style="height:24px;" align="left">
							<form method="POST" action="{$_SERVER['PHP_SELF']}">
									<input type="hidden" name="fnc" value="1">
									<input type="hidden" name="set_staffCode" value="{$staffCode}">
								<td>
									<select name="set_sectionCode" style="width:200px;">
										<option value="{$sectionCode}" selected>{$sectionName}
										{$opt_section}
									</select>
								</td>
								<td>
									{$staffCode}
								</td>
								<td>
									<input type="text" name="set_lastName" style="width:100px;" value="{$lastName}">
								</td>
								<td>
									<input type="text" name="set_firstName" style="width:100px;" value="{$firstName}">
								</td>
								<td>{$staffName}</td>
								<td>
									<input type="submit" value="修正" style="width:60px;">
								</td>
							</form>
							</tr>
						EOD;
							}
						
						echo <<<EOD
							</table>
						</body>
						
						</html>
						EOD;

						?>

４）失敗で理解する

下記のような入力をするとエラーがでます。

これは得意先コードが数値(int)のカラムであるのに全角の数字（文字として識別）のためです。
＜エラー画面＞

＜対応方法＞
全角の文字が入力されても自動的に半角に変換する関数で対応します。
部署マスターの場合部署コードがintなので、section_input.phpの17行の前に下記の関数を記述します。
$set_sectionCode = mb_convert_kana($set_sectionCode, 'a');
これは$set_sectionCodeのデータの英数字を半角に変換します。
担当者マスターは、担当者コードがintなのでstaff_input.phpの19行の前に下記の関数を記述します。
$set_staffCode = mb_convert_kana($set_staffCode, 'a');
＜よく使う変換＞
全角→半角
r英字
n数字
a英数字
s空白
kカタカナ
hひらがな→カタカナ
 半角→全角
R英字
N数字
A英数字
S空白
Kカタカナ
Hカタナカ→ひらがな


日報登録画面の作成をします。
＜初心者向けPHP・DB入門メニュー＞
はじめに
0. 1PHP・DBの学習方法
環境構築
1. 1Webサーバ・DBのインストール
1. 2Webサーバ・DBの設定
得意先一覧の作成
2. 1HTMLで簡単な表作成
2. 2DBの基本操作
2. 3PHPからDBを操作
2. 4PHPとDBを使った得意先一覧作成
2. 5PHPのデータ送受信
2. 6得意先登録画面の作成
2. 7得意先登録SQL on duplicate key
2. 8得意先検索＆ソート
2. 9得意先一覧ページ指定・表示
2.10PHP include関数の活用
2.11PHP CSVデータの取り込み
日報の作成
3. 1マスターの作成とリレーション
3. 2日報登録画面の作成
3. 3日報一覧画面の作成

＜技術者向け技術情報メニュー＞
メールの送信
メールの受信
OAuth2認証よるメール受信
CSVの読み込み・書き込み
PDF作成
ファイルのダウンロード
ファイルのアップロード
phpプログラムのジョブ利用
認証とページ滞在時間制限
Xamppのインストール
Lamppのインストール
Xamppのsqlserverへの接続
Lamppのsqlserverへの接続
RSAキーを使った外部サーバへの接続
最終レコードの抽出SQL文
DB移行時の照合順序は重要
記号、予約語データの取扱い
特殊文字・絵文字データの取扱い
DNS・A・MXレコードの概要
Kintoneの外部データ連携
copyright webdata.jp