データベース ４日目
ＤＢが壊れていたら。。。
復旧とかできないので諦めて、初期状態に戻す・・・
JOBTECHを参照！！
MariaDB+PHP
データベースの準備
PHPとMariaDBを連携させてWebシステムを作成する。
まずはDBの準備をする。
DB領域とテーブルの作成
PHPから利用するDB領域とテーブルをスクリプトファイルから作成する。
DB領域の作成
※コマンドプロンプトから送る場合は「Shift-JIS」
phpMyadminから送る場合は「UTF-8」で保存する
文房具管理用・メーカー管理用のテーブル
ユーザの作成
今回利用するDB領域のみ扱えるユーザを作成する。
DB領域「practice」のすべてのテーブルに対して権限をもつユーザ「Tanaka」を作成し、PHPからDBへアクセスする。
GRANT ALL ON practice.* TO Tanaka@localhost IDENTIFIED BY 'Manager';
レコードを取得する
DBに接続して、レコードを取得・表示させる方法を確認する。
今回はstationeryテーブルの情報を表示させる。
<body>
<div>
<?php
$dbobj = mysqli_connect('localhost', 'Tanaka', 'Manager')
or die('DBに接続できませんでした');
mysqli_select_db($dbobj, 'practice');
mysqli_set_charset($dbobj, 'utf8');
$resultSet = mysqli_query($dbobj, 'SELECT * FROM stationery')
or die(mysqli_error($dbobj));
$data = mysqli_fetch_assoc($resultSet);
echo $data['item'];
?>
</div>
</body>
// 【or die関数部分の解説】
// 論理演算子「OR」は「||」と同じ（優先度は||の方が高い）
// ◆ショートサーキット評価
// 第1条件が「true」の時は第2条件を見に行かない
// 第1条件が「false」の時は第2条件を見に行く
// ◇ショートサーキット評価を使って
// 接続OKの時はdie関数を実行しない
// 接続NGの時はdie関数を実行する
// die関数
// 引数に指定されたメッセージを表示して
// プログラムを終了させるDBに接続
MariaDBに接続するためには次の関数を使う。
mysqli mysqli_connect(stringホスト名,stringユーザ名,
stringパスワード)ホスト名・・・DBがインストールされているサーバ名。今回は自分のPCを意味する「localhost」と記述。
DBとの接続に成功するとmysqlクラスという、DBとの接続情報を管理するオブジェクトのアドレスを返すので、$dbobj変数で管理する。
$dbobj = mysqli_connect('localhost', 'Tanaka', 'Manager')
// (DB場所,DBユーザ名,DBパスワード)
or die('DBに接続できませんでした');
// mysqli_connect関数：DB接続用関数
// シェル「mysql -u Tanaka -p」と同じ処理
// 戻り値：接続OK「接続情報をもつオブジェクト」
// 接続NG「false」論理演算子「or」を使って、接続できなかった場合の処理を記述していく、die関数は処理を終了させるための関数。終了時に引数で指定されたMySQLエラーメッセージを表示する。
DB領域を選択
DB領域を選択するためには次の関数を使う。
bool mysqli_select_db(mysqli接続情報,stringデータベース領域)PHPからMariaDBにアクセスする時はmysqli_select_db関数を使ってDB領域を指定する。
mysqli_select_db($dbobj, 'practice');
// (接続許可証, 選択するDB領域)
// mysqli_select_db関数：DB領域選択
// シェル「USE practice」文字コードの指定
mysqli_set_charset関数を使って文字コードの違いによる文字化けを防ぐ。
bool mysqli_set_charset(mysqli接続情報,string文字コード);DB利用時の文字コードを指定するには次のように記述する。
mysqli_set_charset($dbobj, 'utf8');
// (接続許可証, PHPで利用している文字コード)
// mysqli_set_charset関数：文字コード指定
// PHPとDBで文字コードが揃っていれば
// 呼び出す必要なし
// ▲ ▲ここまでDB接続処理：後々関数化する▲ ▲SQL文の実行 – DBからレコードを取得
mixed mysqli_query(mysqli接続情報,string SQL文); $resultSet = mysqli_query($dbobj, 'SELECT * FROM stationery')
// mysqli_query関数：SQL文をDBに渡す関数
// (接続許可証,SQL文(SELECT文))
// SQL文最後の「;」は省略可
// PHPとSQL文を明確にするため
// SQL文を大文字で記述することが多い
// 戻り値：SQL文を実行した結果
// SELECT文を実行したので
// テーブル情報がDBから渡される
or die(mysqli_error($dbobj));
// $data = mysqli_fetch_assoc($resultSet);
// // mysqli_fetch_assoc関数
// // DBから取得した外部リソースを
// // 連想配列に変換する関数
// // 変換後、ポインタ位置を次に動かす
// // 第1引数：連想配列に変換する外部リソー
// // 戻り値：ポインタ位置にレコードを
// // 連想配列に変換して返す
// // 連想配列を変数dataに代入取得したリソースを連想配列に変換
array mysqli_fetch_assoc(mysqli_result リソース);引数にリソースを保存した変数を指定して実行すると、ポインタが指し示すレコードのデータを連想配列で返し、ポインタを次の行に進める。連想配列のキーはフィールド名になる。
$data = mysqli_fetch_assoc($resultSet)DBのフィールド名が連想配列のキーになるので、値の取得が以下のように記述する。連想配列のキーを変更して、どのような値が表示されるか確認する。
$変数名['フィールド名']連想配列を返したあと、ポインｔあは2行目にセットされるので、引き続きmysqli_fetch_assoc()関数を実行することで2行目、3行目・・・とレコードのデータを取得できる。
// $data = mysqli_fetch_assoc($resultSet);
// echo '<pre>';
// var_dump($data);
// echo '</pre>';同じような記述が続くので、繰返し文を利用して効率的にプログラムを記述する。
// 繰返し条件：ポインタ位置のレコードを
// 連想配列に変換して変数dataに代入
// 代入された値が連想配列の場合は繰返し続行
// ※↑ポインタ位置が「null」の場合は繰返しをやめる
// 代入された値が「null」の場合は繰返しをやめる
// ※↑ポインタ位置にレコードなし
while ($data = mysqli_fetch_assoc($resultSet)) {
echo $data['id'] . ' ';
echo $data['item'] . ' ';
echo $data['price'] . ' ';
echo '<br>';
}商品管理システムを作成
DBを利用して商品を管理するシステムを作っていく。
システム概要
このシステムで実現したいこと
- 登録されている商品一覧を表示できる
- 新しい商品を登録できる
- 商品情報を修正できる
- 商品を削除できる
ページ遷移図
登録されている商品一覧を表示
PHPとDBを接続し、stationeryテーブルに登録されている商品一覧を表示する。
<?php
$debug = true;
// デバック領域の表示(false)・非表示(true)
require_once dirname(__FILE__) . '/functions.php';
// 共通関数の読み込み
// h関数、v関数を使えるようにする
// dirname(__FILE__) → __DIR__ 同じ
// dirname関数：引数で指定したファイルパスからファイル名を除いて、
// ディレクトリまでのパスを返す
// __FILE__：ファイルパスを格納したマジカル定数
$dbobj = mysqli_connect('localhost', 'Tanaka', 'Manager')
or die('DBに接続できませんでした');
// (DBの場所, DBユーザ名, DBパスワード)
// ORで条件を評価(ORの特殊な使い方)
// ◆第1条件(ORの左辺)
// mysql_connect関数の実行結果を$dbobjに代入した後
// 変数$dbobj内の値を評価
// 接続OK：変数$dbobjに接続オブジェクト → true
// 第1条件「true」→ 第2条件を確認(実行)しない
// 接続NG：変数$dbobjにfalse
// 第1条件「false」→ 第2条件を確認(実行)して処理停止
mysqli_select_db($dbobj, 'practice');
// DB領域の選択：practice領域を選択
mysqli_set_charset($dbobj, 'utf8');
// 文字化け対策
// ▲ ▲ここまでDB接続処理：後々関数化する▲ ▲
$sql = 'SELECT * FROM stationery';
// SQL文を文字列で作成
// DB領域にSQL文を表示したいので変数に代入
$resultSet = mysqli_query($dbobj, $sql) or die(mysqli_error($dbobj));
// mysqli_query関数：SQL文をDBに渡す
// (接続許可証, SQL文)
// ORで条件を評価(ORの特殊な使い方)
// ◆第1条件(ORの左辺)
// mysqli_query関数の結果を$resultSetに代入した後
// 変数$resultSet内の値を評価
// SQL文実行OK：変数$resultSetにテーブル情報代入
// 第1条「true」 → 第2条件を確認(実行)しない
// SQL文実行NG：false
// 第1条件「false」 → 第2条件を確認(実行)して処理停止
// ◆第2条件(ORの右辺)
// mysqli_error関数でDBからエラーメッセージを取得して
// 表示した後に処理停止
$bl = mysqli_affected_rows($dbobj);
// 変数$blにはint(6)が入っている
// mysqli_affected_rows関数
// → 直前の MySQL の操作で変更された行の数を得る
// SELECT文を実行した後の場合、
// 取得したレコード数を返す
// 今回は個の値を条件判定の真偽値代わりに使っている
// 1以上は「テーブル」を表示
// 0は「商品がありません」を表示
// 直前のSQL実行の結果を元に値を返すので
// 記述位置に注意！！
// ▲ ▲ここまででロジック終了▲ ▲
// 表示に必要な値を変数に代入できた
?>
<!DOCTYPE html>
･
･
･
<body>
<?php if ($debug) : ?>
<div class="debug">
<p>デバッグ用</p>
<p>$sql : <?php print $sql; ?></p>
</div>
<?php endif; ?>
<div id="container">
<div id="head">
<h1>商品一覧</h1>
</div>
<div id="content">
<?php if ($bl) : ?>
<table>
<tr>
<th>ID</th>
<th>商品名</th>
<th>価格</th>
<th>在庫</th>
<th>キーワード</th>
<th>メーカー</th>
<th>編集</th>
<th>削除</th>
</tr>
<?php while ($data = mysqli_fetch_assoc($resultSet)) : ?>
<tr>
<td><?php echo h($data['id']); ?></td>
<td><?php echo h($data['item']); ?></td>
<td><?php echo h($data['price']); ?></td>
<td><?php echo h($data['stock']); ?></td>
<td><?php echo h($data['keyword']); ?></td>
<td><?php echo h($data['maker']); ?></td>
<td>編集</td>
<td>削除</td>
</tr>
<?php endwhile; ?>
</table>
<?php else : ?>
<p>商品がありません</p>
<?php endif; ?>
<p class="btn"><a href="entry.php">新規登録</a></p>
<!--#content--> </div>
<!--#container--> </div>
</body>デバック用の表示領域
ロジックないの変数の値を把握するとエラーを見つけやすくなる。デバック用の領域を作成し、制作時に表示させる。
$debug = true;
// デバック領域の表示(false)・非表示(true)
require_once dirname(__FILE__) . '/functions.php';
// 共通関数の読み込み
// h関数、v関数を使えるようにする
// dirname(__FILE__) → __DIR__ 同じ
// dirname関数：引数で指定したファイルパスからファイル名を除いて、
// ディレクトリまでのパスを返す
// __FILE__：ファイルパスを格納したマジカル定数
<?php if ($debug) : ?>
<div class="debug">
<p>デバッグ用</p>
<p>$sql : <?php print $sql; ?></p>
</div>
<?php endif; ?>DBに商品が登録されていない時の対応
if文を使って商品が登録されていた場合の処理と商品が登録されていなかった場合の処理を分けていく。
リソース取得とレコード件数の判定
リソースの行数、つまり件数を調べるには次の関数を使う。
int mysqli_affected_rows(mysqli 接続情報)判定用の変数$blにリソースの件数を代入する。
対象は直前で取得したリソースになる。
$bl = mysqli_affected_rows($dbobj);
// 直前のSQL実行の結果を元に値を返すので
// 記述位置に注意！！PHPでは「0」以外はtrue判定になることを利用して条件分岐をおこなう。1件以上の該当商品がある場合は商品が登録されている場合の処理、0件の時は商品が登録されていない場合の処理に分岐する。
<?php if ($bl) : ?>
【商品が登録されている場合の処理】
<?php else : ?>
【商品が登録されていない場合の処理】
<?php endif; ?>DBから取得した値
<?php while ($data = mysqli_fetch_assoc($resultSet)) : ?>DBから取得したリソースを引数にmysqli_fetch_assoc()関数を実行すると、ポインタ位置にあるレコードを連想配列にして返す。
ユーザが入力した値を扱うには
string htmlspecialchars(string対象の文字列[,intオプション
[,stringエンコード]])第2引数の「ENT_QUOTES(定義済み定数)」はシングルクォートを変換対象にするパラメータ。省略してもいいが、より安全に利用するため指定するようにする。
リレーショナルを使ってレコードを表示
stationeryテーブルではメーカーを番号で管理しているため、会社名が表示されない。リレーショナルを使って会社名が表示されるように修正する。
まずはMariaDB上でリレーショナルされたSQL文を実行確認する。
stationeryテーブルとtraderテーブルを連結するSQL文
コマンドプロンプトからMariaDBクライアントプログラムを起動させ、DB領域「practice」を選択する。
stationeryテーブルとtraderテーブルを連結して、メーカーの会社名を表示させる。登録されているすべての商品を表示させたいので「LEFT JOIN」を使用。連結させるフィールドはstationeryテーブルのmakerとtraderのm_id。
SELECT * FROM stationery LEFT JOIN trader
ON stationery.maker=trader.m_id ORDER BY id;
PHPでメーカーの会社名を表示させる
mysqli_query関数を引数をリレーショナルでメーカーの会社名を取得できるSQL文に変更する。
$sql = 'SELECT * FROM stationery LEFT JOIN trader
ON stationery.maker=trader.m_id ORDER BY id;';
// SQL文を文字列で作成
// DB領域にSQL文を表示したいので変数に代入
// 変更前SQL文
// $sql = 'SELECT * FROM stationery';
// 変更後SQL文
// $sql = 'SELECT *
// FROM stationery LEFFT JOIN trader
// ON stationery.maker=trader.m_id ORDER BY id;';連想配列のキーをcompanyフィールドに変更して表示させる。
【変更前】
<td><?php echo h($data['maker']); ?></td>【変更後】
<td><?php echo h($data['company']); ?></td>