SQLを使う案件【SQL編】

抽出したいデータが保存されているテーブルとカラムを見つけられましたか?

それでは、答え合わせしていきましょう!!

SQL文を作成する

データベースから必要なデータを取得するための SQL 文を作成していきましょう。

userPraivateDataテーブル

「SELECT カラム名 FROM テーブル名」を使い、userPraivateData テーブルから scheduleUnixTime、options、praivateData を選択します。

SELECT scheduleUnixTime, options, praivateData
FROM wp_booking_package_userPraivateData;
scheduleUnixTime
予約日時
options
サービス名
praivateData
氏名(入力フォームの値)
1668301200[{“name”:”1r reservation”,}]
[{“id”:”lastname”
“name”:”Last name”
“value”:”Fujii”},
{“id”:”firstname”,
“name”:”First name”,
“value”:”Kaoru”,}]

calendarAccountテーブル

SELECT name
FROM wp_booking_package_calendarAccount;
name
カレンダー名
Meeting Room Calendar

2つのテーブルを内部統合する

「INNER JOIN テーブル名 ON 結合条件」を使い、praivateData テーブルの accountKey と calendarAccount テーブルの key が一致する限り、両方のテーブルからすべての行を選択します。

SELECT wp_booking_package_userPraivateData.scheduleUnixTime,
       wp_booking_package_userPraivateData.options,
       wp_booking_package_userPraivateData.praivateData,
       wp_booking_package_calendarAccount.name
FROM wp_booking_package_userPraivateData
INNER JOIN wp_booking_package_calendarAccount
ON wp_booking_package_userPraivateData.accountKey = booking_package_calendarAccount.key;

抽出するデータの条件を追加し、日付順に並べ変える

今日以降の承認されたデータのみを表示したいので、WHERE 句を使って条件を指定します。

また、日付順に表示したいので、ORDER BY 句を使ってデータを並べ替えます。

WHERE wp_booking_package_userPraivateData.scheduleUnixTime > strtotime( date('Y/m/d') )
AND wp_booking_package_userPraivateData.status = 'approved'
ORDER BY scheduleUnixTime;

WHERE 句で条件を指定するさいに、次の2つの PHP 関数を使用しています。

date() は、今日の日付を取得します。
‘Y/m/d’は、日付のフォーマットを指定しています。

strtime() は、英文形式の日付を Unix タイムスタンプに変換します。
scheduleUnixTime が UnixTime だからです。

WordPressでSQLを実行する

必要なデータを抽出するための SQL 文が書けました。

次は、この SQL 文を WordPress で実行する方法を見ていきましょう。

$wpdbグローバルオブジェクトを使用

WordPress の PHP コードで $wpdb にアクセスするための推奨方法は、global キーワードを使用して、$wpdb をグローバル変数として宣言することです。

WordPress は、wpdb クラスのインスタンス化であるグローバル オブジェクト $wpdb を提供します。
$wpdb は WordPress のデータベースと通信するためにデフォルトでインスタンス化されます。

クラスとは設計図で、インスタンスとは実体です。
インスタンスを生成するプロセスをインスタンス化といいます。
クラスをもとに実際のデータを作成します。

$wpdbのプロパティ

利用するプロパティは次の2つです。

$prefix

サイトに割り当てられた WordPress テーブルのプレフィックス

$last_error

MySQL が生成した最新のエラー テキスト

$wpdbのメソッド

利用するメソッドは次の2つです。

get_results

SQL クエリを実行し、データベースから実行結果をすべて取得します。

prepare

安全に実行できるように SQL クエリを準備します。

SQL インジェクション攻撃を防ぐために、SQL クエリを実行する前に、SQL クエリ内のすべてのデータを SQL エスケープします。

$query

実行したい SQL クエリとプレースホルダーを含む sprintf() のようなフォーマット文字列
利用可能なプレースホルダーは、%s(文字列)、%d(整数)、%f (小数)

$args

プレースホルダーに代入する値
ユーザーがこのパラメーターに直接入力できないように注意する

その他

$query は文字列なので、引用符(’ または “)で囲む方法が一般的です。
ここでは、文字列を区切る方法の1つである、ヒアドキュメント構文を使用しています。
ヒアドキュメント構文は、「<<<開始 ID」と「終端 ID」で文字列を囲みます。
ID 名は任意なので、EOD(End Of Document)と命名しています。

コード

// wpdbクラスをグローバル変数として宣言する
global $wpdb;

// 本日以降 AND 承認済み の予約データを取得するための変数を設定する
$today = strtotime( date( 'Y/m/d' ) );
$status = 'approved';

// DBからSQLの実行結果をすべて取得する
$results = $wpdb->get_results(
 $wpdb->prepare(
  <<<EOD
  SELECT {$wpdb->prefix}booking_package_userPraivateData.scheduleUnixTime,
	 {$wpdb->prefix}booking_package_userPraivateData.options,
         {$wpdb->prefix}booking_package_userPraivateData.praivateData,
	 {$wpdb->prefix}booking_package_calendarAccount.name
  FROM {$wpdb->prefix}booking_package_userPraivateData
  INNER JOIN {$wpdb->prefix}booking_package_calendarAccount
  ON {$wpdb->prefix}booking_package_userPraivateData.accountKey = {$wpdb->prefix}booking_package_calendarAccount.key
  WHERE {$wpdb->prefix}booking_package_userPraivateData.scheduleUnixTime > %d
  AND {$wpdb->prefix}booking_package_userPraivateData.status = %s
  ORDER BY scheduleUnixTime
  EOD,
  $today, // 信頼できない整数(関数がサニタイジングを行う)
  $status // エスケープされていない文字列(関数がサニタイジングを行う)
  )
);

// get_results からのエラーを確認する
  if ( $wpdb->last_error ) {
    return '<p>wpdb error: ' . $wpdb->last_error. '</p>';
  }

まとめ

次回は、SQL の実行結果から必要なデータを取り出して、HTML テーブルを作成する方法について解説します。

var_dump()gettype() などの PHP 関数を利用して、取得したデータの構造について調べると、データへのアクセス方法が分かります。

また、json_decode() は JSON 文字列をデコードします。

これらのヒントをもとに、予約一覧表の作成に挑戦してみてください。

コメントする

メールアドレスが公開されることはありません。 が付いている欄は必須項目です