This script is called find-matching-email.php

In searching the Internet, we found no evidence of a PHP script on how to use an inputted comma separated list to look for a match in a field with comma separated values. There are scripts to check for a match between one inputted email value and a single MySQL field with comma separated values. There are scripts to check for a match between one inputted email list with comma separated values and a single MySQL field with one email value. But nothing on how to use an inputted comma separated list to look for a match in a field with comma separated values, where at least one of the inputted emails in the comma separated list must be found in the single MySQL field with the comma separated values.

We found people assuring us that this is bad design: never put a comma separated list in a MySQL column, they say. It is slow and clumsy and not dynamic and it has too many limitations. Build extra tables or have separate fields per value. But we ran into a situation where people might have any number of emails, so we did not know how many fields to add. And since most users would use only one email address, defining 20 email fields per user seemed like overkill and a waste of space. There was a much simpler solution and there was no real down side. We agree with the experts that putting a comma separated list in a MySQL column is usually bad design. More values should mean more fields, tables, or both, not cramming stuff in one field. There is so much more you can do with "one value, one field" designs. Plus, if you have hundreds of thousands or more records, the explode() function and using a for loop like the one we used would be unnecessarily slow. But we had only 1500+ records and the speed was just fine. So we risked the wrath of the design gods and plunged bravely ahead.

We looked for any sign of a more elegant way of doing the job rather than breaking the user's comma separated list into an array and using a for loop with the Select inside it. We saw right away that the LIKE clause would be most relevant, although the IN clause looked promising right at first. But nothing tried to match "anything in the first comma separated list to anything in the second comma separated list." So we seem to be the first.

An HTML form is needed that has a text input field named "email" and that posts the form to the find-matching-email.php script on this page. The instructions are that the user can enter a comma-separated list of emails or just one. There must be a MySQL table named tablename with a varchar type email field that allows 255 characters, and a database connections configuration file named configuration.php.

Again: the user may try several emails, separated by commas. Or just one.

Here is the code description: First we include configuration.php so our connections to our MySQL database are set up and secure. Good data MUST be found in the email address(s) or the data retrieval will not happen. And of course all emails in user input must validate and one must be found in the MySQL database.

We strip any tags with strip_tags() and get rid of extra spaces with trim().

Then we fine-tune the emails—which may be a comma-separated list. We use the str_replace() function to dump all quotes or spaces in $email. Next, just to be cute we put quotes back in $email in such a way that all email addresses are now surrounded by single quotes as well as separated by commas. MySQL expects single quotes, and it does not matter if they are put into the MySQL statement or if they are stuck into the PHP variable you use for the MySQL statement, which uses a WHERE clause to get our SELECT to work right. The single quotes will not work right for the preg_match() function, however, so when we explode the $email variable into an array so that each email can be validated individually, we dump these quotes from each array element (in the $j array).

The $email variable is not actually needed except in if(isset($email)){ which could just as easily have been if(isset($j[0])){. Only the $j() array is needed, both for the validating and the MySQL query. The unneeded codes are just toys for you to play with. At first, the $j() array has quote-surrounded values. Toy 1. And just before being exploded into an array, $email contains a string that has quote-surrounded comma-separated values. Toy 2. If you find a way to use either of these—or even the array—and avoid the for loop, without changing our MySQL table structure, let us know.

An email validator is used on the input before we dare to get it near our MySQL SELECT statement, since it disallows anything scary. The preg_match() function uses a regular expression that's a standard email filter pattern. Unvalidated emails do not even let the db get searched—the user who inputs one or more bad emails is sent back to the form. Who knows what the user stuck in the bad email? We won't be finding out the hard way, security-wise!

Note that the conditional "if" introduces a criterion that makes sure the email was entered. Only if it was entered will there be any running of the clause with the SELECT in it to find out if one of the user's email(s) matches a record in the MySQL database.

To reiterate, the SELECT will occur as long as the required criteria are met: good data MUST be found in the emails, or the data retrieval will not happen. And of course emails must validate and at least one email address must be found in the MySQL database. Of course, just because the right input fields were filled does not mean that the SELECT will determine that the email is indeed in the db. It has to find a record, with at least one of the emails entered in the form input field, in the db.

We use the LIKE keyword in the SELECT statement for the email, which allows the email to be found even though it may only be a part of the data in the email field in the database—which may contain several entries separated by commas. We use the WHERE clause to get our SELECT to work right. Think of the script as saying that MySQL will look through your inputted comma-separated email list for a match—just one will do fine. But the looking will be done by using the array of emails from the user. So we go through the array, grabbing one email at a time in a for loop and seeing if it is in the email field.

When we do not find a matching email, we use the continue statement to skip one loop iteration. If we find a match we increment the $flag and get the record's data, then use the break statement to jump out of the for loop.

This script is called find-matching-email.php

<?php
//find-matching-email.php

include_once"configuration.php";

$email = $_POST['email'];
$email = stripslashes($email);
$email = trim($email);
$email = str_replace("'","",$email);
$email = str_replace(" ","",$email);
$email = str_replace('"','',$email);
$email = str_replace(",","','",$email);
$email = "'".$email."'";
$j=explode(',',$email);
for ($i=0; $i<count($j); $i++){$j[$i]=str_replace("'","",$j[$i]);
if (!preg_match("/([\w\-]+\@[\w\-]+\.[\w\-]+)/",$j[$i])) {
echo '<script language="javascript">alert("You submitted an invalid email address."); window.location = "find-matching-email.html"; </script>';}}
}

if(isset($email)){
$flag=0;
for ($i=0; $i<count($j); $i++){
$v=$j[$i];
$check_user_data = mysql_query("SELECT * FROM tablename WHERE email LIKE '%$v%'") or die(mysql_error());
$rc = mysql_num_rows($check_user_data);
if ($rc==0){continue;}else
{$flag++;
$row=mysql_fetch_assoc($check_user_data);
$e=$row['email'];
break;
}}}
if ($flag>0){echo $e." found!";
}else{echo 'We are unable to find the email address you provided.';}

mysql_close();

?>