select records with valid email address

[jeff5656]

I would like to select ONLY those records that have a valid email address in the field "email", instead of selecting all records and then using eregi in a while loop after the fact.

 

How can I write the query to exclude invalid emails?

 

i.e.

$query = select * from table where email !="invalid emails";

 

Thanks!

[premiso]

You can use Regex in MySQL. Mysql Regular Expressions

 

But the more prudent question to ask, is why are there invalid emails in the table? I would fix that problem first, then clean the database out and or require an updated email for any invalid email when the user logs in (assuming they do login).

[jeff5656]

Holy Cr*p that's complex!

 

http://dev.af83.com/2007/05/15/validate-email-addresses-in-mysql-with-a-pure-select.html

 

I think I will just exclude them in the while loop unless anyone knows of a less complex query?

 

 

[Jessica]

You are doing this only one time, right? As premiso said, you shouldn't allow the invalid ones into the DB anymore.

[jeff5656]

I  promise I will write some code very very soon to force valid email for users.  :-)  But in the meantime  I wanted a quick and dirty way to get all the ones who already HAVE valid emails and export them to an excel file, because otherwise I  have to wait for those users with invalid emails to log on and give me their correct ones.  Until they log in, that record will contain an invalid (or more commonly blank) email field. 

 

Obviously in retrospect I should have required a valid email from the start! :-)

[Jessica]

Then use the code premiso linked to, It's not that complicated.

[Psycho]

Hmm, what constitutes a "valid" email address is a complicated issue. I've never seen one that was 100% correct. And, I doubt that any MySQL solution would be anywhere near as good as good as one in PHP could be.

 

You need to START by creating a function/process for excluding invalid emails to begin with. Then you can use that same function to run a single process to remove any invalid emails from your table. But, you should prevent invalid entries being created before trying to fix the DB.

 

Once you have a validation check in place and you have removed all the invalid entries you can just query the table for all the entries that have a value.

[fenway]

Anything you 'feed' invalid e-mail addresses will just complain or log them -- so you can do the rest on input.