Insert into MySQL table Primary Key Constraint Violation

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
db57
Forum Newbie
Posts: 1
Joined: Mon Feb 03, 2014 9:08 am

Insert into MySQL table Primary Key Constraint Violation

Post by db57 »

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:

Code: Select all

  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?

Code: Select all

 
    $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();
    }
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Insert into MySQL table Primary Key Constraint Violation

Post by requinix »

You don't reset $i=1 inside the loop. It'll bind 1,2,3 on the first pass, then 4,5,6 on the second, 7,8,9 on the third... The username you're executing the statement with never changes (nor does the usertype or hashedpassword).
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Insert into MySQL table Primary Key Constraint Violation

Post by Weirdan »

@req, the topic starter used some confusing formatting. I took a liberty to reformat the code. The username obviously changes in do..while.

@db57, your code overwrites $Row var in the inner loop, however it seems you're assuming it does not. After first iteration of do..while $Row no longer contains associative array.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Insert into MySQL table Primary Key Constraint Violation

Post by requinix »

Weirdan wrote:@req, the topic starter used some confusing formatting. I took a liberty to reformat the code. The username obviously changes in do..while.
Okay, I see that $i is supposed to work the way it does.

Binding works by-reference, not by-value. When you bind $username (and the other two variables) you bind the variable, which means that the value used with the query is that of the variable at the time of execution. You can change $username all you want, it won't matter until you execute the query - at which point you'll be repeating the value of $username howevermany times in the query.
Basically as if you did

Code: Select all

"INSERT INTO user_login (username, usertype, hashedpassword) VALUES ('$username', '$usertype', '$hashedPassword'), ('$username', '$usertype', '$hashedPassword'), ('$username', '$usertype', '$hashedPassword') ...
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Insert into MySQL table Primary Key Constraint Violation

Post by Weirdan »

Binding works by-reference, not by-value.
Good point.

@db57, I'd suggest you to do one by one inserts instead of trying to construct a huge batch insert. If you wrap it into transaction (assuming you use a transactional storage engine like InnoDB) it will be as concurrency-safe as a single insert and just marginally slower. Something like this:

Code: Select all

function usernameExists($con, $username) {
    $sql = "SELECT count(*) FROM user_login WHERE username = :username";
    $sthUser = $con->prepare($sql);

    $sthUser->bindParam(':username', $username);
    $sthUser->execute();
    return $sthUser->fetchColumn() > 0;
}

function generateUsername($firstName, $lastName) {
    $finitial = substr(addslashes(str_replace(" ","",$firstName)), 0, 1);
    $sinitial = substr(addslashes(str_replace(" ","",$lastName)), 0, 1);
    $username = strtolower($finitial) . strtolower($sinitial) . rand(999, 9999);
}

$dbh->beginTransaction(); 
$query = "INSERT INTO user_login (username, usertype, hashedpassword) VALUES (?, ?, ?)";
$sth = $dbh->prepare($query);

try {
    foreach($excelData As $Row) {
        do {
            $username = generateUsername($Row['0']['2'], $Row['0']['3'])
        } while(usernameExists($dbh, $username));

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

    }
    $dbh->commit();
} catch (PDOException $e) {
    $dbh->rollBack();
    print $e->getMessage();
}
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Insert into MySQL table Primary Key Constraint Violation

Post by requinix »

Weirdan wrote:@db57, I'd suggest you to do one by one inserts instead of trying to construct a huge batch insert.
Totally. Using prepared statements for it already gives a pretty good performance boost, and I'm sure that it is at least as fast as running one giant INSERT with a lot of values.
Post Reply