Here's the script - it's a thing of beauty, really:
<?php
// phpBB -> ListMailPRO script
// This script should be run daily before dailymail or more frequently as desired
// Cron commandline: /usr/bin/wget -O /dev/null -T 0 http://example.com/mail/phpbb2lmp.php?pw=1234 1>/dev/null 2>/dev/null
set_time_limit(0);
include('./config.php');
include('./admin.php');
// exit if ListMail admin.php is not loaded
if(!function_exists('adminheader')) exit('please make sure ListMail includes are available');
// CONFIG
// phpBB database connection
$phpbb_host = 'localhost';
$phpbb_user = 'USERNAME';
$phpbb_pass = 'PASSWORD';
$phpbb_db = 'DATABASE';
// list to add phpbb users to
$list='4';
// remove ListMail removed/bounced/unconfirmed from phpBB (set to false or '' if not)
$remove_lmp=1;
// remove phpBB users from listmail who are no longer in phpBB
// note: add a MySQL index to the "phpbb_users" table "user_email" field for best results
$remove_phpbb=1;
// notify of removals and provide a phpBB restore URL (enter email or set to false or '' if not)
$remove_notify='YOU@EXAMPLE.COM';
// ListMail followup seq and delay
$seq=1;
$del=0;
// phpBB database table prefix
$phpbbprefix='phpbb_';
// password for accessing this script (browse with ?pw=PASSWORD)
$password = 'PASSWORD';
// LIVE - write changes (set to false or '' if not)
$live=1;
// test email - always runs live even in demo mode
$test_email='YOURTEST@EXAMPLE.COM';
// END CONFIG, START SCRIPT
if($_GET['pw']<>$password) exit('Access denied - please use the password');
if(strstr($_SERVER['SERVER_PROTOCOL'],'HTTPS')) $baseurl='https'; else $baseurl='http';
$baseurl.="://".$_SERVER['HTTP_HOST'].$_SERVER['SCRIPT_NAME'];
echo "Config: baseurl=$baseurl list=$list remove_lmp=$remove_lmp remove_phpbb=$remove_phpbb remove_notify=$remove_notify phpbbprefix=$phpbbprefix live=$live<br>";
if(!$live) echo "<b>DEMO MODE - Not executing MySQL queries or sending notifications EXCEPT for $test_email</b><br>"; else echo "<b>LIVE MODE - Executing MySQL queries and sending notifications!</b><br>";
// connect to phpbb database
$link2 = mysql_connect($phpbb_host,$phpbb_user,$phpbb_pass) or die(mysql_error());
mysql_select_db($phpbb_db,$link2) or die(mysql_error());
// START RESTORE
if($restore){
if(!$_GET['datastr']) exit('No user data');
$values=explode('*!*',gzuncompress(base64_decode($_GET['datastr'])));
foreach($values as $k=>$v) $values[$k]=addslashes($v);
echo "Restoring user \"".$values[32]."\"<br>Reading datastr...";
echo "<pre>"; print_r($values); echo "</pre>";
$cmd = "insert into ".$phpbbprefix."users values('".implode("','",$values)."');";
echo "SQL=<b>$cmd</b><br>";
if($live || $values[32]==$test_email) mysql_query($cmd,$link2) or die(mysql_error());
if($_GET['ps']){
$ps=explode(':',gzuncompress(base64_decode($_GET['ps'])));
echo "<br>Restoring posts...<br>";
foreach($ps as $id){
$cmd = 'update '.$phpbbprefix.'posts set poster_id = \''.$values[0].'\', post_username = \'\' where post_id = \''.$id.'\'';
echo "SQL=<b>$cmd</b><br>";
if($live || $values[32]==$test_email) mysql_query($cmd,$link2) or die(mysql_error());
}
}
if($_GET['ts']){
$ts=explode(':',gzuncompress(base64_decode($_GET['ts'])));
echo "<br>Restoring topics...<br>";
foreach($ts as $id){
$cmd = 'update '.$phpbbprefix.'topics set topic_poster = \''.$values[0].'\' where topic_id = \''.$id.'\'';
echo "SQL=<b>$cmd</b><br>";
if($live || $values[32]==$test_email) mysql_query($cmd,$link2) or die(mysql_error());
}
}
exit('Done restoring user');
}
// END RESTORE
// START PHPBB2LMP SCRIPT
// check ListMail for removed and bounced users and remove from phpBB and LMP
if($remove_lmp){
echo "<br>Checking ListMail for removed, bounced or unconfirmed users on list $list...<br>";
flush();
$count=0;
$rows = mysql_query("select id,email,cnf from lm_users where list = '$list' and cnf <> '1';",$link) or die(mysql_error());
if(@mysql_num_rows($rows)>0){
// delete from phpbb
while(list($id,$email,$cnf)=mysql_fetch_row($rows)){
$count++;
if($cnf=='2') $found='removed'; elseif($cnf=='3') $found='bounced'; else $found='inactive';
echo "Found $found user \"$email\", deleting... ";
$row = mysql_query("select * from ".$phpbbprefix."users where user_email like '".addslashes($email)."';",$link2);
if(@mysql_num_rows($row)>0){
// build restore URL, first get all of the users' phpBB field values
$rowarr = mysql_fetch_array($row,MYSQL_NUM);
mysql_data_seek($row,0);
$rowarr2 = mysql_fetch_array($row,MYSQL_ASSOC);
$restore_url = $baseurl."?restore=1&pw=$password&datastr=".urlencode(base64_encode(gzcompress(implode('*!*',$rowarr),9)));
// graceful phpBB deletion, taken from the phpBB source
// get poster_id for this user
$post_ids=array();
$cmd = 'select post_id from '.$phpbbprefix.'posts where poster_id = \''.$rowarr2['user_id'].'\'';
$rows2 = mysql_query($cmd) or die(mysql_error());
echo "Updating ".@mysql_num_rows($rows2)." posts, ";
if(@mysql_num_rows($rows2)>0){
while(list($post_id)=mysql_fetch_row($rows2)){
$post_ids[]=$post_id;
$cmd = 'update '.$phpbbprefix.'posts set poster_id = \'-1\', post_username = \'' . str_replace("\\'", "''", addslashes($rowarr2['username'])) . '\' where post_id = \''.$post_id.'\'';
// echo "SQL=<b>$cmd</b><br>";
if($live || $values[32]==$test_email) mysql_query($cmd,$link2) or die(mysql_error());
}
}
// get topic_poster for this user
$topic_ids=array();
$cmd='select topic_id from '.$phpbbprefix.'topics where topic_poster = \''.$rowarr2['user_id'].'\'';
$rows2 = mysql_query($cmd) or die(mysql_error());
echo "Updating ".@mysql_num_rows($rows2)." topics<br>";
if(@mysql_num_rows($rows2)>0){
while(list($topic_id)=mysql_fetch_row($rows2)){
$topic_ids[]=$topic_id;
$cmd = 'update '.$phpbbprefix.'topics set topic_poster = \'-1\' where topic_id = \''.$topic_id.'\'';
//echo "SQL=<b>$cmd</b><br>";
if($live || $values[32]==$test_email) mysql_query($cmd,$link2) or die(mysql_error());
}
}
// script does not currently support remove/restore of moderator groups OR user votes
// add post and topic ids to restore URL
if(count($post_ids)>0) $restore_url.='&ps='.urlencode(base64_encode(gzcompress(implode(':',$post_ids),9)));
if(count($topic_ids)>0) $restore_url.='&ts='.urlencode(base64_encode(gzcompress(implode(':',$topic_ids),9)));
$cmd = "delete from ".$phpbbprefix."users where user_email like '".addslashes($email)."';";
echo "SQL=<b>$cmd</b><br>";
// delete the user from phpBB
if($live || $email == $test_email){
mysql_query($cmd,$link2) or die(mysql_error());
// send notification and forum restore link by email
if($remove_notify){
mail($remove_notify,'User removed from LMP/phpBB: '.$rowarr[32],"User \"".$rowarr[32]."\" removed from phpBB because they were found $found in ListMail.\n\nForum stats:\n\nuser_active: ".$rowarr[1]."\nuser_lastvisit: ".nicedate($rowarr[6])." ago\nuser_posts: ".$rowarr[9]."\n\nClick the following link to restore the user:\n\n$restore_url","From: \"phpbb2lmp\" <noreply@".$_SERVER['HTTP_HOST'].">\nReturn-path: <noreply@".$_SERVER['HTTP_HOST'].">");
}
}
echo "<a href=$restore_url>Restore User</a><br>";
} else {
echo "Not found in forum<br>";
}
echo "removing from ListMail DB... ";
$cmd = "delete from lm_users where id = '$id';";
echo "SQL=<b>$cmd</b><br>";
if($live || $email == $test_email) mysql_query($cmd,$link);
}
echo "$count users removed from phpBB<br>";
} else {
echo "No inactive users to remove from phpBB<br>";
}
}
// check all active ListMail users and remove from ListMail if not found in phpBB
if($remove_phpbb){
echo "<br>Checking ListMail list $list for active users who are no longer in phpBB<br>";
flush();
$rows = mysql_query("select id,email from lm_users where list = '$list' and cnf = '1';",$link) or die(mysql_error());
$count=0;
if(@mysql_num_rows($rows)>0){
while(list($id,$email)=mysql_fetch_row($rows)){
list($test)=@mysql_fetch_row(mysql_query("select user_id from ".$phpbbprefix."users where user_email = '".addslashes($email)."';",$link2));
if(!$test){
$count++;
echo "$email found in ListMail but not phpBB... removing<br>";
$cmd = "delete from lm_users where id = '$id';";
echo "SQL=<b>$cmd</b><br>";
if($live || $email == $test_email) mysql_query($cmd,$link);
}
}
}
echo "$count users removed from ListMail<br>";
}
// check all phpBB users, insert into ListMail if not there already
$rows = mysql_query("select username,user_email from ".$phpbbprefix."users where 1;",$link2) or die(mysql_error());
if(@mysql_num_rows($rows)==0) exit('no active users found in phpbb');
echo "<br>".@mysql_num_rows($rows)." users found in phpBB. Checking to make sure they are in ListMail...<br>";
$count=0;
while(list($username,$user_email)=mysql_fetch_row($rows)){
if(!$user_email) continue;
// check listmail status
$row = mysql_query("select id from lm_users where list = '$list' and email = '".addslashes($user_email)."' and cnf = '1';",$link);
list($added)=@mysql_fetch_row($row);
if(!$added){
echo "\"$username\" <$user_email> - Not in ListMail... adding<br>";
$today = date("Y-m-d");
// create uid
$uniq = ''; while(!$uniq){ $uniq_str = unique_id(7); if(@mysql_num_rows(mysql_query("select id from $utable where uid = '$uniq_str'",$link))==0) $uniq=1; }
// null unsafe vars
for($j=1;$j<=10;$j++) ${'user'.$j}=''; $lname='';
// insert into ListMail
$cmd = "insert into lm_users values ('null','$uniq_str','$list','".addslashes($username)."','$lname','".addslashes($user_email)."','$user1','$user2','$user3','$user4','$user5','$user6','$user7','$user8','$user9','$user10','$seq','$del','1','$today','Unknown','From Forum','1','0')";
echo "SQL=<b>$cmd</b><br>";
if($live || $user_email==$test_email) mysql_query($cmd,$link) or die(mysql_error());
$count++;
} else {
// echo "\"$username\" <$user_email> - Already in ListMail<br>";
}
}
echo "$count users added to ListMail<br><br>Script done!";
// switch back to the LMP database...
mysql_select_db($sqldb,$link);
// END SCRIPT, START FUNCTIONS
// convert seconds into #y#m#d
function nicedate($in){
$s = time()-$in;
$out=''; $d=''; $h=''; $m='';
if($s>=86400){ $d = floor($s/86400); $s = $s - ($d * 86400); $out.=$d.'d'; }
if($s>=3600){ $h = floor($s/3600); $s = $s - ($h * 3600); if($out) $out.=' '; $out.=$h.'h'; }
if($s>=60 && !$d){ $m = floor($s/60); $s = $s - ($m * 60); if($out) $out.=' '; $out .= $m.'m'; }
if($s>0 && !$h){ if($out) $out.=' '; $out .= $s.'s'; }
return $out;
}
// END phpBB -> ListMailPRO script
?>
Good luck!