php - 如何仅更新 PHP 表单中给出的元组的一个属性?
问题描述
我在 PHP 中创建了一个更新表单,它采用元组的属性。以下查询根据输入更新表的元组:
<?php
error_reporting(E_ALL); ini_set('display_errors', 1);
if(isset($_POST['submit'])) {
$sql = "UPDATE member SET MFirst='".$_POST["MFirst"]."', MLast='".$_POST["MLast"]."',Street='".$_POST["Street"]."'
,number='".$_POST["number"]."',postalCode='".$_POST["postalCode"]."',Mbirthdate='".$_POST["Mbirthdate"]."'
WHERE memberID='".$_POST["memberID"]."'";
$result = mysqli_query($conn,$sql);
}
?>
但是,如果不是所有属性都已提交,则不会发生更新。即使没有完成表单的所有属性,我应该如何编写查询以更改元组的属性。
表格如下:
<FORM METHOD="post" ACTION="update.php">
<div class="input-group">
<label>Member Id</label>
<INPUT TYPE="text" name="memberID" SIZE="30">
</div>
<div class="input-group">
<label>First Name</label>
<INPUT TYPE="text" name="MFirst" SIZE="30">
</div>
<div class="input-group">
<label>Last Name</label>
<INPUT TYPE="text" name="MLast" SIZE="30">
</div>
<div class="input-group">
<label>Street Name</label>
<INPUT TYPE="text" name="Street" SIZE="30">
</div>
<div class="input-group">
<label>Street Number</label>
<INPUT TYPE="number" name="number" min=0 SIZE="30">
</div>
<div class="input-group">
<label>Postal Code</label>
<INPUT TYPE="number" name="postalCode" min=0 SIZE="30">
</div>
<div class="input-group">
<label>Birth Day</label>
<INPUT TYPE="date" name="Mbirthdate" SIZE="30">
</div>
<button class="btn" TYPE="submit" name="submit">Submit Info </button>
<button class="btn" TYPE="reset" name="Reset">Reset </button>
</FORM>
解决方案
您可以通过迭代 $_POST 并相应地将它们添加到“SET”列表中来省略任何为空或未提交的列。但是,这是危险的,因为您永远不应该相信从互联网收到的任何输入。(参见SQL 注入)
以下代码在使用 mysqli Prepared Statements时执行您想要的操作,从而降低 SQL 注入风险。它还验证 $_POST 键是有效的表列名。
解释在评论中:
<?php
if (isset($_POST['submit'])) {
// Assume $_POST is in this format:
// $_POST = [ "MFirst" => "John", "MLast" => "Doe", "Street" => "Infinity Loop", "number" => 1, "postalCode" => 95014, "Mbirthdate" => "01-01-1990", "memberId" => "abcde-1", "submit" => 1];
// remove $_POST elements not part of SET list
$_POST = array_filter($_POST); //remove any empty elements
unset($_POST["submit"]);
$member_id = $_POST["memberId"];
unset($_POST["memberId"]);
//stop now if form is empty
if (count($_POST) == 0) exit();
// array of valid columns as keys, and their types as values (s = string, i = integer)
$member_col_types = [ "MFirst" => "s",
"MLast" => "s",
"Street" => "s",
"number" => "i",
"postalCode" => "i",
"Mbirthdate" => "s",
"memberId" => "s"
];
$key_val = [];
$types = "";
$columns = array_keys($member_col_types);
$values = [];
foreach ($_POST as $k => $v) {
// ensure all inputs are valid table columns, otherwise exit
if (in_array($k, $columns) == false) {
exit("");
}
// dynamically adding only valid $_POST
$key_val[] = "$k=?"; // [ "MFirst=?","MLast=?",...]
// grabs values to be inserted into ?
$values[] = $v; //["John","Doe","Infinity Loop",1,95014,"01-01-1990"]
// grabs the variable types for binding values above to types like string, integer
$types .= $member_col_types[ $k ]; // "sssiis"
}
$list = implode(", ", $key_val);
// add binding and value for memberId (part of WHERE) not processed in loop above
$values[] = $member_id; //["John","Doe","Infinity Loop",1,95014,"01-01-1990","abcde-1"]
$types .= "s"; // "sssiiss"
$sql = "UPDATE member SET $list WHERE memberId=?";
//UPDATE member SET MFirst=?, MLast=?, Street=?, number=?, postalCode=?, Mbirthdate=? WHERE memberId=?
// prepare the query (assuming mysqli connection $conn)
$stmt = $conn->prepare($sql) or die($conn->error);
// bind the values to their types
$stmt->bind_param($types, ...$values) or die($conn->error);
$stmt->execute() or die($stmt->error);
}
推荐阅读
- presto - 雅典娜:$path 与分区
- powershell - 使用 Invoke-Command 和 Get-CimInstance 将变量传递给远程计算机
- python - 在数据框中创建具有所需内容的列
- ios - 无法从 UITextField 中删除 CALayer
- pandas - Pandas 数据框复制有什么作用?
- fedora - Fedora 32 是否不再支持 rpm-python 包?如果是这样,它的位置是什么?
- android - 无法使用 Firebase 云消息传递在浏览器中打开 URL
- python - 如何使用堆叠在 python 中的相同装饰器?
- excel - 从 Now() 函数单元触发更改事件的问题
- java - 即使 redo() 工作正常,为什么 UndoManager.canRedo() 会返回 false?