php pdo アクセスするテーブルごとに、$sql文を書き換えて実行させたいのですが、ご教示お願いします。

Question

前提・実現したいこと

PHP,MySQLで、在庫管理のソフトウェアを作っています。
MySQLで、１つのデータベースsampledbを作成し、
複数のテーブルdb01,db02を作りました。
最初のレコードを作成するときに、同時に二つのテーブルに新規のレコードを作成しようと思います。
PHPで、pdo？を使ってinsert,update,deleteなどを実現しています。
最初は１つのテーブルdb01でうまくいきました。
仕様を変えてテーブルを２つに分けて、それぞれに共通のsysidというカラムを使い、他のカラムをそれぞれのテーブルに割り当てました。
最初のテーブルが
sysid,item1,item2,item3,item4
だったのを、

１つめのテーブルは、
sysid,item1,item2

２つめのテーブルは、
sysid,item3,item4

としたというイメージです。
最初に新規のレコードを作成しようとして次のようなことをしたいのですが問題が起きました。

$sql = "insert into DB01 (item1,item2) VALUES (:item1,:item2)";
$stmh = $pdo->prepare($sql);
$stmh->execute();
$pdo->commit();

$sql = "insert into DB02 (item3,item4) VALUES (:item3,:item4)";
$stmh = $pdo->prepare($sql);
$stmh->execute();
$pdo->commit();

複数のテーブルにアクセスするため、アクセスするテーブルごとに、$sql文を書き換えて実行させたいのですが、エラーが出ます。
「$sqlへの代入は１個のみにしてください」
という表示も出ています。

テーブルにアクセスするごとに書き換えて使用するということはできないのでしょうか？
＊＊追記１＊＊
最後に$sqlに複数のｓｑｌ文を追加したのは、以下のようなものでした。

$sql = "insert into DB01 (00_C01_DB01__TohoRef, 00_C01_DB01__UketukeDate, 00_C01_DB01__GijutuTantoCode, 00_C01_DB01__GijutuTanto, 00_C01_DB01__JimuTantoCode, 00_C01_DB01__JimuTanto, 00_C01_DB01__Bikoid01) VALUES (:00_C01_DB01__TohoRef, :00_C01_DB01__UketukeDate, :00_C01_DB01__GijutuTantoCode, :00_C01_DB01__GijutuTanto, :00_C01_DB01__JimuTantoCode, :00_C01_DB01__JimuTanto, :00_C01_DB01__Bikoid01); insert into DB02A (00_C03_RP01_DB02A__ApcntCode) VALUES (:00_C03_RP01_DB02A__ApcntCode)";

＊＊追記２＊＊
こちらの追記もさせていただきます。create tableの部分をマークダウンにしました。不手際多く、ご迷惑をおかけしました。m(__)m

drop table DB01;
create table DB01(
sysid mediumint unsigned not null auto_increment,Lsysid mediumint,00_C01_DB01__TohoRef varchar(128),
00_C01_DB01__UketukeDate char(10),
00_C01_DB01__GijutuTantoCode char(20),
00_C01_DB01__GijutuTanto varchar(128),
...
primary key(sysid)) DEFAULT CHARSET=utf8;
drop table DB02A;
create table DB02A(
sysid mediumint unsigned not null auto_increment,Lsysid mediumint,00_C03_RP01_DB02A__ApcntCode char(20),
00_C03_RP01_DB02A__Apcnt varchar(128),
...
primary key(sysid)) DEFAULT CHARSET=utf8;

Comments

[yambejp]

「$sqlへの代入は１個のみにしてください」という日本語のメッセージがでるのでしょうか？
なにかライブラリの問題では？
もし可能ならエラーメッセージをコピペで提示してください

[m.ts10806]

コードはマークダウンの機能を利用してご提示ください。

[退会済みユーザー]

必要な情報まで削ってしまっています。
可能であれば、再現性のある最小限のコードで提示してください。最小限に出来ないのであれば、全体を提示してください。
今の状態だと回答がブレます。
beginTransactionは？エラーは何が出している？バインドは？等々

