4 データの問合せ

この章では、「Departments」ページに情報を追加して、第3章で作成したAnyco HRアプリケーションを拡張します。また、特定の部門の従業員レコードに対して問合せ、挿入、更新および削除を行う機能も実装します。

この章の内容は次のとおりです。

データベース・アプリケーション・ロジックの集中化
バインド変数を使用した問合せの作成
データベース・レコードのナビゲート
基本的な「Departments」ページの拡張

データベース・アプリケーション・ロジックの集中化

この項では、PHPアプリケーションにインクルードできるようにデータベース・アクセス・ロジックを個別のファイルに移動することによって、アプリケーション・コードを変更します。

第3章で作成したファイルをchap4という新しいディレクトリにコピーし、新しく作成したディレクトリに移動します。

Windowsの場合:

mkdir c:\program files\Apache Group\Apache2\htdocs\chap4
cd c:\program files\Apache Group\Apache2\htdocs\chap4
copy ..\chap3\* .

Linuxの場合:

mkdir $HOME/public_html/chap4
cd $HOME/public_html/chap4
cp ../chap3/* .

任意のエディタを使用して、anyco_cn.incというファイルを作成し、データベース接続情報の名前付き定数を定義します。このファイルを使用すると、接続情報を1箇所で変更できます。
```
<?php // File: anyco_cn.inc

define('ORA_CON_UN', 'hr');             // User name
define('ORA_CON_PW', 'hr');             // Password
define('ORA_CON_DB', '//localhost/orcl'); // Connection identifier

?>
```
ここでは、わかりやすくするために、サンプル・アプリケーション・コードにユーザー名およびパスワードが書き込まれています。デプロイするアプリケーションでは、アプリケーションのソース・コードにユーザー名およびパスワードの文字列を直接コーディングしないことをお薦めします。ユーザーにユーザー名およびパスワードの入力を求めるダイアログ・ボックスを実装する方法などのより安全な方法を使用することをお薦めします。

セキュリティ機能およびセキュリティ・プラクティスの詳細は、『Oracle Databaseセキュリティ・ガイド』および開発環境のドキュメントを参照してください。
データベース接続の作成、問合せの実行およびデータベースからの切断を実行する関数を宣言するanyco_db.incというファイルを作成します。 db_error ()という追加の関数をコールすることによって管理されるエラー処理を含む次のロジックを使用します。
```
<?php  // File: anyco_db.inc

function db_connect()
{
  // use constants defined in anyco_cn.inc
  $conn = oci_connect(ORA_CON_UN, ORA_CON_PW, ORA_CON_DB);
  if (!$conn) {
    db_error(null, __FILE__, __LINE__);
  }
  return($conn);
}

function db_do_query($conn, $statement)
{
  $stid = oci_parse($conn, $statement);
  if (!$stid) {
    db_error($conn, __FILE__, __LINE__);
  }

  $r = oci_execute($stid, OCI_DEFAULT);
  if (!$r) {
    db_error($stid, __FILE__, __LINE__);
  }
 $r = oci_fetch_all($stid, $results, null, null,
                     OCI_FETCHSTATEMENT_BY_ROW);
  return($results);
}

// $r is the resource containing the error.
// Pass no argument or false for connection errors

function db_error($r = false, $file, $line)
{
  $err =  $r ? oci_error($r) : oci_error();

  if (isset($err['message'])) {
    $m = htmlentities($err['message']);
  }
  else {  
    $m = 'Unknown DB error';
  }

  echo '<p><b>Error</b>: at line '.$line.' of '.$file.'</p>';
  echo '<pre>'.$m.'</pre>';

  exit;
}

?>
```
この例のdb_do_query()関数では、oci_fetch_all() OCI8関数を使用します。 oci_fetch_all()関数は、次の5つのパラメータをとります。
- $stid（実行する文の文識別子）。
- $results（問合せで戻されたデータが格納される出力配列変数）。
- 3つ目のパラメータは最初にスキップする行数を示し、NULLの場合は無視されます。
- 4つ目のパラメータはフェッチする最大行数を示し、NULLの場合は無視されます。この場合、問合せに該当するすべての行が戻されます。この例では、結果セットが大きくないため、それでも問題ありません。
- 最後のパラメータ・フラグOCI_FETCHSTATEMENT_BY_ROWは、$results配列内のデータが行ごとに編成され、各行に列値の配列が含まれていることを示します。値がOCI_FETCHSTATEMENT_BY_COLUMNの場合、$results配列は列ごとに編成され、各列エントリに各行の列値の配列が含まれます。このフラグに選択する値は、ロジックでデータを処理する方法によって異なります。
結果配列の構造を調べるには、問合せの実行後、PHPのvar_dump()関数を使用します。これはデバッグに有効です。次に例を示します。
```
print '<pre>';
var_dump($results);
print '</pre>';
```
db_error()関数は、3つの引数をとります。$rパラメータには、接続エラーを取得する場合はfalseまたはNULLを指定し、接続リソースまたは文リソースに関連するエラーを取得する場合はそのリソースを指定できます。$file値および$line値には __FILE__および __LINE__を実際のパラメータとして使用してそれぞれ値が移入されるため、データベース・エラーのレポート元のソース・ファイルおよび行をエラー・メッセージに表示できます。これによって、エラーの原因を簡単に追跡できるようになります。

db_error()関数は、oci_error()関数をコールしてデータベース・エラー・メッセージを取得します。

db_error()関数は、メッセージを出力する前に isset()関数をコールします。 isset()関数は、データベース・エラー構造のメッセージ・コンポーネントが設定されているかどうか、またはエラーが不明なものであるかどうかを確認します。

anyco_ui.incを編集します。 DEPARTMENTS表の問合せから1行取得した結果をHTML表形式に設定するには、次の関数を挿入します。

function ui_print_department($dept)
{
  if (!$dept) {
    echo '<p>No Department found</p>';
  }
  else {
    echo <<<END
  <table>
  <tr>
    <th>Department<br>ID</th>
    <th>Department<br>Name</th>
    <th>Manager<br>Id</th>
    <th>Location ID</th>
  </tr>
  <tr>
END;
    echo '<td>'.htmlentities($dept['DEPARTMENT_ID']).'</td>';
    echo '<td>'.htmlentities($dept['DEPARTMENT_NAME']).'</td>';
    echo '<td>'.htmlentities($dept['MANAGER_ID']).'</td>';
    echo '<td>'.htmlentities($dept['LOCATION_ID']).'</td>';
    echo <<<END
  </tr>
  </table>
END;
  }
}

第3章で説明したように、END;行の先頭には空白を配置しないでください。配置すると、残りのドキュメントが、出力対象テキストの一部として処理されます。

anyco.phpファイルを編集します。anyco_ui.incおよびanyco_db.incファイルをインクルードし、次のコードを使用して、department_idが80の部門の情報を問い合せて表示するデータベース関数をコールします。ファイルは、次のようになります。

<?php // File: anyco.php

require('anyco_cn.inc');
require('anyco_db.inc');
require('anyco_ui.inc');

$query =
  'SELECT   department_id, department_name, manager_id, location_id
   FROM     departments
   WHERE    department_id = 80';

$conn = db_connect();

$dept = db_do_query($conn, $query);
ui_print_header('Departments');
ui_print_department($dept[0]);
ui_print_footer(date('Y-m-d H:i:s'));

?>

アプリケーションに対して行った変更をテストするには、ブラウザのウィンドウに次のURLを入力します。

Windowsの場合:
```
http://localhost/chap4/anyco.php
```
Linuxの場合:
```
http://localhost/~<username>/chap4/anyco.php
```
ブラウザ・ウィンドウに、次のようなページが戻されます。

chap4_db_connect_002.gifの説明

バインド変数を使用した問合せの作成

WHERE句に値を含めた問合せを使用すると有効な場合があります。ただし、問合せの条件値が変更される可能性が高い場合は、問合せに値をエンコードするのは適切ではありません。バインド変数を使用することをお薦めします。

バインド変数は、問合せ内でコロンの後に指定するシンボリック名で、リテラル値のプレースホルダとして動作します。たとえば、anyco.phpファイルに作成した問合せ文字列は、バインド変数:didを使用して次のように記述しなおすことができます。

$query =
  'SELECT   department_id, department_name, manager_id, location_id
   FROM     departments
   WHERE    department_id = :did';

バインド変数を使用してSQL文をパラメータ化すると、次のメリットがあります。

コードを変更せずに様々な入力値で文を再利用できます。
Oracle Databaseでは、以前に同じ問合せ文字列を呼出したときの解析情報を再利用できるため、サーバーの問合せ解析時間が短縮され、問合せのパフォーマンスが向上します。
SQLインジェクションというセキュリティの問題に対する保護機能があります。
ユーザー入力で、引用符を特別に処理する必要はありません。

問合せでバインド変数を使用する場合は、問合せを実行する前に、PHPコードで、問合せで使用している各バインド変数（プレースホルダ）に実際の値を関連付ける必要があります。このプロセスは、ランタイム・バインディングと呼ばれます。

PHPアプリケーションで問合せのバインド変数を使用できるようにするには、PHPアプリケーション・コードに対して次の変更を行います。

anyco.phpファイルを編集します。バインド変数が使用されるように問合せを変更し、バインド変数に関連付ける値を格納するための配列を作成して、db_do_query()関数に$bindargs配列を渡します。
```
<?php // File: anyco.php
...

$query =
'SELECT   department_id, department_name, manager_id, location_id
 FROM     departments
 WHERE    department_id = :did';

$bindargs = array();
// In the $bindargs array add an array containing
// the bind variable name used in the query, its value, a length
array_push($bindargs, array('DID', 80, -1));

$conn = db_connect();
$dept = db_do_query($conn, $query, $bindargs);

...
?>
```
この例では、DIDというバインド変数は、パラメータ化した問合せの入力引数であり、値80に関連付けられています。このバインド変数の値は、後で動的に決定されます。また、OCI8レイヤーで長さを決定できるように、長さ構成要素を-1として渡します。バインド変数を使用してデータベースから出力を戻す場合は、サイズを明示的に指定する必要があります。
anyco_db.incファイルを編集します。$bindvars配列変数を3つ目のパラメータとしてとるようにdb_do_query()関数を変更します。oci_bind_by_name() OCI8コールをコールして、$bindvarsパラメータに指定したPHP値を問合せのバインド変数に関連付けます。ファイルは、次のようになります。
```
function db_do_query($conn, $statement, $bindvars = array())
{
  $stid = oci_parse($conn, $statement);
  if (!$stid) {
    db_error($conn, __FILE__, __LINE__);
  }

  // Bind the PHP values to query bind parameters
  foreach ($bindvars as $b) {
    // create local variable with caller specified bind value
    $$b[0] = $b[1];  
    // oci_bind_by_name(resource, bv_name, php_variable, length)
    $r = oci_bind_by_name($stid, ":$b[0]", $$b[0], $b[2]); 
    if (!$r) {
      db_error($stid, __FILE__, __LINE__);
    }
  }
  $r = oci_execute($stid, OCI_DEFAULT);
  if (!$r) {
        db_error($stid, __FILE__, __LINE__);
  }
  $r = oci_fetch_all($stid, $results, null, null,
                         OCI_FETCHSTATEMENT_BY_ROW);
  return($results);
}
```
バインドは、oci_execute()が実行される前にforeachループで実行されます。

$bindvars配列の各エントリでは、最初の要素に問合せのバインド変数名が含まれており、この名前を使用して同じ名前のPHP変数が作成されます。つまり、$$b[0]は$b[0]の値DIDをとり、$DIDというPHP変数を作成します。このPHP変数の値は、エントリの2つ目の要素から割り当てられます。

oci_bind_by_name()関数は、4つのパラメータをとります。リソースとしての$stid、配列エントリの最初の要素から導出された問合せ内のバインド変数名を表す文字列、そのバインド変数に関連付けられる値が格納されているPHP変数、および入力値の長さの4つです。
ここまでの変更の結果をテストするには、anyco.phpファイルおよびanyco_db.incファイルを保存し、次のURLを入力します。

Windowsの場合:
```
http://localhost/chap4/anyco.php
```
Linuxの場合:
```
http://localhost/~<username>/chap4/anyco.php
```
ブラウザ・ウィンドウに、次のようなページが戻されます。

chap4_db_connect_003.gifの説明

データベース・レコードのナビゲート

データベース・レコードのナビゲーションを追加するには、アプリケーション・ロジックに対していくつかの重要な変更を行う必要があります。この変更を行うには、次の操作を組み合せる必要があります。

データベース・レコードを1つずつ移動する「Next」および「Previous」ナビゲーション・ボタンを提供するHTMLフォームをインクルードします。
ページのHTTPリクエストが、「Next」または「Previous」ボタンをクリックしてポストされたかどうかを検出します。
HTTPセッション・ステートを使用して最後に問い合せた行を追跡します。HTTPリクエスト間で特定のクライアントの状態情報を保持するためにPHPセッションを開始します。最初のHTTPリクエストが最初のデータ行を取り出し、セッション・ステートを初期化します。ナビゲーション・ボタンで開始した後続リクエストでは、前のHTTPリクエストのセッション・ステートが組み合されるため、問合せで次に取り出すレコードを制御する変数をアプリケーションに設定できます。
アプリケーションの状態によって値が決まる一連の条件に基づいて、行のサブセットを戻す問合せを作成します。

データベース行のナビゲーションを追加するには、次の手順を実行します。

anyco_ui.incファイルを編集します。「Departments」ページに「Next」および「Previous」ナビゲーション・ボタンを追加します。ui_print_department()関数に、フォーム属性actionの値を指定する$posturlという2つ目のパラメータを追加します。 </table>タグを出力した後、「Next」および「Previous」ボタンのHTMLフォーム・タグをインクルードします。
```
<?php // File: anyco_ui.inc
...
function ui_print_department($dept, $posturl)
{
  ...
    echo <<<END
  </tr>
  </table>
  <form method="post" action="$posturl">
  <input type="submit" value="< Previous" name="prevdept">
  <input type="submit" value="Next >"     name="nextdept">
  </form>
END;
  }
}

?>
```
anyco.phpファイルを編集します。「Next」または「Previous」ボタンを使用してページを起動したかどうかを検出し、セッション・ステートを追跡するには、PHP関数session_start()をコールし、construct_departments()という関数を作成します。

データベース・アクセス・ロジックを新しいconstruct_departments()関数に移動して変更します。この関数は、ナビゲーションが実行されたかどうかを検出し、セッション・ステートを管理し、処理するデータベース・アクセス・レイヤーの副問合せを定義し、db_get_page_data()関数に接続してコールします。ファイルは、次のようになります。
```
<?php // File: anyco.php

require('anyco_cn.inc');
require('anyco_db.inc');
require('anyco_ui.inc');

session_start();
construct_departments();

function construct_departments()
{
  if (isset($_SESSION['currentdept']) &&
      isset($_POST['prevdept']) && 
      $_SESSION['currentdept'] > 1) {
    $current = $_SESSION['currentdept'] - 1;
  }
  elseif (isset($_SESSION['currentdept']) && 
          isset($_POST['nextdept'])) {
    $current = $_SESSION['currentdept'] + 1;
  }
  elseif (isset($_POST['showdept']) && 
          isset($_SESSION['currentdept'])) {
    $current = $_SESSION['currentdept'];
  }
  else {
    $current = 1;
  }

  $query = 'SELECT department_id, department_name, 
                   manager_id, location_id
            FROM   departments
            ORDER BY department_id asc';

  $conn = db_connect();

  $dept = db_get_page_data($conn, $query, $current, 1);
  $deptid = $dept[0]['DEPARTMENT_ID'];

  $_SESSION['currentdept'] = $current;

  ui_print_header('Department');
  ui_print_department($dept[0], $_SERVER['SCRIPT_NAME']);
  ui_print_footer(date('Y-m-d H:i:s'));
}

?>
```
construct_departments()関数の先頭にあるifおよび elseif構文は、HTTPポスト・リクエストでナビゲーション・ボタンを使用してページを処理したかどうかを検出し、セッション・ステートにcurrentdeptの数値が設定されているかどうかを追跡するために使用されています。変数$currentは状況によって異なり、「Previous」ボタンをクリックすると1つ減分され、「Next」ボタンをクリックすると1つ増分されます。それ以外の場合は、現行の部門に設定されるか、または初めて処理に対して1に初期化されます。

department_idのすべての部門行を昇順で取得するように問合せが形成されています。ORDER BY句は、ナビゲーション・ロジックに不可欠な部分です。この問合せは、多くの行で構成されているページを取得する db_get_page_data()関数内で副問合せとして使用されています。取得するページのページ当たりの行数は、db_get_page_data()関数の4つ目の引数として指定されています。データベースに接続した後、指定した問合せで取得した行セットを取り出すためにdb_get_page_data()がコールされています。 db_get_page_data()関数には、接続リソース、問合せ文字列、必要なデータ行の次ページの最初の行を示す$currentの値およびページ当たりの行数（この例では1ページ当たり1行）が指定されています。

1ページ分の行を取得するためにdb_get_page_data()がコールされた後、$current()の値がアプリケーションのセッション・ステートに保存されています。

ページのヘッダーとフッターを出力する間に、最近フェッチした部門行を表示するために ui_print_department()関数がコールされています。ui_print_department()関数は、$_SERVER['SCRIPT_NAME']を使用して、$posturlパラメータに現行のPHPスクリプト名を指定します。これで、HTMLフォームにアクション属性が設定され、「Next」または「Previous」ボタンをクリックするたびに、anyco.phpファイルがコールされます。
anyco_db.incファイルを編集します。行のサブセットを問い合せるようにdb_get_page_data()関数を実装します。
```
// Return subset of records
function db_get_page_data($conn, $q1, $current = 1,
                 $rowsperpage = 1, $bindvars = array())
{
  // This query wraps the supplied query, and is used
  // to retrieve a subset of rows from $q1
  $query = 'SELECT *
            FROM (SELECT A.*, ROWNUM AS RNUM
                  FROM ('.$q1.') A
                  WHERE ROWNUM <= :LAST)
            WHERE :FIRST <= RNUM';

  // Set up bind variables.
  array_push($bindvars, array('FIRST', $current, -1));
  array_push($bindvars,
             array('LAST', $current+$rowsperpage-1, -1));

  $r = db_do_query($conn, $query, $bindvars);
  return($r);
}
```
db_get_page_data()関数で問合せの構造を使用すると、一連の（1ページ分の）データベース行をナビゲートできます。

$q1に指定した問合せは、次の副問合せ内に副問合せとしてネストされています。
```
SELECT A.*, ROWNUM AS RNUM FROM $q1 WHERE ROWNUM <= :LAST
```
$q1に指定した問合せは、順序づけられた一連の行を取り出します。これらの行は、最初の行から次ページのサイズ（$rowsperpage）までのすべての行を戻す囲み問合せでフィルタ処理されています。 OracleのROWNUM関数（または疑似列）が、$q1に指定した問合せによって戻される各行に対して1で始まる整数を戻すため、この操作を実行できます。

副問合せを囲む問合せ$q1によって戻される行セットは、次の最も外側の問合せの条件で再度フィルタ処理されています。
```
WHERE :FIRST <= RNUM
```
この条件によって、:FIRSTの値（$currentの値）より前にある行が最終的な行セットから除外されます。この問合せを使用すると、最初の行が$current値で決まり、ページ・サイズが$rowsperpage値で決まる行セットをナビゲートできます。

$current値は、:FIRSTというバインド変数に関連付けられています。式$current+$rowsperpage-1によって、:LASTバインド変数に関連付けられている値が設定されます。
アプリケーションに対して行った変更をテストするには、変更後のファイルを保存し、Webブラウザに次のURLを入力します。

Windowsの場合:
```
http://localhost/chap4/anyco.php
```
Linuxの場合:
```
http://localhost/~<username>/chap4/anyco.php
```
anyco.phpページをリクエストすると、DEPARTMENT表の最初のレコードである「Administration」部門が表示されます。

chap4_db_nagivation_001.gifの説明
次の部門レコード（「Marketing」）にナビゲートするには、「Next」をクリックします。

chap4_db_nagivation_002.gifの説明
最初の部門レコード（「Administration」）に戻るには、「Previous」をクリックします。

chap4_db_nagivation_003.gifの説明

必要に応じて、「Next」および「Previous」をクリックして DEPARTMENTS表の他のレコードにナビゲートして、アプリケーションのテストおよび試用を継続して行うことができます。

注意:

DEPARTMENTS表の最後のレコードを越えてナビゲートすると、エラーが発生します。エラー処理の詳細は、第5章の「エラー・リカバリの追加」を参照してください。

ROWNUMとROW_NUMBER()

ハードコードされた問合せでPHP関数を記述する場合、戻される行数を制限する方法としてはROW_NUMBER()関数の方が簡単なことがあります。たとえば、すべての従業員の姓を戻す問合せがあるとします。

SELECT last_name FROM employees ORDER BY last_name;

次のように記述すると、51〜100までの行を選択できます。

SELECT last_name FROM
  SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name R FROM employees)
  where R BETWEEN 51 AND 100;

基本的な「Departments」ページの拡張

次の追加情報が含まれるように、「Departments」ページを拡張します。

部門のマネージャの名前
部門に配属されている従業員の数
部門の場所を識別する国名

これらの追加情報は、DEPARTMENTS、EMPLOYEES、LOCATIONS、COUNTRIESの各表間で結合操作を実行するように問合せを変更することによって取得できます。

「Departments」ページを拡張するには、次の手順を実行します。

anyco_ui.incファイルを編集します。「Manager ID」および「Location ID」への参照をそれぞれ「Manager Name」および「Location」に置き換え、「Department Name」の後に「Number of Employees」フィールドを挿入して、ui_print_department()関数を変更します。表のヘッダーおよびデータ・フィールドに必要な変更を行います。ファイルは、次のようになります。

function ui_print_department($dept, $posturl)
{
  if (!$dept) {
    echo '<p>No Department found</p>';
  }
  else {
    echo <<<END
  <table>
  <tr>
    <th>Department<br>ID</th>
    <th>Department<br>Name</th>
    <th>Number of<br>Employees</th>
    <th>Manager<br>Name</th>
    <th>Location</th>
  </tr>
  <tr>
END;
  echo '<td>'.htmlentities($dept['DEPARTMENT_ID']).'</td>';
  echo '<td>'.htmlentities($dept['DEPARTMENT_NAME']).'</td>';
  echo '<td>'.htmlentities($dept['NUMBER_OF_EMPLOYEES']).'</td>';
  echo '<td>'.htmlentities($dept['MANAGER_NAME']).'</td>';
  echo '<td>'.htmlentities($dept['COUNTRY_NAME']).'</td>';
  echo <<<END
  </tr>
  </table>
  <form method="post" action="$posturl">
  <input type="submit" value="< Previous" name="prevdept">
  <input type="submit" value="Next >"     name="nextdept">
  </form>
END;
  }
}

anyco.phpファイルを編集します。 construct_departments()の問合せ文字列を次のように置き換えます。

$query = 
  "SELECT d.department_id, d.department_name, 
       substr(e.first_name,1,1)||'. '|| e.last_name as manager_name,
       c.country_name, count(e2.employee_id) as number_of_employees
   FROM  departments d, employees e, locations l, 
         countries c, employees e2
   WHERE d.manager_id = e.employee_id
   AND d.location_id = l.location_id
   AND d.department_id = e2.department_id
   AND l.country_id = c.country_id
   GROUP BY d.department_id, d.department_name, 
         substr(e.first_name,1,1)||'. '||e.last_name,  
         c.country_name
   ORDER BY d.department_id ASC";

この文ではSQLリテラル文字列が一重引用符で囲まれているため、文の記述を簡略化するために問合せ文字列は二重引用符で囲まれています。

ファイルへの変更を保存し、Webブラウザに次のURLを入力して、変更をテストします。

Windowsの場合:
```
http://localhost/chap4/anyco.php
```
Linuxの場合:
```
http://localhost/~<username>/chap4/anyco.php
```
Webページの結果は、次のような出力になります。

chap4_enhance_dept_001.gifの説明