PHP+SQLite:出演者の平均年齢を求める(データベースの編集)

(1/1)
今回は、少し実用的なデータベース・プログラムをつくってみる。
毎週日曜日の朝、TBS系『サンデーモーニング』を見ているのだが、出演者の年齢が気になり、いちいち調べてツイートしている。
そこで今回は、あらかじめ出演者の氏名と生年月日をデータベースに登録しておき、その日の年齢を計算で求めるPHPプログラムを作ってみることにする。
出演者は毎週入れ替わっており、簡単に追加や編集が行える機能も備えることにする。
あわせて、これまで取り上げなかったデータベースの更新を行う UPDATE文について解説する。

(2024年4月7日)データベース登録時の不具合を解決
(2024年2月4日)名前の区切り文字に空白を追加,pahooInputData対応

サンプル・プログラムの実行例

PHP+SQLite:出演者の平均年齢を求める

サンプル・プログラム

圧縮ファイルの内容
averageAge.phpサンプル・プログラム本体。
pahooInputData.phpデータ入力に関わる関数群。
使い方は「数値入力とバリデーション」「文字入力とバリデーション」などを参照。include_path が通ったディレクトリに配置すること。
averageAge.php 更新履歴
バージョン 更新日 内容
4.8.1 2024/04/07 storeBirthdays(), makeCommonBody() -- bug-fix
4.8.0 2024/02/04 名前の区切り文字に空白を追加,pahooInputData対応
4.7.0 2023/07/30 名前の末尾の「さん」を読み飛ばす
4.6.0 2023/01/22 英全角を含む氏名を対象に加えた.
4.5.0 2022/12/18 PHP8.2対応
pahooInputData.php 更新履歴
バージョン 更新日 内容
1.5.0 2024/01/28 exitIfExceedVersion() 追加
1.4.2 2024/01/28 exitIfLessVersion() メッセージ修正
1.4.1 2023/09/30 コメントの訂正
1.4.0 2023/09/09 $_GET, $_POST参照をfilter_input()関数に置換
1.3.0 2023/07/11 roundFloat() 追加

テーブル定義

TABLE_BIRTHDAY:出演者情報
No. 名前 内容
1id整数出演者ID
2nameテキスト氏名
3birthdayテキスト生年月日
4dtテキスト登録日時

解説:初期化

 179: /**
 180:  * データベースを初期化する.
 181:  * データベースが存在しなければCREATEする.
 182:  * @param   なし
 183:  * @return  bool TRUE/FALSE
 184: */
 185: function initialize() {
 186:     try {
 187:         $pdo = new PDO('sqlite:' . DBFILE);
 188:         $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 189: 
 190:         //テーブル作成:出演者情報
 191:         //id:出演者ID, name:氏名,birthday:生年月日,dt:登録更新日時
 192:         $pdo->exec('CREATE TABLE IF NOT EXISTS ' . TABLE_BIRTHDAY . '(
 193:          id       INTEGER PRIMARY KEY AUTOINCREMENT,
 194:             name     TEXT,
 195:             birthday TEXT,
 196:             dt       TEXT
 197:         )');
 198:      $res = TRUE;
 199:     } catch (PDOException $e) {
 200:         $res = FALSE;
 201:     }
 202: 
 203:     return $res;
 204: }

プログラム起動時に必ず呼び出す関数。ここでは、データベースが存在しないときに、自動的に生成するコードを用意した。
登録する人物(レコード)は同姓同名の方がいるかもしれないので、それらをも識別できるように、ユニークな番号 ID を追加した。ID は整数だが、AUTOINCREMENT を付与することで INSERT文 を実行するときに自動的に加算していくので、重複する ID を付与することはない。

