Synchronizing Joomla 1.5 w/phpBB2

[cpreston]

I know some of you are looking at this and saying "who the heck would want to do that?" Why wouldn't you upgrade to phpbb3 and get the new bridge? (Which works great, BTW.) Well, some of us are still tied to phpBB2 due to mail2forum.com, the software that lets you integrate phpbb w/mailing lists.

I hacked the phpbb login process to work with the Joomla 1.0.15 salting process, but when I upgraded to Joomla 1.5, I found that thing weren't working. (I have a php script that synchronizes the Joomla users, passwords, and email addresses, but once I upgraded new users couldn't log into phpbb.)

So... I figured it out. The problem was that the new salt is bigger than the max size for the default password field in phpbb! So all I did was increase the size of the user_password field in the phpbb_users table, and voila!

Of course, you have to hacked phpbb to work with the salt, which I did by doing this to login.php:

Look for this line:

Code: Select all

                              $storedpw =  $row['user_password'] ;

After it you will see these two lines:

Code: Select all

                                        $hash = $row['user_password'] ;
                                        $cryptpass = md5($password) ;

I surround them with the following if/else loop:

Code: Select all

                                //if the password has a ":" in it
                                if (ereg (":", $storedpw)) {
                                        //split out the salt and the encrypted password
                                        list($hash, $salt) = explode(':', $row['user_password']) ;
                                        //encrypt the password using the salt
                                        $cryptpass = md5($password.$salt);
                                } else {
                                        //it used to just be these two lines
                                        //i added this if/else loop
                                        $hash = $row['user_password'] ;
                                        $cryptpass = md5($password) ;
                                }

I then have a php script that runs every minute in cron that adds new users from joomla to phpbb, and updates their password if they've logged in within the past few minutes. (Cause they might have changed their joomla password when the logged in.)

