teratail
回答率
85.30%
teratail header banner
teratail header banner
質問するログイン新規登録
トップに関する質問SQLでのエラー文の対応を教えてください

編集履歴

質問編集履歴

2

全体的に修正

2020/05/14 01:31

投稿

kaito2414
kaito2414

スコア11

title CHANGED
File without changes
body CHANGED
@@ -10,282 +10,12 @@
10
10
  環境:MYSQL PHP
11
11
 
12
12
  ```php
13
- echo "usersインサート開始";
14
- $insert_users = "INSERT INTO ".$users_table."
13
+ $users_lock = $dbh[0]->prepare("SELECT * FROM ".$users_table."WHERE ID = ? LOCK IN SHARE MODE");
15
- (
16
- name,
17
- loginid,
18
- passwd,
19
- authority,
20
- created,
21
- modified
22
- )
23
- VALUES
24
- (
25
- 'ASシステム管理',
26
- 'as_system',
27
- 'cd6736d1ad6f1d61a85f29be8684343a040fecb3',
28
- 99,
29
- cast(now() as datetime),
30
- cast(now() as datetime)
31
- ),
32
- (
33
- 'BTMシステム管理',
34
- 'btm_system',
35
- 'cd6736d1ad6f1d61a85f29be8684343a040fecb3',
36
- 99,
37
- cast(now() as datetime),
38
- cast(now() as datetime)
39
- ),
40
- (
41
- 'DSシステム管理',
42
- 'ds_system',
43
- 'cd6736d1ad6f1d61a85f29be8684343a040fecb3',
44
- 99,
45
- cast(now() as datetime),
46
- cast(now() as datetime)
47
- )";
48
- echo $insert_users.'<br>';
49
-
50
- // // usersアップデート
51
- // echo "usersアップデート準備";
52
- // $update_users = "UPDATE ".$users_table."
53
- // SET
54
-
55
-
56
-
57
-
58
- // echo $update_users.'<br>';
59
-
60
- // レコード数カウント
61
- $sql_users = "SELECT * FROM ".$users_table;
62
- $res_users = $dbh[0]->query($sql_users);
63
- $row_users = $res_users->rowCount();
64
-
65
- // 実行
66
- if ($row_users === 0) {
67
- $insert_users_check = $dbh[0]->query($insert_users);
68
- if (!$insert_users_check) {
69
- throw new Exception('user:インサート失敗');
70
- }
71
- }
72
- else {
73
- // $update_start_users = $dbh[0]->prepare($update_users);
74
- // if (!$update_start_users) {
75
- // throw new Exception('users:アップデート準備失敗');
76
- // }
77
- }
78
-
79
- echo"site_configsインサート開始";
80
- $insert_site_configs = "INSERT INTO ".$site_configs_table."
81
- (
82
- site_url,
83
- site_name,
84
- wrt_user_id,
85
- created,
86
- modified
87
- )
88
- VALUES
89
- (
90
- 'CCI',
91
- 'CCIホーム',
92
- 99,
93
- cast(now() as datetime),
94
- cast(now() as datetime)
95
- )";
96
- echo $insert_site_configs.'<br>';
97
-
98
- echo "site_configsアップデート準備";
99
- $update_site_configs = "UPDATE ".$site_configs_table."
100
- SET
101
- site_url = 'CCI',
102
- site_name = 'CCIホーム',
103
- wrt_user_id = 99,
104
- created = cast(now() as datetime),
105
- modified = cast(now() as datetime)
106
- WHERE id = 1";
107
-
108
- // レコード数カウント
109
- $sql_site_configs = "SELECT * FROM ".$site_configs_table;
14
+ $site_configs_lock = $dbh[0]->prepare("SELECT * FROM ".$site_configs_table." WHERE ID = ? LOCK IN SHARE MODE");
110
- $res_site_configs = $dbh[0]->query($sql_site_configs);
111
- $row_site_configs = $res_site_configs->rowCount();
112
-
113
- // 実行
114
- if ($row_site_configs === 0) {
115
- $insert_site_configs_check = $dbh[0]->query($insert_site_configs);
116
- if (!$insert_site_configs_check) {
117
- throw new Exception('site_configs:インサート失敗');
118
- }
119
- }
120
- else {
121
- $update_start_site_configs = $dbh[0]->prepare($update_site_configs);
122
- if (!$update_start_site_configs) {
123
- throw new Exception('site_configs:アップデート準備失敗');
124
- }
125
- }
126
-
127
-
128
- echo "synonymsインサート準備";
129
- $insert_synonyms = "INSERT INTO ".$synonyms_table."
15
+ $synonyms_lock = $dbh[2]->prepare("SELECT * FROM ".$synonyms_table." WHERE ID = ? LOCK IN SHARE MODE");
130
- (
131
- synonym1,
132
- synonym2,
133
- synonym3,
134
- synonym4,
135
- synonym5
136
- )
137
- VALUES
138
- (
139
- :synonym1,
140
- :synonym2,
141
- :synonym3,
142
- :synonym4,
143
- :synonym5
144
- )";
145
- echo $insert_synonyms.'<br>';
146
- $insert_start_synonyms = $dbh[2]->prepare($insert_synonyms);
147
- if (!$insert_start_synonyms) {
148
- throw new Exception('synonyms:インサート準備失敗');
149
- }
150
-
151
- echo "synonymsアップデート準備";
152
- $update_synonyms = "UPDATE ".$synonyms_table."
153
- SET
154
- synonym1 = :synonym1,
155
- synonym2 = :synonym2,
156
- synonym3 = :synonym3,
157
- synonym4 = :synonym4,
158
- synonym5 = :synonym5
159
- WHERE id = :id";
160
- echo $update_synonyms.'<br>';
161
- $update_start_synonyms = $dbh[2]->prepare($update_synonyms);
162
- if (!$update_start_synonyms) {
163
- throw new Exception('synonyms:アップデート準備失敗');
164
- }
165
-
166
- echo "suggest_wordsインサート準備";
167
- $insert_suggest_words = "INSERT INTO ".$suggest_words_table."
16
+ $suggest_words_lock = $dbh[2]->prepare("SELECT * FROM ".$suggest_words_table." WHERE ID = ? LOCK IN SHARE MODE");
168
- (
169
- suggest1,
170
- suggest2,
171
- suggest3,
172
- kana1,
173
- kana2,
174
- kana3
175
- )
176
- VALUES
177
- (
178
- :suggest1,
179
- :suggest2,
180
- :suggest3,
181
- :kana1,
182
- :kana2,
183
- :kana3
184
- )";
185
- echo $insert_suggest_words.'<br>';
186
- $insert_start_suggest_words = $dbh[2]->prepare($insert_suggest_words);
187
- if (!$insert_start_suggest_words) {
188
- throw new Exception('suggest_words:インサート準備失敗');
189
- }
190
-
191
- echo "suggest_wordsアップデート準備";
192
- $update_suggest_words = "UPDATE ".$suggest_words_table."
193
- SET
194
- suggest1 = :suggest1,
195
- suggest2 = :suggest2,
196
- suggest3 = :suggest3,
197
- kana1 = :kana1,
198
- kana2 = :kana2,
199
- kana3 = :kana3
200
- WHERE id = :id";
201
- echo $update_suggest_words.'<br>';
202
- $update_start_suggest_words = $dbh[2]->prepare($update_suggest_words);
203
- if (!$update_start_suggest_words) {
204
- throw new Exception('suggest_words:アップデート準備失敗');
205
- }
206
-
207
- echo "categoriesインサート準備";
208
- $insert_categories = "INSERT INTO ".$categories_table."
17
+ $categories_lock = $dbh[2]->prepare("SELECT * FROM ".$categories_table." WHERE ID = ? LOCK IN SHARE MODE");
209
- (
210
- site_id,
211
- name,
212
- pass,
213
- sort,
214
- level,
215
- color,
216
- user_id,
217
- created
218
- )
219
- VALUES
220
- (
221
- :site_id,
222
- :name,
223
- :pass,
224
- :sort,
225
- :level,
226
- :color,
227
- :user_id,
228
- cast(now() as datetime)
229
- )";
230
- echo $insert_categories.'<br>';
231
- $insert_start_categories = $dbh[2]->prepare($insert_categories);
232
- if (!$insert_start_categories) {
233
- throw new Exception('categories:インサート準備失敗');
234
- }
235
-
236
- echo "categoriesアップデート準備";
237
- $update_categories = "UPDATE ".$categories_table."
238
- SET
239
- site_id = :site_id,
240
- name = :name,
241
- pass = :pass,
242
- sort = :sort,
243
- level = :level,
244
- color = :color,
245
- user_id = :user_id,
246
- created = cast(now() as datetime)
247
- WHERE id = :id";
248
- echo $update_categories.'<br>';
249
- $update_start_categories = $dbh[2]->prepare($update_categories);
250
- if (!$update_start_categories) {
251
- throw new Exception('categories:アップデート準備失敗');
252
- }
253
-
254
- echo "categories_linksインサート準備";
255
- $insert_categories_links = "INSERT INTO ".$categories_links_table."
18
+ $categories_links_lock = $dbh[2]->prepare("SELECT * FROM ".$categories_links_table." WHERE ID = ? LOCK IN SHARE MODE");
256
- (
257
- categories_id,
258
- name,
259
- url,
260
- created
261
- )
262
- VALUES
263
- (
264
- :categories_id,
265
- :name,
266
- :url,
267
- cast(now() as datetime)
268
- )";
269
- echo $insert_categories_links.'<br>';
270
- $insert_start_categories_links = $dbh[2]->prepare($insert_categories_links);
271
- if (!$insert_start_categories_links) {
272
- throw new Exception('categories_links:インサート準備失敗');
273
- }
274
-
275
- echo "categories_linksアップデート準備";
276
- $update_categories_links = "UPDATE ".$categories_links_table."
277
- SET
278
- categories_id = :categories_id,
279
- name = :name,
280
- url = :url,
281
- created = cast(now() as datetime)
282
- WHERE id = :id";
283
- echo $update_categories_links.'<br>';
284
- $update_start_categories_links = $dbh[2]->prepare($update_categories_links);
285
- if (!$update_start_categories_links) {
286
- throw new Exception('categories_links:アップデート準備失敗');
287
- }
288
-
289
19
  .
290
20
  .
291
21
  .

1

コード編集

2020/05/14 01:31

投稿

kaito2414
kaito2414

スコア11

title CHANGED
File without changes
body CHANGED
@@ -10,68 +10,6 @@
10
10
  環境:MYSQL PHP
11
11
 
12
12
  ```php
