PHPBB3 Integration with users table - Salman A’s Method
From http://www.phpbb.com/community/viewtopic.php?f=64&t=993475
Scenario:
I have one existing system with a “user” table. The key elements of this table:
UserID > Auto increment primary key
Email > varchar with unique constraint; this also acts as the login for the “website”
Password > varchar
IsActive > integer for storing user’s “active” status (null or 1)
I also have a fresh phpbb3 installation into which i need to integrate the website users.
I also have to create a list of categories and forums that match the structure of my main websites main categories and sub-categories - a 1 to 1 match. I will discuss this if necessary.
Additional task: I have to integrate the website’s interface elements into the phpbb theme. I used simple approach. I will probably not discuss it as there are very easy solutions already available.
Mapping:
Have to create mapping between the users inside the two tables. These will be referred as:
user (website users)
- and -
phpbb_user (phpbb users)
The question, how should I map website users onto phpbb users? There are many ways to do this. I chose the simplest one to implement. Map user.UserID to phpbb_users.user_id. This way a user can always be looked up into phpbb users and vice versa.
There is one problem however. PHPBB3 fresh installation creates about 50 users by default. These include the super administrator, anonymous and search bots. That means user_id 1 to 50 are unavailable.
I could have created 50 fake users in the user table so that any further website users can be mapped properly. However, I already had about 250+ users by the time integration was supposed to be made. So I chose to map by adding a constant - 1000. So:
Website user #1 becomes phpbb user #1001
Website user #2 becomes phpbb user #1002
…
and so on.
This way I don’t have to run any lookup queries when I have for find phpbb user given website user id and vice versa.
Drawback:
The process creates clones of website users inside phpbb. It should not go the other way round. So, before integration you should disable phpbb “registration”. You can do this from the phpbb admin section. When you do this, no new user can “register” him/herself. The registration should be done on the main website. Later in the discussion i will provide the hack for creating the user.
Next step is to write functions that ease the task of importing the users. While we are on it, it is also desirable to create another function that synchronizes information of existing users. For example, if someone changes his/her email address on the main website, we should have the mechanism that copies the change into the corresponding phpbb user record. These the fields I am interested in:
Email, Password, IsActive
There may be other fields too but in my scenario, there aren’t any. I am not storing persons age, date of birth, aim/icq information, location, work information etc. I’ll leave it on phpbb user control panel to allow user to edit phpbb related information.
Besides creating new users, the function must place the newly created user in registered users group. I also hacked into phpbb code to find out there are a few other things that are done when a user is created. Usually the default group settings are copied into the the user record.
Note: The way I am implementing the integration it is *not necessary* to create a clone of user into phpbb immediately upon registration although this is a good idea.
- Next step is to write functions that ease the task of importing the users. While we are on it, it is also desirable to create another function that synchronizes information of existing users. For example, if someone changes his/her email address on the main website, we should have the mechanism that copies the change into the corresponding phpbb user record. These the fields I am interested in:
Email, Password, IsActive
There may be other fields too but in my scenario, there aren’t any. I am not storing persons age, date of birth, aim/icq information, location, work information etc. I’ll leave it on phpbb user control panel to allow user to edit phpbb related information.
Besides creating new users, the function must place the newly created user in registered users group. I also hacked into phpbb code to find out there are a few other things that are done when a user is created. Usually the default group settings are copied into the the user record.
Note: The way I am implementing the integration it is *not necessary* to create a clone of user into phpbb immediately upon registration although this is a good idea.
function phpbb_synchronize_user( $UserID, $Email, $Password, $IsActive )
{
global $cn1;
$message = array( );
$librium_default_group_id = 2;
static $phpbb_config;
static $group_config;
if ( is_array( $phpbb_config ) == false )
{
$phpbb_config = array( );
$query = "SELECT config_name, config_value FROM phpbb_config";
$ident = mysql_query( $query, $cn1 ) or die( mysql_error( $cn1 ) );
while ( $myrow = mysql_fetch_assoc( $ident ) )
{
$phpbb_config[ $myrow[ "config_name" ] ] = $myrow[ "config_value" ];
}
}
if ( is_array( $group_config ) == false )
{
$query = sprintf( “SELECT group_id, group_colour, group_rank, group_avatar, group_avatar_type, group_avatar_width, group_avatar_height FROM phpbb_groups WHERE group_id = %d”, $librium_default_group_id );
$ident = mysql_query( $query, $cn1 ) or die( mysql_error( $cn1 ) );
$group_config = mysql_fetch_assoc( $ident );
}
$bbuser_newid = $UserID + 1000;
$bbuser_email = strtolower( $Email );
$bbuser_passw = $Password;
$bbuser_class = $IsActive ? 0 : 1;
$bbuser_login = $bbuser_email;
$bbuser_login = explode( “@”, $bbuser_login );
$bbuser_login = $bbuser_login[ 0 ];
$bbuser_login = preg_replace( “/[^a-z0-9]/i”, “”, $bbuser_login );
$bbuser_login = $bbuser_login . $bbuser_newid;
$query = sprintf( “SELECT username, username_clean, user_type FROM phpbb_users WHERE user_id = %d”, $bbuser_newid );
$ident = mysql_query( $query, $cn1 ) or die( mysql_error( $cn1 ) );
$myrow = mysql_fetch_assoc( $ident );
if ( $myrow === false )
{
$dataarray = array( );
$dataarray[ "user_id" ] = $bbuser_newid;
$dataarray[ "user_email" ] = $bbuser_email;
$dataarray[ "user_email_hash" ] = crc32( $bbuser_email ) . strlen( $bbuser_email );
$dataarray[ "user_password" ] = md5( $bbuser_passw );
$dataarray[ "username" ] = $bbuser_login;
$dataarray[ "username_clean" ] = $bbuser_login;
$dataarray[ "group_id" ] = $librium_default_group_id;
$dataarray[ "user_type" ] = $bbuser_class;
$dataarray[ "user_colour" ] = $group_config[ "group_colour" ];
$dataarray[ "user_rank" ] = $group_config[ "group_rank" ];
$dataarray[ "user_avatar" ] = $group_config[ "group_avatar" ];
$dataarray[ "user_avatar_type" ] = $group_config[ "group_avatar_type" ];
$dataarray[ "user_avatar_width" ] = $group_config[ "group_avatar_width" ];
$dataarray[ "user_avatar_height" ] = $group_config[ "group_avatar_height" ];
$dataarray[ "user_lang" ] = $phpbb_config[ "default_lang" ];
$dataarray[ "user_timezone" ] = $phpbb_config[ "board_timezone" ];
$dataarray[ "user_dst" ] = $phpbb_config[ "board_dst" ];
$dataarray[ "user_dateformat" ] = $phpbb_config[ "default_dateformat" ];
$dataarray[ "user_style" ] = $phpbb_config[ "default_style" ];
$dataarray[ "user_actkey" ] = “”;
$dataarray[ "user_allow_massemail" ] = 1;
$dataarray[ "user_allow_pm" ] = 1;
$dataarray[ "user_allow_viewemail" ] = 1;
$dataarray[ "user_allow_viewonline" ] = 1;
$dataarray[ "user_emailtime" ] = 0;
$dataarray[ "user_full_folder" ] = -3;
$dataarray[ "user_inactive_reason" ] = $bbuser_class ? 3 : 0;
$dataarray[ "user_inactive_time" ] = $bbuser_class ? time( ) : 0;
$dataarray[ "user_interests" ] = “”;
$dataarray[ "user_ip" ] = “”;
$dataarray[ "user_last_privmsg" ] = 0;
$dataarray[ "user_lastmark" ] = time( );
$dataarray[ "user_lastpage" ] = “”;
$dataarray[ "user_lastpost_time" ] = 0;
$dataarray[ "user_lastvisit" ] = 0;
$dataarray[ "user_message_rules" ] = 0;
$dataarray[ "user_new_privmsg" ] = 0;
$dataarray[ "user_notify" ] = 0;
$dataarray[ "user_notify_pm" ] = 1;
$dataarray[ "user_notify_type" ] = 0;
$dataarray[ "user_occ" ] = “”;
$dataarray[ "user_options" ] = 895;
$dataarray[ "user_pass_convert" ] = 0;
$dataarray[ "user_permissions" ] = “”;
$dataarray[ "user_posts" ] = 0;
$dataarray[ "user_sig" ] = “”;
$dataarray[ "user_sig_bbcode_bitfield" ] = “”;
$dataarray[ "user_sig_bbcode_uid" ] = “”;
$dataarray[ "user_unread_privmsg" ] = 0;
$dataarray[ "user_regdate" ] = time( );
$dataarray[ "user_passchg" ] = time( );
$dataarray[ "user_form_salt" ] = substr( md5( microtime( ) ), 4, 16 );
$query = “”;
foreach ( $dataarray as $column => $value )
{
if ( $query == “” )
{
$query .= “INSERT phpbb_users SET “;
}
else
{
$query .= “, “;
}
$query .= sprintf( “%s = ‘%s’”, $column, addslashes( $value ) );
}
$ident = mysql_query( $query, $cn1 ) or die( mysql_error( $cn1 ) );
$message[ ] = sprintf( “Created user <b>%s</b>”, rtext( $bbuser_login ) );
$query = sprintf( ”
INSERT INTO phpbb_user_group
( group_id, user_id, group_leader, user_pending )
VALUES
( %d, %d, 0, 0 )
“, $librium_default_group_id, $bbuser_newid
);
$ident = mysql_query( $query, $cn1 ) or die( mysql_error( $cn1 ) );
$message[ ] = “› Assigned user to registered users group”;
}
else
{
$dataarray = array( );
$dataarray[ "user_email" ] = $bbuser_email;
$dataarray[ "user_email_hash" ] = crc32( $bbuser_email ) . strlen( $bbuser_email );
$dataarray[ "user_password" ] = md5( $bbuser_passw );
if ( $myrow[ "username" ] == “” || $myrow[ "username_clean" ] == “” )
{
$dataarray[ "username" ] = $bbuser_login;
$dataarray[ "username_clean" ] = $bbuser_login;
}
if ( $myrow[ "user_type" ] != $bbuser_class )
{
$dataarray[ "user_type" ] = $bbuser_class;
$dataarray[ "user_inactive_reason" ] = $bbuser_class ? 3 : 0;
$dataarray[ "user_inactive_time" ] = $bbuser_class ? time( ) : 0;
}
$query = “”;
foreach ( $dataarray as $column => $value )
{
if ( $query == “” )
{
$query .= “UPDATE phpbb_users SET “;
}
else
{
$query .= “, “;
}
$query .= sprintf( “%s = ‘%s’”, $column, addslashes( $value ) );
}
$query .= sprintf( ” WHERE user_id = %d”, $bbuser_newid );
$ident = mysql_query( $query, $cn1 ) or die( mysql_error( $cn1 ) );
if ( mysql_affected_rows( $cn1 ) )
{
$message[ ] = sprintf( “Updated user <b>%s</b>”, rtext( $bbuser_login ) );
}
}
return implode( “<br>\n”, $message );
}
Function description:
Input:
$UserID user id of the user (user = user of the website) whose clone is to be created or whose information is to be updated
$Email is the email address of the user
$Password is the email address of the user
$IsActive is the flag that states whether the user is active or not (1 or NULL)
Local variables:
global $cn1 is a valid, open mysql connection identifier. Its you job to do handle it. Replace with your mysql connection identifier.
$message is An array that will contain strings of information/debug info/whatever
$phpbb_config will contain information from phpbb_config table that will be used later.
$group_config will contain information from phpbb_groups table for group #2 - registered users. This information will be used later.
The above two are made static so that they are faster to fetch if you call the function in a loop (10,000 times for example)
$bbuser_newid contains the id that will be assigned to the new user. As discussed, I wont allow phpbb to generate user ids. Instead I’ll use my own to keep things simple. I just add 1000 to the $UserID.
$bbuser_email same as $Email except transformed - trimmed and lowercased
$bbuser_passw same as $Password
$bbuser_class more like a flag for checking if the user is active or not
$bbuser_login is a “generated” phpbb login name. The steps to create this “generated login” are up to you. I just extract the first part of email address, shave it clean and append the $bbuser_newid to make sure it is unique
Rest of the function is pretty simple. You check for the presence of a phpbb user based on the user id of the website user.
If not found then
- prepare a phpbb user record (most of the process is copied from phpbb native function) and inert
- assign this new user to registered users group
otherwise
- update phpbb user email address, password, username (only in rare cases) and active status
How to use the function:
IMPORTING USER
Loop over all of your users and call the function:
$message = array( );
$query = "SELECT UserID, Email, Password, IsActive FROM user";
$ident = mysql_query( $query, $cn1 ) or die( mysql_error( $cn1 ) );
while ( $myrow = mysql_fetch_assoc( $ident ) )
{
$result = phpbb_synchronize_user( $myrow[ "UserID" ], $myrow[ "Email" ], $myrow[ "Password" ], $myrow[ "IsActive" ] );
if ( $result )
{
$message[ ] = $result;
}
}
UPON USER REGISTRATION
Call the function once, pass the ID of the newly inserted user and additional fields that you just stored in the database:
mysql_query( "INSERT INTO user YADA YADA", $cn1 ) or die( mysql_error( $cn1 ) );
$result = phpbb_synchronize_user( mysql_insert_id( $cn1 ), $_POST[ "Email" ], $_POST[ "Password" ], 1 );
UPON USER PROFILE UPDATE
Call the function once, pass the ID logged in user and additional fields that you just stored in the database:
mysql_query( "UPDATE user YADA YADA WHERE UserID = {$_SESSION["UserID"]}”, $cn1 ) or die( mysql_error( $cn1 ) );
$result = phpbb_synchronize_user( $_SESSION[ "UserID" ], $_POST[ "Email" ], $_POST[ "Password" ], 1 );
Finally, Login:
I hacked into phpbb code and invented this solution for seamless login:
- make sure user is logged in
- synchronize information before phpbb login (optional… use it if you do not call the synchronize function frequently)
- use phpbb functions to start a phpbb session
it kind of goes like this:
#
# since we are not storing phpbb username in the website user table
# we need to look it up
#
$query = sprintf(
“SELECT username FROM phpbb_users WHERE user_id = %d + 1000″,
$_SESSION[ "UserID" ]
);
$ident = mysql_query( $query, $cn1 ) or die( mysql_error( $cn1 ) );
$myrow = mysql_fetch_assoc( $ident );
if ( $myrow === false )
{
die( “PHPBB USER NOT FOUND” );
}
#
# phpbb init
#
define( “IN_PHPBB”, true );
$phpbb_root_path = “./bb/”;
$phpEx = substr( strrchr( __FILE__, “.” ), 1 );
require( $phpbb_root_path . “common.” . $phpEx );
require( $phpbb_root_path . “includes/functions_user.” . $phpEx );
require( $phpbb_root_path . “includes/functions_module.” . $phpEx );
$user->session_begin();
#
# hack that i used from phpbb code
#
$result = $auth->login( $myrow[ "username" ], $_SESSION[ "Password" ] );
if ( $result[ "status" ] == LOGIN_SUCCESS )
{
if ( defined( “IN_CHECK_BAN” ) && $result[ "user_row" ][ "user_type" ] != USER_FOUNDER )
{
return;
}
$successurl = $_GET[ "successurl" ];
if ( $successurl == “” )
{
$successurl = “/bb/”;
}
$successurl = reapply_sid( $successurl );
$successurl = str_replace( “&”, “&”, $successurl );
header( “Location: $successurl” );
exit( 0 );
}
else
{
die( $result[ "error_msg" ] );
}