Insert into MySQL table Primary Key Constraint Violation

Question

I'm trying to generate a unique username that is not already in my database and then add it as a primary key into my InnoDB database table plus some other field entries.

I get the error code:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'ft6888' for key 'PRIMARY'

Checking the database table manually I can see that it does not already contain the value I'm passing and by echoing the username values I'm binding I can see each is only bound once. The field is not set to auto_increment as in some of these questions but it used as a foreign key in some other tables (but the values I'm binding don't exist in those either).

If I echo out the variables I'm binding just before I bind them I get two sets of correct data. When I insert this same data (copy and pasted) into the table using phpmyadmin it works fine no errors. I can only assume my code itself is somehow trying to insert twice?

$query = "INSERT INTO user_login (username, usertype, hashedpassword) VALUES";
$qPart = array_fill(0, count($excelData), "(?, ?, ?)");
$query .=  implode(",",$qPart);
$sth = $dbh->prepare($query); 
$i = 1;

$sql = "SELECT username FROM user_login WHERE username = :username";
$sthUser = $dbh->prepare($sql);

Foreach($excelData As $Row){
    Do{
        //Create unique userID
        $finitial = substr(addslashes(str_replace(" ","",$Row['0']['2'])),0,1);
        $sinitial = substr(addslashes(str_replace(" ","",$Row['0']['3'])),0,1);
        $username = strtolower($finitial).strtolower($sinitial).rand(999,9999);

        try {
             $sthUser->bindParam(':username', $username);
             $sthUser->execute();
             $Row = $sthUser->fetch(PDO::FETCH_ASSOC);
         }catch (PDOException $e) {
             print $e->getMessage();
                                }
         }while(!empty($Row));

         $hashedPassword = create_hash($Row['0']['1']);
         $usertype = 'Student';
         $sth->bindParam($i++, $username);
         $sth->bindParam($i++, $usertype);
         $sth->bindParam($i++, $hashedPassword);

    }
    try {
          $sth->execute();
    }catch (PDOException $e) {
          print $e->getMessage();
    }

Could you explain further? As far as I can see my execute statement is outside both loops and the random number username should be prevented from holding the same value twice by the do..while loop? — baarkerlounger, Feb 02 '14 at 15:01
That should just execute the SELECT query checking if the username already exists - it shouldn't add anything to the table? — baarkerlounger, Feb 02 '14 at 15:13
If I echo out the variables I'm binding just before I bind them I get two sets of correct data. When I insert this same data (copy and pasted) into the table using phpmyadmin it works fine no errors — baarkerlounger, Feb 03 '14 at 14:31
possible duplicate of [Mysql/PDO Integrity Violation Duplicate Primary - although nothing already in table](http://stackoverflow.com/questions/19859460/mysql-pdo-integrity-violation-duplicate-primary-although-nothing-already-in-ta) — Kermit, Feb 03 '14 at 16:06

score 0 · Accepted Answer · edited May 23 '17 at 12:03

0

Found the answer here - It seems that bindParam inside the loop binds by reference and is only evaluated at execute statement so it always contains the last bound value for each field.

Changing it to bindValue worked.

edited May 23 '17 at 12:03

Community

1
1

answered Feb 03 '14 at 15:58

baarkerlounger

1,217
8
40
57

Insert into MySQL table Primary Key Constraint Violation

1 Answers1