
PHP 5から PHP 7へ移行して最大の懸案はDB読み書き系。当時から PDO で組んでいたはず、と思い実際ほぼその通りだったのですが、DB書き込み系に PHP 7では古くて使えなくなったスクリプトがいました。 PDO へ書き換えるに当たり、 SQL 文のデバッグ出力が見にくいのがとても不便なので、 debugDumpParams を使い改善出来ないか調べてみました。
PHP 5での書き込み系
mysql_connectによるレコード追加SQLの例を示します。これはブログと同じサーバで運用している Hong Kong Tram Archive と言う日々のトラム画像のデータベースサイトの運用に使われているもので、新規画像の情報をフォト蔵よりAPIでXML取得し、レコードをループで追加しています(INSERT IGNORE として、テーブルに既にあるレコードはスキップする仕様)。
1 2 3 4 5 6 7 8 9 10 11 |
if(!$mdb = mysql_connect("IP_ADDR","USER","PASS")) {die;} if (!(mysql_select_db("DBNAME"))) {die;} foreach ($root->info->photo as $photo) { $sql="INSERT IGNORE INTO photozo (pid, aid, ttl, hpxl, wpxl, url1, url2, url3, regtime) VALUES ($photo->photo_id, $photo->album_id, '$photo->photo_title', $photo->original_height, $photo->original_width, '$photo->image_url', '$photo->original_image_url', '$photo->thumbnail_image_url', '".date('Y/m/d', strtotime($photo->regist_time))."')"; if (!(mysql_query($sql))) {die;} echo $sql; echo "<br />"; $sql = ""; } mysql_close($mdb); |
実際にはこのスクリプトを開発サーバで実行、テーブルレコードの差分CSVを本番DBへインポート、が定期更新作業ですが、PHPスクリプトをブラウザから叩いた戻りにSQL文字列を出力させてデバッグ確認としていました。
PHP 7の PDO による書き込み系
PHP触るのは数年ぶりですが、PDOでmysql DBへINSERTするひな型は次のように出来ました。
SQLステートメントの入れ物をオブジェクトとして生成し(prepare)、そこへパラメータをバインド(bindParam)して実行(execute)。この一連の動作全体をtry〜catchで囲みエラーハンドリングします。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
try { // MAKE DB CONNECTION $pdo = new PDO('mysql:host=IP_ADDR;dbname=DBNAME', 'USER', 'PASSWORD'); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $stmt = $pdo->prepare("INSERT IGNORE INTO photozo (pid, aid, ttl, hpxl, wpxl, url1, url2, url3, regtime) VALUES (:pid, :aid, :ttl, :height, :width, :irul, :ourl, :turl, :rtime)"); foreach ($root->info->photo as $photo) { $stmt->bindParam(':pid', $photo->photo_id, PDO::PARAM_INT); $stmt->bindParam(':aid', $photo->album_id, PDO::PARAM_INT); $stmt->bindParam(':ttl', $photo->photo_title, PDO::PARAM_STR); $stmt->bindParam(':height', $photo->original_height, PDO::PARAM_INT); $stmt->bindParam(':width', $photo->original_width, PDO::PARAM_INT); $stmt->bindParam(':irul', $photo->image_url, PDO::PARAM_STR); $stmt->bindParam(':ourl', $photo->original_image_url, PDO::PARAM_STR); $stmt->bindParam(':turl', $photo->thumbnail_image_url, PDO::PARAM_STR); $stmt->bindParam(':rtime', date('Y/m/d', strtotime($photo->regist_time)), PDO::PARAM_STR); $stmt->execute(); // DEBUG OUTPUT // DEBUG OUTPUT } $stmt = null; $pdo = null; } catch(PDOException $e) { echo "DBConn. Failed" . PHP_EOL; echo $e->getMessage(); exit; } |
問題はデバッグ出力をどうするか。SQLステートメントオブジェクトを単純にprint_rしただけでは、空っぽな入れ物が表示されるのみ。
1 2 |
// DEBUG OUTPUT print_r($stmt) |
1 2 3 4 5 |
PDOStatement Object ( [queryString] => INSERT IGNORE INTO photozo (pid, aid, ttl, hpxl, wpxl, url1, url2, url3, regtime) VALUES (:pid, :aid, :ttl, :height, :width, :irul, :ourl, :turl, :rtime) ) |
debugDumpParams を知る
debugDumpParams を使えばプリペアドステートメントの情報を直接出力出来るとあり、早速使ってみます。
1 2 |
// DEBUG OUTPUT $stmt->debugDumpParams(); |
これをブラウザソースで見ると1レコード分は次のように構成されているのが判ります。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
SQL: [153] INSERT IGNORE INTO photozo (pid, aid, ttl, hpxl, wpxl, url1, url2, url3, regtime) VALUES (:pid, :aid, :ttl, :height, :width, :irul, :ourl, :turl, :rtime) Sent SQL: [359] INSERT IGNORE INTO photozo (pid, aid, ttl, hpxl, wpxl, url1, url2, url3, regtime) VALUES (267485076, 9365846, 'P_20210211_074610', 4576, 3432, 'http://kura1.photozou.jp/pub/650/127650/photo/267485076.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267485076_org.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267485076_thumbnail.jpg', '2021/02/11') Params: 9 Key: Name: [4] :pid paramno=-1 name=[4] ":pid" is_param=1 param_type=1 Key: Name: [4] :aid paramno=-1 name=[4] ":aid" is_param=1 param_type=1 Key: Name: [4] :ttl paramno=-1 name=[4] ":ttl" is_param=1 param_type=2 Key: Name: [7] :height paramno=-1 name=[7] ":height" is_param=1 param_type=1 Key: Name: [6] :width paramno=-1 name=[6] ":width" is_param=1 param_type=1 Key: Name: [5] :irul paramno=-1 name=[5] ":irul" is_param=1 param_type=2 Key: Name: [5] :ourl paramno=-1 name=[5] ":ourl" is_param=1 param_type=2 Key: Name: [5] :turl paramno=-1 name=[5] ":turl" is_param=1 param_type=2 Key: Name: [6] :rtime paramno=-1 name=[6] ":rtime" is_param=1 param_type=2 |
実際は数十レコードを処理するのが通例なので、実行後にレコード数をカウントしやすくする意味でも、必要な情報の1レコード1行への整形が必要です。
debugDumpParams を取り込む
debugDumpParamsはあくまで直接出力なので、それをそのまま変数に入れたり整形したりすることは出来ません。それを可能にするためのひと工夫が、先ほどのリファレンスページ下端にありました。その参照元としているのがこちら(Great Tnx!!)。
Output Bufferを使ってdebugDumpParamsの出力を変数へ取り込む関数を定義し、ステートメントオブジェクトを引数に渡します。
1 2 3 4 5 6 7 |
function pdo_debugStrParams($s) { ob_start(); $s->debugDumpParams(); $r = ob_get_contents(); ob_end_clean(); return $r; } |
debugDumpParams を整形
debugDumpParamsの出力のうち欲しいのは上項のブラウザソース出力の2行目の部分ですが、実際には改行されていないようで、行単位での抽出は出来ませんでした。逆に全ては1行であると考えて、パターンマッチングを使った抽出にしてみます。
preg_match関数で「Sent SQL:」より以降初回に出現する「Params:」迄の間の中身 を抽出して表示します。
1 2 3 |
// DEBUG OUTPUT preg_match('/(?<=Sent SQL:).*?(?=Params:)/s', pdo_debugStrParams($stmt), $matches); echo $matches[0].'<br />'; |
1 2 3 4 5 6 7 8 9 10 |
[359] INSERT IGNORE INTO photozo (pid, aid, ttl, hpxl, wpxl, url1, url2, url3, regtime) VALUES (267544464, 9365846, 'P_20210222_074320', 4576, 3432, 'http://kura1.photozou.jp/pub/650/127650/photo/267544464.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267544464_org.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267544464_thumbnail.jpg', '2021/02/22') <br /> [359] INSERT IGNORE INTO photozo (pid, aid, ttl, hpxl, wpxl, url1, url2, url3, regtime) VALUES (267544463, 9365846, 'P_20210222_074258', 4576, 3432, 'http://kura1.photozou.jp/pub/650/127650/photo/267544463.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267544463_org.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267544463_thumbnail.jpg', '2021/02/22') <br /> [359] INSERT IGNORE INTO photozo (pid, aid, ttl, hpxl, wpxl, url1, url2, url3, regtime) VALUES (267544461, 9365846, 'P_20210222_074127', 4576, 3432, 'http://kura1.photozou.jp/pub/650/127650/photo/267544461.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267544461_org.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267544461_thumbnail.jpg', '2021/02/22') <br /> [359] INSERT IGNORE INTO photozo (pid, aid, ttl, hpxl, wpxl, url1, url2, url3, regtime) VALUES (267544460, 9365846, 'P_20210222_074119', 4576, 3432, 'http://kura1.photozou.jp/pub/650/127650/photo/267544460.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267544460_org.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267544460_thumbnail.jpg', '2021/02/22') <br /> [359] INSERT IGNORE INTO photozo (pid, aid, ttl, hpxl, wpxl, url1, url2, url3, regtime) VALUES (267544459, 9365846, 'P_20210222_073846', 4576, 3432, 'http://kura1.photozou.jp/pub/650/127650/photo/267544459.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267544459_org.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267544459_thumbnail.jpg', '2021/02/22') <br /> [359] INSERT IGNORE INTO photozo (pid, aid, ttl, hpxl, wpxl, url1, url2, url3, regtime) VALUES (267544458, 9365846, 'P_20210222_073813', 4576, 3432, 'http://kura1.photozou.jp/pub/650/127650/photo/267544458.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267544458_org.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267544458_thumbnail.jpg', '2021/02/22') <br /> [359] INSERT IGNORE INTO photozo (pid, aid, ttl, hpxl, wpxl, url1, url2, url3, regtime) VALUES (267544456, 9365846, 'P_20210222_073745', 4576, 3432, 'http://kura1.photozou.jp/pub/650/127650/photo/267544456.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267544456_org.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267544456_thumbnail.jpg', '2021/02/22') <br /> [359] INSERT IGNORE INTO photozo (pid, aid, ttl, hpxl, wpxl, url1, url2, url3, regtime) VALUES (267544454, 9365846, 'P_20210222_073724', 3432, 4576, 'http://kura1.photozou.jp/pub/650/127650/photo/267544454.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267544454_org.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267544454_thumbnail.jpg', '2021/02/22') <br /> [359] INSERT IGNORE INTO photozo (pid, aid, ttl, hpxl, wpxl, url1, url2, url3, regtime) VALUES (267544444, 9365846, 'P_20210222_073628', 4576, 3432, 'http://kura1.photozou.jp/pub/650/127650/photo/267544444.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267544444_org.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267544444_thumbnail.jpg', '2021/02/22') <br /> [359] INSERT IGNORE INTO photozo (pid, aid, ttl, hpxl, wpxl, url1, url2, url3, regtime) VALUES (267544441, 9365846, 'P_20210222_073606', 4576, 3432, 'http://kura1.photozou.jp/pub/650/127650/photo/267544441.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267544441_org.jpg', 'http://kura1.photozou.jp/pub/650/127650/photo/267544441_thumbnail.jpg', '2021/02/22') |
これで今まで通りに、INSERT文実行後のデバッグ確認を出来るようになりました。
※ちなみにpreg_match関数で使った正規表現は、html xml タグの中身を取り出す時によく使う手法です。