teratail
回答率
85.48%
質問するログイン新規登録
トップMySQLに関する質問配列をSQL文に含めたいです

編集履歴

回答編集履歴

2

typo

2016/03/04 11:09

投稿

mpyw
mpyw

スコア5223

test CHANGED
@@ -16,15 +16,15 @@
16
16
 
17
17
  // ユニークな1次元の数値添字配列を強制
18
18
 
19
- $city_id = isset($_GET['city_cd']) ? (array)$_GET['city_id'] : [];
19
+ $city_cd = isset($_GET['city_cd']) ? (array)$_GET['city_cd'] : [];
20
20
 
21
- $city_cd = array_values(array_unique(array_filter($city_id, 'is_string')));
21
+ $city_cd = array_values(array_unique(array_filter($city_cd, 'is_string')));
22
22
 
23
23
 
24
24
 
25
- // city_idが空なら異常終了
25
+ // city_cdが空なら異常終了
26
26
 
27
- if (!$city_id) {
27
+ if (!$city_cd) {
28
28
 
29
29
  header('Content-Type: text/plain; charset=UTF-8', true, 400);
30
30
 
@@ -34,15 +34,15 @@
34
34
 
35
35
 
36
36
 
37
- // SELECT ... FROM .. WHERE city_id IN (?, ?, ?, ..., ?)
37
+ // SELECT ... FROM .. WHERE city_cd IN (?, ?, ?, ..., ?)
38
38
 
39
39
  $sql = sprintf(
40
40
 
41
41
  'SELECT town_cd, town_name
42
42
 
43
- FROM town WHERE city_id IN (%s) ORDER BY town_cd',
43
+ FROM town WHERE city_cd IN (%s) ORDER BY town_cd',
44
44
 
45
- implode(', ', array_fill_keys($city_id, '?'))
45
+ implode(', ', array_fill_keys($city_cd, '?'))
46
46
 
47
47
  );
48
48
 
@@ -52,13 +52,13 @@
52
52
 
53
53
  // ?に値をバインドして実行
54
54
 
55
- $stmt->execute($city_id);
55
+ $stmt->execute($city_cd);
56
56
 
57
57
  ```
58
58
 
59
59
 
60
60
 
61
- もし`city_id`が整数型である場合は
61
+ もし`city_cd`が整数型である場合は
62
62
 
63
63
 
64
64
 
@@ -76,15 +76,15 @@
76
76
 
77
77
  // ユニークな整数値を持つ数値添字配列を強制
78
78
 
79
- $city_id = isset($_GET['city_cd']) ? (array)$_GET['city_id'] : [];
79
+ $city_cd = isset($_GET['city_cd']) ? (array)$_GET['city_cd'] : [];
80
80
 
81
- $city_cd = array_values(array_unique(array_map($city_id, 'intval')));
81
+ $city_cd = array_values(array_unique(array_map($city_cd, 'intval')));
82
82
 
83
83
 
84
84
 
85
- // city_idが空なら異常終了
85
+ // city_cdが空なら異常終了
86
86
 
87
- if (!$city_id) {
87
+ if (!$city_cd) {
88
88
 
89
89
  header('Content-Type: text/plain; charset=UTF-8', true, 400);
90
90
 
@@ -100,9 +100,9 @@
100
100
 
101
101
  'SELECT town_cd, town_name
102
102
 
103
- FROM town WHERE city_id IN (%s) ORDER BY town_cd',
103
+ FROM town WHERE city_cd IN (%s) ORDER BY town_cd',
104
104
 
105
- implode(', ', implode($city_id))
105
+ implode(', ', implode($city_cd))
106
106
 
107
107
  );
108
108
 

1

整数型の場合について補足

2016/03/04 11:09

投稿

mpyw
mpyw

スコア5223

test CHANGED
@@ -55,3 +55,61 @@
55
55
  $stmt->execute($city_id);
56
56
 
57
57
  ```
58
+
59
+
60
+
61
+ もし`city_id`が整数型である場合は
62
+
63
+
64
+
65
+ ```php
66
+
67
+ <?php
68
+
69
+
70
+
71
+ // 接続
72
+
73
+ require 'db_con.php';
74
+
75
+
76
+
77
+ // ユニークな整数値を持つ数値添字配列を強制
78
+
79
+ $city_id = isset($_GET['city_cd']) ? (array)$_GET['city_id'] : [];
80
+
81
+ $city_cd = array_values(array_unique(array_map($city_id, 'intval')));
82
+
83
+
84
+
85
+ // city_idが空なら異常終了
86
+
87
+ if (!$city_id) {
88
+
89
+ header('Content-Type: text/plain; charset=UTF-8', true, 400);
90
+
91
+ exit('パラメータが不正です');
92
+
93
+ }
94
+
95
+
96
+
97
+ // 実行
98
+
99
+ $sql = sprintf(
100
+
101
+ 'SELECT town_cd, town_name
102
+
103
+ FROM town WHERE city_id IN (%s) ORDER BY town_cd',
104
+
105
+ implode(', ', implode($city_id))
106
+
107
+ );
108
+
109
+ $stmt = $dbh->query($sql);
110
+
111
+ ```
112
+
113
+
114
+
115
+ でいいかと思います．最初の`$_GET`のバリデーションが重要です，特に後者においては必須です．ここは手を抜いてはいけません．