imap email into db

[mrweirdo]

Hello all I'm looking to make a script for an app I'm working on that will be run from a cron every couple of minutes and insert emails in a imap mail account into a database.

outlined requirements:
1) script leaves all emails on mail server
2) only inports new messages on the mail server into db
*note I'm hopeing to be able to delete records of emails in the db but not on the mail server itself and i dont want this script to be readding them to the db so they dont show up again in my app.

mailcheck.php

<?
// mail acount settings
$server="{localhost/imap}";
$email = "";
$password = "";

// mysql database conection setings
$dbhost = "localhost";
$dbuser = "";
$dbpass = "";
$dbname = "";

// conection function to the  mysql db
function dbopen($dbname = ""){
global $dbuser,$dbpass,$dbhost,$dbname;
$startconnection = @mysql_connect($dbhost, $dbuser, $dbpass) or print "Unable to connect to the Database.";
@mysql_select_db($dbname, $startconnection) or print "Unable to select database";
return $startconnection;
}

// conects to imap server and gets the email
$link=imap_open($server,$email,$password);
$headers=imap_headers($link);
$numEmails = sizeof($headers);
for($i = 1; $i < $numEmails+1; $i++){
	$mailHeader = @imap_headerinfo($link, $i);
	$id = $mailHeader->message_id;
	$date = $mailHeader->date;
	$from = $mailHeader->fromaddress;
	$subject = strip_tags($mailHeader->subject);
	$message = @imap_body($link, $i);
	$message = substr($message, 0, 20);

// insert the email into the db table
$dbcnx = dbopen();
$sql = mysql_query("INSERT INTO sms_emails ( id , date , from , subject , message ) VALUES ( '$id', '$date', '$from', '$subject', '$message' )") or die (mysql_error());
mysql_close($dbcnx);
}
?>

[Weedpacket]

Good to hear. Have fun.

[mrweirdo]

For some reason mysql is not happy about my sql statement giving me an SQL syntax error.

$sql = mysql_query("INSERT INTO sms_emails (id, date, from, subject, message) VALUES ('$id', '$date','$from','$subject','$message' )") or die(mysql_error());

Anyone know why?

[cyberlew15]

Okay I'm not sure what your query is so dump the query into a text file and either check it yourself with a mysql syntax checker or upload the entire query and I'll tell you whats wrong with it. Remember to replace the e-mail contents for security you don't want to be giving out poples e-mails or your private information on a public board 😉

[mrweirdo]

good news after much playing around with it and phpmyadmin I was able to get it working however didnt figure out what was causing the problem in the first place. updated the script to show the new query.

With out of the way it actualy inports all the emails into the db the first time I run the script. Now the problem is if I run it again it craps out with the following:
Duplicate entry '<42EB6187.7010304@linuxbox>' for key 1
because it tries to enter the same emails yet again.

here is an sql dump of the table:

[CODE]

-- Table structure for table sms_emails

