PDOで実行したSQL文をdebugDumpParamsから抽出表示する

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 として、テーブルに既にあるレコードはスキップする仕様）。

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);

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 " ";

$sql = "";

}

mysql_close($mdb);

実際にはこのスクリプトを開発サーバで実行、テーブルレコードの差分CSVを本番DBへインポート、が定期更新作業ですが、PHPスクリプトをブラウザから叩いた戻りにSQL文字列を出力させてデバッグ確認としていました。

図1.PHP5 mysql_connectでのデバッグ出力例

PHP 7の PDO による書き込み系

PHP触るのは数年ぶりですが、PDOでmysql DBへINSERTするひな型は次のように出来ました。

SQLステートメントの入れ物をオブジェクトとして生成し（prepare）、そこへパラメータをバインド（bindParam）して実行（execute）。この一連の動作全体をtry〜catchで囲みエラーハンドリングします。

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;
}

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

}

$stmt = null;

$pdo = null;

} catch(PDOException $e) {

echo "DBConn. Failed" . PHP_EOL;

echo $e->getMessage();

exit;

}

問題はデバッグ出力をどうするか。SQLステートメントオブジェクトを単純にprint_rしただけでは、空っぽな入れ物が表示されるのみ。

// DEBUG OUTPUT
print_r($stmt)

1 2	// DEBUG OUTPUT print_r($stmt)

図2.PHP7 PDO print_r出力

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)
)

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 を使えばプリペアドステートメントの情報を直接出力出来るとあり、早速使ってみます。

// DEBUG OUTPUT
$stmt->debugDumpParams();

1 2	// DEBUG OUTPUT $stmt->debugDumpParams();

図3.PHP7 PDO debugDumpParams直接出力

これをブラウザソースで見ると1レコード分は次のように構成されているのが判ります。

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

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!!）。

Stack Overflow

Something like debugDumpParams in pdo settable to a string

https://stackoverflow.com/questions/22157331/something-like-debugdumpparams-in-pdo-settable-to-a-string

I am trying to create a useful email to myself on pdo mysql errors, I am looking at "debugDumpParams", but sadly for some reason it just dumps the info right on the screen, you need to use output c...

Output Bufferを使ってdebugDumpParamsの出力を変数へ取り込む関数を定義し、ステートメントオブジェクトを引数に渡します。

function pdo_debugStrParams($s) {
  ob_start();
  $s->debugDumpParams();
  $r = ob_get_contents();
  ob_end_clean();
  return $r;
}

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:」迄の間の中身 を抽出して表示します。

// DEBUG OUTPUT
preg_match('/(?<=Sent SQL:).*?(?=Params:)/s', pdo_debugStrParams($stmt), $matches);
echo $matches[0].'<br />';

// DEBUG OUTPUT

preg_match('/(?<=Sent SQL:).*?(?=Params:)/s', pdo_debugStrParams($stmt), $matches);

echo $matches[0].' ';

図4.PHP7 PDO debugDumpParamsを正規表現抽出

[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')

[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')

[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')

[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')

[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')

[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')

[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')

[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')

[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')

[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')

[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 タグの中身を取り出す時によく使う手法です。

PDOで実行したSQL文をdebugDumpParamsから抽出表示する

PHP 5での書き込み系

PHP 7の PDO による書き込み系

debugDumpParams を知る

debugDumpParams を取り込む

debugDumpParams を整形

関連記事

コメントを残すコメントをキャンセル

2021年3月
日	月	火	水	木	金	土
	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

PHP 5での書き込み系

PHP 7の PDO による書き込み系

debugDumpParams を知る

debugDumpParams を取り込む

debugDumpParams を整形

共有:

関連記事

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル