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.

In fact, the code to check whether some value exists in the database would be the just a regular routine for select queries.

And of course, a prepared statement is obligatory. NEver ever put a data variable directly tho the query. Always use a placeholder instead!

How to check whether a value exists?

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

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: