データベースへ格納

データベース内にテーブル作成

  • テーブル名「user」


データベースへ格納


《input.php

<?php
$name = "";
$email ="";
$message = "";

session_start();

if( isset($_SESSION[ "name" ]) ){
  $name = $_SESSION[ "name" ];
}
if( isset($_SESSION[ "email" ]) ){
  $name = $_SESSION[ "email" ];
}
if( isset($_SESSION[ "message" ]) ){
  $name = $_SESSION[ "message" ];
}
?>
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="utf-8">
<title>お問い合わせフォーム入力画面</title>
<link rel="stylesheet" href="style.css">
</head>
<body>
<div class="container">
<h1>お問い合わせ入力画面</h1>
<p>※必須項目の確認をお願いします。</p>
<form action="check.php" method="post">
<table class="input">
<tr>
<th>名前</th><td><input type="text" name="name" class="field"></td>
</tr>
<tr>
<th>メールアドレス</th><td><input type="text" name="email" class="field"></td>
</tr> 
<tr>
<th>内容</th><td><textarea name="message" rows="3" cols="31"></textarea></td>
</tr>
<tr>
<td colspan="2" class="btn"><input type="submit" value="確認"></td>
</tr>
</table>
</form>
</div><!-- /.container -->
</body>
</html>

《check.php

<?php
// フォームデータが空の場合は処理終了
  if (empty($_POST)) {
    print "処理終了";
    exit;
  }

// セッションの開始
  session_start();
  
  $name = htmlspecialchars( $_POST[ 'name' ], ENT_QUOTES );
  $email = htmlspecialchars( $_POST[ 'email' ], ENT_QUOTES );
  $message = htmlspecialchars( $_POST[ 'message' ], ENT_QUOTES );
  $flg = true;
  $_SESSION[ 'name' ]= $name;
  $_SESSION[ 'email' ] = $email;
  $_SESSION[ 'message' ] = $message;
?>
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
<title>PHP+MySQL課題</title>
<link rel="stylesheet" href="style.css">
</head>
<body>
<div id="container">
<h1>確認画面</h1>
<p>内容を確認してください。</p>
<form method="post" action="thanks.php">
<table border="1">
<tr>
<th>お名前</th>
<!-- 入力内容の確認表示 -->
<td>
<?php 
  if($name=='') {
    print 'お名前が、入力されていません。';
  } else {
    print $name; 
  }
?>
</td>
</tr>
<tr>
<th>メールアドレス</th>
<td>
<?php 
  if($email=='') {
    print 'メールアドレスが、入力されていません。';
  } else {
    print $email; 
  }
?>
</td>
</tr>
<tr>
<th>メッセージ</th>
<!-- メッセージの改行 -->
<td>
<?php 
  if($message=='') {
    print 'お問い合わせの内容が、入力されていません。';
  } else {
    print nl2br($message); 
  }
?>
</td>
</tr>
<tr>
<td colspan="2" class="button">
<?php
  if( $name == '' || $email == '' || $message  == '' || $flg === false ) {
    print '<span>未入力の項目があります。「戻る」ボタンをクリックし入力してください。</span><br>';
    print '<input type="button" onclick="history.back()" value="戻る">';
  } else {
    print '<input type="submit" name="sub1" value="送信する">';
  }
?>
</td>
</tr>
</table>
</form>
</div>
</body>
</html>

《thanks.php

<?php
// セッションの開始
session_start();

  $name = htmlspecialchars( $_POST[ 'name' ], ENT_QUOTES );
  $email = htmlspecialchars( $_POST[ 'email' ], ENT_QUOTES );
  $message = htmlspecialchars( $_POST[ 'message' ], ENT_QUOTES );
  $flg = true;
  $name = $_SESSION[ 'name' ];
  $email = $_SESSION[ 'email' ];
  $message = $_SESSION[ 'message' ];
?>
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
<title>PHP+MySQL課題(1)</title>
<link rel="stylesheet" href="style.css">
</head>
<body>
<div id="container">
<h1>完了画面</h1>
<?php
$dsn = 'mysql:dbname=アカウント_db;host=localhost(MySQLサーバー名)';
$user = 'MySQLアカウント';
$password = 'MySQLパスワード';
$dbh = new PDO($dsn,$user,$password);
//$dbh -> query('SET NAMES utf8');


