82

I am trying to do an SQL query, but I need to check somehow if the value is an email address. I need a way to check if $user is an email address, because I have user values such as this in my table.

test test2 [email protected] [email protected] test392 [email protected]

and so on...

I need to make it so $useremail checks $user to find if it's an email address. So I can UPDATE the values, WHERE user=test OR [email protected], etc.

$user = strtolower($olduser); $useremail = ""; mysql_query("UPDATE _$setprofile SET user=$sn, fc=$fc WHERE user='$user' OR user='$useremail");
Improve this question
2
  • 2
    Why does it matter if a value is an email address? A string can be a perfectly formatted email address and still not be valid, like [email protected]. So the only way to know for sure if an address is valid is to try to send mail to it. And if you're not going to send mail to it, why do you care? Commented Nov 12, 2009 at 23:23
  • @Daniel Pryden I have stored usernames in my database. I want to update the email values to regular values, so when I run my script, it pulls the username data from the API, and then I can replace the old email value, with the new username value. It's more convenient for the script I am trying to write. Commented Nov 13, 2009 at 0:06

11 Answers 11

Reset to default
293

Without regular expressions:

<?php if(filter_var("[email protected]", FILTER_VALIDATE_EMAIL)) { // valid address } else { // invalid address } ?>
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Top tip! NB this is PHP v5.2.0 or greater only
@richsage: Right, the filter extension has been enabled by default since PHP 5.2.0 (released 2006-11-02). For earlier PHP versions, you could download and install the filter extension from PECL.
This allows for somename@somedomain, without a domain extension such as .com. Is there a way to fix this?
@MikeMoore -- there are legal (though rare) addresses in top-level domains, so it would actually be incorrect to require a dot in the domain name.
@MikeMoore from php.net/manual/en/filter.filters.validate.php "In general, this validates e-mail addresses against the syntax in RFC 822, with the exceptions that comments and whitespace folding and dotless domain names are not supported."
20 
if (filter_var($email, FILTER_VALIDATE_EMAIL)) { echo 'This is a valid email address.'; echo filter_var($email, FILTER_VALIDATE_EMAIL); // exit("E-mail is not valid"); } else { echo 'Invalid email address.'; }
Improve this answer

1 Comment

This is a much underrated answer for some reason!
11

This is not a great method and doesn't check if the email exists but it checks if it looks like an email with the @ and domain extension.

function checkEmail($email) { $find1 = strpos($email, '@'); $find2 = strpos($email, '.'); return ($find1 !== false && $find2 !== false && $find2 > $find1); } $email = '[email protected]'; if ( checkEmail($email) ) { echo $email . ' looks like a valid email address.'; }
Improve this answer

4 Comments

email@com is a valid email address: en.wikipedia.org/wiki/Email_address#Valid_email_addresses
[email protected] is invalid, that's not working
@MohsenSafari Just take out the && $find2 > $find1 part in the return and it will just check if it has an @ and .
dont use this, it simply fails if there's a . before @ as its comparing the position of @ and . would fail on something like [email protected] which is a valid email
8

The simplest approach is to use a regular expression to check email addresses, although there's some disagreement about how accurate this can be. This process is discussed in detail here:

Using a regular expression to validate an email address

You can use REGEXP in MySQL to select from the database based on your regular expression:

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Improve this answer

Comments

6

This function is_email() will give you an definite answer to whether the string is a valid email address or not. As far as I know, no other solution will do this with the same level of authority.

If I understand the example correctly, you would use it like this

$user = strtolower($olduser); $useremail = (is_email($user)) ? $user : '';

The PHP built-in function is incomplete. I'm the principle author of is_email() so I'm blowing my own trumpet here, but I've put a lot of work into this in order that nobody else should have to ever again.

Improve this answer

Comments

5

You can use regular expressions to validate your input string to see if it matches an email address:

<?php $email = "[email protected]"; if(eregi("^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$", $email)) { echo "Valid email address."; } else { echo "Invalid email address."; } ?>

From: http://www.totallyphp.co.uk/code/validate_an_email_address_using_regular_expressions.htm

EDIT: for more accurate expressions, please refer to Travis answer on this question

Improve this answer

3 Comments

How about .info and .museum tlds?
Oh, also, how about the upcoming ICANN decision to allow Chinese and Arabic in URL's?
And eregi is deprecated. Not a recommended solution
3

I've been using this function for many years across hundreds of sites. It's probably not perfect, but i've never had a complaint of false negatives (or positives):

function validate_email($email) { return (preg_match("/(@.*@)|(\.\.)|(@\.)|(\.@)|(^\.)/", $email) || !preg_match("/^.+\@(\[?)[a-zA-Z0-9\-\.]+\.([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$/", $email)) ? false : true; }
Improve this answer

Comments

3

Since everybody is posting their regular expression, here's mine: ^((([\w+-]+)(.[\w+-]+)*)|(\"[^(\|\")]{0,62}\"))@(([a-zA-Z0-9-]+.)+([a-zA-Z0-9]{2,})|[?([1]?\d{1,2}|2[0-4]{1}\d{1}|25[0-5]{1})(.([1]?\d{1,2}|2[0-4]{1}\d{1}|25[0-5]{1})){3}]?)$

To the best of my knowledge, it supports everything in the RFC specification, including a lot of things that most people don't usually include.

Improve this answer

Comments

3

Apart from all regex suggestions which mostly doesn't support all of the available TLD's (including for example .info and .museum) and the upcoming ICANN decision to allow Chinese and Arabic in URL's (which would let [a-z] and \w fail) for which the following more generic regex is sufficient

([^.@]+)(\.[^.@]+)*@([^.@]+\.)+([^.@]+)

I would also mention that doing a WHERE user=test OR [email protected] is too error prone. Better use an autogenerated PK in the table and use it in the WHERE clause.

I don't really see the value of strict, long and unreadable mailregexes conforming the RFCxxxx specs. Best way is still to send a mail with a link with an activation key to the end user to let it confirm the mail address. Inform that in the form as well. If necessary let the user type the email address twice like you do for passwords.

Improve this answer

Comments

2 
$email = "[email protected]"; function isEmail($email) { if(eregi("^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$", $email)) { return true } else { return false } } if (isEmail($user_email)) { // email is valid, run the query mysql_query("UPDATE _$setprofile SET user=$sn, fc=$fc WHERE user='$user' OR user='$user_email"); }
Improve this answer

Comments

0 
$user_email = "email@gmailcom"; function isEmail($email) { if(eregi("^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$", $email)) { return true; } else { return false; } } if (isEmail($user_email)) { // email is ok! } else { // email not ok }
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.