How do I fix this code to unique username / email registration

Question

this codes adds username and email to MYSQL database successfully but it accepts the SAME USER NAME and the SAME EMAIL if they are submitted in Upper case / lower case / or comobo of both ? what do I need to do to avoid adding the same USERNAME OR EMAIL all together ?

Note if I use for example USERNAME: moenagy18 I can also sign up with MoeNAGY18

this is the code in my register.php file.

//if logged in redirect to members page
if( $user->is_logged_in() ){ header('Location: memberpage.php'); exit(); }

//if form has been submitted process it
if(isset($_POST['submit'])){

if (!isset($_POST['username'])) $error[] = "Please fill out all fields";
if (!isset($_POST['email'])) $error[] = "Please fill out all fields";
if (!isset($_POST['password'])) $error[] = "Please fill out all fields";

$username = $_POST['username'];

//very basic validation
if(!$user->isValidUsername($username)) {
$error[] = 'Usernames must be at least 3 Alphanumeric characters';
} else {
$stmt = $db->prepare('SELECT username FROM members WHERE username = :username');
$stmt->execute(array(':username' => $username));
$row = $stmt->fetch(PDO::FETCH_ASSOC);

if(!empty($row['username'])){
$error[] = 'Username provided is already in use.';
}

}

if(strlen($_POST['password']) < 3){
$error[] = 'Password is too short.';
}

if(strlen($_POST['passwordConfirm']) < 3){
$error[] = 'Confirm password is too short.';
}

if($_POST['password'] != $_POST['passwordConfirm']){
$error[] = 'Passwords do not match.';
}

//email validation
$email = htmlspecialchars_decode($_POST['email'], ENT_QUOTES);
if(!filter_var($email, FILTER_VALIDATE_EMAIL)){
$error[] = 'Please enter a valid email address';
} else {
$stmt = $db->prepare('SELECT email FROM members WHERE email = :email');
$stmt->execute(array(':email' => $email));
$row = $stmt->fetch(PDO::FETCH_ASSOC);

if(!empty($row['email'])){
$error[] = 'Email provided is already in use.';
}

}


//if no errors have been created carry on
if(!isset($error)){

//hash the password
$hashedpassword = $user->password_hash($_POST['password'], PASSWORD_BCRYPT);

//create the activasion code
$activasion = md5(uniqid(rand(),true));

try {

//insert into database with a prepared statement
$stmt = $db->prepare('INSERT INTO members (username,password,email,active) VALUES (:username, :password, :email, :active)');
$stmt->execute(array(
':username' => $username,
':password' => $hashedpassword,
':email' => $email,
':active' => 'Yes' 
));
$id = $db->lastInsertId('memberID');

//send email
$to = $_POST['email'];
$subject = "Registration Confirmation";
$body = "<p>Thank you for registering at demo site.</p>
<p>To activate your account, please click on this link: <a href='".DIR."activate.php?x=$id&y=$activasion'>".DIR."activate.php?x=$id&y=$activasion</a></p>
<p>Regards Site Admin</p>";

$mail = new Mail();
$mail->setFrom(SITEEMAIL);
$mail->addAddress($to);
$mail->subject($subject);
$mail->body($body);
$mail->send();

//redirect to index page
header('Location: index.php?action=joined');
exit;

//else catch the exception and show the error.
} catch(PDOException $e) {
$error[] = $e->getMessage();
}

}

}

Answer 1

Use BINARY in the query and it will do a byte by byte comparison this will check for an exact match(it will check if it is case sensitive).

 $stmt = $db->prepare('SELECT username FROM members WHERE BINARY username = :username');

Another method is using php strcmp(). Example: This compares 2 strings. If the same will = 0.

if(strcmp($dataA, $dataB) !== 0){$response = "Username or Password are Invalid!";
echo $response;
exit();}