How to check if email exists in the database?

  1. A common mistake
  2. How to check whether a value exists?
  3. Notes
  4. Comments (5)

A common mistake

A very common newbie mistake is to check the result of the query, like

$check_email $pdo->query("SELECT * FROM users WHERE email='test@example.com'");
if (
$check_email) {
    
// found
}

Alas, it's entirely wrong. The result of the query has nothing to do with the number of rows found. An empty result is still a legitimate result, it cannot be false only because the query didn't find anything. On the contrary, in will be the same statement you are getting from any other query and therefore you have to do exactly the same as any other query as well:

How to check whether a value exists?

To check whether particular value exists in DB, you simply have to run just a regular SELECT query, fetch a row and see whether anything has been fetched.

Of course, prepared statement must be used as it always should be when a variable is involved in the query.

Given $pdo contains a valid PDO instance, the code would be

$email "test@example.com";
$stmt $pdo->prepare("SELECT * FROM users WHERE email=?");
$stmt->execute([$email]); 
$user $stmt->fetch();
if (
$user) {
    
// email found
} else {
    
// or not

Here we are selecting a row matching our criteria, then fetching it and then checking whether anything has been selected or not.

Notes

It also makes sense to select just a single constant value to reduce overhead a little, if we aren't going to use any selected data. For that purpose simple replace * with 1. Having a function or a class' method for the purpose also would be handy:

function emailExists($pdo$email) {
    
$stmt $pdo->prepare("SELECT 1 FROM users WHERE email=?");
    
$stmt->execute([$email]); 
    return 
$stmt->fetchColumn();
)

if (
emailExists($pdo$email)) {
    
// found
}

here we are using a handy PDO's method fetchColumn that returns a selected value or false otherwise. Given 1 will be treated as true by if statement, we can return this method's result directly. However, in case you prefer strict typing, the return value could be cast to boolean:

    return (bool)$stmt->fetchColumn();

Also, as can be seen from above, a popular solution with getting the number of rows returned is just unnecessary. Calling $stmt->numRows() is just superfluous, not to mention it can be just unavailable due to some drivers or settings.


Related articles: