时间戳

首页 > 解决方案 > 运行 SQL INSERT 以“错误 1064”结束

php - 运行 SQL INSERT 以“错误 1064”结束

问题描述

我编写了这段代码，它应该将一堆值插入数据库，但是每当我运行这段代码时，它都会显示“致命错误：未捕获的异常 'PDOException' 并带有消息'SQLSTATE [42000]：语法错误或访问冲突：1064您的 SQL 语法有错误；请查看与您的 MySQL 服务器版本相对应的手册，以了解要使用的正确语法 near”和“PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL语法；查看与您的 MySQL 服务器版本相对应的手册，以了解在附近使用的正确语法。有人可以帮忙吗？

我的代码，根据这些错误消息，错误位于 INSERT 部分：

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

if (session_status() == PHP_SESSION_NONE) {
  session_start();
}

function getValue1() {
    if (!isset($_GET[r])) {
        return false;
        $referral_code = "-";
    }
    return $_GET[r];
    $referral_code = $_GET[r];
}

// initializing variables
$username = "";
$email    = "";
$errors = array(); 

   require("php_db_info.php");

   $dsn = 'mysql:host=127.0.0.1;dbname=user_db;charset=utf8';

       $conn = new PDO($dsn, $username1, $password);
       $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
       $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// REGISTER USER
if (isset($_POST['reg_user'])) {
  // receive all input values from the form
  $username = $_POST['username'];
  $email = $_POST['email'];
  $password_1 = $_POST['password_1'];
  $password_2 = $_POST['password_2'];
  if (getValue('r')) {
    $referral_code = $_GET['r'];
} else {
      $referral_code = "-";
  } 

  if (empty($username)) { array_push($errors, "Username is required."); }
  if (empty($email)) { array_push($errors, "Email is required."); }
  if (empty($password_1)) { array_push($errors, "Password is required."); }
  if ($password_1 != $password_2) {
      array_push($errors, "The two passwords do not match.");
  }
  if(preg_match("/[^a-zA-Z0-9_-]/i", $username)) {
    array_push($errors, "You can only use letters, numbers, underscores and dashes.");
  }
  if (strlen($username) <= 1) {
    array_push($errors, "Your username must have at least 2 characters.");
  } 
  if (strlen($username) >= 22) {
  array_push($errors, "Your username must have below 22 characters.");
  } 
  require "server_bannedwords.php";

  $stm_reg1 = $conn->prepare('SELECT * FROM users WHERE username = :username');
  $stm_reg1->bindParam(":username", $username, PDO::PARAM_STR);
  $stm_reg1->execute();
  $row = $stm_reg1->fetch(PDO::FETCH_ASSOC);
  if( ! $row) {} else {
    array_push($errors, "Username already exists.");
  }

  $stm_reg2 = $conn->prepare('SELECT * FROM users WHERE email = :email');
  $stm_reg2->bindParam(":email", $email, PDO::PARAM_STR);
  $stm_reg2->execute();
  $row = $stm_reg2->fetch(PDO::FETCH_ASSOC);
  if( ! $row) {} else {
    array_push($errors, "Email already exists.");
  }

  if(filter_var($email, FILTER_VALIDATE_EMAIL)) {
     if (count($errors) == 0) {
        $password2 = password_hash($password_1, PASSWORD_DEFAULT);
        $stm_reg_fin = $conn->prepare("INSERT INTO users (username, email, password, coins, alltimecoins, earnedcoins, referralcoins, vouchercoins, paidout, paypal, bitcoin, referred_by, deleted, date_deleted, verified, date_created, date_active) VALUES (':username', ':email', ':password2', '0', '0', '0', '0', '0', '0', '-', '-', ':referral_code', '0', NULL, '0', curdate(), curdate()");
     /*   $stm_reg_fin->bindParam(":username", $username, PDO::PARAM_STR);
        $stm_reg_fin->bindParam(":email", $email, PDO::PARAM_STR);
        $stm_reg_fin->bindParam(":password2", $password2, PDO::PARAM_STR);
        $stm_ref_fin->bindParam(":referral_code", $referral_code, PDO::PARAM_INT);*/
        $stm_reg_fin->execute(array(':username' => $_POST['username'], ':email' => $_POST['email'], ':password2' => $_POST['password_1'], ':referral_code' => $referral_code));
        $_SESSION['username'] = $username;
        $hour = time() + 15 * 24 * 60 * 60;
        setcookie('c_username', $username, $hour);
        setcookie('c_password', $password2, $hour);  
        /* VERIFY OBAVEZNO DODATI ------------------------ */
        header("location: home.php");
  }
} else {
  array_push($errors, "The e-mail does not exist.");
}
}

标签： phpsqlpdo

解决方案

在您的插入查询中，您在)查询末尾有遗漏，如下所示： 

 $stm_reg_fin = $conn->prepare("INSERT INTO users (username, email, password, coins,
alltimecoins, earnedcoins, referralcoins, vouchercoins, paidout, paypal, bitcoin,
 referred_by, deleted, date_deleted, verified, date_created, date_active) VALUES
 (':username', ':email', ':password2', '0', '0', '0', '0', '0', '0', '-', '-', 
':referral_code', '0', NULL, '0', curdate(), curdate())"); 
                                                      ^ here

推荐阅读