// メール本文の組み立て
mb_language("Ja");
mb_internal_encoding("UTF-8");

$subject = "お問い合せを受け付けました。";
$to = $email;
$title = "【お客様の入力確認メール】";
$body =  <<<EOM
--------------------------------------------------
【フォームへの入力内容】

お名前:{$name}
メールアドレス:{$email}
メッセージ:{$message}
--------------------------------------------------
EOM;

$body_client =  <<<EOM
--------------------------------------------------
【お客様の入力内容】

お名前:{$name}
メールアドレス:{$email}
メッセージ:{$message}
--------------------------------------------------
EOM;

// メール送信の実行
$rc = mb_send_mail($to, $title, $body);
$rc = mb_send_mail("xxxxx@gmail.com","お客様からのメール",$body_client);

if (!$rc) {
    exit;
} else {
    $_SESSION = NULL;
}

$sql = 'INSERT INTO inquiry(name,email,message) VALUES("'.$name.'","'.$email.'","'.$message.'")';
$stmt = $dbh -> prepare($sql);
$stmt -> execute();

$dbh = null;
?>
<!-- 処理結果を表示 -->
<p>以下の内容を受けたまわりました。</p>
<table border="1">
<tr>
<td>お名前</td>
<td width="300"><?php print $name; ?></td>
</tr>
<tr>
<td>メールアドレス</td>
<td width="300"><?php print $email; ?></td>
</tr>
<tr>
<td>メッセージ</td>
<td width="300"><?php print nl2br($message); ?></td>
</tr>
</table>
<p><a href="input.php">トップページに戻る</a></p>
</div>
</body>
</html>




SQLを使う(3)

リレーション

  • 複数のテーブルに情報をわけて管理し、必要に応じて組み合わせて使うことでデータの取り扱いがしやすくなります
  • データベースについて
生産者用のテーブルを作る




データの挿入

INSERT INTO makers SET name=’山田’, address='東京都港区', tel='000-111-2222';
INSERT INTO makers SET name=’斉藤’, address='北海道小樽市', tel='111-222-3333';
INSERT INTO makers SET name=’川上’, address='神奈川県横浜市', tel='222-333-4444';


テーブルを結びつけるフィールドを作る
  • テーブル「my_items」の、idフィールドの後に「maker_id」フィールドを「INT型」で作成します



  • 値を入れ以下のように設定しておきます

複数のテーブルを横断して検索する
  • 「りんご」のID「2」を検索して、生産者のIDを取得します
SELECT maker_id FROM my_items WHERE id=2;


  • この結果から「maker_id」が「2」であることがわかります
  • 生産者ID「2」を検索します
SELECT * FROM makers WHERE id=2;


  • 生産者は「斉藤」さんであることがわかります
リレーションを使う
  • 2段階に検索したのでは、効率が悪いので「Relation(関連性)」という機能を使い、複数のテーブルをその関連性からつないで1つのテーブルのように扱います
SELECT * FROM makers, my_items WHERE my_items . id=2 AND makers . id=my_items . maker_id;



生産者を一覧表示

SELECT * FROM makers , my_items WHERE makers . id=my_items . maker_id;


WHEREでリレーションを張る
  • 複数のテーブルを指定した場合は、必ずキーとなるフィールドを使って結びつけなければなりません
AND makers . id=my_items . maker_id;

これは、「makerテーブルのidとmy_itemsテーブルのmaker_id」が一致したデータを検索するという意味。

  • テーブル名に続いて「.(ドット)」でつないでフィールド名を記述します
  • 条件にもテーブル名を付加する必要があります

GROUP BY - 複雑な集計

購入履歴を管理するテーブルを作る


  • 以下のようなデータを挿入しておきます

テーブル「my_items」とリレーションを張る
SELECT my_items . item_name , carts . count FROM my_items , carts WHERE my_items . id=carts . item_id;


  • 「item_id」フィールドに入っている数字は、テーブル「my_items」の「id」と一致しています
  • これによりどの商品がいくつ売れたがわかります
