Hi,
This is a real strange one. I am writing a shell script in PHP which I am
cronning to run every 5 mins. Basically it downloads email via imap. Splits
it up into it relevant sections and adds it to a database. It does multiple
inserts, and update and multiple selects. The problem I have is that some of
the inserts dont work. There is no error returned ny mysql_error() and if
echo the SQL out and run in the MySQL client then it inserts ok. The thing
is that it is not the same query every time either. Sometimes its the first
insert and naother time its the second insert. I have attached the script in
case you want to check it out.. Any ideas anyone?
Thanks
Jon
#!/usr/local/bin/php -q
<?
// A script which dowmloads the support email and processes it
$dbhost = "localhost";
$dbuser = "user";
$dbpassword = "password";
$dbname = "support";
$time = time();
mysql_pconnect($dbhost, $dbuser, $dbpassword);
mysql_select_db($dbname);
//Open the mailbox and check if mail waiting
$mailstream = imap_open("{imap.server.com:143}INBOX","user","password");
$email = imap_headers($mailstream);
if (count($email) == 0) {
//echo "Nothing to process ... \n";
imap_expunge($mailstream);
imap_close($mailstream);
mysql_close();
exit;
}
//echo count($email) ." \n";
for ($i=1; $i<=count($email); $i++) {
;
$thisheader = imap_header($mailstream, $i);
$subject = $thisheader->subject;
$from = $thisheader->from;
$name = $from[0]->personal;
$fromaddr = sprintf("%s@%s",$from[0]->mailbox,$from[0]->host);
//echo $subject . " " . $fromaddr . " " . $name . "\n";
$struc = imap_fetchstructure($mailstream, $i, FT_UID);
//echo "type: " . $struc->bytes . "\n";
if (empty($struc->bytes)) {
//echo "Type: " . $struc->type . "\n";
//echo "Sub-type: " . $struc->subtype . "\n";
$mailtext = trim(imap_fetchbody($mailstream, $i, 1));
//echo "Parts Count: " . Count($struc->parts) . "\n";
for ($z = 1; $z < Count($struc->parts) + 1; $z++) {
//echo "Part No. " . $z . ": " . $struc->parts[$z]->type .
"\n";
if ($struc->parts[$z]->ifparameters) {
$parameters = $struc->parts[$z]->parameters;
$resarray = get_object_vars($parameters[0]);
$filename[] = $resarray["value"];
$filesrc[] = addslashes(imap_fetchbody($mailstream,
$i, $z + 1));
switch ($struc->parts[$z]->type) {
case 0:
$type = "text";
case 1:
$type = "multipart";
case 2:
$type = "message";
case 3:
$type = "application";
case 4:
$type = "audio";
case 5:
$type = "image";
case 6:
$type = "video";
case 7:
$type = "other";
default:
$type = "text";
}
$filetype[] = addslashes($type . "/" .
strtolower($struc->parts[$z]->subtype));
if ($type . "/" .
strtolower($struc->parts[$z]->subtype) == "text/html")
{
if (eregi("\.htm", $resarray["value"]) ||
eregi("\.html",
$resarray["value"])) {
} else {
$HTML = TRUE;
}
}
$attachments = TRUE;
}
}
} else {
$mailtext = trim(imap_body($mailstream, $i));
$attachments = FALSE;
}
//echo "$mailtext\n";
if ($HTML) {
//echo "html\n";
imap_delete($mailstream, $i);
$bodytext = "Sorry that's HTML mail and we dont accept it\n";
mail ($fromaddr, "RE: " . $subject, $bodytext, "From: Customer Support
<[EMAIL PROTECTED]>");
continue;
}
if (eregi("SUP[0-9][0-9][0-9][0-9][0-9][0-9]", $subject)) {
//echo "Existing support call\n";
} else {
//echo "New Support Call\n";
//get next support number
$sSQL = "select * from ticketnumbers";
$result = mysql_db_query($dbname, $sSQL);
$row = mysql_fetch_array($result);
$thisnumber = $row[0];
$nextnumber = $thisnumber;
$thisnumber++;
$sSQL = "update ticketnumbers set ticketnumber = " . $thisnumber;
//echo $sSQL . "\n";
$result = mysql_db_query($dbname, $sSQL);
//add new support request to db
$ticket = "SUP" . sprintf("%07d", $nextnumber);
$sSQL = "insert into supportrequests (ticketnumber, dateopened,
datelastactioned, origemail, subject) values ('$ticket', $time, $time,
'$fromaddr', '" . trim($subject) . "');";
//echo $sSQL1 . "\n";
mysql_query($sSQL);
//echo $result1 . " " . mysql_errno() . ":" . mysql_error() . "\n";
//get new supportrequestid
$sSQL = "select * from supportrequests where ticketnumber = '" .
$ticket .
"'";
$result = mysql_db_query($dbname, $sSQL);
$row = mysql_fetch_array($result);
$supportid = $row[0];
$emailtext = trim(imap_body($mailstream, $i));
$sSQL = "insert into supportdetail (supportrequestid, fromname, email,
text, datereceived) values (" . $supportid . ", '" . $name . "', '" .
$fromaddr . "', '" . $mailtext . "', " . $time . ")";
//echo $sSQL . "\n";
mysql_query($sSQL);
usleep(1000);
//get supportdetailid for this email
if ($attachments) {
$sSQL = "select supportdetailid from supportdetail where
datereceived = "
. $time . " and supportrequestid = " . $supportid;
//echo $sSQL4 . "\n";
$result = mysql_db_query($dbname, $sSQL);
$row = mysql_fetch_array($result4);
$supportdetailid = $row[0];
$numatt = count($filesrc);
for ($h =0; $h<$numatt; $h++) {
$sSQL = "insert into attachments (supportdetailid,
filename, attachment,
filetype) values ($supportdetailid[0], '$filename[$h]', '" . $filesrc[$h] .
"', '$filetype[$h]')";
//echo $sSQL5 . "\n";
$result = mysql_db_query($dbname, $sSQL);
}
}
$bodytext = "Thank you for your support request\n\n";
$bodytext = $bodytext . "We have assigned the following ticket number
to
this request " . $ticket . "\n";
if ($attachments) {
$bodytext = $bodytext . "\nWe received the following
attachments from
you\n";
for ($u=0; $u<$numatt; $u++) {
$bodytext = $bodytext . "File: " . $filename[$u] . "
type: " .
$filetype[$u] . "\n";
}
}
$bodytext = $bodytext . "\nPlease make sure that the ticket number is
quoted in all correspondence\n"; $bodytext = $bodytext . "and
that it
appears in the subject line of all emails\n";
$bodytext = $bodytext . "you send regarding this matter\n\n";
$bodytext = $bodytext . "Customer Service Team\n";
mail($fromaddr, "RE: " . $subject . " " . $ticket, $bodytext,
"From:Customer Support <[EMAIL PROTECTED]>");
}
imap_delete($mailstream, $i);
}
//Close the mail box like a good little boy
imap_expunge($mailstream);
imap_close($mailstream);
mysql_close();
?>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
