２０．商品管理データベースを作ってみる

２０．商品管理データベースを作ってみる
　　商品管理データベースを例にデータベースの設計、PHPによるプログラミングを学習します。
　　以下のような、商品管理票について考えてみましょう。

表１

ID	商品コード	商品名	単価	在庫数	メーカー名
１	１０１	消しゴム	５０	２５	株式会社トンボ鉛筆
２	１０２	鉛筆（HB）	７０	１００	三菱鉛筆株式会社
３	１０３	消しゴム	５０	３０	株式会社サクラクレパス
４	１０４	鉛筆（H）	８０	５０	三菱鉛筆株式会社
５	１０５	シャープペンシル	１００	２０	株式会社パイロットコーポレーション
｜	｜	｜	｜	｜	｜

　１）表の正規化を行います。
　　　①第一正規形
　　　　　繰り返し同じ項目が現れる場合は、別表にします。
　　　　　商品名に繰り返しがあるので別表にします。（商品表）

表２

ID	商品コード	商品名
１	１０１	消しゴム
２	１０２	鉛筆（HB）
３	１０３	消しゴム
４	１０４	鉛筆（H）
５	１０５	シャープペンシル
｜	｜	｜

　　　　　メーカ名に同じ切り返しがあるので別表にします。（メーカ表）

表３

ID	メーカー名
１	株式会社トンボ鉛筆
２	三菱鉛筆株式会社
３	株式会社サクラクレパス
４	株式会社パイロットコーポレーション
｜	｜

　　　　　商品管理表は、以下のようになります。（商品管理表）

表４

ID	商品コード	単価	在庫数	メーカーID
１	１０１	５０	２５	１
２	１０２	７０	１００	２
３	１０３	５０	３０	３
４	１０４	８０	５０	２
５	１０５	１００	２０	４
｜	｜	｜	｜	｜

　　　②第二正規形
　　　　　複合キーがあればそれぞれのキーが従属しているかを調べます。
　　　　　簡単に言うと複数の主キー（同じ値が存在しない列）にできる候補があれば、別表にするか取り除きます。
　　　　　上記の商品表は、「商品名」は「ID」と「商品コード」に従属しているので「ID」は削除できます。（商品表）

表５

商品コード	商品名
１０１	消しゴム
１０２	鉛筆（HB）
１０３	消しゴム
１０４	鉛筆（H）
１０５	シャープペンシル
｜	｜

　　　　③第三正規形
　　　　　　推移的従属しているかどうかを調べます。
　　　　　　簡単に言うと非キーで識別できる列があれば、それを別表にするか取り除きます。
　　　　　上記の商品管理表の「価格」は、「ID」と「商品コードID」に従属しているので別表にできます。（価格表）
　　　　　上記の商品管理表の「在庫数」は、「ID」と「商品コードID」に従属しているので別表にできます。（在庫表）
　　　　　上記の商品管理表の「メーカID」は、「ID」と「商品コード」に従属しているので別表にします。（商品メーカ表）

　　　　　表６　価格表

　　　　　　　表７　在庫表

　　　　表８　商品メーカ表

ID	商品コード	単価
１	１０１	５０
２	１０２	７０
３	１０３	５０
４	１０４	８０
５	１０５	１００
｜	｜	｜

ID	商品コード	在庫数
１	１０１	２５
２	１０２	１００
３	１０３	３０
４	１０４	５０
５	１０５	２０
｜	｜	｜

ID	商品コード	メーカID
１	１０１	１
２	１０２	２
３	１０３	３
４	１０４	２
５	１０５	４
｜	｜	｜

　２）データベースを作成します。
　　①データベース名：product_db
　　②テーブルの作成
　　　・メーカテーブル：maker_tbl（表３）

列名	データ型	属性
id	MEDIUMINT	UNSIGNED　NOT　NULL　AUTO_INCREMENT　 PRIMARY　KEY
makerName	VARCHAR(100)	NOT　NULL

　　　・価格テーブル：price_tbl（表６）

列名	データ型	属性
id	MEDIUMINT	UNSIGNED　NOT　NULL　AUTO_INCREMENT　 PRIMARY　KEY
productId	VARCHAR(10)	NOT　NULL
unitPrice	VARCHAR(10)	NOT　NULL

　　　・商品メーカテーブル：productmaker_tbl （表８）

列名	データ型	属性
id	MEDIUMINT	UNSIGNED　NOT　NULL　AUTO_INCREMENT　 PRIMARY　KEY
productId	VARCHAR(10)	NOT　NULL UNIQUE
makerId	VARCHAR(10)	NOT　NULL

