PHP と MySQL を使用してデータベース主導の Web サイトを構築する (10)

mysql|データ|データベース

現在のケースでは、必要な列は、Jokes テーブルの JokeText 列と、Authors テーブルの Name 列と Email 列です。 Jokes テーブルと Authors テーブルの関連付け条件は、Jokes テーブルの AID 列の値が Authors テーブルの ID 列の値と等しいことです。これは結合の例です (最初の 2 つのクエリは、2 つのテーブルに含まれるコンテンツを表示するためにのみ使用されます):


mysql> SELECT LEFT(JokeText,20), AID FROM Jokes;
+---- - -------+------+
| ジョークテキスト,20 |
+---------- - ----------+------+
| なぜ鶏が入ってきた | 1 |
| ------------------------+------+
セット内の 3 行 (0.00 秒)

mysql> SELECT * FROM Authors;
+ ----+----------------+---------------------+
| 名前 |メール |
+ ----+----------------+---------------------+
| 1 | ケビン・ヤンク@attglobal.net |
| joan@somewhere.net | -------- -------+
2 行セット (0.00 秒)

mysql> SELECT LEFT(JokeText,15)、名前、メール
-> ジョーク、著者の場所= 著者.ID;
+- ------------------+----------------+------ --- -- -
| メール
+---------------------+---------- --+------- -- -
| ケビン ヤンク | kyank@attg...
|ノック。ジョアン・スミス | joan@somew..
+-------------------+-------------- -+--------- -- -
3行セット(0.00秒)



理解できましたか？ 3 番目の SELECT の結果は接続であり、2 つのテーブルに格納されているデータ関連付けデータが 1 つの結果テーブルに表示されます。データは 2 つのテーブルに格納されていますが、ジョークに必要なすべての情報を取得するためにデータベース クエリを使用できます。当社ウェブページのリストに掲載されています。

ここで注意が必要なのは、どちらのテーブルにもIDという列があるため、AuthorsテーブルでID列を使用する場合はテーブル名(Authors.ID)を指定する必要があることです。テーブル名を指定しない場合、MySQL はその ID でどのテーブルを参照しているのかを認識できず、次のようなエラーが発生します:


mysql> SELECT LEFT(JokeText,20), Name, Email
- > FROM Jokes, Authors WHERE AID = ID;
エラー 1052: 列: where 句の 'ID' があいまいです



2 つのテーブルから効率的に情報を取得する方法がわかったので、結合を使用してテーブルを再プログラムできます。ジョークリスト:


$jokelist = mysql_query(
"SELECT JokeText, Name, EMail " .
"FROM Jokes, Authors WHERE AID=Authors.ID");