商品の購入数を算出する
SELECT SUM(count) FROM carts;


GROUP BY - 商品ごとの購入数を算出する
SELECT item_id , SUM(count) FROM carts GROUP BY item_id;


商品名を結果に表示する
SELECT i . item_name , SUM( c . count ) FROM my_items i , carts c WHERE i . id=c . item_id GROUP BY c . item_id;





SQLを使う(2)

ORDER BY - データの並び替え

データを昇順 (ascending order) で並べる

idの昇順に並べる

SELECT * FROM my_items ORDER BY id ASC;





金額の昇順に並べる

SELECT * FROM my_items ORDER BY price ASC;



データを降順 (descending order) で並べる
SELECT * FROM my_items ORDER BY id DESC;



WHEREと組み合わせる
  • WHEREを使って検索をする場合には、WHEREの後にORDER BYを続けます
SELECT * FROM my_items WHERE price<=180 ORDER BY price;



DATETIME型とTIMESTAMP型

DATETIME - 入力日用のフィールドを作成する
  • テーブル「my_items」に、1つのフィールド名「created」、データ型「DATETIME」を最後に追加します


UPDATE my_items SET created = '2013-09-01' WHERE id=2;



データを挿入した日時を取得

UPDATE my_items SET created = NOW() WHERE id=3;



TIMESTAMP - 変更日用のフィールドを作成する
  • データを更新した日を基準にしたい場合
  • テーブル「my_items」に、1つのフィールド名「modified」、データ型「TIMESTAMP」を最後に追加します


  • keywordの後にフィールド名「sales」を挿入します
  • その値を入れることにより、変更時点で「modified」に時刻が書き込まれます


COUNT、SUM、MAX、MIN - 計算・集計

SUM - 値を合計する
SELECT SUM(price) FROM my_items;


さまざまな算出機能

最も高価格な商品を検索

SELECT MAX(price) FROM my_items;


最も低価格な商品を検索

SELECT MIN(price) FROM my_items;


ヒットしたデータの件数を取得

SELECT COUNT(id) FROM my_items;


価格の平均を算出

SELECT AVG(price) FROM my_items;



SQLを使う(1)

データベースの作成

  • データベース名「mydb」
  • テーブル名「items」


カラム名 カラム型

(長さ)
NOT NULL

制約
説明
id INT(11) 書き込みの通し番号。主キー。自動採番にする
name text  

テーブルの構造を変更

フィールドを追加する
  • テーブルの末尾に1個の idフィールドを追加する




WHEREで検索

  • 検索対象のデータを挿入しておきます

比較演算子を使って検索

比較演算子 意味 例の意味
A = B AとBが等しい 単価=200 単価が200
A > B AとBより大きい 単価>200 単価が200より大きい

A >= B

AとBより大きいか等しい 単価>=200 単価が200以上
A < B AとBより小さい 単価<200 単価が200未満
A <= B AとBより小さいか等しい 単価<=200 単価が200以下
A <> B AとBは等しくない 単価<>200 単価が200でない

INT型に等号を使う
SELECT * FROM my_items WHERE price=180;



VARCHAR型、TEXT型に等号を使う
SELECT * FROM my_items WHERE item_name='いちご';



不等号を使う
SELECT * FROM my_items WHERE price<180;



LIKE - 部分検索

  • LIKEは半角空白に続けて検索したい文字を追加します
  • 前後に「%」がついていることに注意
前後をあいまいにした部分検索
SELECT * FROM my_items WHERE keyword LIKE '%甘い%';



後ろのみをあいまいにした部分検索(前方一致)
SELECT * FROM my_items WHERE keyword LIKE '赤い%';



  • 「いちご」だけがヒットします
  • 「りんご」にも「赤い」というキーワードが含まれていますが、検索条件で後ろにしか「%」がついていないため、これは先頭が「赤い」というキーワードを検索するという条件になっています

AND、OR - 複数の検索を組み合わせて使う

複数の条件をすべて満たすデータの検索
  • 「AND」で複数の条件をつなぐと「かつ」「さらに」といった条件を加えることができます
