PDO MySQL case insensitive query is case sensitive AND SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry for key 'user_name'

Question

I noticed this problem for the first time when someone (earlier this week) was able to register two accounts by using the same email address (case sensitive). I've fixed that by adding strtolower when registering and, for time being, added LOWER for query (I think I should make all previously existing emails lowercase in the database so I don't need to use LOWER in query).

The part I'm having trouble with now (I've been trying to figure it for about 5 days now) is figuring out how to query case insensitive the existing usernames in the database and compare (again case insensitive) to new usernames during registration.

It's not working (and I thought the query was, by default, case insensitive but it appears that it's not?) because I keep getting this SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '[name entered in registration for here]' for key 'user_name'

That part seems, at least to me, straightforward, it's unique so I cant have two of the same usernames in the database.

The problem that I'm having is querying case insensitive or maybe it's the collation for usernames in the database (but I've tried dozens now. The only collation which seems to do what it's intended to is (though not what I need) latin1_bin which will allow case sensitive usernames (e.g., Duke, duke, DuKE) and if anyone else tries registering the exact same they wont be allowed (e.g., DuKE can't register if DuKE exists but can register duKe). That's great (I'll maybe use this sometime later for another of my cheesy sites, mostly hobby and book reading stuff) but for I now I need to be able to prevent a user for registering the same names with variations of uppercase and lowercase letters (e.g., if 'Duke' is registered then someone else attempting to register 'duke' will not be allowed to do so).

All of the above may appear to be a duplicate questions found elsewhere but I believe I've tried all the answers provided in similar questions (I've changed the code dozens of times to fit answers but none of them gives different results) . I have also tried changing collation (dozens of times) in the database for the usernames (currently it's utf8mb4_general_ci).

Each time I try the registration script it allows duplicate usernames (e.g. duke goes through even though Duke is already in the database) to pass through and then (which I believe is expected) I get the SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry.

If anyone here could provide some insight into what I'm doing wrong I'd very grateful! Thanks! Code is below:

<?php

require_once 'dbconfig.php';

if($user->is_loggedin()!="")
{
$user->redirect('home.php');
}

if(isset($_POST['btn-signup']))
{
$uname = trim($_POST['txt_uname']);
$umail = strtolower(trim($_POST['txt_umail']));
$upass = trim($_POST['txt_upass']); 

if($uname=="")  {
    $error[] = "provide username !";    
}
else if($umail=="") {
    $error[] = "provide email id !";    
}
else if(!filter_var($umail, FILTER_VALIDATE_EMAIL)) {
    $error[] = 'Please enter a valid email address !';
}
else if($upass=="") {
    $error[] = "provide password !";
}
else if(strlen($upass) < 6){
    $error[] = "Password must be atleast 6 characters"; 
}
else
{
    try
    {

        $stmt = $DB_con->prepare("SELECT user_name,user_email FROM users 
WHERE user_name = :uname OR LOWER(user_email) = LOWER(:umail)");
        $stmt->execute(array(':uname'=>$uname, ':umail'=>$umail));
        $row=$stmt->fetch(PDO::FETCH_ASSOC);

        if($row['user_name']==$uname) {
            $error[] = "sorry username already taken !";
        }
        else if($row['user_email']==$umail) {
            $error[] = "sorry email id already taken !";
        }
        else
        {
            if($user->register($fname,$lname,$uname,$umail,$upass)) {

                $user->redirect('sign-up.php?joined');
            }
        }
    }
    catch(PDOException $e)
    {
        echo $e->getMessage();
    }
}   
}

?>

Answer 1

Try this code

$stmt = $DB_con->prepare("SELECT user_name,user_email FROM users WHERE user_name LIKE :uname OR user_email LIKE :umail ");

$stmt->execute([':uname' => $uname, ':umail' => $umail]);

$found = false;
$inRecord = [];

while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
    $__uname = $row['user_name'];
    $__umail = $row['user_email'];

    if ($__uname == $uname || strtolower($__uname) == strtolower($uname))
    {
        $inRecord['user_name'] = true;
    }

    if ($__umail == $umail || strtolower($__umail) == strtolower($umail))
    {
        $inRecord['user_email'] = true;
    }

    if (count($inRecord) > 0)
    {
        $found = true;
        break;
    }
}

if ($found === true)
{
    if (isset($inRecord['user_name']))
    {
        echo "Username exists!";
    }

    if (isset($inRecord['user_email']))
    {
        echo "Email exists!";
    }
}
else
{
    // register here...
}

Will Output Only if a match is found.

Username exists! Email exists!