Update query with PDO and MySQL

Question

Im trying to write an update query with PDO only I cant get my code to execute?

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

The query is wrong. You're using INSERT like syntax. Have a look at the syntax for update here: http://dev.mysql.com/doc/refman/5.0/en/update.html — vee, Aug 19 '13 at 21:03

score 64 · Accepted Answer · edited Nov 13 '14 at 19:15

64

Your UPDATE syntax is wrong
You probably meant to update a row not all of them so you have to use WHERE clause to target your specific row

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

edited Nov 13 '14 at 19:15

danronmoon

3,814
5
34
56

answered Aug 19 '13 at 21:09

peterm

91,357
15
148
157

1

This is a great and simple answer. Separately, how did you auto-format that SQL so cleanly? – SWL Oct 07 '14 at 15:22
@SWL Thanks. The formatting is done manually in this case, although some IDEs allow you to set formatting rules and perform the auto-formatting for you. – peterm Oct 07 '14 at 15:38
can i use this ``UPDATE `access_users` SET `contact_first_name` = :firstname, `contact_surname` = :surname, `contact_email` = :email, `telephone` = :telephone WHERE `user_name` = :user_name`` user name is in session – sanoj lawrence Apr 25 '15 at 23:19

score 5 · Answer 2 · answered Aug 19 '13 at 21:09

This has nothing to do with using PDO, it's just that you are confusing INSERT and UPDATE.

Here's the difference:

INSERT creates a new row. I'm guessing that you really want to create a new row.
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.

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.

MySQL also supports an alternative syntax for 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.

score 3 · Answer 3 · answered Aug 19 '13 at 21:08

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";

answered Aug 19 '13 at 21:08

vee

38,255
7
74
78

Update query with PDO and MySQL

3 Answers3

Linked

Related