Page 1 of 1

Insert into MySQL table Primary Key Constraint Violation

Posted: Mon Feb 03, 2014 9:11 am
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();
    }

Re: Insert into MySQL table Primary Key Constraint Violation

Posted: Mon Feb 03, 2014 2:30 pm
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).

Re: Insert into MySQL table Primary Key Constraint Violation

Posted: Tue Feb 04, 2014 9:15 am
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.

Re: Insert into MySQL table Primary Key Constraint Violation

Posted: Tue Feb 04, 2014 4:01 pm
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') ...

Re: Insert into MySQL table Primary Key Constraint Violation

Posted: Wed Feb 05, 2014 7:50 am
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();
}

Re: Insert into MySQL table Primary Key Constraint Violation

Posted: Wed Feb 05, 2014 5:24 pm
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.