[退会済みユーザー]

関係するテーブルの構造をCREATE TABLE文で第三者も再現できるように示してほしい。また、sysidはなにかの連番なのか固定値なのか、それなのにINSERT INTO文に組み込まれていないのが不思議。default値やautoincrement値なら省略できますが、そうでないなら必ず値を与える必要があります。

[bellevue]

「$sqlへの代入は１個のみにしてください」というのは、NetBeansIDEで［の端に表示されるワーニングです。
マークダウン、勉強します。すいません。
全体は長かったものですから、確認したいポイントに絞りました。再現性を犠牲にしてしまい、申し訳ありません。
sysidはautoincrementとしています。
皆さん、関心を持っていただいて、ありがとうございます。

Answer 1

「$sqlへの代入は１個のみにしてください」のメッセージは
NetBeans IDEのおせっかい機能で、
$sqlに繰り返し値を代入しているけどいいのかい？と。
なにかの間違いで別の変数名を使いたいところで間違って$sqlに代入していませんか、というおせっかい。
問題なければ無視して良いけど、
変数名の書き間違いの可能性もあるのでしっかり見定めてから無視するように。

最初のテーブルが

sysid,item1,item2,item3,item4
だったのを、

１つめのテーブルは、

sysid,item1,item2

２つめのテーブルは、

sysid,item3,item4

sysidはautoincrement

すごくヤバみを感じる。
「どうせ一連の動き」と甘く考えてそれぞれで１ずつ増えれば同じ番号が両方につくとでも思っちゃいないだろうか？

一つのテーブルだったときと違って、
別テーブルに別れた上でそれぞれのテーブルでsysidがautoincrementしているとね、
一方のみINSERT INTO文が成功してもう一方が失敗する場合や、
あるいは同時アクセス的にAさんのリクエストとBさんのリクエストがかち合って
順番が保証されなかったりする場合もないとはいえないので、
同じsysidを他方で使えるようにしないといけないのではないかと。

その場合、
PHP: PDO::lastInsertId - Manual
lastInsertId()を使って取得した値を他方のテーブルへのINSERT INTO文に使わないといけないはず。

それと、
prepare()
bindValue()
execute()
で一連の流れ。
PHP: PDOStatement::bindValue - Manual
プレースホルダーに変数を当てるのに必要。

1つ目のINSERT INTO文を実行してすぐcommit()してるけど、
2つ目の方で失敗したらどうなるんだろうな。
1つ目を実行する前に
PHP: PDO::beginTransaction - Manual
beginTransaction() して一貫性の確保が必要。
1つ目実行、2つ目実行して例外やエラーが発生しなかったことを確認した後、
やっとcommit()する。
途中で例外やエラーがあったらrollback()して一貫性を保証。

Comments

[Orlofsky]

CREATE TABLEくらい載せるように書いているんですけど、無視するひとが多いです。

[退会済みユーザー]

テーブルの構造を示さずにズバリな回答は出ないよね。わかっていただきたいものですね。

[bellevue]

ご指摘ありがとうございます。今後はtable構造を示すようにします。
同期の問題、頭の隅にあったのですが、心配しすぎかなという迷いもありました。やはり対策が必要なのだということがはっきりしましたので、大変参考になりました。ありがとうございます。
commit()の位置の考慮についてもよく分かりました。ありがとうございます。

[Orlofsky]

「今後」ですか？
今回は治す気はないと。

[bellevue]

実際のもののcreatetableを貼り付けます。質問のものとだいぶ違うものですから。
`drop table DB01;
create table DB01(
sysid mediumint unsigned not null auto_increment,Lsysid mediumint,00_C01_DB01__TohoRef varchar(128),
00_C01_DB01__UketukeDate char(10),
00_C01_DB01__GijutuTantoCode char(20),
00_C01_DB01__GijutuTanto varchar(128),
...
primary key(sysid)) DEFAULT CHARSET=utf8;
drop table DB02A;
create table DB02A(
sysid mediumint unsigned not null auto_increment,Lsysid mediumint,00_C03_RP01_DB02A__ApcntCode char(20),
00_C03_RP01_DB02A__Apcnt varchar(128),
...
primary key(sysid)) DEFAULT CHARSET=utf8;`