SELECT * FROM my_items WHERE price>=50 AND price<150;



複数の条件をどれかを満たすデータの検索
  • 「OR」は、「または」といった条件を加えることができます
SELECT * FROM my_items WHERE id=2 OR id=4;



複雑な条件での検索
SELECT * FROM my_items WHERE (id=2 OR id=4) AND price<150 AND keyword LIKE '%甘い%';



OR条件を使う時の注意

  • 「OR」と「AND」を同時に使う場合「()」で条件を囲まなければ正常に動作しません
SELECT * FROM my_items WHERE id=2 OR id=4 AND price<150 AND keyword LIKE '%甘い%';


これは以下のような条件になってしまっています。

id=2 
OR 
id=4 AND price<150 AND keyword LIKE '%甘い%';


つまり、id=2の商品が必ずヒットしてしまいます。


SQLを使う - 基本

SQLは何のため?

  • phpMyAdminのマウス操作だけで作ることができるSQLには限界がある
  • PHP+MySQLを使ったWebシステムを構築するには、SQLの知識が必須

phpMyAdminを利用する

データを取得して表示する

localhostでデータベース一覧を表示する
SHOW DATABASES;


データベースのテーブルを一覧で表示する
SHOW TABLES;


テーブルの内容をすべて表示する
  • 「mydb」を選択する
  • SQL」タブを選択する


  • 以下のように入力し、実行する
SELECT * FROM items;


データをSQL文で挿入する

INSERT INTO items SET ID=100, name='商品100';



phpMyAdminが作成するSQL文を確認する
  • データベースは、データの挿入や表示、その他の変更や削除、テーブルの作成やデータベーススペースにいたるまで、すべてをSQLで操作する必要があります
  • SQL文そのものを記述するのではなくマウス操作で利用可能なのは、phpMyAdminが実行を代行してくれているからです
  1. 画面左側のテーブル一覧から「items」を選択する
  2. 画面上部のタブで「表示」を選択する



テーブルを作るSQL:CREATE

CREATE文
  • テーブルを作成するには「CREATE TABLE」構文を使います
  • テーブル名に続いてそのテーブルに準備するフィールドとその型を指定していきます


CREATE TABLE テーブル名(フィールド1 型, フィールド2 型・・・)

CREATE TABLE my_items (id INT, item_name TEXT, price INT);


  • 実行すると以下のようになります


データを挿入するSQL:INSERT

INSERT文
  • 作ったテーブルにデータを挿入するには「INSERT」構文を使います
  • ここでは「SET」を使った書式を使用します


INSERT テーブル名 SET フィールド名1=値, フィールド名2=値・・・;


INSERT テーブル名(フィールド名1, フィールド名2・・・)VALUES(値1, 値2・・・);

INSERT INTO my_items SET id=1, item_name='いちご', price=200;


  • 実行すると以下のようになります


データを更新するSQL:UPDATE

UPDATE文
  • 条件文を使います
  • ここでは最も簡単な条件文を使用します


UPDATE テーブル名 SET フィールド名1=値, フィールド名2=値・・・ WHERE 条件文;

UPDATE my_items SET price=180 WHERE id=1;


  • 実行すると以下のようになります


データを削除するSQL:DELETE

DELETE文
  • 挿入したデータが不要になった場合には削除することができます
  • 削除には「DELETE」を使います


DELETE FROM テーブル名 WHERE 条件文

DELETE FROM my_items WHERE id=1;



データを検索するSQL:SELECT

SELECT文
  • 検索には「SELECT」を使います


SELECT フィールド名1, フィールド名2・・・FROM テーブル名 WHERE 条件文;

SELECT id, item_name FROM my_items WHERE id=1;



  • 実行すると以下のようになります

プライマリーキー:DBで一番大切なキー

キーを設定
  • プライマリーキー(Primary key):主キー
テーブルのデーターを「空にする」
  • テーブルをきれいにするためにいったん「空にする」ボタンをクリックして、テーブルを空にしておきます

主キーを追加する
  • 「id」フィールドの右端にある「鍵マーク」をクリックする


キーの重複はNG

INSERT INTO my_items SET id=1, item_name='いちご', price=180;