while ($joke = mysql_fetch_array ($jokelist)) {
$joketext = $joke["JokeText"];
$name = $joke["Name"];
$email = $joke["EMail"];

// ジョークを作者情報とともに表示します
echo( "

$joketext
" .
"(by $name)

" );
}



データベースを使用するにつれて、接続機能の重要性がますますわかるようになります。たとえば、次のクエリは Joan Smith によって書かれたすべてのジョークを表示するために使用されます:


mysql> SELECT JokeText FROM Jokes, Authors WHERE
-> Name="Joan Smith" AND AID=Authors.ID;上記のクエリの出力は Jokes テーブルからのみ取得されますが、結合を使用して Authors テーブルに格納されている値によってジョークを検索します。この記事では、このような高度なクエリをさらに紹介します。実際のアプリケーションでは、接続がよく使用されます。これにより、作業が大幅に簡素化されます。

単純なデータ関係

特定の状況に最適なデータ モデルは、多くの場合、作業している 2 つのデータ型間の関係のタイプによって決まります。この記事では、典型的な関係タイプを検討し、それらをリレーショナル データで最適に記述する方法を学びます。

単純な 1 対 1 の関係の場合、テーブルが 1 つだけで十分です。 1 対 1 の関係の例は、前に見た、ジョーク データベース内の各著者の電子メール アドレスです。各作成者には電子メール アドレスが 1 つだけ存在し、各電子メール アドレスに対して作成者は 1 人だけであるため、それらを 2 つのデータベースに分割することは意味がありません。

多対 1 の関係はもう少し複雑かもしれませんが、データベース内の各ジョークには 1 人の作成者しか存在しませんが、同じ作成者が多数のジョークを書いている可能性があります。ジョークと作者の関係は多対一の関係です。私たちには、ジョークに関連付けられた著者情報を同じデータベースに入れるという暫定的な解決策がありました。ただし、これを行うと、同じデータのコピーが多数存在することになり、同期が困難になるだけでなく、スペースも無駄に消費されます。データを 2 つのテーブルに分割し、ID 列を使用して 2 つのテーブルを結合する (前述の結合を使用する) ことで、すべての問題は解決されます。

これまでのところ、私たちは一対多の関係には触れていませんが、そのような関係を想像するのは難しくないはずです。先ほど作成したデータベースでは、作成者が電子メール アドレスを 1 つだけ持っていると仮定しました。実際、これは常に当てはまるわけではありません。この制限の理由は単純に、著者に連絡するために電子メール アドレスのみが必要なためです。私たちは、著者が常に通常の電子メール アドレス、または少なくとも通常使用している電子メール アドレスを入力すると単純に想定していました。複数の電子メール アドレスをサポートしたい場合は、1 対多の関係に直面することになります (作成者は複数の電子メール アドレスを持ちますが、電子メール アドレスは特定の作成者にのみ対応します)。

経験の浅いデータベース設計者が 1 対多の関係に直面したとき、最初に考えるのは、次のように複数のデータを 1 つのデータベース フィールドに格納しようとすることです。

この構造が使用された後、データベースから単一の電子メール アドレスを取得する場合は、カンマ (または任意のその他の区切り文字) を検索して文字列を分割する必要がありますが、これは単純ではなく、時間がかかる可能性があります。 PHP を使用して作成者の電子メール アドレスを削除したい場合を想像してみてください。それは非常に困難です。さらに、EMAIL 列には非常に長い長さが必要ですが、ほとんどの作成者は電子メール アドレスを 1 つしか持たないため、ディスク領域が無駄になります。

一対多の関係を解くことは、上で解決した多対一の関係と非常によく似ています。実際、この 2 つは単純な反転にすぎません。 Authors テーブルを Authors と EMails の 2 つのテーブルに分割し、EMails テーブルの作成者の ID (AID) などの列を使用して 2 つのテーブル間の接続を実現します。

接続を使用して作成者を表示する すべて電子メール アドレスは非常に単純になります:


mysql> SELECT EMail FROM Authors, EMails WHERE
—> Name="Kevin Yank" AND AID=Authors.ID;
+------ ----- ----------+
| メール |
+----------+
| kevin@sitepoint.com @attglobal.net |
+---------------------+
セット内の 2 行 (0.00 秒)



多対多

OK , これで、Web サイトに公開されたジョークのデータベースが着実に増加しました。実際、この増加は非常に急速であるため、ジョークの数が管理できなくなる可能性があります。訪問者は、何百ものジョークが散乱した巨大なページに直面することになります。ここで、いくつかの変更を検討する必要があります。

あなたはジョークをさまざまなカテゴリに分類することにしました。これらは、「ノックノックジョーク」、「道路横断ジョーク」、「弁護士ジョーク」、「政治ジョーク」などです。前の処理ルールを思い出してください。ジョーク カタログは異なる種類の「物」であるため、新しいデータ テーブルを作成する必要があります:


mysql> CREATE TABLE Categories (
-> ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> 名前 VARCHAR(100),
-> 説明 TEXT
-> クエリ OK、影響を受ける行は 0 秒 (0.00 秒)



ジョークが属するディレクトリを定義するのは困難ですタスク。なぜなら、「政治的」ジョークは「道路横断」ジョークである可能性もあり、同様に「ノックノック」も「弁護士」ジョークである可能性があるからです。 1 つのジョークが多くのカテゴリに属する​​場合があり、各カテゴリに多数のジョークが含まれる場合があります。これは多対多の関係です。

経験の浅いデザイナーの多くは、複数のデータを 1 つの列に保存することを考えるでしょう。最も直接的な解決策は、Jokes テーブルに Catalog 列を追加し、ジョークが属するディレクトリの ID をリストすることです。ここで 2 番目の経験則が適用されます。列に複数の値を保存する必要がある場合は、設計に欠陥がある可能性があります。

多対多の関係を記述する正しい方法は、「ルックアップ」テーブルを使用することです。このテーブルには実際のデータは含まれておらず、関連するものを定義するためにのみ使用されます。これはデータベース設計のこの部分の概略図です:

JokeLookup テーブルは、ジョーク ID (JID) をディレクトリ ID (CID) に関連付けます。上の例から、「弁護士は何人...」で始まるジョークは「弁護士」と「電球」の両方のカテゴリに属していることがわかります。

ルックアップテーブルの作成方法は、基本的に他のテーブルの作成方法と同じです。違いは主キーの選択にあります。以前に作成したすべてのテーブルには ID という名前の列があり、これを PRIMARY KEY として定義しました。列を主キーとして定義すると、その列には重複する値が存在しなくなります。また、この列に基づいて結合操作を高速化できます。

ルックアップ テーブルの場合、単一の列で値が重複しないことを保証することはできません。各ジョークは複数のディレクトリに属する​​ことができるため、ジョーク ID が複数回表示される場合があります。同様に、ディレクトリに複数のジョークが含まれる場合があるため、カテゴリ ID が複数回表示される場合があります。必要なのは、同じデータのペアが 2 回出現しないことだけです。このテーブルの唯一の目的は接続を実装することであるため、主キーを使用して接続操作の速度を上げることは間違いなく有益です。したがって、通常はルックアップ テーブルの複数列の主キーを作成します:


mysql> CREATE TABLE JokeLookup (
-> JID INT NOT NULL,
- -> CID INT NOT NULL,
-> PRIMARY KEY (JID ,CID)
-> );



これで、テーブル内の JID と CID が一緒になって、このテーブルの主キーを形成します。ルックアップ テーブル内のデータを一意に保つことは重要であり (特定のディレクトリに属する​​ジョークの定義が重複するのを防ぐため)、このテーブルへの結合速度が向上します。

ルックアップ テーブルに含まれるディレクトリ割り当てを使用すると、結合を使用していくつかの興味深く非常に便利なクエリを構築できます。次のクエリは、「Knock-Knock」ディレクトリ内のすべてのジョークをリストします。 ; CID=Categories.ID AND JID=Jokes.ID;



次のクエリは、「弁護士は何人...」で始まるジョークが属するすべてのカテゴリをリストします。 ; FROM ジョーク、カテゴリ、JokeLookup
-> WHERE JokeText LIKE "何人の弁護士%"
-> AND CID=Categories.ID AND JID=Jokes.ID;



Authors テーブルは 4 つのテーブルの結合 (!!!) を形成し、Knock-Knock ジョークを書いたすべての著者の名前をリストします。この章では、適切なデータベース設計の基本原則と、MySQL (そして実際には他のリレーショナル データベースも) がイベント間のさまざまなタイプの関係を記述するサポートをどのように提供するかを学びました。 1 対 1 の関係だけでなく、多対 1、1 対多、多対多の関係についても詳しく説明しました。

このプロセスでは、SQL コマンドについていくつかの新しいことも学びました。特に、SELECT を使用して複数のテーブルのデータを結合し、結果セットに反映する方法を学びました。

第 6 章では、これまでに習得した知識を使用し、少し新しい知識を追加して、PHP でコンテンツ管理システムを構築します。私たちは、このシステムが、MySQL コマンド ラインで問題を解決するのではなく、データベースの内容を管理するためのカスタマイズ可能で安全な Web ベースのインターフェイスを提供できることを期待しています。