php read file for mysql import

Question

i have a website that allow people to unsubscribe to other websites (to mailing list, or spam etc...)

my web app allow clients and users to upload encrypted email list for mass unsubscribtion.

i have a table: unsubs. that table have email and domain.

the file structure is like this:

jhq232q3hq2yq3yuh2qyuqU/A$Ja324ju3a4jah34u3w$UQ"$Uq4u
q34/hRYHSEa34uw34uQ"/YQ/$84?%JHHdfhdFJKAjaRJSErjsrjse
W%$?p09-*Y+_)y8p7uYJgADGq2/TYQgSj1qR"3tQ"/gEHseruDUId

here's my PHP:

function decrypt($line) {
// do my logic etc...
return $line;
}
function isEmail($email) {
if(preg_match("/^([a-zA-Z0-9_-])*@([a-zA-Z0-9_-])+([a-zA-Z0-9_-]+)+$/", $email)){
return true;
}
return false;
}

function checkEmail($email) {
// logic
return array('baddomain.com');
}

function emailExists($email) {
// my logic
return TRUE; // for example
}

$file = file_get_contents('sample.txt');

$lines= explode("\n", $file);
foreach($lines as $line) {
if(!empty($line)) {
$line = decrypt($line);
if(isEmail($line)) {
 $services = checkEmail($line);
 if(is_array($service)) {
  foreach($services as $service) {
   insertEmail($db, $service, $line); // this is used
  }
 }
}
}
}

the check email function check if the email is unsub in all the lists, returns false if everything is unsubscripbed and an array (list of services = domain) if none.

now my problem is everytime i want to check if an email is valid it returns false. my encryption is working fine and it'S bullet proof.

what am i missing?

Answer 1

First, your script imports file using \n but if someone upload the file with windows character like \r\n your script won't work.

Second, you email regexp is not working properly. I recommend to use filter_var.

What I would recommend for your table is to create daily tables. This way when you query your table it will be faster to execute. If you have only one table with varchar as indexes this can be slow after some times (assuming people want to be opt-out of bad site [which i think they do]) - but that is just me.

CREATE TABLE `unsub_20111127` (
  `email` varchar(255) NOT NULL,
  `domain` varchar(255) NOT NULL,
  `is_unsubscribed` tinyint(3) NOT NULL DEFAULT 0,
  PRIMARY KEY (`email`, `domain`),
  KEY is_unsubscribed (`is_unsubscribed`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

This way it's easier to process small amount of data each time. I added a status in case you have to repeat process again so you only process the data with the is_unsubscribed = 0 (not processed)

(if you need to create a table that reads all the tables you can create a merge table);

PHP:

function insertEmail($db, $service, array $data) {
  // logic for insert
  $sql = 'INSERT IGNORE INTO table_YYYYMMDD VALUES ' . implode(',', $data);
}

function createTable($date) {
  // logic for insert
  $sql = 'CREATE TABLE IF NOT EXISTS table_' . $date . ' /* etc...*/';
}

// start 

// create the table for the next day if not exists
createTable(date('Ymd', strtotime('tomorrow')));

$file = file('sample.txt', FILE_IGNORE_NEW_LINES ^ FILE_SKIP_EMPTY_LINES);

$emailList = array();

foreach($file as $line) {
 $line = decrypt($line);
 if(!filter_var($line, FILTER_VALIDATE_EMAIL)) {
  $services = checkEmail($line);
  if(is_array($services)) {
   foreach($services as $service) {
     $emailList[] = "('" . $line . "', '" . $service . "', 1)";
   }
   if(!empty($emailList)) {
    insertEmail($db, $service, $emailList);
   }
   $emailList = array();
  }
 }
}