続けて、次の実行をします。

INSERT INTO my_items SET id=1, item_name='りんご', price=90;

  • エラーになりデータは挿入されません


これは、「idフィールドが1のデータと重複(Duplecate)しています」という意味です。
プライマリーキーを設定すれば、このような重複チェックを自動的に行ってくれます。

キーには必ず値を入れる
  • 「idフィールド」は、削除したのちも既に利用した「数値」が再利用されることはありません
  • 「空」であることも許可しません


次のSQLを実行します

INSERT INTO my_items SET id=NULL, item_name='りんご';
  • エラーになりデータは挿入されません


「NULL(ヌル)」というのは、コンピュータ用語では「何もない」といった意味で、元はラテン語のです。(ドイツ語では、数字の0をnullと言います)

オートインクリメント(Auto Increment)

  • 自動採番
  • テーブル「my_items」をクリックし、「構造」タブをクリックし、idフィールドの「変更」をクリックする

  • エラーになる場合は「デフォルト値」をなしにして実行します




リレーショナルデータベース

リレーショナル型データベース

  • リレーショナルデータベースは、いくつかの表が集まってできています
  • 表のことをテーブル(table)と呼び、列をカラム(column)、行をレコード(record)と呼びます
  • PHPにデータの型があるように、データベースにもカラム型というものがあります
Point!
  • データベースにデータを集め、データを使いやすくする
  • DBMSでデータを管理する

MySQL

  • PHPと相性が良いDBMS
  • MySQLは、リレーショナルデータベース(RDB:Relational Database)と言う種類のデータベースで、現在最も広く利用されているオープンソースデータベースです
  • 高速
  • 使いやすい
  • 無料
Check Test
  • MySQLは、PHPと相性のよい「 リレーショナルデータベース 」で、表のような形式でデータを管理している
  • データベースの名称を書きなさい(table, record, column)

基本リレーショナル代数演算

  • Fundamental Relational-Algebra Operations
  • リレーショナルデータベースは、数学の演算をもとにデータの操作をおこなうことができます
  • 厳密に定義された演算を繰り返すことによって、データを「表」として抽出することができます
和(union)
  • 表「商品1」と表「商品2」に存在するすべての商品を抽出することができます


差(difference)
  • 表「商品1」と表「商品2」のどちらかにのみ存在する商品を抽出することができます


積(intersection)
  • 表「商品1」と表「商品2」のどちらにも共通して存在する商品を抽出することができます


直積(cartesian product)
  • 2つの表の行の組み合わせをすべて組み合わせる演算です



和・差・積・直積は「集合演算」と呼びます。
それ以外にリレーショナルデータベース特有の演算があります。
射影・選択・結合・商は「関係演算」と呼びます。

射影(projection)
  • 表中の列を抽出する演算です
  • 表「商品」の中から商品名だけを取り出す演算です


選択(selection)
  • 表中の行を取り出す演算です
  • 表「商品」の中から複数行を抽出する演算です


結合(join)
  • 表を「つなぎ合わせる」演算です
  • 表「売上」には、商品名は記載されていませんが、商品コードによりどの商品であるかは判別できます
  • つまり表「売上」では、表「商品」の主キーである「商品コード」を参照(reference)することで、どの商品であるかを判別できる状態にあります
  • このとき表「売上」の商品コードを「外部キー(foreign key)」と呼んでいます
  • 外部キーが他の表の主キーを参照しているとき、結合によって表をつなぎ合わせます


商(division)
  • 割られる方の表から、割る方の表のすべての行を含むものを取り出し、そこから割る方の行を除く演算です


E-R モデル(Entity-Relationship model)

  • 現状から実体(Entity)となるものをとらえます
  • 実体がどのように関係してるかを分析することで、現状を把握しようという考え方です
  • 実体同士が対応する数のことを「カーディナリティ(cardinality)」と呼びます
E-R モデルの分析方法
  1. 1対1の関係
  2. 1対多の関係
  3. 多対多の関係

表を正規化(normalization)する

  • 現状の実体をリレーショナルデータベースの表に落とし込む作業です
