PR

PHP Mysqliでテーブルからデータを読み込みながら同じDBの別テーブルを参照するには

IT関連
2023.12.10
この記事は約10分で読めます。

PHPのMysqli拡張モジュールでのデータ読込を見よう見まねでサンプルコーディンクを見ながら作っているPHP初心者なのですが、落とし穴に落ちてしまいなかなか抜け出せなかったタイトルの件について解決方法をご紹介したいと思います。

そもそも「はまってしまった原因」は、SQLServerのように「カーソルを2つ開いて、それぞれのカーソルを使ってテーブルを読めば良いのでは?」と勝手に思い込んだためではあります。

ただそうは言っても見よう見まねでコーディングしていると、どうしてもPHPやMySQLに対する「基本の基」を理解しようとはせず、ひたすら試行錯誤を繰り返してしまう事になりがちです。

今後はそうならないように忘備録として解決方法を残して置きたいと思います。

※なおPHPはver8.1.22、MySQLはver5.7を使用しています。

スポンサーリンク

別々のテーブルを開くシチュエーション

よくあるのは、トランザクションテーブルの項目からマスターテーブルの情報を読み込んで処理をする形です。

当然だだ単にIDから名前を引くような簡単な処理であれば、SQL文のなかでLEFT JOIN(左外部結合)などを使って一緒に必要なデータを読み込めば良いのですが、少し複雑な処理をしたい時にはSQL文だけで実装するのが難しい場合があります。

このような場合は、トランザクションテーブルからデータを1件づつ読み込んでループ処理をしながら、必要なマスターテーブルから情報を読み込んで複雑な計算や場合分けの処理をする事になります。

PHPのMySQLの用語を使わせていただくと、1つのテーブルをfetchで読み込みながら、同じデータベースの別のテーブルに対してqueryを開いて情報を読み出すような事になります。

PHPでMySQLを使うには

PHPからMySQLに接続する際にPHP Ver5以降では次の2つの主要な方法がありますが、今回は「1」のmysqli 拡張モジュールのコーディング事例でご紹介いたします。

  1. PHP の mysqli 拡張モジュール
    • MySQL のバージョン 4.1.3 以降の新機能を利用するために開発されました。
      • 使用タイプとしてオブジェクト指向型と手続き型がありますが、今回はオブジェクト指向型を使っています。
  2. PHP Data Objects (PDO)
    • MySQL以外のデータベースサーバー、例えばPostgreSQLなどを含めて同じようにコーディングをする事ができます。
      • データベースサーバーはMySQLのみを使用しているので、こちらは選択しませんでした。

なお両者の違いの詳細は下記のPHPドキュメントをご参照ください。

PHP: mysqli の概要 - Manual
PHP is a popular general-purpose scripting language that powers everything from your blog ...
www.php.net

同一データベースでfetchをしながら別テーブルを読もうとすると…

今回ご紹介するコーディング事例ではSQL文のなかでLEFT JOIN(左外部結合)を使えば良い程度に簡単なものにしていますので、その点はお含み置きください。

サンプル題材としては、本サイトではWordPressにテーマとしてはCocoonを使用しているので、WordPressのデータベースの中にある、Cocoonのアクセスログを書込むトランザクションテーブル「接頭辞(prefix)_cocoon_accesses」とWordPressのポストを管理するマスターテーブル「接頭辞(prefix)_posts」を使用いたします。

なお、この2つのテーブルは同じWordPressのデータベースの中に存在しています。

コーディング事例の処理内容

処理内容を簡単に列挙いたします。

  • WordPressのデータベースにコネクションをオープンします。
  • Cocoonのアクセスログ テーブル(cocoon_accesses)をID(自動附番)を使って範囲指定して、post_idとその他の項目を読み込むSelect文を準備します。
  • 結果を保存するための変数をバインドします。
  • 対象範囲のすべてのデータに対して、結果を取得してバインド変数に格納します。
    • 取得したpost_idを使ってWordPressのpostsテーブルからpost_titleを読み込みます。
    • 得られた情報をまとめて書出します。

ざっくりと、このような処理内容になります。

間違ったコーディング事例①と発生するエラーの内容

この事例のままではエラーになるのですが、まずはその状態でご紹介いたします。

<?php
$dbwp = new mysqli('DBサーバーホスト名', 'DBユーザー名', '〃パスワード', 'DB名');
/* 接続状況をチェックします */
if ($dbwp->connect_errno) {
  printf("Connect failed:%s\n", $dbwp->connect_error);
  exit();
}

$id1 = 1;
$id2 = 20;
// プリペアドステートメントを実行
$stmt = $dbwp->prepare('select post_id, date, count from prefix_cocoon_accesses where id between ? and ?');
$stmt->bind_param('ii', $id1, $id2);
$stmt->execute();
$stmt->bind_result($post_id, $date, $count);
while($stmt->fetch()){
  $response = $dbwp->query('select post_title from prefix_posts where ID = '.$post_id);
  $pt = '';
  if($items = $response->fetch_assoc())  $pt = $items['post_title'];
  unset($response);
  printf('1:%d 2:%s 3:%d 4:%d<br>', $post_id, $date, $count, $pt);
}
// ステートメントを閉じる
$stmt->close();
// 接続を閉じます
$dbwp->close();
?>

