MySQL PDO Duplicate exception

Started by autorenta, Sep 12, 2022, 01:23 AM

Previous topic - Next topic

autorentaTopic starter

I am struggling to understand why the system throws a duplicate exception when a player attempts to register with an alternative nickname that has the same spelling but different letter case. For instance, the nicknames "alternative" and "AlTeRnAtiVe" are not duplicates if the letter case is taken into account, but PDO identifies them as duplicates and refers to them as ":Login." Despite having a unique identifier UserID, the error persists even though there are no issues with mysql_query.

Here is a sample of the database's structure:
                    $sql_insert = $this->engine->getPDO()->prepare('insert into Users (Login,Email,Password,DataRegister,RegisterIP,StatusActiveProfile) values(:Login,:Email,:Password,:DataRegister,:RegisterIP,:status)');
                    $status = "0";



The error being thrown is not from PDO, but rather from MySQL. From the error message, it seems that there is an index on the Login column and it is marked as UNIQUE. However, because the mapping is case-insensitive (as indicated by the _ci at the end of the name), variations in letter case are not being recognized as unique. To resolve this issue, it may be necessary to switch to a case-sensitive or binary mapping. This setting can be adjusted in the field settings within phpMyAdmin by selecting a mapping that ends in _cs or _bin.

It is worth noting that while no exception is being thrown in this scenario, it is still important to ensure that the data being stored in the database is accurate and unique. Failing to address this issue could lead to further problems down the line.


The system currently identifies the nicknames "alternative" and "AlTeRnAtiVe" as duplicates because it does not take into account the letter case.

To address this issue, you can modify the registration process to enforce case sensitivity for the nickname field. This way, the system will recognize "alternative" and "AlTeRnAtiVe" as unique nicknames.

One approach could be to convert all nicknames to a consistent letter case (e.g., lowercase) before storing them in the database. You can use functions like strtolower() in PHP to achieve this. Then, when checking for duplicate nicknames, you can compare the lowercase versions of the nicknames to ensure uniqueness.

Here's an example modification to the code:

// CreateUser

$lowercaseLogin = strtolower($array
  • );
// Check if the lowercase login already exists in the database
$sql_check_duplicate = $this->engine->getPDO()->prepare('SELECT * FROM Users WHERE LOWER(Login) = :login');
$sql_check_duplicate->bindParam(':login', $lowercaseLogin, PDO::PARAM_STR);

if ($sql_check_duplicate->rowCount() > 0) {
    // Handle duplicate nickname error
} else {
    // Create user in the database
    $status = "0";
    $sql_insert = $this->engine->getPDO()->prepare('INSERT INTO Users (Login,Email,Password,DataRegister,RegisterIP,StatusActiveProfile) VALUES(:Login,:Email,:Password,:DataRegister,:RegisterIP,:status)');
    $sql_insert->bindValue(":Login", $array
  • , PDO::PARAM_STR); // Store original nickname
    $sql_insert->bindValue(":Email", $array[3], PDO::PARAM_STR);
    $sql_insert->bindValue(":Password", $hashedPassword, PDO::PARAM_STR);
    $sql_insert->bindValue(":DataRegister", $dataTime, PDO::PARAM_STR);
    $sql_insert->bindValue(":RegisterIP", $registerIp, PDO::PARAM_STR);
    $sql_insert->bindValue(":status", $status, PDO::PARAM_INT);

    // Commit transaction and complete user creation

By converting the nickname to lowercase and performing a case-insensitive check for duplicates, you should be able to resolve the issue with duplicate exceptions while still maintaining the unique identifier UserID.