The following is code from Xeoport.
It is suppose to access my pop3 account and load email into Mysql
database.
It's not inserting and there is no log or anything to tell me why.
Here is the page.
http://www.dafella.com/xeoport/
The original coder left no contact info.
The reason I think it's the code is because of another issue.
I had to remove a section of code that checked for a db, table, if not
there it created one.
The code worked fine if no db. But it failed if the table is there.
1. How can I view the output of the sql statements to see what they
contain before excution.
2. Anyone see something that jumps out as a possible problem
Thanks
Steve
Anyway here is the code.
<?PHP
/*
XeoPort (mailserver-2-mysql-import-module)
(c) 2002 xeoman
http://xeoman.com/code/php/xeoport
NOTHING TO BE CONFIGURED HERE!!!
look into "config.inc" for customization
*/
include('includes/config.inc');
include('includes/functions.inc');
/* won't work on php run in safe mode */
set_time_limit($time_limit);
/* preparing header of feedback page */
$html_output = '<?xml version="1.0" encoding="iso-8859-1"?>'. "\r\n";
$html_output .= '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0
Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">'.
"\r\n";
$html_output .= '<html xmlns="http://www.w3.org/1999/xhtml">'. "\r\n";
$html_output .= '<head><title>XeoPort ' . $version_nr . '</title>'.
"\r\n";
$html_output .= '<meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-1" />'. "\r\n";
$html_output .= '<meta name="author" content="Xeoman" />'. "\r\n";
$html_output .= '<meta name="copyright" content="2002 Xeoman" />'.
"\r\n";
$html_output .= '<meta name="robots" content="index, follow" />'.
"\r\n";
$html_output .= '<meta name="keywords" content="XeoPort import IMAP
POP3 email messages PHP MySQL module Xeoman" />'. "\r\n";
$html_output .= '<meta name="description" content="XeoPort ' .
$version_nr . ' - an PHP script to import and backup IMAP or POP3
email messages into MySQL." />'. "\r\n";
$html_output .= '<meta name="MSSmartTagsPreventParsing" content="TRUE"
/>'. "\r\n";
$html_output .= '<meta http-equiv="imagetoolbar" content="no" />'.
"\r\n";
$html_output .= '<link href="includes/xeoport.css" rel="stylesheet"
type="text/css" />'. "\r\n";
$html_output .= '</head><body>'. "\r\n";
$html_output .= '<table width="100%" border="0" cellpadding="3"
cellspacing="1">'. "\r\n";
$html_output .= '<tr class="report"><th colspan="5" class="report"><br
/><h1 class="report">XeoPort ' . $version_nr . '</h1><br
/></th></tr>'. "\r\n";
$html_output .= '<tr class="status"><td colspan="5"
class="status"><p>XeoPort started on ' . date("Y-m-d [H:i:s]") .
'</p>'. "\r\n";
/*__________________ open mailbox or exit if we can not
___________________________*/
$inbox = @imap_open ('{' . $mail_host . '/' . $mail_protocol . '}',
$mail_user, $mail_pass);
if (!$inbox) {
/* okay we give some feedback now */
if ($html_feedback == TRUE) {
$html_output .= '<p class="status">XeoPort could not connect:
<strong>' . imap_last_error() . '</strong></p></td></tr>'. "\r\n";
$html_output .= '</table></body></html>';
echo $html_output;
$html_output = NULL;
}
@imap_close($inbox);
exit;
}
$html_output .= '<p class="status">XeoPort sucessfully connected to
<strong>' . $mail_host . '</strong></p>'. "\r\n";
/*__________________ count messages and exit if inbox is empty
___________________________*/
$total = @imap_num_msg($inbox);
if ($total < 1) {
/* okay we give some feedback now */
if ($html_feedback == TRUE) {
$html_output .= '<p class="status">No messages found on <strong>' .
$mail_host . '</strong>. Disconnected on ' . date("Y-m-d [H:i:s]") .
'</p></td></tr>'. "\r\n";
$html_output .= '</table></body></html>';
echo $html_output;
$html_output = NULL;
}
@imap_close($inbox);
exit;
}
/*__________________ open mysql-link or exit if we can not / echo
result / create table ___________________________*/
$sql_link = mysql_connect ("$sql_host", "$sql_user", "$sql_pass")
or exit;
mysql_select_db("$sql_db");
$html_output .= '<p class="status">XeoPort sucessfully connected to
database <strong>' . $sql_db . '</strong></p>'. "\r\n";
/* check if table exists if not create it */
$$sql_table_ok = @mysql_num_rows(@mysql_query("SELECT * FROM
$sql_table"));
if ($$sql_table_ok == FALSE) {
$sql_createtable = 'CREATE TABLE ' . $sql_table . ' (
xp_nr int(11) NOT NULL auto_increment,
xp_id tinytext,
xp_updated bigint(20) default NULL,
xp_md5 tinytext,
xp_time_unix bigint(20) default NULL,
xp_time_iso time default NULL,
xp_date_iso date default NULL,
xp_date_full tinytext,
xp_from_name tinytext,
xp_from_address tinytext,
xp_from_full tinytext,
xp_from_replyto tinytext,
xp_to_name tinytext,
xp_to_address tinytext,
xp_to_full tinytext,
xp_subject_text text,
xp_subject_inreplyto tinytext,
xp_header_raw longtext,
xp_body_raw longtext,
xp_body_text longtext,
xp_attachments tinytext,
xp_size smallint(6) default NULL,
xp_type tinytext,
PRIMARY KEY (xp_nr)
) TYPE=MyISAM;';
$sql_ok = @mysql_query($sql_createtable);
if ($sql_ok == TRUE) {
$html_output .= '<p class="status">Table <strong>' . $sql_table .
'</strong> successfully created</p>'. "\r\n";
} else {
$html_output .= '<p class="status">Table <strong>' . $sql_table .
'</strong> could NOT be created<br />Please create it manually<br
/>XeoPort will exit now.</p></td></tr>'. "\r\n";
$html_output .= '</table></body></html>';
/* okay we give some feedback now */
if ($html_feedback == TRUE) {
echo $html_output;
}
exit;
}
}
if ($insert_raw == TRUE) {
$html_output .= '<p class="status">All headers and the entire message
will be imported into <strong>' . $sql_db . '</strong></p></td></tr>'.
"\r\n";
} else {
$html_output .= '<p class="status">Only the text part of messages
will be imported into <strong>' . $sql_db . '</strong></p></td></tr>'.
"\r\n";
}
/* okay we give some feedback now */
if ($html_feedback == TRUE) {
echo $html_output;
$html_output = NULL;
}
/* timer for the entire script */
$time_looper = timer_start();
/*__________________ now we actually loop thru the messages on server
___________________________*/
for($x=1; $x <= $total; $x++) {
$time_scriptstart = timer_start();
/* first all vars from the previous mail got killed */
$html_output = NULL;
$structure = NULL;
$headers = NULL;
$xp_id = NULL;
$xp_md5 = NULL;
$xp_time_unix = NULL;
$xp_date_full = NULL;
$xp_subject_text = NULL;
$xp_subject_inreplyto = NULL;
$xp_from_full = NULL;
$xp_from_name = NULL;
$xp_from_address = NULL;
$xp_from_replyto = NULL;
$xp_to_full = NULL;
$xp_to_name = NULL;
$xp_to_address = NULL;
$xp_header_raw = NULL;
$xp_body_raw = NULL;
$xp_body_text = NULL;
$xp_attachments = NULL;
$xp_size = NULL;
$xp_type = NULL;
$parts_type = NULL;
$parts_encoding = NULL;
$parts_size = NULL;
$parts_filename = NULL;
$parts_filesize = NULL;
$parts_structure = NULL;
$sql_ok = NULL;
$temp_html_key = NULL;
$temp_p = NULL;
$temp_b = NULL;
$temp_s = NULL;
$temp_t = NULL;
$temp_y = NULL;
$temp_z = NULL;
$temp_k = NULL;
$temp_v = NULL;
/* get header and structure */
$headers = imap_header($inbox, $x);
$structure = imap_fetchstructure($inbox, $x);
/* initiate most of our vars */
$xp_id = $headers->message_id;
$xp_time_unix = $headers->udate;
$xp_time_iso = date("H:i:s", $xp_time_unix);
$xp_date_iso = date("Y-m-d", $xp_time_unix);
$xp_date_full = $headers->Date;
$xp_subject_text = decode_header($headers->subject);
if (strlen($xp_subject_text) > 30 && strpos(substr($xp_subject_text,
0, 30), " ") < 1) {
$xp_subject_show = substr($xp_subject_text, 0, 30) . ' ' .
substr($xp_subject_text, 31);
} else {
$xp_subject_show = $xp_subject_text;
}
$xp_subject_inreplyto = $headers->in_reply_to;
$xp_from_full = decode_header($headers->fromaddress);
$xp_from_address = get_substring($xp_from_full, '<', '>');
$xp_from_name = get_name($xp_from_full);
$xp_from_replyto = decode_header($headers->reply_toaddress);
$xp_to_full = decode_header($headers->toaddress);
$xp_to_address = get_substring($xp_to_full, '<', '>');
$xp_to_name = get_name($xp_to_full);
/* leave the imap-prefs-file alone */
if (substr_count($xp_from_name, "Mail System Internal Data") > 0) {
continue;
}
/* construct message-id if missing */
if (!$xp_id) {
$xp_id = md5(imap_fetchheader($inbox, $x));
}
/* unified id consisting only of ascii for later callback*/
$xp_md5 = md5($xp_id);
/*__________________ here we decide if we insert the email into
database or skip that part ___________________________*/
$html_output .= '<tr class="col">'. "\r\n";
$html_output .= '<td class="col1nr">' . $x . ' / ' . $total .
'</td>'. "\r\n";
$html_output .= '<td class="col2subject"><em>' .
htmlspecialchars($xp_from_name) . '</em> ==> <em>' .
htmlspecialchars($xp_to_name) . '</em><br /><strong>' .
htmlspecialchars($xp_subject_show) . '</strong></td>'. "\r\n";
$counter_rows = mysql_num_rows(mysql_query("SELECT * from $sql_table
WHERE xp_id='$xp_id'"));
if ($counter_rows > 0) {
/* if the mail is already in the database skip the rest */
// mysql_query("UPDATE $sql_table SET xp_id_pulled = '1' WHERE
xp_id='$xp_id'");
$counter_found++;
$html_output .= '<td class="col3parts"> </td>'. "\r\n";
$html_output .= '<td class="col4indatabase">in database</td>'.
"\r\n";
$html_output .= '<td class="col5timer">' .
number_format(timer_stop($time_scriptstart), 2, ',', '.') .
'</td></tr>'. "\r\n";
/* okay we give some feedback now */
if ($html_feedback == TRUE) {
echo $html_output;
$html_output = NULL;
}
/* and delete the message from inbox or not according to prefs */
if ($message_delete == TRUE) {
imap_delete($inbox, $x);
}
continue;
}
/*__________________ get the structure of the mail, count its parts
and split it up ___________________________*/
$xp_header_raw = imap_fetchheader($inbox, $x);
$parts = $structure->parts;
$parts_count = count($parts);
/*__________________ loop thru all parts and subparts of message and
build arrays accordingly ___________________________________*/
for($temp_z=0; $temp_z<$parts_count; $temp_z++) {
$temp_p = NULL;
$temp_b = NULL;
$parts_type_main = NULL;
$parts_subtype_main = NULL;
if ($parts[$temp_z]->type == "") {
$parts[$temp_z]->type = 0;
}
$temp_y = $temp_z + 1;
$parts_number = '_' . $temp_y;
$parts_type_main = strtolower($type[$parts[$temp_z]->type]);
$parts_type["$parts_number"] = $parts_type_main . '/' .
strtolower($parts[$temp_z]->subtype);
$parts_encoding["$parts_number"] =
$encoding[$parts[$temp_z]->encoding];
$parts_size["$parts_number"] = $parts[$temp_z]->bytes;
if (strtolower($parts[$temp_z]->disposition) == "attachment") {
$temp_b = $parts[$temp_z]->dparameters;
if(is_array($temp_b) || is_object($temp_b)) {
reset($temp_b);
while (list(, $temp_p) = each ($temp_b)) {
if ($temp_p->attribute == "FILENAME") {
$xp_attachments .= decode_header($temp_p->value) . ' [' .
ceil($parts[$temp_z]->bytes / 1024) . ' KB]' . $line_break;
$parts_filename["$parts_number"] =
decode_header($temp_p->value);
$parts_filesize["$parts_number"] = $parts[$temp_z]->bytes;
}
}
}
}
/* if there are inline parts dig deeper */
if ($parts_type_main == 'multipart') {
$parts_sub = $parts[$temp_z]->parts;
$parts_sub_count = count($parts_sub);
for ($temp_s = 0; $temp_s < $parts_sub_count; $temp_s++) {
$temp_t = $temp_s + 1;
$parts_sub_number = $parts_number . '.' . $temp_t;
$parts_subtype_main =
strtolower($type[$parts_sub[$temp_s]->type]);
$parts_type["$parts_sub_number"] = $parts_subtype_main . '/' .
strtolower($parts_sub[$temp_s]->subtype);
$parts_encoding["$parts_sub_number"] =
strtolower($encoding[$parts_sub[$temp_s]->encoding]);
$parts_size["$parts_sub_number"] = $parts_sub[$temp_s]->bytes;
/* 3level parts are rare but we want to be sure */
if ($parts_subtype_main == 'multipart') {
$parts_subsub = $parts_sub[$temp_s]->parts;
$parts_subsub_count = count($parts_subsub);
for ($temp_m = 0; $temp_m < $parts_subsub_count; $temp_m++) {
$temp_n = $temp_m + 1;
$parts_subsub_number = $parts_sub_number . '.' . $temp_n;
$parts_type["$parts_subsub_number"] =
strtolower($type[$parts_subsub[$temp_m]->type]) . '/' .
strtolower($parts_subsub[$temp_m]->subtype);
$parts_encoding["$parts_subsub_number"] =
strtolower($encoding[$parts_subsub[$temp_m]->encoding]);
$parts_size["$parts_subsub_number"] =
$parts_subsub[$temp_m]->bytes;
}
}
}
}
}
/*__________________ get the parts of the message we want
__________________________________________________ ___*/
if (is_array($parts_type)) {
while (list ($key, $val) = each ($parts_type)) {
if (strlen($key) < 3) {
$parts_structure .= '<strong>' . str_replace("_", "", $key) .
'</strong>';
} else {
$parts_structure .= ' <strong>' .
str_replace("_", "", $key) . '</strong>';
}
$parts_structure .= ' _ ' . $val . ' <em>' . $parts_encoding[$key]
.. ' _ </em> [' . $parts_size[$key] . ']<br />';
if ($val == 'text/plain' || $val == 'message/rfc822') {
$xp_body_text = decode_text(imap_fetchbody($inbox, $x,
str_replace("_", "", $key)), $parts_encoding[$key]);
}
/* we need this just in case message has only html-part */
if ($val == 'text/html') {
$temp_html_key = $key;
}
}
/* if the array is empty there's only text so we can simply get the
body-part */
} else {
/* decode if body is encoded */
if ($structure->encoding > 0) {
$xp_body_text = decode_text(imap_body($inbox, $x),
$encoding[$structure->encoding]);
$parts_structure .= '<strong>0</strong> _ text/plain <em>' .
$encoding[$structure->encoding] . '</em> _ [' . $structure->bytes .
']<br />';
} else {
$xp_body_text = imap_body($inbox, $x);
$parts_structure .= '<strong>0</strong> _ text/plain <em>7bit</em>
_ [' . $structure->bytes . ']<br />';
}
}
/* if we have no text till now we try to check for the html-part */
if (($xp_body_text == '') && ($temp_html_key)) {
$xp_body_text = strip_tags(decode_text(imap_fetchbody($inbox, $x,
str_replace("_", "", $temp_html_key)),
$parts_encoding[$temp_html_key]));
}
/* the raw email will be saved or not according to prefs */
if ($insert_raw == TRUE) {
$xp_header_raw = imap_fetchheader($inbox, $x);
$xp_body_raw = imap_body($inbox, $x);
}
/* replacing line breaks according to prefs */
$xp_body_text = preg_replace("/(\015\012)|(\015)|(\012)/",
"$line_break", $xp_body_text);
$xp_attachments = str_replace("$line_break$line_break","$line_break" ,
$xp_attachments);
/* calculating the message size */
if (is_array($parts_size)) {
$xp_size = ceil(array_sum($parts_size) / 1024);
} else {
$xp_size = ceil($structure->bytes / 1024);
}
/* this will make all data safe for mysql */
if ($conf_magicquotes == 0) {
foreach($GLOBALS as $temp_k=>$temp_v) {
if (substr_count($temp_k, "xp_") > 0) {
$GLOBALS[$temp_k] = addslashes($temp_v);
}
}
}
if ($xp_body_text == '') {
$counter_empty++;
$html_output .= '<td class="col3parts">' . $parts_structure .
'</td>'. "\r\n";
$html_output .= '<td class="col4empty">empty</td>'. "\r\n";
$html_output .= '<td class="col5timer">' .
number_format(timer_stop($time_scriptstart), 2, ',', '.') .
'</td></tr>'. "\r\n";
/* okay we give some feedback now */
if ($html_feedback == TRUE) {
echo $html_output;
$html_output = NULL;
}
}
$sql_insertstring = "INSERT INTO $sql_table(xp_id, xp_md5,
xp_time_unix, xp_time_iso, xp_date_iso, xp_date_full, xp_from_name,
xp_from_address, xp_from_full, xp_from_replyto, xp_to_name,
xp_to_address, xp_to_full, xp_subject_text, xp_subject_inreplyto,
xp_header_raw, xp_body_raw, xp_body_text, xp_attachments, xp_size,
xp_type) VALUES ('$xp_id', '$xp_md5', '$xp_time_unix', '$xp_time_iso',
'$xp_date_iso', '$xp_date_full', '$xp_from_name', '$xp_from_address',
'$xp_from_full', '$xp_from_replyto', '$xp_to_name', '$xp_to_address',
'$xp_to_full', '$xp_subject_text', '$xp_subject_inreplyto',
'$xp_header_raw', '$xp_body_raw', '$xp_body_text', '$xp_attachments',
'$xp_size', '$xp_type')";
/* check if everything went smooth */
$sql_ok = mysql_query($sql_insertstring);
if ($sql_ok == TRUE) {
$counter_inserted++;
$counter_size += $xp_size;
$html_output .= '<td class="col3parts">' . $parts_structure .
'</td>'. "\r\n";
$html_output .= '<td class="col4inserted">inserted</td>'. "\r\n";
$html_output .= '<td class="col5timer">' .
number_format(timer_stop($time_scriptstart), 2, ',', '.') .
'</td></tr>'. "\r\n";
/* okay we give some feedback now */
if ($html_feedback == TRUE) {
echo $html_output;
$html_output = NULL;
}
if ($message_delete == TRUE) {
imap_delete($inbox, $x);
}
} elseif ($sql_ok == FALSE) {
$counter_sqlerrors++;
$html_output .= '<td class="col3parts">' . $parts_structure .
'</td>'. "\r\n";
$html_output .= '<td class="col4failed">failed to insert</td>'.
"\r\n";
$html_output .= '<td class="col5timer">' .
number_format(timer_stop($time_scriptstart), 2, ',', '.') .
'</td></tr>'. "\r\n";
/* okay we give some feedback now */
if ($html_feedback == TRUE) {
echo $html_output;
$html_output = NULL;
}
}
/* we're thru with all the messages */
}
/* stop the timer for the script */
$time_counter = timer_stop($time_looper);
/* gather some final information about errors, inserts etc */
$html_output .= '<tr class="status"><td colspan="5" class="status">' .
"\r\n";
$html_output .= '<p class="status">' . $total . ' messages on
<strong>' . $mail_host . '</strong></p>' . "\r\n";
$html_output .= '<p class="status">Already in database: <strong>' .
$counter_found . '</strong></p>' . "\r\n";
$html_output .= '<p class="status">Inserted into database: <strong>'
.. $counter_inserted . '</strong></p>' . "\r\n";
$html_output .= '<p class="status">MySql-errors (not inserted):
<strong>' . $counter_sqlerrors . '</strong></p>' . "\r\n";
$html_output .= '<p class="status">Empty messages: <strong>' .
$counter_empty . '</strong></p>' . "\r\n";
$html_output .= '<p class="status">Overall processing time: <strong>'
.. round($time_counter, 1) . '</strong> sec</p>' . "\r\n";
$html_output .= '<p class="status">Seconds per message: <strong>' .
round(($time_counter / $x), 3) . '</strong></p></td></tr>' . "\r\n";
$html_output .= '</table></body></html>';
/* give some final feedback */
if ($html_feedback == TRUE) {
echo $html_output;
$html_output = NULL;
}
/* close mysql-connection */
mysql_close ($sql_link);
/* kill all messages marked as deleted on server and/ or close
connection */
if ($message_delete == TRUE) {
imap_close($inbox, CL_EXPUNGE);
}
imap_close($inbox);
?>
