PHP＋SQLite：SELECT文の使い方

今回からはいよいよ、PHPを使ってSQLiteを操作していく。
なお、PHPバージョン5以上を対象としている。SQLiteへの接続とSQL文の実行については、データベースに依存しない方式である PDO（PHP Data Objects）クラスを利用する。

（2023年11月26日）PHP8.3に関する記事を追加
（2022年11月13日）PHP8対応，他．

1. データベースを検索する手順
2. サンプル・プログラム
3. 解説：準備
4. 解説：PDOの紹介
5. 解説：DBへの接続
6. 解説：SQLの実行
7. 解説：DBの解放
8. サンプル・プログラム：徳川家康の生年・没年を取得
9. 解説：一部カラムを取り出すSQL文
10. 解説：カラムを数値で指定する
11. 参考サイト

SQLite に限らず、一般にSQLタイプのデータベースシステム（RDBMS）を操作して検索するには、次のような手順になる。

1. DBをオープンする
2. SQLを実行する
3. 結果を取り出す
4. 結果を表示する
5. DBをクローズする

この手順は、じつはファイル操作とほとんど同じである。ファイルをオープンした後にSQLを実行する操作が加わったと考えてもらえると分かりやすいだろう。とくに SQLite の場合、DB本体が1つのファイルになっているので、これらを含めた取り扱いがファイル操作に近い。

プログラムを実行する

ダウンロード（PHP8対応）

サンプル・プログラムは、前回、SQLiteManager で実行させたのと同じSQL文

SELECT * FROM chronologic WHERE name='徳川家康';

の実行を目標にしている。

では、それぞれの手順を細かく見ていくことにしよう。

  45: //表示幅（ピクセル）
  46: define('WIDTH', 600);
  47: 
  48: //SQLite DBファイル名
  49: define('DBFILE', './sqlitemanager/pahoodb.sqlite3');    //各自の環境に合わせて変更すること
  50: 
  51: //実行するSQL
  52: define('SQL', "SELECT * FROM chronologic WHERE name='徳川家康';");

まず、文字コードを定数 INTERNAL_ENCODING に記載する。「SQLiteManagerでDB作成」で解説したように、データベース "pahoodb.sqlite3" は UTF-8 で作ってある。
他の文字コードも利用できるのだが、PHPバージョン5.6でUTF-8がデフォルトになったこともあり、データベースもそれに合わせておいた方が無難である。

各自の環境に合わせ、SQLiteのDBファイル名を定数 DBFILE に記述する。相対パスで記述する場合は、このスクリプトがある位置からの相対位置である。

実行するSQL文は、定数 SQL に記述する。これは、前回、SQLiteManagerで使ったものと同じである。

PHPは、SQLiteをはじめ、オープンソースの MySQL、PotstgreSQL、商用RDBMSの Microsoft SQL Server、Oracle、IBM DB2 など、さまざまなRDBMSに対応しており、それぞれ固有の関数群が用意されている。
だが、固有の関数群を使ってしまうと、後々、RDBMSを変更した際の改造コストが大きくなってしまう。

一方、「データベースを検索する手順」で述べたように、RDBMSの操作手順というのは製品によって大きな違いはない。製品固有の命令を使わなければ、SQL文も共通である。
そこでPHPバージョン5にはPDO（PHP Data Objects）クラスが用意され、RDBMSの違いに関係なく、同じプログラムでDB操作ができるようになった。

また、PHP 5.5 では、MySQLの操作方法として、固有の関数が廃止されることがアナウンスされた。SQLite 固有の関数は引き続きサポートされるが、せっかくなので、ここでは PDO を使っていくことにする。

 234: if (! isphp5over()) {
 235:     $errmsg = 'PHP5以上でないと動作しません．';
 236: } else if (! isSQLiteReady()) {
 237:     $errmsg = 'SQLiteが利用できません．';
 238: } else {
 239:     $errmsg = '';
 240:     try {
 241:         $pdo = new PDO('sqlite:' . DBFILE);
 242:         $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 243:     } catch (PDOException $e) {
 244:         $errmsg = 'SQLite : ' . $e->getMessage();
 245:     }
 246: }

まず、ユーザー関数 isphp5over を使って、PHP 5以上であることをチェックする。

