Custom PHP script PDO is throwing exception 23000,1062 duplicate entry

Question

I am working on a PHP script where I am using PDO to insert data in mySQL. I am getting an error "23000",1062,"Duplicate entry 'email@email.com-username' for key 'email' but its inserting the data in database.

So here is my PHP codes:

if(isset($_POST['email'])){
            $this->db = new connect();
            $this->db = $this->db->dbConnect();
            $this->encryption = new Encryption();

            isset($_POST['timezone']) AND $_POST['timezone'] != 'null' ? date_default_timezone_set($_POST['timezone']): date_default_timezone_set('America/Chicago');

            $this->email        = $_POST['email'];

            $this->username     = $_POST['username'];

            $this->password     = $this->encryption->encode($_POST['password']);

            $this->dTime        = date("Y-m-d H:i:s");;

            $this->sessionKey   = $_POST['key'];

            $this->country      = $_POST['country'];

            $this->region       = $_POST['uregion'];

            $this->browser      = $_POST['browser'];

            $this->ip           = $_POST['accessFrom'];

            $regMessage = array('error'=>false);

            try{

            $query = "INSERT INTO `users` (

                        id, email, uname, password, regtime, sessionkey, country, region, browser, ip

                        ) VALUES (

                        (SELECT MAX(id) + 1 FROM `users` AS `maxId`), :email, :uname, :password, :regtime, :sessionkey, :country, :region, :browser, :ip

                        )";

            $register = $this->db->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));


            if($this->sessionKey === $_SESSION['token']){

                $register->bindParam(':uname', $this->username);     
                $register->bindParam(':email', $this->email);   
                $register->bindParam(':password', $this->password); 
                $register->bindParam(':regtime', $this->dTime); 
                $register->bindParam(':sessionkey', $this->sessionKey); 
                $register->bindParam(':country', $this->country);
                $register->bindParam(':region', $this->region); 
                $register->bindParam(':browser', $this->browser); 
                $register->bindParam(':ip', $this->ip);
                $register->execute();       

                    if($register->rowCount() > 0){

                        $regMessage = array('error'=>false);

                    }else{
                        $regMessage = array('error'=>true);
                    }                   

                }else{

                    throw new PDOException ('Error');

                    }

            }

            catch(PDOException $e){



//this is where I am getting error so I am echoing pdo exception error 

 $regMessage = array('error'=>$e);



            }

            header('Content-Type: application/json');

            echo json_encode($regMessage);

        }else{

            header('Location: /');
        }

At the error, it is showing me duplicate entry of emailid + username for key email which looks like email@email.com-username

But in data base, I am getting email id only in email column and username only in username column. So can any one tell me whats wrong in my codes?

My users table structure is

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(250) CHARACTER SET utf8 NOT NULL,
  `uname` varchar(20) CHARACTER SET utf8 NOT NULL,
  `password` varchar(100) CHARACTER SET utf8 NOT NULL,
  `regtime` datetime NOT NULL,
  `sessionkey` varchar(10) CHARACTER SET utf8 NOT NULL,
  `country` varchar(25) CHARACTER SET utf8 NOT NULL,
  `region` varchar(25) CHARACTER SET utf8 NOT NULL,
  `browser` varchar(25) CHARACTER SET utf8 NOT NULL,
  `ip` varchar(16) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`,`uname`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

So can anyone tell me where and what is wrong?

Thank you for helping me.

Answer 1

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `uname` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `password` varchar(100) CHARACTER SET utf8 NOT NULL,
  `regtime` datetime NOT NULL,
  `sessionkey` varchar(10) CHARACTER SET utf8 NOT NULL,
  `country` varchar(25) CHARACTER SET utf8 NOT NULL,
  `region` varchar(25) CHARACTER SET utf8 NOT NULL,
  `browser` varchar(25) CHARACTER SET utf8 NOT NULL,
  `ip` varchar(16) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

this is the solution.

Answer 2

The phrasing in the error message: 'email@email.com-username' for key 'email' directly corresponds to your unique key UNIQUE KEY 'email' ('email','uname'). With that line, you are creating a compound key, which you can think of as an invisible column in the index that is comprised of email-uname. There will not be a column added to your table with this format, and you are seeing the expected behavior that email and uname are treated separately in the table and together for the key.

If you want to test over and over again with the same email and username combo, you'll need to delete that row every time. Without doing this, the error you are seeing is exactly what I would expect to see if you are POST-ing the same data over and over again.

I want to also mention that you have (appropriately) specified that your id column is AUTO_INCREMENT, but then you are calculating the value manually. I would like to discourage you from doing this, and instead use NULL as the insert value. MySQL will use the correct key value in this column, and you will avoid the potential for key collision if you ever had two of these things executing at the same exact moment.