使用PDO扩展的execute(array())插入语句不工作 - Insert statement not working using execute(array()) of PDO Extension

 $stmt = $conn->prepare("INSERT INTO user VALUES ('',:username,md5(:password),'',1,'','',:email,'',0,0,'',:cover,:dateofbirthYear:dateofbirthMonth:dateofbirthDay,NOW(),:sex,:country)");
 $stmt->execute(array(
  ':username'   => $username,
  ':password' => $password,
  ':email'   => $email,
  ':cover' => $cover,
  ':dateofbirthYear'   => $dateofbirthYear,
  ':dateofbirthMonth' => $dateofbirthMonth,
  ':dateofbirthDay'   => $dateofbirthDay,
  ':sex' => $sex,
  ':country'   => $country 
    ));

由于某些原因，这个插入语句不工作。我是PDO的新手，所以我不太了解它。我做错了什么?

这个语句给了我这个错误:

致命错误:未捕获异常'PDOException'与消息'SQLSTATE[HY093]:无效参数数:绑定变量的数量不匹配令牌的数量'在/home/manga/public_html/new/register.php:80堆栈跟踪:
#0/home/漫画/public_html/new/register.php(80): PDOStatement->execute(Array)
#1 {main}在/home/manga/public_html/new/register.php第80行抛出

您以错误的方式准备了查询

INSERT INTO user VALUES ('',:username,md5(:password),'',1,'','',:email,'',0,0,'',
:cover,:dateofbirthYear:dateofbirthMonth:dateofbirthDay,NOW(),:sex,:country
     // ^ These need to either single or separated

对于你正在尝试的，你可以这样做

//Prepare the date of birth earlier
$dob = $dateofbirthYear.$dateofbirthMonth.$dateofbirthDay;
//Then pass it as a single $variable
$stmt = $conn->prepare("INSERT INTO user VALUES ('',:username,md5(:password),'',1,'','',:email,'',0,0,'',:cover,:dob,NOW(),:sex,:country)");
 $stmt->execute(array(
  ':username'   => $username,
  ':password' => $password,
  ':email'   => $email,
  ':cover' => $cover,
  ':dob'   => $dob, // <-- Problem solved
  ':sex' => $sex,
  ':country'   => $country 
    ));
 // Then it will execute

确切的错误信息是:

SQLSTATE[HY093]: Invalid parameter number:绑定变量个数与令牌个数不匹配

这意味着您传递的参数的数量/名称(execute中的array())与您在prepare() sql查询中的参数的数量/名称不匹配。

如果您将其与包含 SQLSTATE[HY093] 的其他问题进行比较，您将看到它通常与大型和不良格式的代码有关，难以阅读。这使得它难以计数。然后你有一些疏忽，然后错误发生了。

只要修复它就行了，例如，你不能在三个名称中使用一个参数:

,:dateofbirthYear:dateofbirthMonth:dateofbirthDay,

为生日传递一个参数:

, :dateofbirth,

你也可以让你的代码更容易读:

$stmt = $conn->prepare(
    "INSERT INTO user
     VALUES (
        '', :username, md5(:password), '', 1, '', '', :email, '', 0, 0, '',
        :cover, :dateofbirth, NOW(), :sex, :country
     )"
);
$stmt->execute(array(
    ':username'    => $username,
    ':password'    => $password,
    ':email'       => $email,
    ':cover'       => $cover,
    ':dateofbirth' => $dateofbirthYear . $dateofbirthMonth . $dateofbirthDay,
    ':sex'         => $sex,
    ':country'     => $country
));

然后你有一个密码哈希的安全问题:

md5(:password)

请参考PHP关于安全密码散列的常见问题解答

更正后的准备查询:

$stmt = $conn->prepare("INSERT INTO user VALUES ('',:username,md5(:password),'',1,'','',:email,'',0,0,'',:cover,:dateofbirthYear,:dateofbirthMonth:,dateofbirthDay,NOW(),:sex,:country)");
//:dateofbirthYear,:dateofbirthMonth:,dateofbirthDay place holders are seprated 
$stmt->execute(array(
 ':username'   => $username,
  ':password' => $password,
  ':email'   => $email,
  ':cover' => $cover,
  ':dateofbirthYear'   => $dateofbirthYear,
  ':dateofbirthMonth' => $dateofbirthMonth,
  ':dateofbirthDay'   => $dateofbirthDay,
  ':sex' => $sex,
  ':country'   => $country 
));