Mysql 将我的日期列保存为 0000-00-00?

2022-01-15 00:00:00 datetime format mysql

我已插入除错误插入为 0000-00-00 的日期部分以外的所有记录.我的代码有什么问题?

I have inserted all records except the date part which inserted incorrectly as 0000-00-00. What is the problem inside my code?

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

        $birthDay= $_POST["Birthday_Year"] . "-" . $_POST["Birthday_Month"] . "-" . $_POST["Birthday_day"];


        $sql="SELECT * FROM student WHERE stud_id=1";
        $result = mysqli_query($db,$sql)  or die("Error: ".mysqli_error($db));
        $row=mysqli_fetch_array($result,MYSQLI_ASSOC);
        if(mysqli_num_rows($result) == 1)
        {
            $msg = "Sorry...This Studnent ID is already exist...";
        }
        else
        {
            $query = mysqli_query($db, "INSERT INTO student (stud_fname, stud_lname, stud_gfname,stud_id,stud_gender,stud_dob,stud_dep,stud_year,stud_section)
            VALUES ('$fname', '$lname', '$gfname','$studid', '$gender', '$birthDay','$department', '$year', '$section')");
            if($query)
            {
                $msg = "Thank You! you are now registered.";
            }
        }
    }

推荐答案

在所有情况下，日期保存为 0000-00-00 意味着 MySQL 获得了无效值(包括根本没有值).检查您的代码以确保它始终具有有效的日期值格式.

In all cases date saved as 0000-00-00 means that MySQL got an invalid value (including no value at all). Check your code to make sure that it always has a valid value format for the date.

来自 MySQL 文档:

无效的 DATE、DATETIME 或 TIMESTAMP 值将转换为相应类型的零"值('0000-00-00' 或 '0000-00-00 00:00:00').

Invalid DATE, DATETIME, or TIMESTAMP values are converted to the "zero" value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').

为您的代码添加一些验证:

Add some validation to your code:

if (!empty($_POST["Birthday_Year"]) && !empty($_POST["Birthday_Month"]) && !empty($_POST["Birthday_day"]))
{
    // do stuff
} else 
{
    // validation error: some of the values are empty
}

PS:另一个问题是，如果你让用户自己输入值，无需验证.

PS: Another problem is, that your code has a hole for some SQL injection attack, if you let user input the values on his own, without validation.

相关文章