Author Topic: phpBB to ListMail Pro integration on Prepress Forum  (Read 118066 times)

prepress forums

  • Posts: 24
    • View Profile
    • http://prepressforum.com
phpBB to ListMail Pro integration on Prepress Forum
« on: August 20, 2007, 07:40:38 pm »
I have several phpBB communities.  DW and I have been working on an integration for phpBB communities like this one and this weeekend got it working pretty nicely.  Although it is most likely to go through a few improvements anf further testing, I went through a live test on my prepress forum last night and it worked very well.

If you need more powerful mail services for your phpBB community, or if you have mailing lists that you would like to move towards a forum community, this is a great new integration.

This is available to any licensed user of ListMail Pro and as soon as it's really dialed in, DW will probably be posting it up soon.  Since DW and I put up the blood and treasure to deliver it, a backlink would be a nice gesture for anybody who uses it.  Don't forget, we deserve a little webmaster Karma ;)

Generally the integration script will do:

1. Loop all inactive (removed, unconfirmed and bounced users) ListMail users on selected list, removing them from phpBB (optional) and deleting them from the ListMail database.  I can create a notification and 'restore user' link to re-add them to phpBB if this ever happens by accident.  This would mean you receive two notifications, one from ListMail and one from the custom phpBB script, which you would need to match up manually based on send time.

2. Loop all users in phpBB, adding them to ListMail on the selected list if
they aren't there already. Should the forum username become the ListMail users' "First Name"?  Do you want a welcome message sent to the users? (I am assuming not.)

I will add more detail on features as we improve this and finish it

I can use it to sync my mail list to my phpBB community, the phpBB is my user table, the integration syncs up my mailing list.  So, I can use powerful mailing list management (ListMail Pro) WITH my phpBB community.

TO MOST COMMUNITIES:
Even if you do not want to mail your members much, there is full bounce handling and the integration can help manage identity and purge of all bogus users accounts whose mail is dead or bogus.

phpBB webmasters have a problem with bogus emails used to sign-up and dead accounts who have non-working emails.  This integration can enable you to use ListMail Pro to mail and verify all user emails, if they bounce, it will facilitate an user deletion process.  Then, it even has an un-do if you delete a user that you want to restore, BAM, re-establishes that user into phpBB again with a click.

Thanks to DW, it's a valuable new tool for all phpBB webmasters and ListMail Pro licensees.

I'm testing this on my Corvette Forums next and will try to firm up this info and make sure this is all ready for whoever needs it.
--
JP

http://prepressforum.com - corvette-forum - http://free-preflight.com - http://printing-quote.us - http://destin-florida-hotel.com - Destin Florida Forum
corvette forum, corvette z06 forum, corvette c6 forum, c5 corvette forum, prepress forum, postcards, brochures, destin florida hotels

weathers

  • Posts: 10
    • View Profile
How do I get - phpbb list mail integration script
« Reply #1 on: February 16, 2008, 10:08:08 pm »
Where can I get this phpbb-listmail integration script?  I cannot locate it on the Prepress Forum.

Also, does it work with phpbb3

Thanks

Larry

DW

  • Administrator
  • Posts: 3787
    • View Profile
    • http://listmailpro.com
phpBB to ListMail Pro integration on Prepress Forum
« Reply #2 on: February 26, 2008, 06:16:49 am »
Here's the script - it's a thing of beauty, really:
Code: [Select]
<?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_emailmysql_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_emailmysql_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_emailmysql_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_emailmysql_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_emailmysql_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_emailmysql_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_emailmysql_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\" &lt;$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_emailmysql_query($cmd,$link) or die(mysql_error());
  
$count++;

 } else {
   
// echo "\"$username\" &lt;$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>&& !$h){ if($out$out.=' '$out .= $s.'s'; }
 return 
$out;
}
// END phpBB -> ListMailPRO script
?>

Good luck!
Dean Wiebe
ListMailPRO Author & Developer - Help | Support | Hosting

weathers

  • Posts: 10
    • View Profile
phpbb-listmail bridge
« Reply #3 on: February 26, 2008, 07:31:26 am »
Does it work with phpBB3?

thanks

Larry