　　　・商品テーブル：product_tbl （表２）

列名	データ型	属性
id	MEDIUMINT	UNSIGNED　NOT　NULL　AUTO_INCREMENT　 PRIMARY　KEY
productId	VARCHAR(10)	NOT　NULL UNIQUE
productName	VARCHAR(50)	NOT　NULL

　　　・在庫テーブル：stock_tbl（表７）

列名	データ型	属性
id	MEDIUMINT	UNSIGNED　NOT　NULL　AUTO_INCREMENT　 PRIMARY　KEY
productId	VARCHAR(10)	NOT　NULL
stockNumber	VARCHAR(10)	NOT　NULL

　３）表５（商品テーブル）に商品データを登録するプログラムを作成します。
　　①商品データを登録するフォームのプログラム（productTou.html）

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>商品登録</title>
</head>
<body>
商品登録<br>
<form id="form1" name="form1" method="post" action="productTou.php">
商品コード：
<input type="text" name="productCode"><br>
商品名：
<input type="text" name="product" size="50">
<input type="submit" name="productTou" value="登録">
</form>
</body>
</html>

　　②商品データを登録するPHPのプログラム（productTou.php）

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>商品登録</title>
</head>
<body>
<?php
  $productCode=$_POST['productCode'];
  $product=$_POST['product'];
  $conn=mysqli_connect('localhost','******','******');
  mysqli_select_db($conn,'product_db');
  
  //すでに同じ商品が登録されているかをチェックします。
  $sql="SELECT * FROM product_tbl WHERE productId='{$productCode}';";
  if($result=mysqli_query($conn,$sql)){
    if(mysqli_num_rows($result)!=0){  //商品コードが同じの場合は、一つのレコードが読み込まれます。
      $row=mysqli_fetch_array($result,MYSQL_ASSOC);
      echo "商品コード「{$row['productId']}」、商品名「{$row['productName']}」は、すでに登録されています。<br>\n";
      echo "<span style=\"color:red;\">もう一度、確認して登録してください。</span><br>\n";
      echo "商品登録<br>
            <form id=\"form1\" name=\"form1\" method=\"post\" action=\"productTou.php\">\n
            商品コード：\n
            <input type=\"text\" name=\"productCode\"><br>\n
            商品名：\n
            <input type=\"text\" name=\"product\" size=\"50\">\n
            <input type=\"submit\" name=\"productTou\" value=\"登録\">\n
            </form>\n";
    }
    else{  //同じ商品コードが存在しない場合は、登録します。
      $sql="INSERT INTO product_tbl values(null,'{$productCode}','{$product}');";
      if($result=mysqli_query($conn,$sql)){
        echo "以下の商品を登録しました。<br>\n";
        echo "　　　商品コード：{$productCode}<br>\n";
        echo "　　　商品名：{$product}<br>\n";
      }
      else{
        echo "データの書き込みに失敗しました。\n";
      }
    }
  }
  else{
    echo "データの抽出に失敗しました。\n";
  }
  
  //登録情報を表示しています。
  $sql="SELECT * FROM product_tbl;";
  if($result=mysqli_query($conn,$sql)){
    if(mysqli_num_rows($result)!=0){
      echo "<br>現在登録されている商品は、以下です。<br>\n";
      echo "<table border=\"1\">\n
            <tr>\n
            <th>商品コード</th><th>商品名</th>\n
            </tr>\n";
      while($row=mysqli_fetch_array($result,MYSQL_ASSOC)){
        echo "<tr>\n
              <td>{$row['productId']}</td><td>{$row['productName']}</td>\n
              </tr>\n";
      }
      echo "</table>\n";
    }
    else{
      echo "商品は、登録されていません。\n";
    }
  }
  else{
    echo "データの抽出に失敗しました。\n";
  }
  mysqli_close($conn);
?>
</body>
</html>

　４）表３（メーカーテーブル）にメーカーを登録するプログラムを作ります。
　　　３）を参考に作ってみましょう。（ファイル名：makerTou.html、makerTou.php）

　５）表５（商品テーブル）の商品データを削除するプログラムを作ります。
　　　データを削除する場合は、複数のデータを同時に削除する場合を考えて下表のように各行に削除の有無のチェックできるようにチェックボックスを配置します。

削除有無	商品コード	商品名
	E1001	鉛筆（HB）
	E1002	鉛筆（H)
	E1003	ボールペン（黒）
	E1004	シャープペンシル
	E1005	消しゴム

　　　　この場合、商品コード「E1002」、「E1004」の商品が削除されることになります。生成されたHTMLコードは、以下のようになります。