CREATE TABLE sms_emails (
id varchar(60) NOT NULL default '',
date varchar(60) NOT NULL default '',
from varchar(60) NOT NULL default '',
subject varchar(60) NOT NULL default '',
message varchar(255) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;

--

-- Dumping data for table sms_emails

INSERT INTO sms_emails VALUES ('<42EB6187.7010304@linuxbox>', 'Sat, 30 Jul 2005 04:16:23 -0700', 'mrweirdo <mrweirdo@linuxbox>', 'test1', 'this is a test\r\n\r\n--');
INSERT INTO sms_emails VALUES ('<42EB619D.7070303@linuxbox>', 'Sat, 30 Jul 2005 04:16:45 -0700', 'mrweirdo <mrweirdo@linuxbox>', 'test2', 'this is anouther tes');

[/CODE]

I need to find a way to stop that error from appearing so new messages can be added but at the same time not create duplicates in the db that have been added allready. Any ideas?

[leatherback]

Well,

You need to do a query to the database, and see which email is already there. Subsequently you only imporet the records which are new.

Best would be to use a timestamp field I think. Check the timestamp of the last email which was imported against the email on the server. Or if the original email is stored in a database with an ID colum, you could keep the ID values of those records to check.

J.

[mrweirdo]

luckily php imap functions can get the unique id of each email and I am able to inport it into the database so hopefully what I came up with will work. Basicaly an if statement inside the for loop that checks if there is allready an id that matches the emails inside the table and if not it adds it.

[mrweirdo]

Well it doesnt seem to work my script will block itself from trying to readd an email that is allready in the db table but it doesnt add new emails.

here is the code as it currently stands:

<?
// requires the setting vars for conecting to mysql db and imap email acount
require 'conf.php';

// conects to imap server and gets the email
$link=imap_open($server,$email,$password);
$headers=imap_headers($link);
$numEmails = sizeof($headers);
echo "emails:<br>";
for($i = 1; $i < $numEmails+1; $i++){
	$mailHeader = @imap_headerinfo($link, $i);
	$id = $mailHeader->message_id;
	$date = $mailHeader->date;
	$from = $mailHeader->fromaddress;
	$subject = strip_tags($mailHeader->subject);
	$message = @imap_body($link, $i);
	$message = substr($message, 0, 20);
	$message = addslashes($message);
	echo "<br>Id:$id<br>From:$from<br>Subject:$subject<br>Message:$message<br><br>";

// conects to the db to check for emails/add emails
$dbcnx = dbopen();
$sql = "SELECT id FROM sms_emails";
$result = mysql_query($sql) or die("Couldn't Execute Query, MySQL Said: " . mysql_error() ."");
while ($rows = mysql_fetch_array($result)){
	$sql_id = $rows[id];
}
// stops or allows the emails to be added to the db
if ($id = $sql_id){
	echo "duplacate email with id: $id not added<br><br>";
} else {
	echo "email $id has been added<br>";
	// insert the emails into the db table
	$sql1 = mysql_query("INSERT INTO `sms_emails` ( `id` , `date` , `from` , `subject` , `message` ) VALUES ( '$id', '$date', '$from', '$subject', '$message' )") or die (mysql_error());
}
mysql_close($dbcnx);
}
?>

here is what it produces outputwise (with echo statements and run via a browser for debuging):

emails:

Id:<42EB6187.7010304@linuxbox>
From:mrweirdo
Subject:test1
Message:this is a test --

duplacate email with id:<42EB619D.7070303@linuxbox> not added


Id:<42EB619D.7070303@linuxbox>
From:mrweirdo
Subject:test2
Message:this is anouther tes

duplacate email with id:<42EB619D.7070303@linuxbox> not added


Id:<42EB8C86.6040900@linuxbox>
From:mrweirdo
Subject:test3
Message:this is test three o

duplacate email with id:<42EB619D.7070303@linuxbox> not added

Notice the common theme of duplacate email statement after each email grabed from the imap account is the same? hope someone has an idea as I'm now stuck

sql table dump:

CREATE TABLE `sms_emails` (
  `id` varchar(60) NOT NULL default '',
  `date` varchar(60) NOT NULL default '',
  `from` varchar(60) NOT NULL default '',
  `subject` varchar(60) NOT NULL default '',
  `message` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) TYPE=MyISAM;

-- 
-- Dumping data for table `sms_emails`
-- 

INSERT INTO `sms_emails` VALUES ('<42EB6187.7010304@linuxbox>', 'Sat, 30 Jul 2005 04:16:23 -0700', 'mrweirdo <webmaster@linuxbox>', 'test1', 'this is a test\r\n\r\n--');
INSERT INTO `sms_emails` VALUES ('<42EB619D.7070303@linuxbox>', 'Sat, 30 Jul 2005 04:16:45 -0700', 'mrweirdo <webmaster@linuxbox>', 'test2', 'this is anouther tes');