First you need a joomphpbb_bridge field in your phpbb database with two fields: joom_id and phpbb_id
(This field was actually put there by the old joomla/phpbb bridge, but that doesn't work anymore for me.)

Then you run the following script every minute in cron. (Some of this code was borrowed from the old joomla/phpbb bridge which I no longer use.) You'll notice this script also sends a "welcome aboard" email that mentions that they should have already received a subscription request to our mailing list. That is done via another section of this script. It is is commented out. Look for listname-request to find that section.

Code: Select all

<?php

//joomla_database
//            #Basic user table
//            jos_users table with fields id, username, email
//
//phpbb_database
//            #User table
//            phpbb_users table with fields 
//user_id, username, user_regdate, user_password, user_email, user_active
//            #Group table
//            phpbb_groups table with fields group_name, group_description, 
//group_single_user, group_moderator
//            #Associates user with own group
//            phpbb_user_group table with fields user_id, group_id, user_pending
//            #Associates a joomla user_id with a phpbb_id
//            joomphpbb_bridge table with fields joom_id, phpbb_id

$password="admin_password";

$db1 = mysql_connect("localhost", "database_user", "$password");
$db2 = mysql_connect("127.0.0.1", "database_user", "$password");

mysql_select_db("joomla_database", $db1) or die ("Couldn't select database.");
mysql_select_db("phpbb_database", $db2) or die ("Couldn't select database.");

$then= date("Y-m-d H:i:s", (time() - 120));
//print "$date \n";

$sql = "SELECT id, username, email, password FROM jos_users WHERE `registerDate` > '". $then . "' OR `lastvisitDate` > '" . $then . "'";
//print "$sql\n";
$results1 = mysql_query($sql, $db1) or die(mysql_error());

while ($row = mysql_fetch_array($results1)){

        $id = $row['id'];
        $username = $row['username'];
        $email = $row['email'];
        $password = $row['password'];

        //print "Updating $username\n";

        $sql = "SELECT * from joomphpbb_bridge where `joom_id` = '$id'";

        $bridge_results = mysql_query($sql,$db2) or die(mysql_error());
        $myrow = mysql_fetch_array($bridge_results) ;
        $phpbb_id = $myrow['phpbb_id'];

        if ( $phpbb_id ) {

        //echo "Found ID: $id, $username, $email, $password\n";
        $sql = "UPDATE `phpbb_users` SET `user_email` = '$email', `username` = '$username', `user_password` = '$password' WHERE `user_id` = $phpbb_id LIMIT 1";

        //echo "$sql\n";
        mysql_query($sql, $db2) or die('Error, update failed');

        } else {

        //If we've never seen them before, we also invite them to the mailing by 
        //Sending a subscription on their behalf
        //Send email invite to mailing list
        //Mail gets sent from them to the mailing list address
        //[email protected];
        //$ladditional_header = "From: New User <$email>";
        //$lsubject='Subscribe';
        //$lmessage='Subscribe';
        //mail($lmail, $lsubject, $lmessage, $ladditional_header );


        //echo "NOT Found ID: $id, $username, $email, $password\n";
        $sql = "SELECT MAX(user_id) FROM `phpbb_users`" ;
        $maxid_results = mysql_query($sql, $db2) or die('Could not get max user id');
        $user_id = mysql_result($maxid_results,0,"max(user_id)") ;
        $user_id++;
        //echo "New user id:$user_id\n";


//------- remove some invalid entries -------------------------------------------------------
//-------- because user_id for phpbb is not auto increment -----------------------------------
$sql = "DELETE FROM joomphpbb_bridge WHERE phpbb_id>=" . $user_id ;
//echo "$sql\n";
$result = mysql_query($sql, $db2) or die('Error');

// user_active is already by default=1 but I prefer to insist
$sql = "INSERT INTO `phpbb_users`  (`user_id`, `username`, `user_regdate`, `user_password`, `user_email`, `user_active`) VALUES ('$user_id', '$username', '" . time() . "', '$password', '$email',  1 ) ";
//echo "$sql\n";
$result = mysql_query($sql, $db2) or die('Error');

//------ is user registration ended  ?  No way!!! We must create a dedicated group and associate it to the user           
$sql = "INSERT INTO `phpbb_groups` (`group_name`, `group_description`, `group_single_user`, `group_moderator`) VALUES ('', 'Personal User', 1, 0)";
//echo "$sql\n";
$result = mysql_query($sql, $db2) or die('Error');

//----- we can determine group_id after, because it is auto increment
        $sql = "SELECT MAX(group_id) FROM `phpbb_groups`" ;
        $maxgid_results = mysql_query($sql, $db2) or die('Could not get max group id');
        $group_id = mysql_result($maxgid_results,0,"max(group_id)") ;
        //echo "Group id $group_id\n";

//----- associate group to user
$sql = "INSERT INTO `phpbb_user_group` (`user_id`, `group_id`, `user_pending`) VALUES ('$user_id', '$group_id', 0)";
//echo "$sql\n";
$result = mysql_query($sql, $db2) or die('Error');

//------ do not forget to associate the joomla user to the phpbb user -----------
//------ we can end transaction now ---------------------------------------------

$sql = "INSERT INTO `joomphpbb_bridge` (`joom_id`, `phpbb_id` ) VALUES ('$id', '$user_id')";
//echo "$sql\n";
$result = mysql_query($sql, $db2) or die('Error');

$additional_header = "From: Admin <[email protected]>";
$subject = "PLEASE READ before using Web Site";

$message = "

Thanks for signing up for Web Site! 

You should have already received an email that asks you to confirm
a request to join the [email protected] mailing list.  
(It will come from [email protected] and have a subject line
that starts with \"confirm\" followed by a random string.) This is an
ANNOUNCEMENT ONLY LIST that I use to send occasional messages to Web
Site users.  An example is when I'm wanting to talk directly to users
of a particular product to confirm how it behaves in the real world.
It has very low traffic and your email address is never seen by anyone.

If you are OK receiving such emails from me, just reply to that email 
or click on the URL in the email to confirm.  If you do not want to join
the list, you do not need to do anything.  The request will expire.

Here are a few tips for using the site.

Blah, blah..

---
My Name
Webmaster, Web Site";

mail($email, $subject, $message, $additional_header );

        }

}

mysql_close($db1);
mysql_close($db2);

?>

[fudgonaut]

I realize this post is old but I just wanted to say thank cpreston you for posting this! Mail2forum for phpbb3 is an abandoned Alpha product, so if you have problems with installation (like me) you're out of luck as far as support.

I wish someone out there would dedicate their php skills to a stable email-to-post+post-to-email extension, I have seen consistent requests for this feature over the years. I'm going to try your code to see if I can finally get a working Joomla-phpbb-mail2forum configuration. Grazie!