How to check if a row exists in MySQL? (i.e. check if username or email exists in MySQL)

I need help checking if a row exists in the database. In my case, that row contains an email address. I am getting the result:

email no longer exists publisher@example.com

This is the code I'm currently using:

if (count($_POST)) {
    $email = $dbl->real_escape_string(trim(strip_tags($_POST['email'])));

    $query = "SELECT `email` FROM `tblUser` WHERE `email` = '$email'";
    $result = mysqli_query($dbl, $query);
    if (is_resource($result) && mysqli_num_rows($result) == 1) {
        $row = mysqli_fetch_assoc($result);
        echo $email . " email exists " .  $row["email"] . "\n";
    } else {
        echo "email no longer exists" . $email . "\n";
    }
}

Is there a better way to check if a row exists in MySQL database (in my case, check if an email exists in MySQL)?

Solution

Using mysqli prepared statements legacy approach:

$query = "SELECT 1 FROM `tblUser` WHERE email=?";
$stmt = $dbl->prepare($query);
$stmt->bind_param("s", $email);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();
$emailExists = (bool)$row;

Using mysqli modern approach staring PHP 8.2:

$query = "SELECT 1 FROM `tblUser` WHERE email=?";
$result = $dbl->execute_query($query, [$email]);
$row = $result->fetch_assoc();
$emailExists = (bool)$row;

Using PDO prepared statement:

$email = $_POST['email'];
$stmt = $conn->prepare('SELECT 1 FROM `tblUser` WHERE email = :email');
$stmt->execute(["email" => $_POST['email']]);
$row = $result->fetch();
$emailExists = (bool)$row;

Prepared statements are best to be used to help protect against an SQL injection.

N.B.:

When dealing with forms and POST arrays as used/outlined above, make sure that the POST arrays contain values, that a POST method is used for the form and matching named attributes for the inputs.

FYI: Forms default to a GET method if not explicity instructed.

Note: <input type = "text" name = "var"> - $_POST['var'] match. $_POST['Var'] no match.

POST arrays are case-sensitive.

Consult:

http://php.net/manual/en/tutorial.forms.php

Error checking references:

Please note that MySQL APIs do not intermix, in case you may be visiting this Q&A and you're using mysql_ to connect with (and querying with).

You must use the same one from connecting to querying.

Consult the following about this:

Can I mix MySQL APIs in PHP?

If you are using the mysql_ API and have no choice to work with it, then consult the following Q&A on Stack:

Check if a row exists using old mysql_* API

The mysql_* functions are deprecated and will be removed from future PHP releases.

It's time to step into the 21st century.

You can also add a UNIQUE constraint to (a) row(s).

References:

How add unique key to existing table (with non uniques rows)