Lsysidというのは、sysidで同期をとるのはご指摘があったように無理があるかと思って、自分で同期を確保するために利用しようと考えていたカラムです。

[Orlofsky]

ここではなく、質問を修正してください。

[退会済みユーザー]

コメントにコードを書かれるとみづらいだけじゃなく他の回答者に失礼なので。
そして、現状なにが問題なのかがぼやけているので質問文中に補足をお願いしたい。

Answer 2

テーブルにアクセスするごとに書き換えて使用するということはできないのでしょうか？

できます。

「可変になるところ」を引数にした関数を作ると良いです。
そこは考えてやってみてください。（「可能か？」という質問なので）

あと既に指摘があるようにパラメータだけ指定してbindValueによる値のbindが行われていないためのエラーが起きてるのではないでしょうか。

サンプル（※動作未検証）

関数を用意

function connection(){
    return new PDO(xxxxx); //ここは自身の設定を反映すること
}
function createInsertSQL(string $table,array $item):string
{
    $clms = [];
    $values = [];
    foreach($item as $clm=>$value){
        $clms[] = $clm;
        $values[] = ':'.$clm;
    }
    return "insert into {$table} (".implode(',',$clms).") VALUES (".implode(',',$values).")";
}
function stmtBind(PDOStatement $stmt,array $item)
{
    foreach($item as $clm=>$value){
        $stmt->bindValue(':'.$clm, $value);
    }
}

１個ずつ実行

try{
    $pdo = connection();
    $pdo->beginTransaction();

    $table = 'DB01';
    $set = ['item1'=>1,'item2'=>2];
    $stmt = $pdo->prepare(createInsertSQL($table,$set));
    stmtBind($stmt,$set);
    
    if(!$stmt->execute()){
        throw new PDOException();
    }
    $table = 'DB02';
    $set = ['item3'=>3,'item4'=>4];
    $stmt = $pdo->prepare(createInsertSQL($table,$set));
    stmtBind($stmt,$set);
    if(!$stmt->execute()){
        throw new PDOException();
    }
    $pdo->commit();
}catch(PDOException $e){
    $pdo->rollBack();
    die(json_encode($e));
}

まとめて実行

try{
    $pdo = connection();
    $pdo->beginTransaction();

    $sqls = [];

    $set1 = ['item1'=>1,'item2'=>2];
    $sqls[] = createInsertSQL('DB01',$set1);

    $set2 = ['item3'=>3,'item4'=>4];
    $sqls[] = createInsertSQL('DB02',$set2);

    $stmt = $pdo->prepare(implode(';',$sqls),
        array(PDO::MYSQL_ATTR_MULTI_STATEMENTS=>true));
    stmtBind($stmt,array_merge($set,$set2));

    if(!$stmt->execute()){
        throw new PDOException();
    }
    $pdo->commit();
}catch(PDOException $e){
    $pdo->rollBack();
    die(json_encode($e));
}

Comments

[bellevue]

ありがとうございます。可能であるか否か、まずはそれが知りたかったです。関数として、$sqlに、分けて代入するのですね。やってみます。変数の有効範囲の把握が苦手なのですが、やってみます。私が書いたようにベタで代入するやり方は基本的にはだめということなんでしょうか。

[m.ts10806]

関数作ったなら変数のスコープは参照渡ししない限りその関数内だけです。
適切にバインドまでできればダメではないですが、非効率なので共通関数作ってまとめましょうという話です。

[m.ts10806]

あとSQLも;で区切れるので1つにまとめて全部バインドするのもあり。

$sql = "
insert into DB01 (item1,item2) VALUES (:item1,:item2);
insert into DB02 (item3,item4) VALUES (:item3,:item4);
";

