如何确保为空表单字段发送null而不是0

I have a table with columns that allow null values and has a default null value. On update, if the field is empty (not data inserted) my script inserts 0 instead of null. I have gone through similar questions as mine and i have tried the advice given but am still not able to fix my issue. Here's my code

我有一个表，其列允许空值，并具有默认的空值。在更新时，如果该字段为空（未插入数据），则我的脚本将插入0而不是null。我和我一样经历过类似的问题，我已经尝试过给出的建议，但仍然无法解决我的问题。这是我的代码

<?php
if (isset($_POST['submit'])) {

    # process the form
    $student_id = $_POST["student_id"];
    $subject_id = $_POST['subject_id'];
    if (is_null($_POST["test1"])){$test1 = null;} else {$test1 = $_POST["test1"];}
    if (is_null($_POST["test2"])){$test2 = null;} else {$test2 = $_POST["test2"];}
    if (is_null($_POST["test3"])){$test3 = null;} else {$test3 = $_POST["test3"];}

    for($i=0; $i < count($student_id); $i++) {
        $studentid = mysqli_real_escape_string($connection, $student_id[$i]);
        $subjectid = mysqli_real_escape_string($connection, $subject_id);
        $test_1 = mysqli_real_escape_string($connection, $test1[$i]);
        $test_2 = mysqli_real_escape_string($connection, $test2[$i]);
        $test_3 = mysqli_real_escape_string($connection, $test3[$i]);

        $query = "UPDATE fullresult SET test1='{$test_1}', test2='{$test_2}', test3='{$test_3}' WHERE student_id={$studentid} AND subject_id={$subjectid}";
        $result = mysqli_query($connection, $query);
    }
}
?>

When i echo the query, this is what i see and am wondering why i still get 0 inserted

当我回应查询时，这就是我所看到的，并且想知道为什么我仍然插入0

UPDATE fullresult SET test1=' 10', test2=' ', test3=' ' WHERE student_id=51 AND subject_id=2

4 个解决方案

#1

is_null does not return true for an empty string. Try changing your if statements to something like this:

对于空字符串，is_null不返回true。尝试将if语句更改为以下内容：

$test1 = trim($_POST["test1"])
if (!strlen($test1)) $test3 = null;

#2

You could use

你可以用

ctype_digit

to check if there are numeric characters in it.

检查其中是否有数字字符。

The function

功能

mysqli::real_escape_string -- mysqli_real_escape_string — Escapes special characters in a string for use in an SQL statement, taking into account the current charset of the connection

(Source: http://php.net/manual/en/mysqli.real-escape-string.php)

（来源：http：//php.net/manual/en/mysqli.real-escape-string.php）

Since you want to have null inside the database you should rewrite the code

由于您希望在数据库中使用null，因此您应该重写代码

    if (is_null($_POST["test1"])){$test1 = null;} else {$test1 = mysqli_real_escape_string($connection, $_POST["test1"]);}

to have the values escaped only if needed (which is in case you have a value in $_POST)

仅在需要时转义值（如果您在$ _POST中有值）

#3

What about

关于什么

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

    # process the form
    $student_id = $_POST["student_id"];
    $subject_id = $_POST['subject_id'];

    # only retrieve FILLED IN answers
    $tests = array();
    if(isset($_POST["test1"]) && strlen($_POST["test1"])) $tests['test1'] = $_POST["test1"];
    if(isset($_POST["test2"]) && strlen($_POST["test2"])) $tests['test2'] = $_POST["test2"];
    if(isset($_POST["test3"]) && strlen($_POST["test3"])) $tests['test3'] = $_POST["test3"];

    if(!empty($tests)){ # if there were no answers, there's no point in updating the database
        for($i=0; $i < count($student_id); $i++) {
            $studentid = mysqli_real_escape_string($connection, $student_id[$i]);
            $subjectid = mysqli_real_escape_string($connection, $subject_id);

            # now let's build the "SET" part of the query
            $set = array();
            foreach($tests as $key => $value) $set[]=mysqli_real_escape_string($key)."='".mysqli_real_escape_string($value)."'";
            $set = implode(', ',$set);

            # ...and finally update
            $query = "UPDATE fullresult SET {$set} WHERE student_id={$studentid} AND subject_id={$subjectid}";
            $result = mysqli_query($connection, $query);
        }
    }
}

The point of this approach is that if you don't include a key=>value pair in your UPDATE query, it will be filled in with its default value.

这种方法的要点是，如果您在UPDATE查询中没有包含key => value对，它将使用其默认值填充。

#4

-1

You must set 'null' word, not null value.

您必须设置'null'字，而不是null值。

if (is_null($_POST["test1"])){$test1 = 'null';} else {$test1 = $_POST["test1"];}
if (is_null($_POST["test2"])){$test2 = 'null';} else {$test2 = $_POST["test2"];}
if (is_null($_POST["test3"])){$test3 = 'null';} else {$test3 = $_POST["test3"];}

#1