<form method="POST" action="productDelete.php">
<table border="1">
       <tr>
         <td>削除有無</td>
         <td>商品コード</td>
         <td>商品名</td>
        </tr>
       <tr>
         <td><input type="checkbox" name="productCode[]" value="E1001,鉛筆（HB）"></td>
         <td>E1001</td>
         <td>鉛筆（HB）</td>
        </tr>
       <tr>
         <td><input type="checkbox" name="productCode[]" value="E1002,鉛筆（H)"></td>
         <td>E1002</td>
         <td>鉛筆（H)</td>
       </tr>
       <tr>
         <td><input type="checkbox" name="productCode[]" value="E1003,ボールペン（黒）"></td>
         <td>E1003</td>
         <td>ボールペン（黒）</td>
       </tr>
       <tr>
         <td><input type="checkbox" name="productCode[]" value="E1004,シャープペンシル"></td>
         <td>E1004</td>
         <td>シャープペンシル</td>
       </tr>
       <tr>
         <td><input type="checkbox" name="productCode[]" value="E1005,消しゴム"></td>
         <td>E1005</td>
         <td>消しゴム</td>
        </tr>
     </table>
</form>

　　　　<input type="checkbox" name="productCode[]" value="E1005,消しゴム">は、チェックボックスですが、チェックされていない項目は、送信されず、チェックされた項目のみ
　　　送信されます。
　　　　複数選択されることを考えて「name」属性の値を配列にして、配列で送信されるようにしています。「value」属性の値は、削除する商品コードと商品名が「，（カンマ）」区切り
　　　で配置できるようにしています。
　　　　以下は、その条件になるようにPHPで書いたものです。
　　①商品データを削除項目を選択するプログラム（ファイル名：productDelSelect.php）

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>商品削除選択</title>
</head>
<body>
<?php
	echo "登録商品の削除メニュー<br>\n";
	echo "削除する商品をチェックしてください。<br>\n";
	$conn=mysqli_connect('localhost','******','******');
	mysqli_select_db($conn,'product_db');
	$sql="SELECT * FROM product_tbl;";
	if($result=mysqli_query($conn,$sql)){
		if(mysqli_num_rows($result)!=0){
			echo "<form method=\"POST\" action=\"productDelete.php\">\n";
			echo "<table border=\"1\">\n";
			echo "<tr><td>削除有無</td><td>商品コード</td><td>商品名</td></tr>\n";
			while($row=mysqli_fetch_array($result,MYSQL_ASSOC)){
				echo "<tr>\n";
				echo "<td><input type=\"checkbox\" name=\"productCode[]\" value=\"{$row['productId']},{$row['productName']}\"></td>\n
				      <td>".$row['productId']."</td>\n
				      <td>".$row['productName']."</td>\n";
				echo "</tr>\n";
			}
			echo "</table>\n";
			echo "<input type=\"submit\" value=\"削除\">\n";
			echo "</form>\n";
		}
		else{
			echo "商品データは、登録されていません。<br>\n";
		}
	}
	else{
		echo "条件に合ったデータは存在しません<br>\n";
	}
	mysqli_close($conn);
?>
</body>
</html>

