我想更新我的 SQL 數據庫，而我的表單中沒有空值。這就是我所擁有的：if(isset($_POST['account_details_submit'])) { $user_id = array_values($_SESSION['user_info'])[9]; $edited = date('d.m.Y h:i a'); if(isset($_POST['account_details_first_name']) and !empty($_POST['account_details_first_name'])) { $add .= " and `first_name` = '$_POST[account_details_first_name]'"; } if(isset($_POST['account_details_last_name']) and !empty($_POST['account_details_last_name'])) { $add .= " and `last_name` = '$_POST[account_details_last_name]'"; } if(isset($_POST['account_details_phone_number']) and !empty($_POST['account_details_phone_number'])) { $add .= " and `phone_number` = '$_POST[account_details_phone_number]'"; } if(isset($_POST['account_details_address_1']) and !empty($_POST['account_details_address_1'])) { $add .= " and `address_1` = '$_POST[account_details_address_1]'"; } if(isset($_POST['account_details_address_2']) and !empty($_POST['account_details_address_2'])) { $add .= " and `address_2` = '$_POST[account_details_address_2]'"; } if(isset($_POST['account_details_city']) and !empty($_POST['account_details_city'])) { $add .= " and `city` = '$_POST[account_details_city]'"; } if(isset($_POST['account_details_post_code']) and !empty($_POST['account_details_post_code'])) { $add .= " and `post_code` = '$_POST[account_details_post_code]'"; } if(isset($_POST['account_details_country']) and !empty($_POST['account_details_country'])) { $add .= " and `country` = '$_POST[account_details_country]'"; } $update = "UPDATE `users` SET `edited` = '$edited'".$add." WHERE `id` = '$user_id'"; if ($conn->query($update) === TRUE) { echo "Record updated successfully"; } else { echo "Error updating record: " . $conn->error; }}消息是“記錄更新成功”，但只有一個正在更新的行是被稱為已編輯的行，它總是更新為 0我對其他方法持開放態度。PS我嘗試使用數組值來做，但結果不是我想要的
2 回答
犯罪嫌疑人X
TA貢獻2080條經驗 獲得超4個贊
盡管未經測試，但您可以做的是使用表單字段名稱和數據庫列名稱的數組來幫助以更安全的方式動態構建您的 sql，使用prepared statement
if( !empty( $_SESSION['user_info'] ) && $_SERVER['REQUEST_METHOD']=='POST' ) {
$fields=array(
'account_details_first_name' => 'first_name',
'account_details_last_name' => 'last_name',
'account_details_phone_number' => 'phone_number',
'account_details_address_1' => 'address_1',
'account_details_address_2' => 'address_2',
'account_details_city' => 'city',
'account_details_post_code' => 'post_code',
'account_details_country' => 'country'
);
/* default variables... */
$user_id = array_values( $_SESSION['user_info'] )[9];
$edited = date('d.m.Y h:i a');
/* placeholders used to generate sql statement */
$params=array();
$values=array();
$types=array();
/*
iterate through all submitted POST fields -
if they are not empty add them to the placeholders
*/
foreach( $_POST as $field => $value ){
if( !empty( $value ) ){
$params[]=sprintf( '`%s`=?', $fields[ $field ] );
$values[]=$value;
$types[]='s';
}
}
/*
add semi-static variables to placeholders too
*/
$values[]=$user_id;
$types[]='s';
/* create a sql statement and the use that to create the `prepared statement` */
$sql = sprintf( 'update `users` set %s where `id`=?', implode( ',', $params ) );
#echo $sql;
$stmt=$db->prepare( $sql );
/* bind the types and assign variables with a SPLAT */
$stmt->bind_param( implode('',$types), ...$values );
$result=$stmt->execute();
echo $result ? 'Record updated successfully' : 'Error updating record';
}
通過echo在任何調用之前退出 SQL，$db我能夠生成以下 SQL，它看起來可以在以下環境中使用prepared statement：
update `users` set `first_name`=?,`last_name`=?,`phone_number`=?,`address_1`=?,`address_2`=?,`city`=?,`post_code`=?,`country`=? where `id`=?
沒有架構和數據，我無法進一步測試，但看起來還不錯?，F在是時候喝杯酒了……
不負相思意
TA貢獻1777條經驗 獲得超10個贊
您需要使用 ',' 而不是 'and' 來分隔字段，我建議您使用受函數 htmlspecialchars 保護的 xss，請參閱如何使用 HTML/PHP 防止 XSS？. 嘗試這個：
if(isset($_POST['account_details_submit'])) {
$valuesToUpdate = [];
$fields = [
'first_name' => 'account_details_first_name',
'last_name' => 'account_details_last_name',
'phone_number' => 'account_details_phone_number',
'address_1' => 'account_details_address_1',
'address_2' => 'account_details_address_2',
'city' => 'account_details_city',
'post_code' => 'account_details_post_code',
'country' => 'account_details_country'
];
foreach ($fields as $key => $field) {
$protectedFromXss = trim(htmlspecialchars($_POST[$field]));
if ($protectedFromXss) {
$valuesToUpdate[] = "$key = '$protectedFromXss'";
}
}
if (count($valuesToUpdate)) {
$values = ', ' . implode(', ', $valuesToUpdate);
}
$edited = date('d.m.Y h:i a');
$user_id = array_values($_SESSION['user_info'])[9];
$update = "UPDATE `users` SET `edited` = '{$edited}' {$values} WHERE `id` = '$user_id'";
if ($conn->query($update) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
}
- 2 回答
- 0 關注
- 104 瀏覽
添加回答
舉報
0/150
提交
取消