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

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

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

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

 2023-06-10 (更新日:2024-06-23)

<学習する内容>

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

1)マスターの設計

訪問件数や履歴、商談の流れをスムーズに把握するためにも日報には部署検索、担当者検索が必須となります。 そのためのマスターを作成します。
部署マスター テーブル名: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
担当者名 staffName varchar 20
<テーブルの作成>
MariaDB [(sales)]> create table Mstaff ( staffCode smallint(4) primary key,sectionCode smallint(4)
,lastName varchar(20),firstName varchar(10),staffName varchar(20));

2)部署マスター登録画面の作成

部署マスター
下記のサンプルプログラムをコピーもしくはダウンロードし指定のフォルダーに配置してください。
ファイル名: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;

						?>
						

3)担当マスターの作成とリレーション

担当マスター
下記のサンプルプログラムをコピーもしくはダウンロードし指定のフォルダーに配置してください。
ファイル名: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;

						?>
						

4)失敗で理解する

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

これは得意先コードが数値(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カタナカ→ひらがな


日報登録画面の作成をします。