php 使用 PDO 和 MySQL 更新查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18323065/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update query with PDO and MySQL
提问by Liam
Im trying to write an update query with PDO only I cant get my code to execute?
我试图用 PDO 编写更新查询，但我无法执行我的代码？
try {
$conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
$conn->exec("SET CHARACTER SET utf8"); // Sets encoding UTF-8
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "UPDATE `access_users`
(`contact_first_name`,`contact_surname`,`contact_email`,`telephone`)
VALUES (:firstname, :surname, :telephone, :email);
";
$statement = $conn->prepare($sql);
$statement->bindValue(":firstname", $firstname);
$statement->bindValue(":surname", $surname);
$statement->bindValue(":telephone", $telephone);
$statement->bindValue(":email", $email);
$count = $statement->execute();
$conn = null; // Disconnect
}
catch(PDOException $e) {
echo $e->getMessage();
}
回答by peterm
- Your
UPDATEsyntax is wrong - You probably meant to update a row not all of them so you have to use
WHEREclause to target your specific row
- 你的
UPDATE语法错误 - 您可能打算更新一行而不是全部，因此您必须使用
WHERE子句来定位您的特定行
Change
改变
UPDATE `access_users`
(`contact_first_name`,`contact_surname`,`contact_email`,`telephone`)
VALUES (:firstname, :surname, :telephone, :email)
to
到
UPDATE `access_users`
SET `contact_first_name` = :firstname,
`contact_surname` = :surname,
`contact_email` = :email,
`telephone` = :telephone
WHERE `user_id` = :user_id -- you probably have some sort of id
回答by Bill Karwin
This has nothing to do with using PDO, it's just that you are confusing INSERT and UPDATE.
这与使用 PDO 无关，只是您混淆了 INSERT 和 UPDATE。
Here's the difference:
这是区别：
INSERTcreates a new row. I'm guessing that you really want to create a new row.UPDATEchanges the values in an existing row, but if this is what you're doing you probably should use a WHERE clause to restrict the change to a specific row, because the default is that it applies to everyrow.
INSERT创建一个新行。我猜你真的想创建一个新行。UPDATE更改现有行中的值，但如果这是您正在执行的操作，您可能应该使用 WHERE 子句将更改限制为特定行，因为默认情况下它适用于每一行。
So this will probably do what you want:
所以这可能会做你想要的：
$sql = "INSERT INTO `access_users`
(`contact_first_name`,`contact_surname`,`contact_email`,`telephone`)
VALUES (:firstname, :surname, :email, :telephone);
";
Note that I've also changed the order of columns; the order of your columns must match the order of values in your VALUES clause.
请注意，我还更改了列的顺序；列的顺序必须与 VALUES 子句中的值顺序匹配。
MySQL also supports an alternative syntax for INSERT:
MySQL 还支持 INSERT 的替代语法：
$sql = "INSERT INTO `access_users`
SET `contact_first_name` = :firstname,
`contact_surname` = :surname,
`contact_email` = :email,
`telephone` = :telephone
";
This alternative syntax looks a bit more like an UPDATE statement, but it creates a new row like INSERT. The advantage is that it's easier to match up the columns to the correct parameters.
这种替代语法看起来有点像 UPDATE 语句，但它创建了一个新行，如 INSERT。优点是更容易将列与正确的参数匹配。
回答by vee
Your update syntax is incorrect. Please check Update Syntaxfor the correct syntax.
您的更新语法不正确。请检查更新语法以获取正确的语法。
$sql = "UPDATE `access_users` set `contact_first_name` = :firstname, `contact_surname` = :surname, `contact_email` = :email, `telephone` = :telephone";