非正規形
  • 繰り返しの項目が排除されていない表です
  • リレーショナルデータベースはこのような表ではデータをうまく管理できないことが知られています
第一正規形(first normal form)
  • 表を2次元の単純な表としたものです
  • 1つのセルに1つの項目が入るような表にしたもの
  • 繰り返し項目を排除するように表を分割しています
第二正規形(second normal form)
  • データを識別できるキーによって他の列の値が決まるように分割したものです
  • 「主キー」によって他の列の値が決まるようにします
  • リレーショナルデータベースでは、「ある列の値によって他の列の値が決まる」ということを「関数従属している(functionally dependant)」と言います
  • 第二正規形では、主キーに他の列が関数従属するように表を分割するようにします
第三正規形(third normal form)
  • 各項目の値が、主キー以外の項目によって決まらないように分割したものです
  • リレーショナルデータベースでは、関数従属のうち「ある列の値によって間接的に他の列の値が決まる」ことを「推移従属している(transitively dependant)」と言います
  • 第三正規形では、推移従属を除くように表を分割するようにします

データベースを設計

概念スキーマ(conceptual schema)
  • 現実の世界をモデル化する段階のことを言います
  • データベースの論理的な構造を決定する段階のことです
  • 概念スキーマの設計は、E-Rモデルによって現実の世界を把握したり、表を正規化することで行われます
内部スキーマ(internal schema)
  • コンピュータ内部からみたデータベースのことを言います
  • データベースの物理的な構造を決定する段階のことを言います
  • 内部スキーマの設計は、データベースの高速な検索方法を設計することなどで行われます
外部スキーマ(external schema)
  • ユーザーやアプリケーションからみたデータベースのことを言います
  • 外部スキーマの設計は、アプリケーションプログラムが必要とするデータを設計することで行われます

Point!

  • PHPでデータベースに接続するには、PDOを使う
  • データベースのデータを扱うには、SQL文を使う
  • SELECT文:データの取得
  • INSERT文:データの削除
  • DELETE文:データの追加
到達度チェック
  • データベースを管理しているシステムを何と言いますか? 
    • DBMS(データベース管理システム)
  • MySQLで、データベースを指定する文はなんですか? 
    • USE文(データベース名で指定したデータベースに接続します。ここで接続というのは、接続以降に行われる操作の時にデータベースが省略された場合にデフォルトで使用されるデータベースを設定するということです。)
  • PDOは「 抽象化レイヤー 」に位置し、データベースごとの差を吸収する


データベース

データーベース(database, DB)

  • Data Base
  • データを一箇所に集めて後から使いやすくする
  • 情報を管理するためのシステム(データベース管理システム DataBase Management System : DBMS
  • 特定のテーマに沿ったデータを集めて管理し、容易に検索・抽出などの再利用をできるようにしたもの
  • 単位としての「データベース」
  • SQL(Structured Query Language)という、データの操作や定義を行うためのデータベース言語で命令する
  • SQL(Structured Query Language)という呼び名はIBMが作って使っていましたが、他メーカーやオープンソースの団体がその呼び名を嫌って、あくまでも「SQL」という別ものだという主張もあります


DBMSは、データをルールに基づいて整理整頓しながら格納し、データベース利用者の要求に対して必要なデータを検索したり、登録したり、削除したりします。DBMSがあって始めて、データベースが快適に利用できるようになります。

データベースのタイプ(データモデル:data model)

リレーショナル型データベース

  • リレーショナル型は、データを表形式で管理します
  • 列は、各管理項目を表し、行は、データのレコードを表します
  • 階層型やネットワーク型と違って、データ構造の変化がプログラムに影響しない構造であることと、データの検索や更新などの操作も柔軟かつ容易であるなどの利点があり、現在の主流のデータベースです


階層型データベース

  • 階層型は、ツリー構造でデータを管理しています
  • あるデータが他の複数のデータに対して、親子関係を持ちます


ネットワーク型データベース

  • ネットワーク型は、データが網の目のようになっています
  • 各データが網の目のように繋がっていて、複数の親データへのアクセスが可能になっています


カード型データベース

オブジェクト型データベース

分散型データベース


現在の主流は、「リレーショナル型データベース」です。