PHP do/while Prepared Statement failing

I have a code that is meant to run a check on my database to see if a given value is unique, and if so, insert it into the db. If it isn't unique, reiterate the process until it finds a unique value.

do {
    // Generate a new user ID (15 numbers) and check to make sure it doesn't already exist.
    $new_user_id = mt_rand(1000000000, 9999999999);

    // Generate a fake Login Email address and check to make sure it doesn't already exist.
    $new_username = rand_str(13, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890');
    $new_username_email = 'BP' . $new_username . '@web.com';

    // Generate a new fake password
    $new_p开发者_如何学运维assword = rand_str(15, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890!@#$%^&*()');

    // Check to make sure the extremely random Username and user ID doesn't already exist in the database
    // On the off chance that it does, we need to regenerate and try again.

    $preparedStatement = $connection->prepare("SELECT user_id, username FROM `{$mysql_table}` WHERE user_id = :new_user_id OR username = :new_username");
    $preparedStatement->execute(array(':new_user_id' => $new_user_id, ':new_username' => $new_username_email));
    $result_2 = $preparedStatement->fetchAll();

    //TODO Not sure if this is actually working if there is a duplicate entry
} while (!empty($result_2));

// Once it is unique, insert the values into the database

$preparedStatement = $connection->prepare(
    "INSERT INTO `{$mysql_table}` (
        open_id, 
        user_id, 
        username, 
        password
    ) VALUES (
        :open_id_value, 
        :user_id_value, 
        :username_value, 
        :password_value
    )");

    if (!$preparedStatement->execute(array(
        ':open_id_value' => $_SESSION['user'], 
        ':user_id_value' => $new_user_id, 
        ':username_value' => $new_username_email, 
        ':password_value' => $new_password
    ))) {
        $arr = $preparedStatement->errorInfo();
                    die(print_r($arr));                             
    } else {
    // Send the new user to the account settings page, where they can set up their account information
        $_SESSION['new_user'] = 1;
        //echo 'You are a new user!';
        header("Location: /account-settings/");
        exit;                       
    }

The problem that I'm getting is that the value generated by mt_rand says that it is a duplicate.

Array ( [0] => 23000 [1] => 1062 [2] => Duplicate entry '2147483647' for key 1 ) 1

Firstly, I don't know why I'd be getting a duplicate error from this code - what is wrong about it? Secondly, on the chance I DO get a duplicate, it is supposed to regenerate and try again until it works - but that is obviously not happening.

Any clues?

---

To elaborate on my comment...Your script generated a random number that will ~80% of the time be above the max of an int. Your query to test if the user_id had already been taken would return false (mysql allows you to query outside the range limit of a column, it just says there's no record with that id), but then the insert query would fail since the number would be reduced to the maximum INT size. To fix this you switch to BIGINT or limit your random range.

---

Try changing your ID column to BIGINT vs INT as it appears your are trying to generate random number above INT types capacity. This is why it's dropping it down to 2147483647 which is INT max number.

---

Limit mt_rand(1000000000,2147483647); An int data type cannot handle more than that.

---

It would altogether be better to use an auto_increment field, so you could just insert each record as long as the user name would be unique. You can retreive the id of the last inserted record by using mysql_insert_id() if you really need it.

If you really want the numbers to appear random, you can use some encription (like xor, or a specific bit shuffle) to generate another number which you can always calculate back to the real userid which is stored in the database.