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
Got a question?
I am the only person to hold a gold badge in
,
and
on Stack Overflow and I am eager to show the right way for PHP developers.
Besides, your questions let me make my articles even better, so you are more than welcome to ask any question you have.
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