データベース:サーバにデータを保存しよう
はじめに
これから始めるPHP入門コラムでは、PHPを学ぶ人が、PHPで簡単なプログラムを書けるようになるまでに必要な知識とポイントをTips的に書いていきます。今後PHPのスキルを身につけて仕事に役立てたい、という方のために「PHP技術者認定初級試験」の出題範囲を意識しながら進めていきますので、ぜひ最後までお付き合いください。
今回のあらすじ
- Webシステムでデータを扱うには
- データベースマネジメントシステムとは
- データベースへ接続する
- DBMSを準備する
- データを作成する
- データを更新する
- データを削除する
- データを検索する
Webシステムでデータを扱うには
ほとんどのWebシステムでは、何かしらのデータを取り扱っています。実際、どのようなデータが考えられるでしょうか。
(A)その場限りで破棄されるデータ
- 心理テストの設問に対する回答
- 文字数カウンターで入力されたテキスト
(B)システムで使用するため、あらかじめ用意しておくデータ
- ショッピングサイトの商品情報
- 住宅情報サイトの住宅情報
- 経路検索サイトの電車情報、時刻表情報
(C)ユーザーに登録してもらうデータ
- ユーザーの氏名や住所、メールアドレスなど
- レシピサイトの投稿レシピ
- 掲示板サイトの投稿やコメント
その場で破棄される以外のデータは、どこかへ保存しておかなければなりません。保存先には、いくつかの候補があります。
(A)Webシステムが置かれているサーバ(自サーバ)の
- データベース
- ファイル
(B)クラウドなどのデータ保存サービス(外部サーバ)
(C)ユーザーのブラウザの
- クッキー
- ローカルストレージ
(最近のブラウザに備わっているデータ置き場。ブラウザを終了すると破棄される) - セッションストレージ
(最近のブラウザに備わっているデータ置き場。ブラウザを終了しても破棄されない)
ショッピングサイトの商品情報など、複数のユーザーが見るためのデータは、サーバ上に置かれます。一方「最後に開いていたメニュー」などのように、ユーザー自身しか使用しない情報で(メインブラウザの交換などにより)、消えてしまっても特に問題ないものは、クライアントであるユーザーのブラウザに保存することもあります。
データベースマネジメントシステムとは
数あるデータ置き場のうち、今回はデータベースを取り上げます。ファイルシステム(テキストファイルなどにデータを保存しておくこと)と比較してデータベースのよいところは、以下の通りです。
- データの保存、更新、削除、検索などの操作が簡単である
- データに対する権限を細かく設定できる(見るだけなのか、削除もできるのかなど)
- セキュリティが高い
ここでいう「データベース」とは、MySQLやORACLEなどの「データベースマネジメントシステム(DBMS)」のことです。DBMSはPHPなどのプログラムから「商品価格が500円以下の商品のうち、最も売れている商品を知りたい」などの命令を受け取り、実行します。命令は主にSQL(Structured Query Language)という構文を用いて行います。
リレーショナルデータベース
DBMSには複数のデータ形式がありますが、最も一般的なものは「リレーショナルデータベース」という表形式のものです。
リレーショナルデータベースでは、表のことを「テーブル」と呼びます。 図1.2の例では「レシピテーブル」「カテゴリーテーブル」「ユーザーテーブル」と3つのテーブルがあり、「レシピテーブルのカテゴリーIDとカテゴリーテーブルのカテゴリーID」、「レシピテーブルの登録者IDとユーザーテーブルのユーザーID」に、それぞれ関連(リレーション)があります。
1つのデータベースの中には複数のテーブルを用意することができますので、Webシステムで使用する場合、最低1つのデータベースを作成すれば大丈夫です。
リレーショナルデータベースには、MySQL、PostgreSQL、ORACLE、SQLiteなど様々な製品があります。基本的な仕様は同じですが、ライセンス費用の有無、パフォーマンス、特殊な機能などに差があります。少し複雑な命令を行おうとすると、それぞれ異なるSQL文になってしまうこともあります。
また、PHPには特定のDBMS向けの関数が用意されていますが、それらを使用することはあまりありません。なぜなら、特定のデータベースでのみ動くようなプログラミングをしてしまうと、あとからデータベースの切り替えがスムーズにできなくなるからです。
Webシステムを運用している途中でMySQLからPostgreSQLに交換(またはその逆)をする例は少なくありません。またパッケージ製品を納品する場合ですと、こちらの会社にはPostgreSQLで納品し、あちらの会社にはORACLEで納品するといったこともありえます。特定のデータベースを対象にしたプログラムを書いていると、データベースを変える際にそれらの箇所をすべて修正しなければなりません。これは、かなり無謀なことです。
そこで、どのDBMSでも同じように使用できるように、PHPのフレームワークやライブラリを導入します。DBMSごとの差異はこのライブラリが吸収してくれるので、使用するDBMSの種類を気にせずに開発できます。
プログラミングにおけるライブラリとは、便利な機能を他の人も使えるようにパッケージ化しているもののことです。PHP以外の言語にもライブラリはあります。PHPにはPEAR(PHP Extension and Application Repository、発音は「ペア」)というライブラリ群があり、以下のサイトで一覧を見ることができます。
Manual :: PEAR とは?
https://pear.php.net/manual/ja/about.pear.php
ライブラリを使わずに、PHPの拡張モジュールを使用する場合もよくあります。データベース関連の情報は、以下のマニュアルで確認できます。
PHP: データベース関連 - Manual
http://php.net/manual/ja/refs.database.php
今回はPEAR DBというデータベース用のライブラリを使用したいと思います。なお、このライブラリはすでに非推奨となっていますので、実際にはMDB2やPDOなど、他のライブラリやモジュールの使用をお勧めします。
データベースへ接続する
PHPでデータベースを操作するためには、まずPHPのプログラムからデータベース(広義でDBMSのこと)に接続します。接続に必要な情報は、「DBMS名」「ホスト名」「ユーザーアカウント名」「パスワード」「データベース名」などです。これらの情報は、Webシステムを設置する環境(客先のサーバに設置するのか、クラウドシステムを使うのかなど)によって異なります。そのような「環境に応じて編集する必要がある情報」は、「定義ファイル」として1つのファイルにまとめておくと便利です。システム構築先の環境を変更する場合(例えばサーバの移動や、データベースの交換など)は、その定義ファイルのみを書き換えれば済むようにしておくのです。
defineを用いて、定数を定義します。「定数」は「変数」と異なり、一度定義したら変更できません。また、先頭に「$」は不要です。
db_test.phpの最初の2行は、PHPファイルから別のPHPファイルを読み込むための処理です。config.phpでは先に定義しておいた情報を、DB.phpではPEAR DBのライブラリを読み込んでいます。
require_once以外にも同じような用途の関数がありますので、使い分けられるようにしましょう。
関数 | 同じファイルを読み込み済みの場合 | ファイルを読み込めなかった場合 |
---|---|---|
require | 何度でも読み込む | Fatal Errorを発生し、処理を中断する |
include | 何度でも読み込む | Warningを発生し、処理は中断しない |
require_once | 二度目以降は読み込まない | Fatal Errorを発生し、処理を中断する |
include_once | 二度目以降は読み込まない | Warningを発生し、処理は中断しない |
「このファイルを読み込まなければそれ以降の処理が実行できない」という場合は、require系を使い、Fatal Errorで処理が中断する方が望ましいでしょう。また何度も同じメッセージを表示したい場合などはrequireやinclude、ライブラリのように一度読み込めば十分なものについてはrequire_once、include_onceを使用するとよいでしょう。大規模な開発になってくると、気が付かないところで同じファイルをすでに読み込んでいる場合もありますので、_once系の関数がよく使われます。
エラーにはFatal ErrorやWarning以外にも種類があり、以下のマニュアルで確認できます。あるレベルのエラーが起きたときに、エラーメッセージを画面に表示するかどうか、ログファイルに記述するかどうかは、php.iniの設定で決めておくことができます。
PHP: 定義済み定数 - Manual
http://php.net/manual/ja/errorfunc.constants.php
続いて3行目ですが、すでにPEAR DBを読み込んでいるので、DBモジュールの機能を使用しています。「DB::connect」を使い、データベースに接続します。config.phpで定義した定数を元の表現に戻すと、db_test.phpの3行目は以下のようになります。
接続した結果を変数「$db」に代入し、次の4行目でデータベース接続処理のエラー判定をしています。エラーの場合は「exit」関数が実行されます。これは文字列を出力し、PHPの処理を中断する関数です。
「exit」関数
http://php.net/manual/ja/function.exit.php
$dbにはオブジェクト型のデータが代入されています。オブジェクト型の変数は、数値や文字列、配列以外にも関数を内部に持つことができます。たとえば「$db->getMessage()」と書くことで、$dbオブジェクト内部のgetMessageメソッド(関数のこと)を実行できます。このメソッドはエラーメッセージを返します。
この時点では、まだデータベースを用意していませんので、接続に失敗します。
DBMSを準備する
PHPからデータベースに接続するためには、以下の準備が必要です。これらの作業はWebシステムを本番サーバへ公開するとき、サーバを移行するときや、データベースを変更する際などに発生しますが、頻度は1回か、多くても数回です。
- DBMSのインストール
- データベースの作成(最低1つ)
- ユーザーアカウントの作成(最低1つ)
- テーブルの作成(1つ以上)
DBMSをインストールする
まずはお好きなDBMSをインストールしましょう。MySQLやPostgreSQLは無料ですし、商用利用もされています。SQLiteはPHPと一緒にすでにインストール済みの場合もあるので、勉強用には手軽でよいと思います。ここでは、DBMSのインストール方法の解説は省きます。
通常はセキュリティを考慮し、Webシステム1つにつき1つのデータベースと1つのユーザーアカウントを作成し、他のWebシステムとは共有しないようにします。そしてWebシステムからの接続に使用するユーザーアカウントには、データの読み書きのみの権限をあたえ、データベースやユーザーアカウント、テーブルなどを作成・削除できないようにしておくと安心です。事前準備であるデータベース、ユーザーアカウント、テーブルの作成は、もっと権限のある別のユーザーアカウント(rootユーザーなど)を使って行います。これらの処理は、デスクトップアプリで作業できるようにもなっています。たとえばWindowsの場合、MySQLであればMySQLAdmin、PostgreSQLであればpgAdminを使用できます。ただ、実際にWebシステムを公開する際には、Linuxベースのサーバを利用することが多いと思いますので、コマンドでも作業できるようになっておくと、後々役立ちます。
これ以降は、MySQLを例に解説を続けます。
DBMSへ接続する
コマンドラインなどから、以下のコマンドでDBMSにログインします。「root」は管理者権限のあるデフォルトのユーザーです。パスワードが必要な場合は最後に「-p」をつけます。
接続が成功すると、以下のようにSQL文の入力待ち画面になります。
データベースを作成する
SQLは大文字・小文字の区別をしませんが、ここでは分かりやすいように、SQLのコマンドに当たるものを大文字で書きます。小文字のところは、任意に書き換えることができます。
作成したデータベースは、以下のコマンドで確認することができます。
ユーザーを作成する
現在rootユーザーで接続していますが、もしrootユーザーのパスワードを設定していなければ、ここで設定しておきましょう。rootユーザーを乗っ取られると、すべてのデータを盗まれてしまう危険があります。
次に新しいユーザーアカウントを作成し、作成したデータベースに対する権限を設定します。例では全権限(ALL PRIVILEGES)を与えていますが、ここは必要に応じて権限を少なくするとセキュリティが高まります。
ここまでのコマンドは、DBMSによって違いがあります。
テーブルを作成する
先ほどの表の中のレシピテーブルを、もう一度見てみましょう。
テーブルの作成では、この表の枠になる部分を作成します。設定する情報は、以下の通りです。
- テーブルの名前
- 各カラム(列)の名前
- 各カラム(列)のデータ型(数値、文字列などを前もって決めておく必要がある)
- 任意でキーとなる列(その値さえわかれば、どの行のデータなのか分かる列のこと。複数列を組み合わせることもできる)の選択
idはINT(数値)型、nameは64文字のVARCHAR(文字列)型などのように指定しています。データ型には他にも種類があります。フラグ値を入れるための「1文字型(CHAR)」は、よく使うかもしれません。またDBMSによっては「JSON型」「木構造型」などの特殊なものもあります。
ここまででデータベースの準備は終わりです。PHPのプログラムに戻りましょう。
データを作成する
テーブルにデータを追加するSQL文は以下のようになります。
前半ではカラム名、後半では実際に登録するデータを指定しています。
PHPからデータベースにSQL文を渡すと実行した結果が返ってきますので、成功したかどうかをチェックします。
IDを連番にするには
上の例では「id」に「1」を指定しましたが、キーであるカラムでは同じ番号を重複できません。そのため新しいデータを作る際には、今あるデータ内で最大となるidの次の番号にしなければなりません。idを省略すると自動的にインクリメント(1を加算)して登録してくれるDBMSもありますが、ここでは次の番号を求めるメソッドを使いましょう。どのデータベースライブラリにも、このような機能はついているはずです。
セキュリティ対策(SQLインジェクション対策)
ユーザーから入力された情報などを登録する場合、入力されたデータは変数に入っているので、以下のようにSQL文を$sql変数に代入したいかもしれません。
しかし、もしも「$name」に「ホットケーキ」ではなく次のようなデータが入力されていると、recipeテーブルのすべてのデータが削除されていまいます。
「--」はSQL文のコメントで、PHPにおける「//」と同様です。
このように任意のSQL文を挟み込まれてしまう攻撃を「SQLインジェクション」と呼びます。SQLインジェクションを使うと、データベースに登録されている他人のパスワードを盗み見たり、データを破壊したりすることができます。
これを避けるためには、SQL文と判断される文字列をカラムの値として設定できないようにします。上の例では、nameカラム用のデータのところでシングルクオート(')が閉じられてしまったことが問題です。そこで、登録するデータをエスケープするように変更します。PHPにはSQL用のエスケープ関数がありますが、ライブラリの「プレースホルダ」を使用したほうが確実です。
SQLに直接変数を埋める代わりに「?」で代用し、「?」に入る内容は配列($placeholder)で指定します。こうすると、SQL文で使用される特殊文字「'」「"」「\」などがエスケープされ、文字列の場合は引用符(')で囲われます。
今度は、$nameの中身がそのままnameカラムに登録されます。データはエスケープされていますが、取得したときに元に戻るので問題ありません。
どのデータベースライブラリを使用しても、SQL文を実行する手順は以下のようになります。
- 登録するデータを用意する
- SQL文を用意する
- SQLインジェクション対策としてプレースホルダを使い、データがエスケープされるようにする
- SQL文を実行したら、成功したかどうか結果をチェックする
データベースライブラリの中には、登録するデータを配列に入れるだけでSQL文を自動で作成してくれるものもあります。
このようなライブラリでは、SQLインジェクション対策であるデータのエスケープなども自動で行ってくれます。
データを更新する
テーブルのデータを更新するSQL文は、以下のようになります。
SET句で更新するカラムとデータを指定し、WHERE句で更新対象になる条件を指定します。
WHERE句で用いる条件式
WHERE句で指定できる条件式には以下のようなものがあります。ほとんどのDBMSでは文字列の置換などの関数も利用できます。
条件式 | 意味 |
---|---|
a = b | aとbが等しい |
a != b | aとbが等しくない |
a <> b | aとbが等しくない |
a > b | aがbより大きい |
a >= b | aがb以上である |
a < b | aがbより小さい |
a <= b | aがb以下である |
a IS NULL | aが空である |
a IS NOT NULL | aが空でない |
a IN (b, c, d) | aがb、c、dのいずれかである |
a LIKE 'b%' | aの文字列にbが前方一致する |
a LIKE '%b' | aの文字列にbが後方一致する |
a LIKE '%b%' | aの文字列にbを含む |
「id > 10」も「10 < id」も意味は同じですが、一般的に、カラム名はbではなくaの位置に指定するとパフォーマンスが良くなります。興味のある方は調べてみてください。
またこれらの条件式は、「AND」や「OR」で複数つなげることができます。
このSQL文をPHPで実行するには以下のようにします。基本的にはデータの作成と同じ流れになります。
データを削除する
テーブルのデータを削除するSQL文は、以下のようになります。
PHP側は、データの更新と同じように書くことができます。
データを検索する
テーブルのデータを検索するSQL文は、以下のようになります。
このSQL文の実行結果は以下のようになります。
id | name | category_id | author_id |
---|---|---|---|
2 | 卵焼き | 1 | 1009 |
5 | ほうれん草の胡麻和え | 1 | 1011 |
PHPでSELECT文を実行すると、結果を配列やオブジェクトで取得できます(ライブラリによります)。
ORDER BY句
リレーショナルデータベースのテーブルでは、データの順番に関する情報を持っていません。ですが、何らかのデータ一覧を表示したい場合、順序というのは重要です。掲示板であれば新しい投稿から順に表示したいですし、会員名簿であれば五十音順がいいかもしれません。
ORDER BY句では、取得したデータを並び替えるための条件を指定します。条件はいくつでも指定することができ、先に書いたほうが優先されます。
ORDER BY句 | 第一条件 | 第二条件 |
---|---|---|
id | idの昇順 | なし |
id ASC | idの昇順(ASCは省略可) | なし |
update_date DESC, author_id | update_dateの降順 | author_idの昇順 |
LIMIT句、OFFSET句
データの一覧画面には、ページングの機能がよくありますよね。たとえば「3ページ目:全76件中、21件~30件を表示中」というようなものです。ページングでは、データベースから全データを取得し、そのうち一部分のみを画面に表示しているわけではありません。全データを取得してしまうと、PHPで使用するメモリが膨大になり、処理が重くなってしまうからです。このような場合は、データベースから条件に合うデータのうち一部のみを取得します。その場合に使用するのが、LIMIT句とOFFSET句です。
LIMIT句では、最大で何件のデータを取得するのかを指定します。10件ずつ表示したい場合は、「LIMIT 10」と指定します。
OFFSET句では何番目以降のデータを取得するかを指定します。10件ずつ表示して3ページ目ならば、「OFFSET 20」となるでしょう。OFFSET句を指定しない場合は、「OFFSET 0」と同じ意味になります。
getAll:検索結果をすべて取得する
getAllメソッドを使用すると、SQL文の実行結果をすべて取得できます。取得したいカラムは、SELECTに続けて指定できます。
SELECT対象 | 意味 |
---|---|
id | idを取得します |
id, name | idとnameを取得します |
id, category_id + 1 | idと、category_idに1を足したものを取得します |
* | すべてのカラムを取得します |
id | name | category_id | author_id |
---|---|---|---|
2 | 卵焼き | 1 | 1009 |
5 | ほうれん草の胡麻和え | 1 | 1011 |
getRow:検索結果を1件のみ取得する
データを1件のみ取得する場合、SQL文で「LIMIT 1」と指定してもよいですし、getRowメソッドを使うこともできます。getRowメソッドを使用すれば、「LIMIT 1」以外は共通している既存のSQL文を流用できます。
id | name | category_id | author_id |
---|---|---|---|
2 | 卵焼き | 1 | 1009 |
getOne:検索結果を1カラムのみ取得する
データを1カラムのみ取得する場合、SQL文で対象カラムを指定してもよいですし、getOneメソッドを使うこともできます。getOneメソッドを使用すれば、対象カラム名以外は共通している既存のSQL文を流用できます。
id |
---|
2 |
5 |
データベースライブラリによって細部は異なりますが、同じような機能を提供しているはずです。
おわりに
いかがでしたか。データベースやSQLについては、分厚い本を一冊書けてしまうくらい様々な機能があります。今回紹介したのは、そのうちの「データのエスケープを行う」「実行結果の成否判定を行う」などの基本的なことのみです。また、リレーショナルデータベースの最大の特徴は、複数のテーブルの連結や、統計的な検索などができることですが、ここでは触れていません。ぜひ専門の書籍などで勉強されることをお勧めします。それではまた。