How to check if email exists in the database?
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:
- SELECT query with PDO
- INSERT query using PDO
- UPDATE query using PDO
- How to connect to MySQL using PDO
- PDO Examples
- Authenticating a user using PDO and password_verify()
- Select the number of rows using PDO
- How to create a WHERE clause for PDO dynamically
- DELETE query using PDO
- How to create a prepared statement for UPDATE query
- Getting a nested array when multiple rows are linked to a single entry
- How to execute 1000s INSERT/UPDATE queries with PDO?
- Adding a field name in the ORDER BY clause based on the user's choice
- INSERT helper function for PDO Mysql
- PDO Examples
- PDO Examples
Add a comment
Please refrain from sending spam or advertising of any sort.
Messages with hyperlinks will be pending for moderator's review.
Markdown is now supported:
>
before and an empty line after for a quote