解説:DBから生年月日を取得

 234: /**
 235:  * 指定した出演者一覧文字列から出演者の情報を取り出し配列に格納する.
 236:  * @param   string $userlist 出演者一覧(区切り文字:改行および空白)
 237:  * @param   array  $users    情報を格納する配列
 238:  * @return  string エラーメッセージ/''
 239: */
 240: function getBirthdays($userlist, &$users) {
 241:     if ($userlist == '')    return 'ユーザー情報がありません';
 242: 
 243:     //出演者一覧を分解
 244:     $userlist = preg_replace('/[\((][^\))]+[\))]/ui', '', $userlist);
 245:                                                         //括弧を読み飛ばす
 246:     $arr = preg_split("/[\n\t&\s]+/ui", $userlist);    //氏名を配列に代入
 247:     $cnt = 0;
 248:     foreach ($arr as $val) {
 249:         $user = trim($val);
 250:         if ($user == '')    continue;
 251:         //英全角、ひらがな、カタカナ、漢字以外で始まるテキストを読み飛ばす
 252:         if (preg_match('/^[A-Za-zぁ-んァ-ヶ一-龠々]/ui', $user) == 0) {
 253:             continue;
 254:         }
 255:         $user = preg_replace('/さん$/ui', '', $user);   //「さん」を読み飛ばす。
 256:         $users[$cnt]['name'] = $user;
 257:         $users[$cnt]['birthday'] = '';
 258:         $users[$cnt]['id'] = '';
 259:         $cnt++;
 260:     }
 261: 
 262:     //DB検索
 263:     try {
 264:         $pdo = new PDO('sqlite:' . DBFILE);
 265:         $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 266:         $stmt = $pdo->prepare(PRE_SELECT);
 267:         $ret  = $stmt->execute();
 268:         while ($row = $stmt->fetch()) {
 269:             foreach ($users as $key=>$user) {
 270:                 if ($user['name'] == $row['name']) {
 271:                     $users[$key]['birthday'] = $row['birthday'];
 272:                     $users[$key]['id'] = $row['id'];
 273:                 }
 274:             }
 275:         }
 276:         $pdo = NULL;
 277:     } catch (PDOException $e) {
 278:         return 'DBアクセスに失敗しました';
 279:     }
 280: 
 281:     return '' ;
 282: }

ユーザー関数 getBirthdays は、まず、出演者一覧(文字列)を分解し、配列 $users に代入する。

次に、データベースを検索し、$users['name'] と合致する氏名があれば、その生年月日とIDを配列 $users に代入する。IDを取得するのは、編集を行う際、レコード抽出のキーとするためだ。

解説:氏名、生年月日をDB登録/更新

 380: /**
 381:  * 氏名、生年月日をデータベースに登録または更新する.
 382:  * ID番号があればUPDATE文を実行する.
 383:  * @param   array $ids      ユーザーID
 384:  * @param   array $names    氏名
 385:  * @param   array $birthday 生年月日
 386:  * @return  string エラーメッセージ/''
 387: */
 388: function storeBirthdays($ids, $names, $birthdays) {
 389:     $msg = '';
 390:     $res = TRUE;
 391:     $errmsg = '';
 392:     $patterns = array("/\p{Cc}/u");     //制御文字を除く
 393:     $cnt = count($names);
 394: 
 395:     $pdo = new PDO('sqlite:' . DBFILE);
 396:     $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 397: 
 398:     //個別処理
 399:     for ($i = 0$i < $cnt$i++) {
 400:         //バリデーション
 401:         if (validString($names[$i], $errmsg, NAME_MIN, NAME_MAX, FALSE, $patterns) == FALSE) {
 402:             return '名前が' . $errmsg;
 403:         }
 404:         //年月日区切り文字対策
 405:         $pat = '/([0-9]+)[年\/]([0-9]*)[月\/]?([0-9]*)[日\/]?/iu';
 406:         if (preg_match($pat, $birthdays[$i], $arr> 0) {
 407:             $yy = sprintf('%04d', $arr[1]);
 408:             $mm = isset($arr[2]) ? sprintf('%02d', $arr[2]) : '??';
 409:             $dd = isset($arr[2]) ? sprintf('%02d', $arr[3]) : '??';
 410:             $birth = $yy . $mm . $dd;
 411:         } else {
 412:             $birth = $birthdays[$i];
 413:         }
 414:         if (validString($birth, $errmsg, 4, 8, FALSE, array('/^[^0-9]+$/i'), TRUE) == FALSE) {
 415:             return '生年月日が' . $errmsg;
 416:         }
 417: 
 418:         //重複チェック
 419:         $stmt = $pdo->prepare(PRE_COUNT);
 420:         $stmt->bindValue(':name', $names[$i], PDO::PARAM_STR);
 421:         $stmt->execute();
 422:         $res = $stmt->fetch();
 423: 
 424:         //UPDATE実行
 425:         if ($res[0> 0) {
 426:             $stmt = $pdo->prepare(PRE_UPDATE);
 427:             $stmt->bindValue(':id',       $ids[$i],       PDO::PARAM_INT);
 428:             $stmt->bindValue(':name',     $names[$i],     PDO::PARAM_STR);
 429:             $stmt->bindValue(':birthday', $birth,         PDO::PARAM_STR);
 430:             $stmt->bindValue(':dt', date(DATE_W3C, time()), PDO::PARAM_STR);
 431: 
 432:         //INSERT実行
 433:         } else {
 434:             $stmt = $pdo->prepare(PRE_INSERT);
 435:             $stmt->bindValue(':name',     $names[$i],     PDO::PARAM_STR);
 436:             $stmt->bindValue(':birthday', $birth,         PDO::PARAM_STR);
 437:             $stmt->bindValue(':dt', date(DATE_W3C, time()), PDO::PARAM_STR);
 438:         }
 439:         $res = $stmt->execute();
 440:         if ($res == FALSE)  return 'DB登録に失敗';
 441:     }
 442: 
 443:     return '';
 444: }

データベースへの登録は、ユーザー関数 storeBirthdays で行う。
引数として、ユーザーIDの配列 $ids、氏名の配列 $names、生年月日の配列 $birthdays を与える。一度の呼び出しで、配列にある複数の出演者の登録、更新ができる。

まず、入力値のバリデーションチェックを行う。
続いて、氏名をキーに重複チェックする。
すでに存在している場合は更新(UPDATE文)を行い、そうでない場合は登録(INSERT文)としている。
UPDATE文は次のように書く。
UPDATE テーブル名 SET (カラム1)=(値1), (カラム2)=(値2),... WHERE (条件);
更新するレコードを一意に絞るためにWHERE句の条件が重要となる。ここでは、ユニーク番号である ID番号 を条件にした。

生年月日は YYYYMMDD 形式のテキストだが、生年しか分からない出演者もいるので、月や日を '00' にしたり '??' にすることができるようにしてある。

解説:年齢計算

 206: /**
 207:  * 指定した生年月日の現在の年齢を計算する.
 208:  * @param   string $birthday 生年月日(yyyymmdd)
 209:  * @return  float 年齢
 210: */
 211: function getAge($birthday) {
 212:     preg_match('/([0-9]{4})([0-9]{2})?([0-9]{2})?/i', $birthday, $arr);
 213:     $yy = isset($arr[1]) ? date('Y'- $arr[1: 0;
 214:     $mm = isset($arr[2]) ? date('n'- $arr[2: 0;
 215:     $dd = isset($arr[3]) ? date('j'- $arr[3: 0;
 216: 
 217:     return ($dd / 30 + $mm) / 12 + $yy;
 218: }

年齢計算はコードの通りだ。
月日まで含め、年齢を小数点計算している。
また、前述のように生年しか分からない出演者もいるので、その場合は、月や日をゼロとして計算している。

解説:出演者一覧をTABLEタグに整形

 284: /**
 285:  * 出演者一覧をHTMLタグ(TABLE)に整形する.
 286:  * @param   array  $users 出演者一覧
 287:  * @param   int    $id    編集したいID
 288:  * @return  array(TABLEタグ, 出力テキスト)
 289: */
 290: function users2table($users, $id) {
 291:     //登録ボタンの要否
 292:     $button = '&nbsp;';
 293:     foreach ($users as $user) {
 294:         if (($user['birthday'] == ''|| ($id == $user['id'])) {
 295:             $button =<<< EOT
 296: <input type="submit" id="insert" name="insert" value="登録">
 297: 
 298: EOT;
 299:             break;
 300:         }
 301:     }
 302: 
 303:     //TABLE作成
 304:     $flag = TRUE;
 305:     $message = '';
 306:     $table =<<< EOT
 307: <table class="stripe_table">
 308: <tr>
 309: <th>氏名</th>
 310: <th>生年月日</th>
 311: <th>年齢</th>
 312: <th>{$button}</th>
 313: </tr>
 314: 
 315: EOT;
 316:     $cnt = 0;
 317:     $n = 0;
 318:     $total = 0;
 319:     foreach ($users as $user) {
 320:         //入力・編集行
 321:         if (($user['birthday'] == ''|| ($id == $user['id'])) {
 322:             $flag = FALSE;
 323:             $message = '';
 324:             $table .=<<< EOT
 325: <tr>
 326: <td><input type="text" name="names[{$cnt}]" size="10" value="{$user['name']}"></td>
 327: <td><input type="text" name="birthdays[{$cnt}]" size="12" value="{$user['birthday']}"></td>
 328: <td>&nbsp;</td>
 329: <td><input type="hidden" name="ids[{$cnt}]" id="ids[{$cnt}]" value="{$user['id']}"></td>
 330: </tr>
 331: 
 332: EOT;
 333:             $cnt++;
 334: 
 335:         //表示行
 336:         } else {
 337:             $birth = formatBirthday($user['birthday']);
 338:             $age = getAge($user['birthday']);
 339:             $total +$age;
 340:             $age = sprintf('%.1f', $age);
 341:             $table .=<<< EOT
 342: <tr>
 343: <td>{$user['name']}</td>
 344: <td style="text-align:center;">{$birth}</td>
 345: <td style="text-align:center;">{$age}</td>
 346: <td style="text-align:center;"><button type="submit" name="edit" value="{$user['id']}">編集</button></td>
 347: </tr>
 348: 
 349: EOT;
 350:             $message .sprintf(MESSAGE_BODY, $user['name'], floor($age));
 351:             $n++;
 352:         }
 353:     }
 354: 
 355:     //平均年齢
 356:     if ($flag) {
 357:         $average = sprintf('%.1f', $total / $n);
 358:         $table .=<<< EOT
 359: <tr>
 360: <td>&nbsp</td>
 361: <td style="text-align:right;">平均年齢</td>
 362: <td style="text-align:center;">{$average}</td>
 363: <td>&nbsp</td>
 364: </tr>
 365: 
 366: EOT;
 367:         $message = sprintf(MESSAGE_HEAD, $average. $message;
 368:     }
 369: 
 370:     $table .=<<< EOT
 371: </table>
 372: 
 373: EOT;
 374: 
 375:     $message .MESSAGE_TAIL;
 376: 
 377:     return array($table, $message);
 378: }

まず、出演者一覧 $users を総なめにして、入力のためのテキストボックスが必要かどうかを調べる。テキストボックスが必要でなければ(すべてデータベースに登録されていれば)、登録ボタンは表示しない。

次に、出演者の一覧(氏名・生年月日・年齢)と編集ボタンを生成する。
生年月日が空文字なら新規登録が必要。引数のIDと一致していたら、その出演者の情報を編集する必要があるとみなす。
inputタグ の名前やIDとして配列を用いることで、複数の出演者を一気に登録できるようにしている。

参考サイト

(この項おわり)
header