Check if a string is an email address in PHP
Determining whether a database column contains a valid email address or just a random username requires more than a simple regex match. You are dealing with data that might look like an email but isn't one, such as test, test2, or test392. Conversely, you have legitimate entries like test@example.com and test@example.net. The goal is to isolate the valid emails so you can perform a conditional update without corrupting plain usernames.
The most straightforward approach in PHP is using the built-in filter extension. Before diving into complex regular expressions, check if filter_var() is available. It handles the RFC 5322 email syntax validation out of the box. The function returns true if the string is a valid email address and false otherwise.
$user = 'test@example.com';
$isValidEmail = filter_var($user, FILTER_VALIDATE_EMAIL);
if ($isValidEmail) {
// It's an email like user@example.com
} else {
// It's likely just a username like test
}
This method is robust because it doesn't require you to maintain a massive regular expression pattern. It also handles edge cases like missing @ symbols, invalid domains, and malformed local parts that a hand-rolled regex might miss depending on how strict your pattern is. If you are working with an older PHP version where the filter extension is not enabled, you would fall back to regex, but modern environments should rely on the standard library first.
Once you have identified which rows contain email addresses, you need to update only those specific entries. The example code snippet shows a query attempting to update both $user and $useremail in the same statement based on a condition. A direct UPDATE ... WHERE user = 'value' OR user = '' approach is syntactically correct but logically fragile if the column stores emails mixed with usernames.
If your database schema allows it, the cleanest solution is to store email addresses in a dedicated column rather than mixing them with simple usernames. However, if you must work with a single user column that contains both types of values, you can construct a dynamic query. You would first fetch the valid emails from the table, filter them using the logic above, and then build the SQL condition dynamically.
$users = ['test', 'test2', 'test@example.com', 'test2@example.com'];
$emailList = [];
foreach ($users as $u) {
// Normalize to lowercase for consistent comparison if your DB is case-insensitive
$cleanUser = strtolower($u);
if (filter_var($cleanUser, FILTER_VALIDATE_EMAIL)) {
$emailList[] = "'$cleanUser'";
}
}
// Only execute the update if there are valid emails found
if (!empty($emailList)) {
$placeholders = implode(', ', $emailList);
$sql = "UPDATE _setprofile SET fc = 'new_value' WHERE user IN ($placeholders)";
// Execute $sql safely using prepared statements in production
}
Using IN with a dynamically built list of valid emails ensures you only touch the rows that actually contain an email address. Attempting to run an update on a column that mixes data types without this check can lead to unintended side effects, such as overwriting a username that looks nothing like an email.
When implementing this logic in Laravel or similar frameworks, remember to sanitize inputs and use prepared statements to prevent SQL injection when injecting the $emailList into your query string. The filter_var() function is your primary defense against false positives, ensuring that test392 never gets flagged as an address for example.com.