13
- <?php
14
- // DB情報
15
- $dbh_admin = null;
16
- $dbh_common = null;
17
- $dbh_front = null;
18
- $db_admin = "sbiccicms_admin_local_auto";
19
- $db_common = "sbiccicms_common_local_auto";
20
- $db_front = "sbiccicms_front_local_auto";
21
- $user = 'root';
22
- $pass = '';
23
-
24
- // URlファイルパス取得
25
- $get_dir = __FILE__;
26
- $get_dir_path = dirname($get_dir);
27
- $csv_synonyms = glob($get_dir_path.'/csv/同義語マスタ_200421.csv');
28
- $csv_suggest_words = glob($get_dir_path.'/csv/サジェストマスタ_200421.csv');
29
- $csv_category_colors = glob($get_dir_path.'/categoris/category_colors.csv');
30
- $csv_categories = glob($get_dir_path.'/categoris/categories.csv');
31
- $csv_categories_links = glob($get_dir_path.'/categoris/categories_links.csv');
32
-
33
- // 使用テーブル
34
- $users_table = "users";
35
- $site_configs_table = "site_configs";
36
- $synonyms_table = "synonyms";
37
- $suggest_words_table = "suggest_words";
38
- $categories_table = "categories";
39
- $categories_links_table = "categories_links";
40
-
41
-
42
-
43
- try {
44
- // DB接続
45
- $dbh_admin = new PDO('mysql:host=127.0.0.1; dbname='.$db_admin.';charset=utf8', $user, $pass);
46
- $dbh_common = new PDO('mysql:host=127.0.0.1; dbname='.$db_common.';charset=utf8', $user, $pass);
47
- $dbh_front = new PDO('mysql:host=127.0.0.1; dbname='.$db_front.';charset=utf8', $user, $pass);
48
- $dbh[] = $dbh_admin;
49
- $dbh[] = $dbh_common;
50
- $dbh[] = $dbh_front;
51
- $dbh_count = count($dbh);
52
-
53
- for ( $i = 0; $i < $dbh_count; $i++) {
54
- // オートコミットオフ
55
- $dbh[$i]->query("SET AUTOCOMMIT=0");
56
-
57
- // 静的プレースホルダを指定
58
- $dbh[$i]->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
59
-
60
- //例外処理を投げる（スロー）ようにする
61
- $dbh[$i]->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
62
-
63
- // トランザクション開始
64
- $dbh[$i]->beginTransaction();
65
- }
66
-
67
- // 共有ロック
68
- $users_lock = $dbh[0]->prepare("SELECT * FROM ".$users_table. "WHERE ID = ? LOCK IN SHARE MODE");
69
- $site_configs_lock = $dbh[0]->prepare("SELECT * FROM ".$site_configs_table. "WHERE ID = ? LOCK IN SHARE MODE");
70
- $synonyms_lock = $dbh[2]->prepare("SELECT * FROM ".$synonyms_table." WHERE ID = ? LOCK IN SHARE MODE");
71
- $suggest_words_lock = $dbh[2]->prepare("SELECT * FROM ".$suggest_words_table." WHERE ID = ? LOCK IN SHARE MODE");
72
- $categories_lock = $dbh[2]->prepare("SELECT * FROM ".$categories_table." WHERE ID = ? LOCK IN SHARE MODE");
73
- $categories_links_lock = $dbh[2]->prepare("SELECT * FROM ".$categories_links_table." WHERE ID = ? LOCK IN SHARE MODE");
74
-
75
13
  echo "usersインサート開始";
76
14
  $insert_users = "INSERT INTO ".$users_table."
77
15
  (