　　②実際にデータを削除するプログラム（ファイル名：productDelete.php）
　　　　ブラウザから送信されて来るデータは、「productCode[]=E1002,鉛筆（H)&productCode[]=E1004,シャープペンシル」になります。
　　　　このデータをPHPで受け取った場合は、「$_POST['productCode'][0]」に「E1002,鉛筆（H)」が代入され、「$_POST['productCode'][1]」に「E1004,シャープペンシル」が
　　　代入されます。これを一つずつ「for」を使って取り出してもかまいませんが、「foreach」を使ったほうが便利なのでこちらを使います。「foreach」は、配列の長さ分を
　　　自動的に繰り返しながら値を取り出すことができます。
　　　　取り出されたデータは、「E1002,鉛筆（H)」のように「，（カンマ）」区切りになっていますので、「explode(",",$chk)」を使ってデータを取り出しています。
　　　　最終的に「$chk[0]」に商品コード（E1002）、「$chk[1]」に商品名（鉛筆（H)）が代入されます。
　　　　SQLの「DELETE」コマンドは、条件で指定したレコードが存在する場合は、実際にその行を削除しますが、削除条件ですでに削除した行や削除条件に一致しない
　　　行を削除しても「TRUE（１）」を返してきます。（エラーの場合は、「FALSE(0)」を返します。）
　　　　したがって「mysqli_query()」を実行しても本当に削除されたのかそうでなかったのかがわkりません。そこでレコードに更新、追加、削除処理を行ったときにその処理を行った
　　　行数を返す関数「mysqli_affected_rows()」を使って判断します。実際に行が削除されない場合は、「０」を返します。

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>商品削除</title>
</head>
<body>
<?php
	$conn=mysqli_connect('localhost','******','******');
	mysqli_select_db($conn,'product_db');
	if(isset($_POST['productCode'])){
		echo "以下の商品を削除しました。<br>\n";
		foreach($_POST['productCode'] as $chk){
			$chk=explode(",",$chk);
			$sql="DELETE FROM product_tbl WHERE productId='{$chk[0]}';";
			if(mysqli_query($conn,$sql)){
				if(mysqli_affected_rows($conn)==0){
					echo "商品：{$chk[0]}:{$chk[1]}は、既に削除されています。<br>\n";
				}
				else{
					echo "商品：{$chk[0]}:{$chk[1]}は、削除しました。<br>\n";
				}
			}
			else{
				echo "商品：{$chk[0]}:{$chk[1]}は、削除できませんでいた。<br>\n";
			}
		}
	}
	else{
		echo "削除する商品が選択されていません。<br>\n";	
   $sql="SELECT * FROM product_tbl;";
		if($result=mysqli_query($conn,$sql)){
			if(mysqli_num_rows($result)!=0){
				echo "<form method=\"POST\" action=\"productDelete.php\">\n";
				echo "<table border=\"1\">\n";
				echo "<tr><td>削除有無</td><td>商品コード</td><td>商品名</td></tr>\n";
				while($row=mysqli_fetch_array($result,MYSQL_ASSOC)){
					echo "<tr>\n";
					echo "<td><input type=\"checkbox\" name=\"productCode[]\" value=\"{$row['productId']},{$row['productName']}\"></td>\n
						  <td>{$row['productId']}</td>\n
				    	  <td>{$row['productName']}</td>\n";
					echo "</tr>\n";
				}
				echo "</table>\n";
				echo "<input type=\"submit\" value=\"削除\">\n";
				echo "</form>\n";
			}
		}
		else{
			echo "条件に合ったデータは存在しません<br>\n";
		}
	}
	echo "<br>現在登録されている商品データは、以下のようになります。<br>\n";
	$sql="SELECT * FROM product_tbl;";
	if($result=mysqli_query($conn,$sql)){
		if(mysqli_num_rows($result)!=0){
			echo "<table border=\"1\">\n";
			echo "<tr><td>商品コード</td><td>商品名</td></tr>\n";
			while($row=mysqli_fetch_array($result,MYSQL_ASSOC)){
				echo "<tr>\n";
				echo "<td>{$row['productId']}</td>\n
					  <td>{$row['productName']}</td>\n";
				echo "</tr>\n";
			}
			echo "</table>\n";
		}
		else{
			echo "商品データは、登録されていません。<br>\n";
		}
	}
	else{
		echo "条件に合ったデータは存在しません<br>\n";
	}
	mysqli_close($conn);
?>
</body>
</html>

　６）表３（メーカーテーブル）のメーカーを削除するプログラムを作ります。
　　　５）を参考に作ってみましょう。（ファイル名：makerDelSelect.php、makerDelete.php）

　７）表５（商品テーブル）の商品データを更新するプログラムを作ります。
　　①更新データを表示するプログラム（ファイル名：productUpDateMenu.php）

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>商品データ更新</title>
</head>
<body>
<?php
	echo "登録商品の更新メニュー<br>\n";
	$conn=mysqli_connect('localhost','******','******');
	mysqli_select_db($conn,'product_db');
	$sql="SELECT * FROM product_tbl;";
	if($result=mysqli_query($conn,$sql)){
		if(mysqli_num_rows($result)!=0){
			echo "<form method=\"POST\" action=\"productUpdate.php\">\n";
			echo "<table border=\"1\">\n";
			echo "<tr><td>ID</td><td>商品コード</td><td>商品名</td></tr>\n";
			while($row=mysqli_fetch_array($result,MYSQL_ASSOC)){
				echo "<tr>\n";
				echo "<td>{$row['id']}<input type=\"hidden\" name=\"id[]\" value=\"{$row['id']}\"></td>\n
				      <td><input type=\"text\" name=\"productId[]\" value=\"{$row['productId']}\"></td>\n
				      <td><input type=\"text\" name=\"productName[]\" value=\"{$row['productName']}\"></td>\n";
				echo "</tr>\n";
			}
			echo "</table>\n";
			echo "<input type=\"submit\" value=\"更新\">\n";
			echo "</form>\n";
		}
	}
	else{
		echo "条件に合ったデータは存在しません<br>\n";
	}
	mysqli_close($conn);
