My code below (must be something to do with sql statement (the UPDATE query statement), basically when I go in browser and visit script with a key that i know exists in the database I get the following error:
[15/04/2012 18:33:57] - exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '' for key 'user_activation_key'' in C:\wamp\www\user-verify.php:53
Stack trace:
#0 C:\wamp\www\user-verify.php(53): PDOStatement->execute(Array)
#1 {main}
Here is my code:, not sure what it saying about duplicate entry, as the user_activation_key column is unique and yes i am using InnoDB and foreign keys for data interigty.
// check if key is set and alphanumeric and equals 40 chars long
// we use sha1 so it will always be 40 chars long.
if(isset($_GET['key']) && ctype_alnum($_GET['key']) && strlen($_GET['key']) == 40){
$key = trim($_GET['key']);
}
// if key isset
if(isset($key)){
try {
// connect to database
$dbh = sql_con();
// if key is of valid length and type we need to update the `user_activation_key` in the `users_status` table to NULL
// and update the `user_status`in the `users` table to 1 (tinyint)(active) based on the condition that the
// activation key can be found in the users_status.user_activation_key column and user_uid match in both users_status and users table
$stmt = $dbh->prepare("
UPDATE
users
JOIN
users_status
ON
users_status.user_activation_key = ?
SET
users.user_status = 1,
users_status.user_activation_key = NULL
WHERE
users_status.user_uid = users.user_uid");
// execute query
$stmt->execute(array($key));
if ( $stmt->rowCount() > 0 ) {
echo 'account now activated';
exit;
} else {
echo 'could not activate account at this time';
exit;
}
// close database connection
$dbh = null;
} // if any errors found log them and display friendly message
catch (PDOException $e) {
ExceptionErrorHandler($e);
require_once($footer_inc);
exit;
}
} else {
// else key not valid or set
echo '<h1>Invalid Activation Link</h1>';
$SiteErrorMessages =
"Oops! Your account could not be activated. Please recheck the link in your email.
The activation link appears to be invalid.<br /><br />
If the problem persists please request a new one <a href='/member/resend-activation-email'>here</a>.";
SiteErrorMessages();
include($footer_inc);
exit;
}
Not sure why I am getting that error, any know what it means exactly ?
It won't perform the update even though the key exists in the users_status table. If I enter an invalid key it says could not activate account at this time which is what it should do but when the key is valid it should update but it's outputting the error above.
UPDATE:
Here is the the database design for those two tables.
CREATE TABLE `users` (
`user_uid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'users unique id',
`user_status` tinyint(1) unsigned NOT NULL COMMENT '0 = verify | 1 = active | 2 = suspended | 3 = delete | 4 = spam |',
`user_login` varchar(15) NOT NULL COMMENT 'users login username',
`user_pass` char(152) NOT NULL,
`user_email` varchar(255) NOT NULL COMMENT 'users email',
`user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'user registration date',
`user_display_name` varchar(60) NOT NULL COMMENT 'users display name (first & last name)',
`user_failed_logins` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'failed login attempts',
PRIMARY KEY (`user_uid`),
UNIQUE KEY `user_login` (`user_login`),
UNIQUE KEY `user_email` (`user_email`),
KEY `user_pass` (`user_pass`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=Users Table';
CREATE TABLE `users_status` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'auto generated id',
`user_uid` int(10) unsigned NOT NULL,
`user_activation_key` char(40) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_uid` (`user_uid`),
UNIQUE KEY `user_activation_key` (`user_activation_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='user status table, when a user registers they must first activate there account';
ALTER TABLE `users_status`
ADD CONSTRAINT `FK_user_status` FOREIGN KEY (`user_uid`) REFERENCES `users` (`user_uid`) ON DELETE CASCADE ON UPDATE CASCADE;
