I'm trying to create an email confirmation script.
Here is my PHP code:
$q = $dbh->prepare("INSERT INTO `email_confirm` (UserID,token,tokenDate) VALUES(:id, :token, UTC_TIMESTAMP()) ON DUPLICATE KEY UPDATE token = VALUES(:token), tokenDate = UTC_TIMESTAMP();");
$result = $q -> execute( array( ":id" => $this->id, ":token" => $token ) );
Caught exception: SQLSTATE: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?), tokenDate = UTC_TIMESTAMP()' at line 1
As documented under PDO::prepare:
You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.
Whilst you could add a
:token2 placeholder or similar that happens to be bound to the same value, actually MySQL's
VALUES() function in the
ON DUPLICATE KEY UPDATE clause takes a column name not a literal. Therefore this will do the trick:
$q = $dbh->prepare(' INSERT INTO email_confirm (UserID, token, tokenDate) VALUES (:id, :token, UTC_TIMESTAMP()) ON DUPLICATE KEY UPDATE token = VALUES(token), tokenDate = UTC_TIMESTAMP() ');
However, you may want to look into Automatic Initialization and Updating for TIMESTAMP and DATETIME, rather than trying to reimplement the wheel.