?>
</body>
</html>

　　②商品データを更新するプログラム（プログラム名：productUpDate.php）

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>商品の更新</title>
</head>
<body>
<?php
	//フォームから送信されてきた商品コードにダブりがないかをチェックしている。
	//同じ商品コードが存在する場合は、その位置を表す配列「$sameCheck[]」に「１」を代入している。
	//それ以外は、「０」を代入している。
	for($i=0;$i<count($_POST['productId']);$i++)$sameCheck[$i]=0;
	for($i=0;$i<count($_POST['productId'])-1;$i++){
		for($j=$i+1;$j<count($_POST['productId']);$j++){
   		if($_POST['productId'][$i]==$_POST['productId'][$j]){
				$sameCheck[$i]=1;
				$sameCheck[$j]=1;
			}
		}
	}

	//商品コードのダブり状態を表示している。
	for($i=0;$i<count($_POST['productId']);$i++){
		echo $sameCheck[$i]."<br>\n";
	}
	$conn=mysqli_connect('localhost','******','******');
	mysqli_select_db($conn,'product_db');
	echo "以下の商品を更新しました。<br>\n";
	//商品コードと商品の更新
	for($i=0;$i<count($_POST['productId']);$i++){
		//商品コードの更新
		if($sameCheck[$i]!=1){  //商品コードにダブりがある場合、更新しないようにしている。
			$sql="SELECT * FROM product_tbl WHERE productId='{$_POST['productId'][$i]}';";
			if($result=mysqli_query($conn,$sql)){
				if(mysqli_num_rows($result)==0){
					$sql="UPDATE product_tbl SET productId='{$_POST['productId'][$i]}' WHERE id='{$_POST['id'][$i]}';";
					if(mysqli_query($conn,$sql)){
						echo "商品コード：{$_POST['productId'][$i]}に更新しました。<br>\n";
					}
					else{
						echo "商品コード：{$_POST['productId'][$i]}の更新に失敗しました。<br>\n";
					}
				}
				else{
					echo "商品コード：{$_POST['productId'][$i]}は、同じなので更新していません。<br>\n";
				}
			}
			else{
				echo "データの抽出に失敗しました<br>\n";
			}
		}
		else{
			echo "同じ商品コード「{$_POST['productId'][$i]}」が既に存在します。<br>\n";
		}

		//商品の更新
		$sql="SELECT * FROM product_tbl WHERE productName='{$_POST['productName'][$i]}';";
		if($result=mysqli_query($conn,$sql)){
			if(mysqli_num_rows($result)==0){ 
				$sql="UPDATE product_tbl SET productName='{$_POST['productName'][$i]}' WHERE id='{$_POST['id'][$i]}';";
				if(mysqli_query($conn,$sql)){
					echo "商品：{$_POST['productName'][$i]}に更新しました。<br>\n";
				}
				else{
					echo "商品：{$_POST['productName'][$i]}の更新に失敗しました。<br>\n";
				}
			}
			else{
				echo "商品：{$_POST['productName'][$i]}は、同じなので更新していません。<br>\n";
			}
		}
		else{
			echo "データの抽出に失敗しました。\n";
		}
	}

	//現在登録されている商品データの表示
	echo "<br />現在登録されている商品データは、以下のようになります。<br />\n";
	$sql="SELECT * FROM product_tbl;";
	if($result=mysqli_query($conn,$sql)){
		if(mysqli_num_rows($result)!=0){
			echo "<table border=\"1\">\n";
			echo "<tr><td>ID</td><td>商品コード</td><td>商品名</td></tr>\n";
			while($row=mysqli_fetch_array($result,MYSQL_ASSOC)){
				echo "<tr>\n";
				echo "<td>".$row['id']."</td>\n
					  <td>{$row['productId']}</td>\n
					  <td>{$row['productName']}</td>\n";
				echo "</tr>\n";
			}
			echo "</table>\n";
		}
		else{
			echo "商品データは、登録されていません。<br>\n";
		}
	}
	else{
		echo "条件に合ったデータは存在しません<br>\n";
	}
	mysqli_close($conn);
?>
</body>
</html>

　８）表３（メーカーテーブル）のメーカーを更新するプログラムを作ります。
　　　７）を参考に作ってみましょう。（ファイル名： makerUpDateMenu.php、makerUpDate.php）
＜リンク＞９）作成したデータベースで各種結合を行ってみる。
＜リンク＞１３）商品メーカ名を更新できるようにする。
＜リンク＞１４）商品の単価、在庫数、商品メーカ名を更新できるようにする。