mysql - 将值一一添加到sql中的表

我要在检查值是否有效且不为空之后一一插入值，提交后，我想显示一条消息，确认已创建我的注册，但它给了我错误：INSERT INTO订户（注释，reg_date）VALUES（ “，当前时间（）） 。

创建表订户

"CREATE TABLE subscribers (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
lastname VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL,
comment VARCHAR(50) NOT NULL,
gender VARCHAR(50) NOT NULL,
reg_date TIMESTAMP
)";

将数据插入MySQL表

if(!empty($_POST["name"]) && preg_match("/^[a-zA-Z ]*$/",$name)) {
$sql = "INSERT INTO subscribers (name)
VALUES ('".$_POST["name"]."')";
}
if(!empty($_POST["lastname"]) && preg_match("/^[a-zA-Z ]*$/",$name)) {
$sql = "INSERT INTO subscribers (lastname)
VALUES ('".$_POST["lastname"]."')";
}
if(!empty($_POST["email"]) && filter_var($email, FILTER_VALIDATE_EMAIL)) {
$sql = "INSERT INTO subscribers (email)
VALUES ('".$_POST["email"]."')";
}
if(!empty($_POST["gender"])) {
$sql = "INSERT INTO subscribers (gender)
VALUES ('".$_POST["gender"]."')";
}

$sql = "INSERT INTO subscribers (comment,reg_date)
VALUES ('".$_POST["comment"]."',CURRENT_TIME())";

if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
    }

最佳答案

您需要编写一个插入所有列的查询，而不是为每个列单独查询。而且，您应该使用准备好的语句，而不是连接变量。

输入验证应报告错误，而不是分配查询。

if(!preg_match("/^[a-zA-Z ]+$/",$_POST['name'])) {
    die("Invalid name");
}
if(!preg_match("/^[a-zA-Z ]*$/",$_POST['lastname'])) {
    die("Invalid last name");
}
if(!filter_var($email, FILTER_VALIDATE_EMAIL)) {
    die("Invalid email");
}
if(empty($_POST["gender"])) {
    die("Empty gender");
}

$sql = "
    INSERT INTO subscribers (name, lastname, email, gender, comment, reg_date)
    VALUES(?, ?, ?, ?, ?, CURRENT_TIME())";
$stmt = $conn->prepare($sql) or die("Error: " . $sql . "<br>" . $conn->error);
$stmt->bind_param("sssss", $_POST['name'], $_POST['lastname'], $_POST['email'], $_POST['gender'], $_POST['comment']);

if ($stmt->execute()) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $stmt->error;
}

关于mysql - 将值一一添加到sql中的表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/55270883/