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

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

TOP >初心者向けPHP・データベース入門 >2.7 得意先登録SQL on duplicate key update

【PHP入門】得意先登録SQL on duplicate key update

 2023-05-02 (更新日:2023-06-18)

<学習する内容>

 新規・更新登録が一度にできる on duplicate key update というSQL文理解します。
 また、if文のデータ有無判定の関数も理解します。

1)SQL文のon duplicate key updateの利用

SQL文のon duplicate key updateを使って更新を簡素化します。
下記のサンプルプログラムをコピーもしくはダウンロードし指定のフォルダーに配置してください。
必ずDBのパスワードを********から設定したパスワードに変更してください
ファイル名:customerlist2_7.php 
配置先:c:\xampp\htdocs\
配置先URL:http://localhost/customerlist2_7.php

サンプルプログラム名:customerlist2_7.php
						<?php
						
						//DBへの接続
							$host = 'localhost';	//サーバ名
							$user = 'root';			//ユーザ名
							$pass = '********';		//DBのパスワード(自環境のパスワードに書き換えのこと)
							$dbnm = 'sales';		//データベース名
							$conn = mysqli_connect($host,$user,$pass,$dbnm) or die("er 接続できません");
						
						//データの受信
							$fnc = filter_input(INPUT_POST, 'fnc');
							$set_customerCode = filter_input(INPUT_POST, 'set_customerCode');
							$set_customerName = filter_input(INPUT_POST, 'set_customerName');
							$set_zipcode = filter_input(INPUT_POST, 'set_zipcode');
							$set_address1 = filter_input(INPUT_POST, 'set_address1');
							$set_address2 = filter_input(INPUT_POST, 'set_address2');
						
						//データ有無チェック
									$mess = null;		//$messのリセット(これを定義しないとwarning)
						if(isset($fnc)){
							if(empty($set_customerCode)){
									$mess = '得意先コードが未登録です。';
								if(empty($set_customerName)){
									$mess = '得意先コードと得意先名が未登録です。';
								}
							}else{
								if(empty($set_customerName)){
									$mess = '得意先名が未登録です。';
								}else{
									$set_customerName = mb_convert_kana($set_customerName,'AKS');		//文字の全角化
									$set_customerName = str_replace(' ','',$set_customerName);			//空白をとる
									$set_customerName = "'$set_customerName'";	//varcharの変数を"''"囲む
								}
							}
							if(empty($set_zipcode)){
								$set_zipcode = 'null';				//データがなければ'null'を代入
							}else{
								$set_zipcode = "'$set_zipcode'";
							}
							if(empty($set_address1)){
								$set_address1 = 'null';			//データがなければ'null'を代入
							}else{
								$set_address1 = "'$set_address1'";
							}
							if(empty($set_address2)){
								$set_address2 = 'null';			//データがなければ'null'を代入
							}else{
								$set_address2 = mysqli_real_escape_string($conn,$set_address2);
								$set_address2 = "'$set_address2'";
							}
						}
						
						//データ登録
						if(empty($mess)){					//データチェックで問題がない($messに何もデータがない)
							if(isset($set_customerCode)){
								$sql = "insert into Mcustomer
											(
												customerCode
												,customerName
												,zipcode
												,address1
												,address2
											) values (
												$set_customerCode
												,$set_customerName
												,$set_zipcode
												,$set_address1
												,$set_address2
											) on duplicate key update
												customerName = $set_customerName
												,zipcode = $set_zipcode
												,address1 = $set_address1
												,address2 = $set_address2
										;";
								mysqli_query($conn,$sql) or die ("error $sql");
							}
						}
						
						echo <<<EOT
						<!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="70">郵便番号</td>
								<td width="200">住所1</td>
								<td width="200">住所2</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_customerCode" style="width:98px;">
								</td>
								<td>
									<input type="text" name="set_customerName" style="width:198px;">
								</td>
								<td>
									<input type="text" name="set_zipcode" style="width:68px;">
								</td>
								<td>
									<input type="text" name="set_address1" style="width:198px;">
								</td>
								<td>
									<input type="text" name="set_address2" style="width:198px;">
								</td>
								<td>
									<input type="submit" value="新規" style="width:60px;">
								</td>
							</form>
							</tr>
						EOT;
						//DBからデータを抽出
							$sql = "select customerCode,customerName,zipcode,address1,address2 from Mcustomer;";
							$res = mysqli_query($conn,$sql) or die("エラー $sql");
							while($row = mysqli_fetch_array($res)){		//while(){ ~ }繰り返し処理
								$customerCode = $row["customerCode"];
								$customerName = $row["customerName"];
								$zipcode = $row["zipcode"];
								$address1 = $row["address1"];
								$address2 = $row["address2"];
								
						echo <<<EOT
							<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_customerCode" value="{$customerCode}">
								<td width="100">
									{$customerCode}
								</td>
								<td>
									<input type="text" name="set_customerName" style="width:198px;" value="{$customerName}">
								</td>
								<td>
									<input type="text" name="set_zipcode" style="width:68px;" value="{$zipcode}">
								</td>
								<td>
									<input type="text" name="set_address1" style="width:198px;" value="{$address1}">
								</td>
								<td>
									<input type="text" name="set_address2" style="width:198px;" value="{$address2}">
								</td>
								<td>
									<input type="submit" value="修正" style="width:60px;">
								</td>
							</form>
							</tr>
						EOT;
							}		//← 繰り返し処理の }
						
						echo <<<EOT
							</table>
						</body>
						
						</html>
						EOT;
						
						?>
						

<解説>
53行目から77行目が変更点
重複チェックもなくし、insert文とupdate文を合わせたSQL文となります。
on dupkicate key update以降の文にはプライマリーキーのcustomerCodeを記述してません。このプライマリーキーデータにてinsertとかupdateの処理をしてます。
また、変数$fncは1としてます。入力データチェックとSQL実行判定として使用。

53行目から56行目は$set_customerCodeのデータの有無(送信されているかどうか)をチェックしあれば下記以降のSQL文を実行します
 有無の判定方法
issetデータ有変数のデータがセットされているか
strlenデータ有データに長さ(桁数)があるか
emptyデータ無データが空(0,null,空文字)
is_nullデータ無データがnull
 この判定関数の前に「!」をつけると逆の判定となります。例)!empty
 nullとは、値が何もないことを表し、また空文字は長さ(桁数)0の文字列を表します。

次は入力画面を作成していきます。