teratail
回答率
85.48%
質問するログイン新規登録
トップMySQLに関する質問SQLでINSERT文の中にサブクエリーがある場合、どのようにしてPrepared Statmentを使えばいいのかを知りたい。

編集履歴

質問編集履歴

2

コードを変更しました。

2023/01/05 10:16

投稿

zy1088710
zy1088710

スコア5

test CHANGED
File without changes
test CHANGED
@@ -18,8 +18,6 @@
18
18
 
19
19
  ```
20
20
  特にエラーメッセージは出ていません。Formをサブミットすると、ページがリロードして終わりといった感じです。データはデータベースに送信されていません。
21
- また、下記のコードも画面に出てきていません。
22
- echo "<p style='color:green;text-align:center;margin-top:1rem; font-weight:bold;'>User Account Successfully Created! <p>";
23
21
 
24
22
  ```
25
23
 
@@ -45,72 +43,39 @@
45
43
 
46
44
  if(!isRegistered($email)){
47
45
  if($password == $confirm_password){
48
-
46
+ $sql = "INSERT INTO address(address1,address2,city,state,postcode,country_id)
49
- $sql = "SELECT id FROM country WHERE country_name = ?";
47
+ VALUES(?,?,?,?,?,(SELECT id FROM country WHERE country_name = ?))";
50
48
  $stmt = mysqli_stmt_init($conn);
51
49
  if(!mysqli_stmt_prepare($stmt,$sql)){
52
- echo "SQL Error1";
50
+ echo "SQL error : INSERT INTO address failed";
51
+ echo $conn -> error;
53
52
  }else{
54
- mysqli_stmt_bind_param($stmt,"s",$country);
53
+ mysqli_stmt_bind_param($stmt,"sssssi",$address1, $address2,$city,$state,$postCode,$country);
55
- mysqli_stmt_execute();
54
+ mysqli_stmt_execute($stmt);
56
- mysqli_stmt_store_result($stmt);
55
+
57
- if(mysqli_stmt_num_rows($stmt) == 1){
58
- $row = mysqli_fetch_assoc($result);
59
- mysqli_stmt_close($stmt);
60
- mysqli_close($con);
56
+ }
61
57
 
62
- $sql2 = "INSERT INTO address(address1, address2, city, state, postcode, country_id)
58
+ $sql2 = "INSERT INTO user(first_name,last_name,email,password,address_id)VALUES(?,?,?,?,(SELECT id FROM address WHERE address1 = ?))";
63
- VALUES(?,?,?,?,?,?);";
64
- $stmt2 = mysqli_stmt_init($conn);
59
+ $stmt2 = mysqli_stmt_init($conn);
65
- if(!mysqli_stmt_prepare($stmt2,$sql2)){
60
+ if(!mysqli_stmt_prepare($stmt2,$sql2)){
66
- echo "SQL Error2";
61
+ echo "SQL error : INSERT INTO user failed";
62
+ echo $conn -> error;
67
- }else{
63
+ }else{
64
+ $encrypted_password = password_hash($password,PASSWORD_BCRYPT);
68
- mysqli_stmt_bind_param($stmt2,"sssssi",$address1,$address2,$city,$state,$postCode,$row["id"]);
65
+ mysqli_stmt_bind_param($stmt2,"ssssi",$firstName,$lastName,$email,$encrypted_password,$address1);
69
- mysqli_stmt_execute();
70
- mysqli_stmt_close($stmt2);
66
+ mysqli_stmt_execute($stmt2);
67
+ echo "<p style='color:green;text-align:center;margin-top:1rem;
68
+ font-weight:bold;'> User account successfully created! <p>";
69
+ }
70
+
71
- mysqli_close($con);
71
+ }else{
72
+ echo "<p style='color:red;text-align:center;margin-top:1rem;
73
+ font-weight:bold;'>Please Re-confirm your password <p>";
74
+ }
72
75
 
73
- $sql3 = "SELECT id FROM address WHERE address = ?";
74
- $stmt3 = mysqli_stmt_init($conn);
75
- if(!mysqli_stmt_prepare($stmt3,$sql3)){
76
- echo "SQL Error3";
77
- }else{
78
- mysqli_stmt_bind_param($stmt3,"s",$address1);
79
- mysqli_stmt_execute();
80
- mysqli_stmt_store_result($stmt3);
81
- if(mysqli_stmt_num_rows($stmt3)==1){
82
- $row2 = mysqli_fetch_assoc($result2);
83
- mysqli_stmt_close($stmt3);
84
- mysqli_close($con);
85
-
86
- $encrypted_password = password_hash($password,PASSWORD_BCRYPT);
87
- $sql4="INSERT INTO user(email,first_name,last_name,password, address_id)
88
- VALUES(?,?,?,?,?);";
89
-
90
- $stmt4 = mysqli_stmt_init($conn);
91
- if(!mysqli_stmt_prepare($stmt4,$sql4)){
92
- echo "SQL ERROR3";
93
- }else{
94
- mysqli_stmt_bind_param($stmt4,"ssssi",$email,$firstName,$lastName,$encrypted_password,$row2["id"]);
95
- mysqli_stmt_execute();
96
- echo "<p style='color:green;text-align:center;margin-top:1rem;
97
- font-weight:bold;'>User Account Successfully Created! <p>";
98
- mysqli_stmt_close($stmt4);
99
- mysqli_close($con);
100
- }
101
- }
102
- }
103
- }
104
- }
105
- }
106
76
  }else{
107
77
  echo "<p style='color:red;text-align:center;margin-top:1rem;
108
- font-weight:bold;'>Please Re-confirm your password <p>";
109
- }
110
-
111
- }else{
112
- echo "<p style='color:red;text-align:center;margin-top:1rem;
113
- font-weight:bold;'>Email already in use. Try again! <p>";
78
+ font-weight:bold;'>Email already in use. Try again! <p>";
114
79
  }
115
80
  }
116
81
  ```
MySQLPHP

1

カッコの閉じ忘れ。

2023/01/05 00:00

投稿

zy1088710
zy1088710

スコア5

test CHANGED
File without changes
test CHANGED
@@ -2,7 +2,7 @@
2
2
  PHPとMySQLを使ってログインのシステムを作っています。
3
3
  SQLインジェクションを防ぐためにPrepared Statementを使っているのですが、
4
4
 
5
- INSERT INTO user(email,first_name,last_name,city,state,postcode,address_id)Values(?,?,?,?,?,?,(SELECT id FROM address WHERE address1 = ?);
5
+ INSERT INTO user(email,first_name,last_name,city,state,postcode,address_id)Values(?,?,?,?,?,?,(SELECT id FROM address WHERE address1 = ?));
6
6
 
7
7
  本当はこの”?”に$address1という$_POSTで取得した変数を入れたい。
8
8
  *addressのテーブルにaddress1というカラムがあります。
MySQLPHP