pdoで配列を格納するスマートな方法

お世話になっております。

以下の【配列の状況】ように$mixedは可変の複数の配列が規則正しく格納されている状況です。

【配列の状況】
$mixed=array(array(a,b,c),array(d,e,f),array(g,h,i)...)
#$mixedに格納される配列個数は可変である。

これをpdo配列の順番([0][0],[0][1],[0][2])通りに次々にレコードに格納していきたいと考えております。
仮にそれぞれのMYSQLのカラムの名前をKO、OTSU、HEIと致します。
その場合には、
KOにa,d,g.....
OTSUにb,e,h......
HEIにg,f,i....
がそれぞれ格納されていくことになります。

ここで以下のようなPDOを準備しております。

$stmt=$pdo->prepare(
"INSERT INTO Xtable
SET(KO,OTSU,HEI)
VALUES(:KO,:OTSU,:HEI);"
for ($i=0; $i <$mixed ; $i++) { 
$stmt->bindParam(':KO',$mixed[$i][0],PDO::PARAMA_INT);
$stmt->bindParam(':OTSU',$mixed[$i][1],PDO::PARAMA_INT);
$stmt->bindParam(':HEI',$mixed[$i][2],PDO::PARAMA_INT);
$stmt->execute();
}

これでも、シンプルには書けるのですが、実際には配列の中のa,b,cの
個数が相当数に及ぶため、stmt->bindParamを書く回数も相当数に及びます。

この点、全ての配列は規則正しく、配列の順番のまま格納したとしてもしかるべき
カラムに収まるように設計されている点に着目して、単純に配列を一つの
レコードに順番に押し込んでいくようなPDO(SQL)のコード等があれば
ご教示願えますでしょうか。

そんなものはない、または配列の順番に万が一秩序がなくなった場合に、
やはりそれぞれのプレースホルダを指定して、格納してく方が安全である等
のご指摘があればその旨のご意見を賜れれば幸いです。

よろしくお願い申し上げます。

行動規範の内容に同意します

回答3件

生PDOの苦手とする部分ですね．何かしらORMを使うのが正解かもしれませんが，初心者さんには少し手出ししにくいと思うので，できるだけ生PDOに小細工をかける程度のスマートな方法を考えてみましょう．

php
1<?php
2
3// パラメータをここで定義
4$parameters = [
5    'KO' => [1, 2, 3, 4],
6    'OTSU' => [5, 6, 7, 8],
7    'HEI' => [9, 10, 11, 12],
8];
9
10// プリペアドステートメントを作成
11$format = 'INSERT INTO XTable(%s) VALUES(%s)';
12$names = array_keys($parameters); // ['KO', 'OTSU', 'HEI']
13$placeholders = array_map(
14    function ($name) { return ":$name"; },
15    $names
16); // [':KO', ':OTSU', ':HEI']
17$stmt = $pdo->prepare(sprintf(
18    $format,
19    implode(',', $names), // 'KO,OTSU,HEI'
20    implode(',', $placeholders) // ':KO,:OTSU,:HEI'
21)); // 'INSERT INTO XTable(KO,OTSU,HEI) VALUES(:KO,:OTSU,:HEI)'
22
23// 順番にINSERTを実行 ($max = 4)
24for ($i = 0, $max = count(current($parameters)); $i < $max; ++$i) {
25    foreach ($parameters as $name => $values) {
26        // $i = 0 のとき:
27        //    $name = 'KO', $values[$i] = 1;
28        //    $name = 'OTSU', $values[$i] = 5;
29        //    $name = 'HEI', $values[$i] = 9;
30        $stmt->bindValue($name, $values[$i], PDO::PARAM_INT);
31    }
32    $stmt->execute();
33}

もしバルクインサートにする場合，名前付きプレースホルダが使用できなくなる点に注意してください．

php
1<?php
2
3// パラメータをここで定義
4$parameters = [
5    'KO' => [1, 2, 3, 4],
6    'OTSU' => [5, 6, 7, 8],
7    'HEI' => [9, 10, 11, 12],
8];
9
10// プリペアドステートメントを作成
11$format = 'INSERT INTO XTable(%s) VALUES%s';
12$names = array_keys($parameters); // ['KO', 'OTSU', 'HEI']
13$placeholder = array_fill(0, count($parameters), '?'); // ['?', '?', '?']
14$placeholders = array_fill(0, count(current($parameters)), '(' . implode(',', $placeholder) . ')'); // ['(?,?,?)', '(?,?,?)', '(?,?,?)', '(?,?,?)']
15$stmt = $pdo->prepare(sprintf(
16    $format,
17    implode(',', $names),  // 'KO,OTSU,HEI'
18    implode(',', $placeholders) // '(?,?,?),(?,?,?),(?,?,?),(?,?,?)'
19)); // 'INSERT INTO XTable(KO,OTSU,HEI) VALUES(?,?,?),(?,?,?),(?,?,?),(?,?,?)'
20
21// 転置して平坦化した配列を作成
22$transflat = call_user_func_array('array_merge', call_user_func_array('array_map', array_merge([null], $parameters))); // [1, 5, 9, 2, 6, 10, 3, 7, 11, 4, 8, 12]
23
24// バルクINSERTを実行
25foreach ($transflat as $i => $value) {
26    $stmt->bindValue($i + 1, $value, PDO::PARAM_INT);
27}
28$stmt->execute();

備考: PHPでデータベースに接続するときのまとめ - Qiita

投稿2016/09/14 19:22

編集2016/09/14 20:03

mpyw

総合スコア5223

mpyw

2016/09/14 19:26

またカラム数が極端に多い場合は，テーブルスキーマの設計ミスを疑ってもいいと思います．正規化について調べた上で，第3正規化までの適用を検討してください．

mpyw

2016/09/14 20:07

ちょっとここは初心者さんには理解苦しいかな…（汗 $transflat = call_user_func_array('array_merge', call_user_func_array('array_map', array_merge([null], $parameters))); // [1, 5, 9, 2, 6, 10, 3, 7, 11, 4, 8, 12]

SugiuraY

2016/09/14 23:18

ご回答を頂き、有難うございます。おっしゃる通り、後半からちょっと難解ゾーンでした笑ただ、ゆっくり咀嚼しながら、理解してみようと思います！

行動規範の内容に同意します

ベストアンサー

ご提示のコードのままでも十分にシンプルだと思いますが、
あえて指摘させていただくと、for を foreach に変更すると毎回"[$i]"を記述しなくて済むため、
よりシンプルにできると思います。
http://php.net/manual/ja/control-structures.foreach.php

php
1foreach ($mixed as $row) {
2    $stmt->bindParam(':KO', $row[0], PDO::PARAMA_INT);
3    $stmt->bindParam(':OTSU', $row[1], PDO::PARAMA_INT);
4    $stmt->bindParam(':HEI', $row[2], PDO::PARAMA_INT);
5    $stmt->execute();
6}

$stmt->bindParam() の第三引数に指定する値が全て同じなら、以下のようにも書けます。

php
1$stmt=$pdo->prepare(
2    "INSERT INTO Xtable
3    SET(KO,OTSU,HEI)
4    VALUES(?, ?, ?);" // 疑問符パラメータに変更
5foreach ($mixed as $row) {
6    $i = 1;
7    foreach ($row as $data) {
8        $stmt->bindParam($i++, $data, PDO::PARAMA_INT);
9    }
10    $stmt->execute();
11}

本題から話はそれますが、上記のように1つの処理で複数回 SQL文を実行する場合、トランザクションを使用する必要がないか、確認してください。
https://dev.mysql.com/doc/refman/5.6/ja/glossary.html#glos_transaction

適切にトランザクションが使用されいないと、処理中に DB が落ちたり DB との接続が切れたりした際に中途半端なデータが DB 内に残ってしまいますので。

yambejp様の回答のように要素の数だけ(?,?,?)部分を動的に連結し、1クエリで SQL文を実行する方法については、
私の個人的な意見としてはお勧めしません。

ソースコードの行数を削減できる反面、以下のようなデメリットがあるからです。

ソースコードが複雑になり、何をしているのか分かりにくい
$mixedが空の配列だった場合、SQL を実行するとシンタックスエラーが発生する
max_allowed_packet の制限に引っかかってエラーになるリスクが大きくなる
（よほどのことがない限り発生しないはずですがw）

また、$stmt->execute() の引数に渡す形式でバインドする値を指定する方法も、お勧めしません。

この場合、指定した値は一律文字列として扱われるため、思わぬ挙動をすることがあるからです。
http://php.net/manual/ja/pdostatement.execute.php

すべての値は PDO::PARAM_STR として扱われます。

http://blog.tokumaru.org/2016/04/pdoint.html

多少面倒でも、必ず bindParam（または bindValue）の第三引数で型を指定することをお勧めします。

投稿2016/09/14 06:13

KiyoshiMotoki

総合スコア4791

SugiuraY

2016/09/14 06:54

ご回答頂き有難うございます。二点、確認させてください。トランザクションするというのは、サイトを見てもピンとこなかったのですが、要するにバッチで処理が完了するまで作業を終わらせない？という事で、start transaction文を入れるという事でしょうか？ $stmt->execute() の引数に渡す形式でバインドする値を指定する方法とは具体的にどのような方法でしょうか？たまに、executeの中に引数を入れている文をみるのですが、実はまだその記法を理解できておりません。実際に同じ目的で、当該方法でバインドする記法をご提示ねがえますでしょうか？まだまだ不案内で誠に申し訳ございませんが、何卒宜しくお願い申し上げます。

KiyoshiMotoki

2016/09/14 18:37

> 要するにバッチで処理が完了するまで作業を終わらせない？という事で、start transaction文を入れるという事でしょうか？トランザクションを使用すると、複数の更新系SQL（INSERT, UPDATE, DELETE）を実行しても、COMMIT するまでその更新が他のアプリケーションなどからは見えなくなります。また、途中でエラーなどが発生した場合、ROLLBACK すれば、トランザクション開始前の状態に戻すことができます。 "START TRANSACTION"文を実行しても良いですが、PDOをお使いなら、beginTransaction() および commiit() / rollback() メソッドを使用する方が（個人的には）スマートだと感じます。 http://php.net/manual/ja/pdo.begintransaction.php http://php.net/manual/ja/pdo.commit.php http://php.net/manual/ja/pdo.rollback.php > $stmt->execute() の引数に渡す形式でバインドする値を指定する方法とは具体的にどのような方法でしょうか？ yambejp様の回答のソースコードにある　$stmt->execute($data); という一文のことです。

行動規範の内容に同意します

たとえばこう

PHP
1$mixed=[["a","b","c"],["d","e","f"],["g","h","i"]];
2$sql="INSERT INTO Xtable (KO,OTSU,HEI) VALUES";
3$sql.=implode(",",array_fill(0,count($mixed),"(?,?,?)"));
4$data=[];
5array_walk($mixed,function($a) use(&$data){$data=array_merge($data,$a);});
6$stmt=$pdo->prepare($sql);
7$stmt->execute($data);
8//参考
9print $sql.";<br>";
10print_r($data);
11

投稿2016/09/13 14:50

yambejp

総合スコア114843

SugiuraY

2016/09/13 15:37

ご回答いただきありがとうございます。まだまだプログラミングに不案内で申し訳ございませんが一点確認をさせてください。 function($a) はどこで定義をした関数なのでしょうか？それともそもそも私がfunctionの使用の仕方を誤認していたらすみませんが、よろしくお願い申し上げます。

yambejp

2016/09/14 00:19

function()構文はいわゆる無名関数です用途はいろいろありますが、今回のようにarray_walk内で使用すると配列の要素を一つ一つ$aに渡し、内部の処理を行います。今回外部変数$dataをuseで渡したので$dataに$aをマージを繰り返します

SugiuraY

2016/09/14 06:58

ご回答頂き、誠に有難うございます無名関数を存じ上げなかったため、また調べてみたす。

yambejp

2016/09/14 10:13

Insert文を複数発行するのは相当効率が悪いですスピードを優先するなら私の提示するバルク処理がお奨めします

mpyw

2016/09/14 18:52 編集

ご提示の文法でのバルクインサートはMySQL/PostgreSQLでは動作しますが，SQLiteでは動作せず，標準SQLではない点にだけ注意してください．（ただ，PHPの配列としてソースコード上で表現できる程度の個数だと全然大した量があるとは思えないので，バルクインサートに固執しなくても良さそうな気はします）

行動規範の内容に同意します

あなたの回答