php - 当 HTML 表单字段之一提交为空白且所有询问列的默认值为 NULL 时，如何更新现有 MySQL 表？

Question

登录后用户需要填写另一个表格。该网站有一个名为 users 的表，其列名为-

Ten_School_name, Ten_Board, Ten_Percentage, Twl_School_Name, Twl_Board, Twl_Percentage

Ten_School_name、Twl_School_Name 设置为 VARCHAR(70)，默认值为 NULLTen_Board、Twl_Board 设置为 VARCHAR(10)，默认值为 NULLTen_Percentage、Twl_Percentage 设置为 int(3)，默认值为 NULL

用户名、电子邮件和密码是另外三列，当用户在网站上注册时这些列被占用，其余设置为NULL 作为默认值。当我通过填写所有六个字段来提交表单时，表单已成功提交，但如果任何字段留空，则会给出处理脚本的错误 第 2 行的“Twl_Percentage”列的“错误整数值:”/

我已经浪费了很多时间寻找解决方案。我希望这里有人能解决我的问题。

这是我的表格-

 <form id="msform" action="includes/cand-reg-dbs.php" method="POST">
    <!--10th Class Details-->
    <label>10th School Name</label><input type="text" name="tenth-school"><br />

    <label>10th Board</label>
    <select name="tenth-board">
        <option name="tenth-board1" value="icse">ICSE</option>
        <option name="tenth-board2" value="cbse">CBSE</option>
        <option name="tenth-board3" value="up-board">UP Board</option>
    </select>
    <br />
    <label>10th Percentage</label>
    <input type="number" name="tenth-percent">
    <br />


    <!--12th Class Details-->
    <label>12th School Name</label><input type="text" name="twl-school"><br />

    <label>12th Board</label>
    <select name="twl-board">
        <option name="twl-board1" value="icse">ISE</option>
        <option name="twl-board2" value="cbse">CBSE</option>
        <option name="twl-board3" value="up-board">UP Board</option>
    </select>
    <br />
    <label>12th Percentage</label>
    <input type="number" name="twl-percent">

    <input type="submit" name="submita" id="" class="" value="Save" /></form>

这是我处理数据的 php 脚本-

if (isset($_POST['submita'])) {

    $dbServername = "localhost";
    $dbUsername = "root";
    $dbPassword = "";
    $dbName = "jobin";

    $con = mysqli_connect($dbServername, $dbUsername, $dbPassword, $dbName);

 if (isset($_SESSION['username'])) {
        $user = $_SESSION['username'];
        echo $user;
        $eml = $_SESSION['cand_email'];
        echo $eml;
    }



$tenthSchool = mysqli_real_escape_string($con, $_POST['tenth-school']);
$tenthBoard = mysqli_real_escape_string($con, $_POST['tenth-board']);
$tenthPercent = mysqli_real_escape_string($con, $_POST['tenth-percent']);
$twlSchool = mysqli_real_escape_string($con, $_POST['twl-school']);
$twlBoard = mysqli_real_escape_string($con, $_POST['twl-board']);
$twlPercent = mysqli_real_escape_string($con, $_POST['twl-percent']);

$sqlsd = "UPDATE users 
SET 
Ten_School_Name = '$tenthSchool',
Ten_Board = '$tenthBoard',
Ten_Percentage = '$tenthPercent',
Twl_School_Name = '$twlSchool',
Twl_Board = '$twlBoard',
Twl_Percentage = '$twlPercent'
WHERE
Email = '$eml';";



if(!$con)
{
die('Could not connect: ' . mysqli_connect_error());
}
$resultsd = mysqli_query($con, $sqlsd);

if(!$resultsd)
{
die('Could not update data: ' . mysqli_connect_error());
}
if ($resultsd) {
    header("Location: ../candidate-registration.php?sd=success");
}   

} else {
echo "nothing";
}

Answer 1

我会将列从 NULL 更改为 NOT NULL，并将默认值设置为表上的空字符串 ''。这样您就可以使用更安全的准备好的语句，如下所示:

$stmt = $link->prepare("UPDATE users SET Ten_School_Name = ?, Ten_Board = ?, Ten_Percentage = ?, Twl_School_Name = ?, Twl_Board = ?, Twl_Percentage = ? WHERE Email = ?");
$stmt->bind_param("ssissis", $tenthSchool, $tenthBoard, $tenthPercent, 
$twlSchool, $twlBoard, $twlPercent, $eml);
$stmt->execute();
$stmt->close();

如果您无法更改列，则必须仅使用非空字段编写查询。像这样:

$sqlsd2 = "";
if ($tenthSchool != "") { $sqlsd2 .= "Ten_School_Name = '$tenthSchool', ";
if ($tenthBoard != "") { $sqlsd2 .= "Ten_Board = '$tenthBoard', ";
if ($tenthPercent != "") { $sqlsd2 .= "Ten_Percentage = $Ten_Percentage, ";
if ($twlSchool != "") { $sqlsd2 .= "Twl_School_Name = '$twlSchool', "
if ($twlBoard != "") { $sqlsd2 .= "Twl_Board = '$twlBoard', "
if ($twlPercent != "") { $sqlsd2 .= "Twl_Percentage = $twlPercent ";

if($sqlsd2 !="")
{ //update)
 $sqlsd = "UPDATE users SET " . $sqlsd2 . "WHERE Email = '$eml'";
}
else
{
//nothing to update
}

请注意，第二种方法不如第一种方法安全，因为 mysqli_real_escape_string 并不能完全避免 mysql 注入(inject)。您必须使用准备好的语句。

有一种方法可以使用call_user_func_array()来制作动态准备语句，有时我无法避免这样做，但它更复杂。我建议简单地更改您的列并执行准备好的语句。