17行目で同じDBのWordPressのpostsテーブルをクエリで読み込もうとすると次のエラーが起こります。 

Fatal error: Uncaught mysqli_sql_exception: Commands out of sync; you can't run this command now

赤字の英文をGoogleで調べると次のMySQLのページが出て来ます。

MySQL :: MySQL 8.0 リファレンスマニュアル :: B.3.2.12 コマンドは同期されていません
dev.mysql.com

mysql_free_result() を呼び出す前に、mysql_use_result() を使用して、新しいクエリーを実行しようとした場合に発生することがあります。 

このエラーの内容をちゃんと調べれば迷走しなくても正解にたどり着けたはずなのですが…ただPHP目線では書かれていないので、PHP初心者としては難しかったのかもしれません…

ちなみにですが、同じデータベースにあるprefix_postsテーブルではなく別データベースにある、例えばextraprefix_postsテーブルであれば(そのためにはもう一つ接続をオープンする必要がありますが、そこだけ直せば)エラーは起こらずに正しく処理する事ができます。

間違ったコーディング事例②：store_resultステートメントを付ける

前章で引用しました”mysql_free_result() を呼び出す前”をどう解釈するか?が解決策に向けた重要なポイントになります。

このmysql_free_resultは、mysqliステートメントのfree_resultに当たりますので、詳細については次のmysqli_resultの中のfreeドキュメントをご参照ください。

PHP: mysqli_result::free - Manual
PHP is a popular general-purpose scripting language that powers everything from your blog ...
www.php.net

ところでfree_resultステートメントを実行すると結果が解放されてしまうので、その前に一度実行したSQL文の結果を内部バッファに保存する必要があります。

このようなステートメントが無いか?調べるとstore_resultステートメントが存在することが解ります。(これもPHP初心者にとってはなかなかたどり着けなかったのですが…)

以上を踏まえて前章のコーディング事例のエラーがでないようにするためには下記のようにexecuteとbind_resultの間に1行「内部バッファに結果を保存するstore_resultステートメント」を追加すれば良いはずです…

---- 省略------
$stmt->execute();
$stmt->store_result(); 
$stmt->bind_result($post_id, $date, $count);
---- 省略------

ただし、こうする事で確かにエラーは出なくなるのですが、肝心なマスターテーブルを読み込んだ結果が表示されませんでした…

なぜ表示されないのか?…もしかしたら何か解決策があるのかもしれないと思って調べたは見ましたが、残念ながら見つけられませんでした…

正しいコーディング事例：get_resultステートメント

どうやらstore_resultステートメントを呼んだだけでは上手くいかない事は解りました。

そうなると、MySQLでは「カーソルを2つ開いて、それぞれのカーソルを使って同じデータベースの別べのテーブルを読み込む事はできない」のではないか?と不安になりますが、再度そのようなステートメントが存在しないのか?調べてみるとget_resultステートメントがある事に気が付きました。

get_resultステートメントはプリペアドステートメントから結果を mysqli_result オブジェクトとして取得する動きをします。

ただしこの場合は結果をbindして取得する事ができなくなり、一度配列変数に保存してから対象項目を配列番号または項目名で取得する形になります。 (今回の事例では項目名を使用しています)

そして配列変数に保存した後は、free_resultステートメントでバッファーを解放することができます。 (今回の事例では明示的に開放しなくても長い処理ではないので大丈夫なのですが…)

なお、バッファー解放とともに注意しなければならないのは、あまり大量なデータ量の結果を受け取らないようにSQL文に適切な条件を設定する事です。

※同じコーディング部分は省略をしていますのでお含み置きください。

---- 省略------
$stmt->execute();
$result = $stmt->get_result();
$stmt->free_result();
while($row = $result->fetch_assoc()){
  $response = $dbwp->query('select post_title from prefix_posts where ID = '.$row['post_id']);
  $pt = '';
  if($items = $response->fetch_assoc())  $pt = $items['post_title'];
  unset($response);
  printf('1:%d 2:%s 3:%d 4:%s<br>', $row['post_id'], $row['date'], $row['count'], $pt);
}
unset($result);
// ステートメントを閉じる
$stmt->close();
---- 省略------

※ステートメントでも結果配列でもfetchで取り出すというのも、PHP初心者には理解し難いところです…

これによってようやく意図した結果を得る事ができるようになりました。

最後に

get_resultステートメント以外に方法が無いのか?は正確には分かっていないのですが、実際に動きを調べた限りではこの解決策にしかたどり着けませんでした。

その理由の一つとしては、なかなかこの辺りの話題を取り上げてくれている記事が見つからなかった事もあるのですが…それが当たり前すぎてなかったのか?、それともそのような事をしたいというケースが少ないのか?も解らない所です…

PHPの公式サイトやMySQLの公式サイトにいろいろな情報はあるのですが初心者には理解し難い内容になっているところもあり、もう少し噛み砕いた書き方がされていたら有難いなあ～と思います。

以上最後までご一読いただき誠にありがとうございました。

スポンサーリンク