[退会済みユーザー]

PDO::MYSQL_ATTR_MULTI_STATEMENTS が初期TRUEで複数のSQL文を一度に実行できますよ、というお話。https://www.php.net/manual/ja/ref.pdo-mysql.php

[bellevue]

変数のスコープの話は、何となく分かるのですが、橋渡ししたつもりでも橋渡しできないことが多いみたいで、苦手です。
複数のsql文をまとめて代入するのは目から鱗です。これでうまくいけばうれしいです。やってみます。
MYSQL_ATTR_MULTI_STATEMENTS 調べてみます。ありがとうございます。

[bellevue]

$sqlへの複数文の代入を試みてみました。$pdo->setAttribute(PDO::MYSQL_ATTR_MULTI_STATEMENTS, true);というのも入れてみました。

エラー：SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'insert into DB02A (00_C03_RP01_DB02A__ApcntCode) VALUES (?)' at line 1
というエラーが出てしまいました。mariadbでコンソール入力で同じコマンドを入力したところ、そちらではエラーなくレコードを作れました。PHPの方から渡そうとすると、エラーになっています。まだ、プログラムの方に不備がありますね。orz

[m.ts10806]

それはSQLの構文エラーですね。
試したコードを質問本文にマークダウンで追記してください

[bellevue]

以下のものをやってみました。非常に変則的な変数名の付け方をしており、見にくいと思います。申し訳ありません。複数文の代入も成功させたいですが、とりあえずは解決しております。お忙しいでしょうから、無視していただいて結構です。もし、お時間許すようでしたら、どこが間違っているのかご指摘いただけるとありがたいです。
```
$sql = "insert into DB01 (00_C01_DB01__TohoRef, 00_C01_DB01__UketukeDate, 00_C01_DB01__GijutuTantoCode, 00_C01_DB01__GijutuTanto, 00_C01_DB01__JimuTantoCode, 00_C01_DB01__JimuTanto, 00_C01_DB01__Bikoid01) VALUES (:00_C01_DB01__TohoRef, :00_C01_DB01__UketukeDate, :00_C01_DB01__GijutuTantoCode, :00_C01_DB01__GijutuTanto, :00_C01_DB01__JimuTantoCode, :00_C01_DB01__JimuTanto, :00_C01_DB01__Bikoid01); insert into DB02A (00_C03_RP01_DB02A__ApcntCode) VALUES (:00_C03_RP01_DB02A__ApcntCode)";
```

[m.ts10806]

コメントではなく、質問本文にマークダウンで追記してください

Answer 3

bindParamやbindValue が必要では？
テーブル定義はCREATE TABLE に修正した方が適切なコメントが付きやすいです。

Comments

[bellevue]

まず、エラーの意味をはっきりさせるために、シンプルに空白に近いレコードを一つ作ってみることにしました。うまくいってからbindなどをさせるつもりでした。ただ、1つめのテーブルではそういった処理を行ってレコードもインサートできました。2つめのテーブルで$sqlにｓｑｌ文を代入するステートメントのところに来てfatalerrorが発生してしまいました。
ありがとうございます。ご指摘の点は今後生かせるように努力してみます。

Answer 4

解決できました。以下のように４つを順番に実行させるということが必須だということなのかなと理解しました。途中をはしょったりするとだめなのではないでしょうか。皆様、お忙しい中で、いろいろ教えていただき、ありがとうございました。

$sql = "insert into DB02A (Lsysid) VALUES (:Lsysid)";
$stmh = $pdo->prepare($sql);
$stmh->bindValue(':Lsysid', $row['sysid'], PDO::PARAM_INT);
$stmh->execute();

sysidというのは、最初にレコードをインサートできた後で、lastInsertId()で得たものです。二つ目以降のテーブルでもsysidは使いますが、同期を確保するためにLsysidというものを用意し、そこに同期させる一つ目のテーブルのsysidを入れるようにしています。