使用PDO和MySQL更新查询

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();
}

3 个解决方案

#1

Your UPDATE syntax is wrong
您的UPDATE语法错误
You probably meant to update a row not all of them so you have to use WHERE clause to target your specific row
您可能想要更新一行而不是所有行，因此您必须使用WHERE子句来定位您的特定行

Change

更改

UPDATE `access_users`   
      (`contact_first_name`,`contact_surname`,`contact_email`,`telephone`) 
      VALUES (:firstname, :surname, :telephone, :email)

至

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

#2

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:

这是区别：

INSERT creates a new row. I'm guessing that you really want to create a new row.
INSERT创建一个新行。我猜你真的想要创建一个新行。
UPDATE changes 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 every row.
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这样的新行。优点是可以更容易地将列与正确的参数进行匹配。

#3

Your update syntax is incorrect. Please check Update Syntax for the correct syntax.

您的更新语法不正确。请检查更新语法以获取正确的语法。

$sql = "UPDATE `access_users` set `contact_first_name` = :firstname,  `contact_surname` = :surname, `contact_email` = :email, `telephone` = :telephone";

#1

Your UPDATE syntax is wrong
您的UPDATE语法错误
You probably meant to update a row not all of them so you have to use WHERE clause to target your specific row
您可能想要更新一行而不是所有行，因此您必须使用WHERE子句来定位您的特定行