MariaDB插入数据时SQLSTATE[42000]:1064语法错误求助

核心语法错误：SQL语句未闭合 #

你的错误提示指向SQL语法错误，根源是INSERT语句的VALUES子句末尾缺少闭合的右括号。

原错误SQL：

$sql = $conn->prepare("INSERT INTO registration (userName, password, firstName, lastName, address1, address2, city, state, zipCode, phone, email, gender, maritalStatus, dateOfBirth)
 VALUES (:userName, :password, :firstName, :lastName, :address1, :address2, :city, :state, :zipCode,:phone, :email, :gender, :maritalStatus, :dateOfBirth");

修正后（添加末尾的)）：

$sql = $conn->prepare("INSERT INTO registration (userName, password, firstName, lastName, address1, address2, city, state, zipCode, phone, email, gender, maritalStatus, dateOfBirth)
 VALUES (:userName, :password, :firstName, :lastName, :address1, :address2, :city, :state, :zipCode,:phone, :email, :gender, :maritalStatus, :dateOfBirth)");

其他潜在问题检查 #

除了核心语法错误，还有几处需要修正的细节：

• 变量绑定不匹配：:address1绑定的是$address，建议保持变量名与字段名一致避免混淆，修改为：

  $sql->bindParam(':address1', $address1);
  

• SESSION赋值错误：$_SESSION["last_id"] = "last_id";是把字符串存入SESSION，应替换为实际自增ID变量：

  $_SESSION["last_id"] = $last_id;
  

• 确保连接文件被引入：insertValidData.php中需通过require_once('connectionInfo.php');引入数据库连接变量，否则会出现未定义变量错误。
• 跳转后终止脚本：header("Location: confirmation.php");后建议添加exit;，避免后续代码继续执行。

修正后的完整核心代码片段 #

<?php
if($isValid){
    try{
        require_once('connectionInfo.php'); // 引入连接变量
        $conn = new PDO ("mysql:host=$serverName;dbname=$dbname", $dbuserName, $dbpassword);

        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $sql = $conn->prepare("INSERT INTO registration (userName, password, firstName, lastName, address1, address2, city, state, zipCode, phone, email, gender, maritalStatus, dateOfBirth)
         VALUES (:userName, :password, :firstName, :lastName, :address1, :address2, :city, :state, :zipCode,:phone, :email, :gender, :maritalStatus, :dateOfBirth)");


        $sql->bindParam(':userName', $userName);
        $sql->bindParam(':password', $password);
        $sql->bindParam(':firstName', $firstName);
        $sql->bindParam(':lastName', $lastName);
        $sql->bindParam(':address1', $address1); // 修正变量名
        $sql->bindParam(':address2', $address2);
        $sql->bindParam(':city', $city);
        $sql->bindParam(':state', $state);
        $sql->bindParam(':zipCode', $zipcode);
        $sql->bindParam(':phone', $phoneNumber);
        $sql->bindParam(':email', $email);
        $sql->bindParam(':gender', $gender);
        $sql->bindParam(':maritalStatus', $maritalStat);
        $sql->bindParam(':dateOfBirth', $birthday);
        
        $sql->execute();

        $last_id = $conn->lastInsertId();
        $_SESSION["last_id"] = $last_id; // 修正SESSION赋值

        header("Location: confirmation.php");
        exit; // 跳转后终止脚本执行
    }
    catch(PDOException $e){
        echo "Connection Failed: " . $e->getMessage();
        
    } finally{
        $conn = null;
    }
}
?>

内容的提问来源于stack exchange，提问作者Mateo John