I've been working on a system in which it takes emails for my Gmail account and certain ones (Which fit the correct criteria) will then be taken and uploaded to a MySQL Database.
The problem is that not all of the records are being inserted. I've echoed what details will be inserted into the database and I've set enough space for each column within the database.
Here is the code:
<!DOCTYPE html>
<html>
<head>
<meta http-equiv='Content-Type' content='text/html; charset=utf-8' />
<title>Mailbox</title>
</head>
<body>
<?php
//connects to gmail
$mail_server = 'imap.gmail.com' ;
$mail_port = 993 ;
$mail_username = 'username' ;
$mail_password = 'password' ;
$mail_folder = 'Inbox';
$mail_certificate = '/imap/ssl/novalidate-cert';
echo '<h1>'.$mail_username.' on '.$mail_server.'</h1>' ;
$mbox = imap_open('{'.$mail_server.':'.$mail_port.$mail_certificate.'}'.$mail_folder, $mail_username, $mail_password) or die('Error opening mailbox: <br /> '.imap_last_error());
$mailboxheaders = imap_headers($mbox);
if ($mailboxheaders == false) {
echo '<p>'.$mail_folder.' is empty.</p>
';
} else {
echo '<h2>'.$mail_folder.'</h2>' ;
$msgno = 0;
foreach ($mailboxheaders as $val) {
$msgno++;
//Getting messages from .....
$pos = strpos($val,'certain_email');
if($pos === false){
//No result
}else{
$msgType = checkMsgType($mbox, $msgno);
if($msgType === "RS-StaffJourno"){
staffJournoMsg($mbox, $msgno);
}else if($msgType === "RS-Freelancer"){
freeLancerMsg($mbox, $msgno);
}else if($msgType === "RS-PRSender"){
prSenderMsg($mbox, $msgno);
}else if($msgType === "RS-Promotions"){
promotionsMsg($mbox, $msgno);
}else if($msgType === "RS-Broadcaster"){
broadcasterMsg($mbox, $msgno);
}else if($msgType === "RS-Blogger"){
bloggerMsg($mbox, $msgno);
}else{
echo "Unknown Type of RS Message,Please Add $msgType";
}
}
}
}
function dbInsert($query){
//Connects to db
$host = 'localhost';
$username = 'dbUsername';
$password = 'dbPassword';
$database = 'dbName';
//Connects to table
mysql_connect($host, $username, $password) or die('Cannot connect to php myadmin :<br> '.mysql_error());
mysql_select_db($database) or die('Cannot select table :<br>'.mysql_error());
// echo $query; //To check what is being input (Testing reasons)
mysql_query($query);
mysql_close();
}
function staffJournoMsg($mbox, $msgno){
$type = "Journo";
setVars($mbox, $msgno, $type);
}
function bloggerMsg($mbox, $msgno){
$type = "Blogger";
setVars($mbox, $msgno, $type);
}
function freeLancerMsg($mbox, $msgno){
$type = "Freelance";
setVars($mbox, $msgno, $type);
}
function prSenderMsg($mbox, $msgno){
$type = "PRSender";
setVars($mbox, $msgno, $type);
}
function promotionsMsg($mbox, $msgno){
$type = "Promotions";
setVars($mbox, $msgno, $type);
}
function broadcasterMsg($mbox, $msgno){
$type = "Broadcaster";
setVars($mbox, $msgno, $type);
}
function setVars($mbox, $msgno, $type){
//Getting the variables values
$mediaOutlet = getMediaOutlet($mbox, $msgno);
$subject = getSubject($mbox, $msgno);
$journalist = getStaffJournalist($mbox, $msgno, $type);
$mediaType = getMediaType($mbox, $msgno);
$deadline = getDeadline($mbox, $msgno);
$mainContent = getQuery($mbox, $msgno);
$replyInfo = getReplyDetails($mbox, $msgno);
$categories = getSuitableCategories($mbox, $msgno, $type);
$emaildate = getEmailDate($mbox, $msgno);
$website = getWebsite($mbox, $msgno, $type);
echo "$mediaOutlet<br>$emaildate<br>$deadline<br>$subject<br>$website<br>$journalist<br>$mediaType<br>$mainContent<br>$replyInfo<br>$categories<br><br>";
$query = "INSERT INTO Email VALUES(null, '$mediaOutlet', '$emaildate', '$deadline', '$subject', '$website', '$journalist', '$mediaType', '$mainContent', '$replyInfo', '$categories');";
dbInsert($query);
}
function checkMsgType($mbox, $msgno){
$header = imap_fetchheader($mbox, $msgno);
$subject = explode("Subject:", $header);
$subject = explode("From:", $subject[1]);
$subject = explode("[", $subject[0]);
$subject = explode("]", $subject[1]);
return $subject[0];
}
function getMediaOutlet($mbox, $msgno){
$allBody = imap_body($mbox, $msgno);
$mediaOutlet = explode("Media outlet: ", $allBody);
$mediaOutlet = explode("(", $mediaOutlet[1]);
return $mediaOutlet[0];
}
function getWebsite($mbox, $msgno, $type){
$allBody = imap_body($mbox, $msgno);
//Setting the ones without websites to null
if($type === "Broadcaster" || $type === "PRSender"){
$mediaWebsite = "No Website";
return $mediaWebsite;
}else{
$mediaWebsite = explode("Media outlet website:", $allBody);
if($type === "Journo")
$mediaWebsite = explode("Staff", $mediaWebsite[1]);
else if ($type === "Freelance")
$mediaWebsite = explode("Freelance", $mediaWebsite[1]);
else if($type === "Promotions")
$mediaWebsite = explode("Editorial", $mediaWebsite[1]);
else if($type === "Blogger")
$mediaWebsite = explode("Independent", $mediaWebsite[1]);
return $mediaWebsite[0];
}
}
function getStaffJournalist($mbox, $msgno, $type){
$allBody = imap_body($mbox, $msgno);
if($type === "Freelance"){
$journalist = explode("journalist:", $allBody);
$journalist = explode("Journalist", $journalist[1]);
}else{
if($type === "Journo")
$journalist = explode("journalist:", $allBody);
else if($type === "PRSender")
$journalist = explode("ResponseSource:", $allBody);
else if($type === "Promotions")
$journalist = explode("promotions:", $allBody);
else if($type === "Broadcaster")
$journalist = explode("producer:", $allBody);
else if($type === "Blogger")
$journalist = explode("blogger:", $allBody);
//All of these have Media after them
$journalist = explode("Media", $journalist[1]);
}
return $journalist[0];
}
function getMediaType($mbox, $msgno){
$allBody = imap_body($mbox, $msgno);
$mediaType = explode("type: ", $allBody);
$mediaType = explode("Deadline", $mediaType[1]);
return $mediaType[0];
}
function getDeadline($mbox, $msgno){
$allBody = imap_body($mbox, $msgno);
$deadline = explode("leads: ", $allBody);
$deadline = explode("Enquiry", $deadline[1]);
return $deadline[0];
}
function getQuery($mbox, $msgno){
$allBody = imap_body($mbox, $msgno);
$content = explode("Query", $allBody);
$content = explode("How To Reply", $content[1]);
return $content[0];
}
function getReplyDetails($mbox, $msgno){
$allBody = imap_body($mbox, $msgno);
$reply = explode("How To Reply", $allBody);
$reply = explode("Media", $reply[1]);
return $reply[0];
}
function getSuitableCategories($mbox, $msgno, $type){
$allBody = imap_body($mbox, $msgno);
$categories = explode("This enquiry is relevant to the following categories:", $allBody);
$categories = explode("These", $categories[1]);
return $categories[0];
}
function getEmailDate($mbox, $msgno){
$header = imap_fetchheader($mbox, $msgno);
$getdate = explode("HTTP; ", $header);
$getdate = explode(" ", $getdate[1]);
$emaildate = "$getdate[1]-$getdate[2]-$getdate[3]";
return $emaildate;
}
function getSubject($mbox, $msgno){
$header = imap_fetchheader($mbox, $msgno);
$subject = explode("Subject:", $header);
$subject = explode("From: ", $subject[1]);
return $subject[0];
}
?>
</body>
</html>
Just a reminder that for the Blogger, Broadcaster and StaffJourno it will upload to the database, just not the rest.
I fixed the problem. There wasn't anything wrong with the code. The problem was the content I was taking in had ' symbols which would mess up the code. To solve this I created a new function that would take away all of the ' symbols.
You have identified one of the problems with the code - the quote character is breaking the SQL. While stripping out the quotes here will help you a little, this is still potentially an opening for an https://en.wikipedia.org/wiki/SQL_injection attack. Bad news indeed!
The full solution will involve using prepared statements. More information on one way of doing prepared statements with PHP can be found on their website: http://php.net/manual/en/pdo.prepared-statements.php. There are a few options depending on your poison of MySQL interface. PDO does seem to be the current favourite flavour.
Depending on what you are planning on doing with the data there may be some other areas which should be addressed too. Can you elaborate a little on the intended use case and how the data will be displayed? I may be able to help you there too.