php - 通过PHP将日期值插入MySQL

Question

我想要做的就是标题暗示将日期插入创建的表中。

我已经做了研究。我考虑了以下主题:

• Parse error: syntax error, unexpected T_VARIABLE on line 107
• SQL DML: Incorrect date value (MySQL)

以及其他。

但是没有什么能真正帮助解决这个问题。 $dob 变量是故意放在那里的。我需要确保它会随着用户的变化而变化。

这是我的 php 代码:

$firstName = $middleName = $lastName = $mobileNumber = $dob = $address = "";
$firstName0 = $middleName0 = $lastName0 = $mobileNumber0 = $dob0 = $address0 = "";
$dobErr = $addressErr = $mobilenumErr = $emailErr = $passwordErr = "";
$email = $password = $fnameErr = $mnameErr = $lnameErr = $conn = $data = "";
$email0 = $password0 = "";

require_once 'login.php';
$conn = new mysqli($hn, $un, $pw, $db);
if($conn->connect_error) die($conn->connect_error);

function test_input($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
    return $data;
}

if(isset($_POST['submit'])) {
    // input validation

    if ($_SERVER["REQUEST_METHOD"] == "post") {
        if (empty($_POST["firstName"])) {
            $fnameErr = "Name is required";             
        } else {
            $firstName0 = test_input($_POST["firstName"]);              
        }
        if (empty($_POST["lastName"])) {
            $lnameErr = "Last name is required";        
        } else {
            $lastName0 = test_input($_POST["lastName"]);                
        }
        if (empty($_POST["dob"])) {
            $dobErr = "Date of birth is required";              
        } else {
            $dob0 = test_input($_POST["dob"]);              
        }
        if (empty($_POST["address"])) {
            $addressErr = "Address is required";                
        } else {
            $address0 = test_input($_POST["address"]);              
        }
        if (empty($_POST["mobileNumber"])) {
            $mobilenumErr = "Mobile number is required";                
        } else {
            $mobileNumber0 = test_input($_POST["mobileNumber"]);                
        }
        if (empty($_POST["email"])) {
            $emailErr = "Email is required";                
        } else {
            $email0 = test_input($_POST["email"]);
        }
        if (empty($_POST["password"])) {
            $passwordErr = "Password is required";              
        } else {
            $password0 = test_input($_POST["password"]);            
        }
    }
    if ($_SERVER["REQUEST_METHOD"] == "post") {
        // sanitizing the input
        $firstName = test_input($_POST["$firstName0"]);
        if (!preg_match("/^[a-zA-Z]*$/",$firstName)) {
            $nameErr = "Only letters are allowed";          
        }
        $middleName = test_input($_POST["$middleName0"]);
        $lastName = test_input($_POST["$lastName0"]);
        $dob = test_input($_POST["$dob0"]);
        $address = test_input($_POST["$address0"]);
        $mobileNumber = test_input($_POST["$mobileNumber0"]);
        $email = test_input($_POST["$email0"]);
        if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
            $emailErr = "Invalid email format";         
        }
        $password = test_input($_POST["$password0"]);   
    }

    // sending valid data to the database   
    $query = "insert into details values" . "("$firstName","$middleName","$lastName",STR_TO_DATE("$dob","%Y/%m/%d"),"$address","$mobileNumber","$email", "$password")";
    $result = $conn->query($query);
    if(!$result) {
        echo 'Insert failed: $query<br>' . $conn->error . '<br><br>';
    } else { 
        echo 'Data saved !';
    }
}

我收到此错误:

Insert failed: insert into details values('','','',STR_TO_DATE("","%Y/%m/%d"),'','','', '') Incorrect datetime value: '' for function str_to_date

Answer 1

$query = "insert into details values ($firstName,$middleName,$lastName,STR_TO_DATE($dob,'%Y/%m/%d'),$address,$mobileNumber,$email, $password)";