Trying to access array offset on value of type bool

Question

$query = $pdo -> prepare("SELECT * FROM Users WHERE Username =:Username");
$query->bindParam(':Username', $name);
$query->execute();

$nameRes = $query->fetch(PDO::FETCH_ASSOC);
if ($nameRes['Username']==$_POST['username']) {
    die ("Username is already in use!");
}   

$query = $pdo -> prepare("SELECT * FROM Users WHERE Email =:Email");
$query->bindParam(':Email', $email);
$query ->execute();
$emailRes = $query->fetch(PDO::FETCH_ASSOC);

if ($emailRes['Email']==$_POST['email']) {
    die ("Email is already in use!");
}

I have this code on the registration page of my app and when Username is free to use but email is not and vice versa I get this

Notice: Trying to access array offset on value of type bool

Ok the result is returning false but what to do in this situation? Note: This is on php v7.4 this same thing was working on v7.3

Solution

You are getting this error probably because there were no records found in the database matching your criteria.

The easiest way to solve this error is to check if the database returned anything first.

$emailRes = $query->fetch(PDO::FETCH_ASSOC);
// VVV - Here I am checking if there was anything returned and then I check the condition
if($emailRes && $emailRes['Email']==$_POST['email']) {
    // ...
}

If you don't care whether the database returned anything, then you can simply provide a default value. For example:

$emailRes = $query->fetch(PDO::FETCH_ASSOC);
$email = $emailRes['Email'] ?? ''; // default: empty string

The correct way to check for existance in DB using PDO is:

$query = $pdo->prepare("SELECT COUNT(*) FROM Users WHERE Username =:Username");
$query->execute([':Username' => $name]);
if ($query->fetchColumn()) {
    throw new \Exception("Username is already in use!");
}

$query = $pdo->prepare("SELECT COUNT(*) FROM Users WHERE Email =:Email");
$query->execute([':Email' => $email]);
if ($query->fetchColumn()) {
    throw new \Exception("Email is already in use!");
}

Instead of fetching the row and doing the comparison again in PHP I am fetching a count of matching rows from the database and I use that count as a boolean in the if statement. fetchColumn() will fetch a single column from the first row and if I use COUNT(*) I know there will always be one row.

You can also do it in one query:

$query = $pdo->prepare("SELECT COUNT(*) FROM Users WHERE Username =:Username OR  Email =:Email");
$query->execute([':Username' => $name, ':Email' => $email]);
if ($query->fetchColumn()) {
    throw new \Exception("Username or email is already in use!");
}

Comments

[Qirel]

Just check if any rows were returned, by replacing if ($emailRes['Email']==$_POST['email']) { with if ($emailRes) { (and the same for $nameRes)

[Admin]

Ok that fixes it , thank you very much. But can someone explain why it used to work on previous version and is not working on this new one?

[aynber]

The issue is probably that your query didn't return any rows. It would work fine if a row was returned.

[Strawberry]

This seems like a weak strategy. What if someone submits the username while this username is checking whether the username exists?

[Qirel]

What Strawberry is talking about here is "race conditions", and you counter this by having an unique key on your columns, and catching the error if the value already exist. There's practically nothing wrong about checking this first as well, but there should be a unique key on the column if its supposed to be unique.

[RiggsFolly]

Of course you could just simplify this down to one query by doing SELECT COUNT(Username) FROM Users WHERE Username =:Username OR Email =:Email

[Admin]

@RiggsFolly I should use it in my answer instead of mention it :)

[Cid]

@RiggsFolly not necessary, user won't know which one of username or email are already taken. I sometime want to register on some sites and I'm happy to have a message letting me know that the email is already used (and then, I already have an account)

[RiggsFolly]

@Cid You are right, so maybe SELECT Username,Email FROM Users WHERE Username =:Username OR Email =:Email No Match, you are good to GO, then fetch the row and see which one was matched

[Cid]

@RiggsFolly yes, that's a good solution, letting the user know what's wrong without querying the DB again. I personnaly prefer to let Symfony + Doctrine doing that for me :D