SQLite DBファイル DBFILE へ接続するには、PDOオブジェクトを生成する。引数は、データソース名（DSN：Data Source Name）である。

DSN は、PDO を使って様々なRDBMSに接続するための識別子である。
たとえば SQLite であれば、"sqlite:DBファイル名" のように指定する。
MySQL なら、"mysql:host=ホスト名;dbname=DB名" のように指定し、第2引数でユーザー名を、第3引数でパスワードを指定する。
その他のRDBMSについては、「PDO ドライバ」を参照していただきたい。

DB接続の際（PDOインスタンス生成時）に例外をキャッチアップしている。PHP 8.3では、SQLite3で例外を使うことが推奨されるようになった。警告は将来のバージョンで削除されるとのこと。

オブジェクトが生成できたら、PDO::setAttribute を使って属性を設定する。ここでは、ATTR_ERRMODE（エラーレポート）として、ERRMODE_EXCEPTION（例外を投げる）を設定する。DB接続に失敗したら、catchでメッセージを受け取るようにしている。

 203:         foreach ($pdo->query($sql) as $row) {
 204:             $res .=<<< EOT
 205: <tr>
 206: <td class="index">{$row['name']}</td>           <!-- カラム name -->
 207: <td class="year">{$row['birth']}</td>           <!-- カラム birth -->
 208: <td class="year">{$row['death']}</td>           <!-- カラム death -->
 209: <td class="comment">{$row['comment']}</td>      <!-- カラム comment -->
 210: </tr>
 211: 
 212: EOT;
 213:         }

SQL文の実行は、PDO::query を利用する。

冒頭で、DB操作はファイル操作とほとんど同じと述べたが、SELECT文は、テキストファイルから1行ずつデータを取り込むのに似ている。
PDO::query を使うと、関数  fgets  を使って1行ずつ読み込むように、for 文を使ってマッチしたデータを1レコードずつ読み込むことができる。

PDO::query は、最初の関数コールの中でSQL文を実行し、結果があれば PDOStatement オブジェクトとして返す。
foreach 文と組み合わせることで、PDO::query が失敗するまで繰り返せば、1レコードずつデータを読み込むことができる。PDO::exec とは異なり、2週目からは PDOStatement オブジェクトからデータを取り出すので、パフォーマンスが低下することもない。

あとは、取り出したオブジェクトを配列 $row に入れ、それを画面表示するためのHTML文に変換するだけである。

 256: $pdo = NULL;

DB処理が終わったら、PDOオブジェクトに NULL を代入して解放する。

プログラムを実行する

2番目のサンプル・プログラムは、徳川家康の生年・没年のみを取り出すものである。

  51: //実行するSQL
  52: define('SQL', "SELECT birth, death FROM chronologic WHERE name='徳川家康';");

最初のSQL文では取り出すカラムの種類は意識していなかったが、今度は、birth, death の2つのカラムだけ取り出す。
2つのSQL文を比較してみよう。

SELECT * FROM chronologic WHERE name='徳川家康';
SELECT birth, death FROM chronologic WHERE name='徳川家康';

上がすべてのカラムを取り出すSQL文、下がbirth, death カラムだけ取り出すSQL文である。SELECT と FROM の間の表現が違う。
SELECTの直後にアスタリスク（＊）を記述すると全てのカラムを、カラム名をカンマ（，）で区切った場合には指定したカラムだけ取り出す指示になるのだ。

PDO::query で配列にレコードを取り出したとき、前回のようにカラム名を添字にすることもできるし、今回のように0ではじまる整数で指定することもできる。この場合、SELECTの直後に指定した順番――すなわち、birth, death の順に配列に格納される。

 200:         foreach ($pdo->query($sql) as $row) {
 201:             $res .=<<< EOT
 202: <tr>
 203: <td class="year">{$row[0]}</td>     <!-- カラム birth -->
 204: <td class="year">{$row[1]}</td>     <!-- カラム death -->
 205: </tr>
 206: 
 207: EOT;
 208:         }

• SQLite関数：PHPファン
• (PHP)PDOで、SQLiteにアクセスしてみた：3流プログラマのメモ書き
• (PHP) SQLiteでカラム名を取得する