MySQL Validate Email Records

php mysql validation email-validation
TheBlackBenzKid picture TheBlackBenzKid · Dec 1, 2011 · Viewed 19.5k times · Source

I know you can use PHP Email Validate filters to check if the data is abc @ abc . ext

Is there anyway I can run a MySQL query to do this, check for only that style format. Otherwise I will have to make a pagination and load balanced limit script to select all records from the DB (50,000+) and the server is already slow for my client on shared hosting.

I just want to see a list of only valid emails and also can I check for duplicates or similarities with like query?

Appreciate any collab on this.

Answer

Oyeme picture Oyeme · Dec 1, 2011

Somethis like this

SELECT * FROM users WHERE email NOT REGEXP '^[^@]+@[^@]+\.[^@]{2,}$';

OR

SELECT * FROM users WHERE email NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$';

Update. It's just an example,how to use regular expression in Mysql.

Related questions

How can I set a date to NULL in Yii?

I have a date column that usually takes values as dd.MM.yyyy. Its validation rule in the model's rules() is this: array('start, end', 'date', 'format' => 'dd.MM.yyyy'), I'm populating the database from a CSV file, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
Import file size limit in PHPMyAdmin

I have changed all the php.ini parameters I know: upload_max_filesize, post_max_size. Why am I still seeing 2MB? Im using Zend Server CE, on a Ubuntu VirtualBox over a Windows 7 host.

Read More