回答編集履歴
4
説明と修正
test
CHANGED
@@ -12,11 +12,11 @@
|
|
12
12
|
|
13
13
|
$parameters = [
|
14
14
|
|
15
|
-
'KO' => [1, 2, 3],
|
15
|
+
'KO' => [1, 2, 3, 4],
|
16
16
|
|
17
|
-
'OTSU' => [
|
17
|
+
'OTSU' => [5, 6, 7, 8],
|
18
18
|
|
19
|
-
'HEI' => [
|
19
|
+
'HEI' => [9, 10, 11, 12],
|
20
20
|
|
21
21
|
];
|
22
22
|
|
@@ -26,7 +26,7 @@
|
|
26
26
|
|
27
27
|
$format = 'INSERT INTO XTable(%s) VALUES(%s)';
|
28
28
|
|
29
|
-
$names = array_keys($parameters);
|
29
|
+
$names = array_keys($parameters); // ['KO', 'OTSU', 'HEI']
|
30
30
|
|
31
31
|
$placeholders = array_map(
|
32
32
|
|
@@ -34,25 +34,33 @@
|
|
34
34
|
|
35
35
|
$names
|
36
36
|
|
37
|
-
);
|
37
|
+
); // [':KO', ':OTSU', ':HEI']
|
38
38
|
|
39
39
|
$stmt = $pdo->prepare(sprintf(
|
40
40
|
|
41
41
|
$format,
|
42
42
|
|
43
|
-
implode(',', $names),
|
43
|
+
implode(',', $names), // 'KO,OTSU,HEI'
|
44
44
|
|
45
|
-
implode(',', $placeholders)
|
45
|
+
implode(',', $placeholders) // ':KO,:OTSU,:HEI'
|
46
46
|
|
47
|
-
));
|
47
|
+
)); // 'INSERT INTO XTable(KO,OTSU,HEI) VALUES(:KO,:OTSU,:HEI)'
|
48
48
|
|
49
49
|
|
50
50
|
|
51
|
-
// 順番にINSERTを実行
|
51
|
+
// 順番にINSERTを実行 ($max = 4)
|
52
52
|
|
53
53
|
for ($i = 0, $max = count(current($parameters)); $i < $max; ++$i) {
|
54
54
|
|
55
55
|
foreach ($parameters as $name => $values) {
|
56
|
+
|
57
|
+
// $i = 0 のとき:
|
58
|
+
|
59
|
+
// $name = 'KO', $values[$i] = 1;
|
60
|
+
|
61
|
+
// $name = 'OTSU', $values[$i] = 5;
|
62
|
+
|
63
|
+
// $name = 'HEI', $values[$i] = 9;
|
56
64
|
|
57
65
|
$stmt->bindValue($name, $values[$i], PDO::PARAM_INT);
|
58
66
|
|
@@ -80,11 +88,11 @@
|
|
80
88
|
|
81
89
|
$parameters = [
|
82
90
|
|
83
|
-
'KO' => [1, 2, 3],
|
91
|
+
'KO' => [1, 2, 3, 4],
|
84
92
|
|
85
|
-
'OTSU' => [
|
93
|
+
'OTSU' => [5, 6, 7, 8],
|
86
94
|
|
87
|
-
'HEI' => [
|
95
|
+
'HEI' => [9, 10, 11, 12],
|
88
96
|
|
89
97
|
];
|
90
98
|
|
@@ -94,33 +102,33 @@
|
|
94
102
|
|
95
103
|
$format = 'INSERT INTO XTable(%s) VALUES%s';
|
96
104
|
|
97
|
-
$names = array_keys($parameters);
|
105
|
+
$names = array_keys($parameters); // ['KO', 'OTSU', 'HEI']
|
98
106
|
|
99
|
-
$placeholder = array_fill(0, count(
|
107
|
+
$placeholder = array_fill(0, count($parameters), '?'); // ['?', '?', '?']
|
100
108
|
|
101
|
-
$placeholders = array_fill(0, count($parameters), '(' . implode(',', $placeholder) . ')');
|
109
|
+
$placeholders = array_fill(0, count(current($parameters)), '(' . implode(',', $placeholder) . ')'); // ['(?,?,?)', '(?,?,?)', '(?,?,?)', '(?,?,?)']
|
102
110
|
|
103
111
|
$stmt = $pdo->prepare(sprintf(
|
104
112
|
|
105
113
|
$format,
|
106
114
|
|
107
|
-
implode(',', $names),
|
115
|
+
implode(',', $names), // 'KO,OTSU,HEI'
|
108
116
|
|
109
|
-
implode(',', $placeholders)
|
117
|
+
implode(',', $placeholders) // '(?,?,?),(?,?,?),(?,?,?),(?,?,?)'
|
110
118
|
|
111
|
-
));
|
119
|
+
)); // 'INSERT INTO XTable(KO,OTSU,HEI) VALUES(?,?,?),(?,?,?),(?,?,?),(?,?,?)'
|
112
120
|
|
113
121
|
|
114
122
|
|
115
123
|
// 転置して平坦化した配列を作成
|
116
124
|
|
117
|
-
$trans = call_user_func_array('array_merge', call_user_func_array('array_map', array_merge([null], $parameters)));
|
125
|
+
$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]
|
118
126
|
|
119
127
|
|
120
128
|
|
121
129
|
// バルクINSERTを実行
|
122
130
|
|
123
|
-
foreach ($trans as $i => $value) {
|
131
|
+
foreach ($transflat as $i => $value) {
|
124
132
|
|
125
133
|
$stmt->bindValue($i + 1, $value, PDO::PARAM_INT);
|
126
134
|
|
3
vd
test
CHANGED
@@ -100,8 +100,6 @@
|
|
100
100
|
|
101
101
|
$placeholders = array_fill(0, count($parameters), '(' . implode(',', $placeholder) . ')');
|
102
102
|
|
103
|
-
var_dump($placeholders);
|
104
|
-
|
105
103
|
$stmt = $pdo->prepare(sprintf(
|
106
104
|
|
107
105
|
$format,
|
2
bulk
test
CHANGED
@@ -66,4 +66,74 @@
|
|
66
66
|
|
67
67
|
|
68
68
|
|
69
|
+
もしバルクインサートにする場合,名前付きプレースホルダが使用できなくなる点に注意してください.
|
70
|
+
|
71
|
+
|
72
|
+
|
73
|
+
```php
|
74
|
+
|
75
|
+
<?php
|
76
|
+
|
77
|
+
|
78
|
+
|
79
|
+
// パラメータをここで定義
|
80
|
+
|
81
|
+
$parameters = [
|
82
|
+
|
83
|
+
'KO' => [1, 2, 3],
|
84
|
+
|
85
|
+
'OTSU' => [4, 5, 6],
|
86
|
+
|
87
|
+
'HEI' => [7, 8, 9],
|
88
|
+
|
89
|
+
];
|
90
|
+
|
91
|
+
|
92
|
+
|
93
|
+
// プリペアドステートメントを作成
|
94
|
+
|
95
|
+
$format = 'INSERT INTO XTable(%s) VALUES%s';
|
96
|
+
|
97
|
+
$names = array_keys($parameters);
|
98
|
+
|
99
|
+
$placeholder = array_fill(0, count(current($parameters)), '?');
|
100
|
+
|
101
|
+
$placeholders = array_fill(0, count($parameters), '(' . implode(',', $placeholder) . ')');
|
102
|
+
|
103
|
+
var_dump($placeholders);
|
104
|
+
|
105
|
+
$stmt = $pdo->prepare(sprintf(
|
106
|
+
|
107
|
+
$format,
|
108
|
+
|
109
|
+
implode(',', $names),
|
110
|
+
|
111
|
+
implode(',', $placeholders)
|
112
|
+
|
113
|
+
));
|
114
|
+
|
115
|
+
|
116
|
+
|
117
|
+
// 転置して平坦化した配列を作成
|
118
|
+
|
119
|
+
$trans = call_user_func_array('array_merge', call_user_func_array('array_map', array_merge([null], $parameters)));
|
120
|
+
|
121
|
+
|
122
|
+
|
123
|
+
// バルクINSERTを実行
|
124
|
+
|
125
|
+
foreach ($trans as $i => $value) {
|
126
|
+
|
127
|
+
$stmt->bindValue($i + 1, $value, PDO::PARAM_INT);
|
128
|
+
|
129
|
+
}
|
130
|
+
|
131
|
+
$stmt->execute();
|
132
|
+
|
133
|
+
```
|
134
|
+
|
135
|
+
|
136
|
+
|
69
137
|
備考: [PHPでデータベースに接続するときのまとめ - Qiita](http://qiita.com/mpyw/items/b00b72c5c95aac573b71)
|
138
|
+
|
139
|
+
|
1
as int
test
CHANGED
@@ -54,7 +54,7 @@
|
|
54
54
|
|
55
55
|
foreach ($parameters as $name => $values) {
|
56
56
|
|
57
|
-
$stmt->bindValue($name, $values[$i]);
|
57
|
+
$stmt->bindValue($name, $values[$i], PDO::PARAM_INT);
|
58
58
|
|
59
59
|
}
|
60
60
|
|