php - HY :093 Invalid parameter number: parameter was not defined

Question

我不明白为什么会出现这个错误

if (isset($_POST['submit'])) {
    require "../config.php";
    require "../common.php";
    try  {
        $connection = new PDO($dsn, $username, $password, $options);

        $new_user = array(
            "Nom"  => $_POST['Nom'],
            "EmailID"     => $_POST['EmailID'],

            "localité"  => $_POST['localité']
        );
        $sql = sprintf(
                "INSERT INTO %s (%s) values (%s)",
                "memfis",
                implode(", ", array_keys($new_user)),
                ":" . implode(", :", array_keys($new_user))
        );

        $statement = $connection->prepare($sql);
        $statement->execute($new_user);
    } catch(PDOException $error) {
        echo $sql . "<br>" . $error->getMessage();
    }
}
?>

<?php require "templates/header.php"; ?>

<?php if (isset($_POST['submit']) && $statement) { ?>
    <blockquote><?php echo $_POST['name']; ?> successfully added.</blockquote>
<?php } ?>

Should Only 添加了用户并显示“'Nom' successfully added”并且 Nom 应该和其他字段应该已经添加。

Answer 1

为什么查询构建如此复杂？可读性和可维护性通常比花哨的更好。

$sql = sprintf(
        "INSERT INTO %s (%s) values (%s)",
        "memfis",
        implode(", ", array_keys($new_user)),
        ":" . implode(", :", array_keys($new_user))
);

也许尝试将其更改为:

$table = "memfis";
$columnNames = implode(", ", array_keys($new_user));
$columnPlaceholders = ":" . implode(", :", array_keys($new_user));
$sql = "INSERT INTO $table ($columnNames) VALUES ($columnPlaceholders)";

$stmt = $pdo->prepare($sql);

然后因为您的查询将包含诸如 :Nom 之类的内容作为占位符，您需要使用 bindParam()。

foreach (array_keys($new_user) as $key) {
    $placeholder = ":" . $key;
    $stmt->bindParam($placeholder, $new_user[$key]);
}

$stmt->execute();

我知道代码更长，但恕我直言，它比您拥有的代码更容易阅读和维护。