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?

Book Of Zeus · Accepted Answer · 2011-11-27 16:38:03Z

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();
  }
 }
}

i like the idea of creating multiple tables for each day so far we get 10 files from clients with 5k or so each, after few years it will be crazy but is that mean after few years we will have like 1000+ tables?
you can archive them in a yearly or monthly tables once a year or month is over

Collectives™ on Stack Overflow

php read file for mysql import

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related