Joomla 1.5 + phpBB3 User Table Integration. Please help!

Discuss the integratoin of phpbb and Joomla! here.

Moderator: General Support Moderators

Forum rules
Locked
theaxiom
Joomla! Fledgling
Joomla! Fledgling
Posts: 4
Joined: Thu May 24, 2007 3:09 am

Joomla 1.5 + phpBB3 User Table Integration. Please help!

Post by theaxiom » Thu May 24, 2007 3:17 am

Here is what I have done to facilitiate a shared user database between phpbb3 rc1 and joomla 1.5.

Can someone please tell me if this is a good way to do this...all my registrations are handled by a third party application, which I can handle separately. I don't know if I changed the right files, and if there is an easier way, or whatever, and I would like to know if there is a better way to accomplish this than the way I have attempted. I want a stable solution, that will be useful to me for a long time. Thanks.

I have made these changes:

Code: Select all

MYSQL PASSWORD FIELD MODIFICATION

includes/functions_user.php

FIND
'user_password'		=> (isset($user_row['user_password'])) ? $user_row['user_password'] : '',

REPLACE
'user_password'		=> (isset($user_row['password'])) ? $user_row['password'] : '',


includes/auth.php

FIND
$sql = 'SELECT user_id, username, user_password, user_passchg, user_email, user_type

REPLACE
$sql = 'SELECT user_id, username, password, user_passchg, user_email, user_type


includes/acp/acp_users.php

FIND
$update_password = ($data['new_password'] && $user_row['user_password'] != md5($data['new_password'])) ? true : false;

REPLACE
$update_password = ($data['new_password'] && $user_row['password'] != md5($data['new_password'])) ? true : false;


includes/auth/auth_apache.php

FIND
$sql = 'SELECT user_id, username, user_password, user_passchg, user_email, user_type

REPLACE
$sql = 'SELECT user_id, username, password, user_passchg, user_email, user_type


includes/auth/auth_db.php

FIND
$sql = 'SELECT user_id, username, user_password, user_passchg, user_pass_convert, user_email, user_type, user_login_attempts

REPLACE
$sql = 'SELECT user_id, username, password, user_passchg, user_pass_convert, user_email, user_type, user_login_attempts

FIND
if (md5($password_old_format) == $row['user_password'] || md5(utf8_to_cp1252($password_old_format)) == $row['user_password'])

REPLACE
if (md5($password_old_format) == $row['password'] || md5(utf8_to_cp1252($password_old_format)) == $row['password'])

FIND
SET user_password = \'' . $db->sql_escape(md5($password_new_format)) . '\',

REPLACE
SET password = \'' . $db->sql_escape(md5($password_new_format)) . '\',

FIND
$row['user_password'] = md5($password_new_format);

REPLACE
$row['password'] = md5($password_new_format);

FIND
if (!$row['user_pass_convert'] && md5($password) == $row['user_password'])

REPLACE
if (!$row['user_pass_convert'] && md5($password) == $row['password'])


includes/auth/auth_ldap.php

FIND
$sql ='SELECT user_id, username, user_password, user_passchg, user_email, user_type

REPLACE
$sql ='SELECT user_id, username, password, user_passchg, user_email, user_type


MYSQL EMAIL FIELD MODIFICATION

viewtopic.php

FIND
$user_cache[$poster_id]['email'] = ($config['board_email_form'] && $config['email_enable']) ? append_sid("{$phpbb_root_path}memberlist.$phpEx", "mode=email&u=$poster_id") : (($config['board_hide_emails'] && !$auth->acl_get('a_email')) ? '' : 'mailto:' . $row['user_email']);

REPLACE
$user_cache[$poster_id]['email'] = ($config['board_email_form'] && $config['email_enable']) ? append_sid("{$phpbb_root_path}memberlist.$phpEx", "mode=email&u=$poster_id") : (($config['board_hide_emails'] && !$auth->acl_get('a_email')) ? '' : 'mailto:' . $row['email']);


memberlist.php

FIND
$sql = "SELECT user_id, username, user_email, user_lang, $sql_field

REPLACE
$sql = "SELECT user_id, username, email, user_lang, $sql_field


includes/functions_user.php

FIND
if (empty($user_row['username']) || !isset($user_row['group_id']) || !isset($user_row['user_email']) || !isset($user_row['user_type']))

REPLACE
if (empty($user_row['username']) || !isset($user_row['group_id']) || !isset($user_row['email']) || !isset($user_row['user_type']))

FIND
'user_email'		=> strtolower($user_row['user_email']),

REPLACE
'user_email'		=> strtolower($user_row['email']),

FIND
'user_email_hash'	=> crc32(strtolower($user_row['user_email'])) . strlen($user_row['user_email']),

REPLACE
'user_email_hash'	=> crc32(strtolower($user_row['email'])) . strlen($user_row['email']),

FIND
$sql = 'SELECT user_id, user_email, username_clean

REPLACE
$sql = 'SELECT user_id, email, username_clean

FIND
$founder[$row['user_id']] = $row['user_email'];

REPLACE
$founder[$row['user_id']] = $row['email'];

LINE 981?

FIND
$sql = 'SELECT u.user_id, u.user_email, u.username, u.username_clean, u.user_notify_type, u.user_jabber, u.user_lang

REPLACE
$sql = 'SELECT u.user_id, u.email, u.username, u.username_clean, u.user_notify_type, u.user_jabber, u.user_lang

FIND
$messenger->to($row['user_email'], $row['username']);

REPLACE
$messenger->to($row['email'], $row['username']);


includes/functions_privmsgs.php

FIND
$sql = 'SELECT user_id, username, user_email, user_lang, user_notify_pm, user_notify_type, user_jabber

REPLACE
$sql = 'SELECT user_id, username, email, user_lang, user_notify_pm, user_notify_type, user_jabber

FIND
if ($row['user_notify_pm'] == 1 && trim($row['user_email']))

REPLACE
if ($row['user_notify_pm'] == 1 && trim($row['email']))

FIND
'email'		=> $row['user_email'],

REPLACE
'email'		=> $row['email'],


includes/functions_posting.php

FIND
$sql = 'SELECT u.user_id, u.username, u.user_email, u.user_lang, u.user_notify_type, u.user_jabber

REPLACE
$sql = 'SELECT u.user_id, u.username, u.email, u.user_lang, u.user_notify_type, u.user_jabber

FIND
'user_email'	=> $row['user_email'],

REPLACE
'user_email'	=> $row['email'],

FIND
$sql = 'SELECT u.user_id, u.username, u.user_email, u.user_lang, u.user_notify_type, u.user_jabber

REPLACE
$sql = 'SELECT u.user_id, u.username, u.email, u.user_lang, u.user_notify_type, u.user_jabber

FIND
'user_email'	=> $row['user_email'],

REPLACE
'user_email'	=> $row['email'],

FIND
if (!$row['allowed'] || !trim($row['user_email']))

REPLACE
if (!$row['allowed'] || !trim($row['email']))

FIND
$msg_list_ary[$row['template']][$pos]['email']	= $row['user_email'];

REPLACE
$msg_list_ary[$row['template']][$pos]['email']	= $row['email'];

FIND
$sql = 'SELECT user_id, username, password, user_passchg, user_email, user_type

REPLACE
$sql = 'SELECT user_id, username, password, user_passchg, email, user_type


includes/ucp/ucp_resend.php

FIND
$sql = 'SELECT user_id, group_id, username, user_email, user_type, user_lang, user_actkey, user_inactive_reason

REPLACE
$sql = 'SELECT user_id, group_id, username, email, user_type, user_lang, user_actkey, user_inactive_reason

FIND
WHERE user_email = '" . $db->sql_escape($email) . "'

REPLACE
WHERE email = '" . $db->sql_escape($email) . "'

FIND
$messenger->to($user_row['user_email'], $user_row['username']);

REPLACE
$messenger->to($user_row['email'], $user_row['username']);

FIND
'EMAIL_ADDRESS'	=> $user_row['user_email'])

REPLACE
'EMAIL_ADDRESS'	=> $user_row['email'])

FIND
$sql = 'SELECT user_id, username, user_email, user_lang, user_jabber, user_notify_type

REPLACE
$sql = 'SELECT user_id, username, email, user_lang, user_jabber, user_notify_type

FIND
$messenger->to($row['user_email'], $row['username']);

REPLACE
$messenger->to($row['email'], $row['username']);


includes/ucp/ucp_remind.php

FIND
$sql = 'SELECT user_id, username, user_email, user_jabber, user_notify_type, user_type, user_lang, user_inactive_reason

REPLACE
$sql = 'SELECT user_id, username, email, user_jabber, user_notify_type, user_type, user_lang, user_inactive_reason

FIND
WHERE user_email = '" . $db->sql_escape($email) . "'

REPLACE
WHERE email = '" . $db->sql_escape($email) . "'

FIND
$messenger->to($user_row['user_email'], $user_row['username']);

REPLACE
$messenger->to($user_row['email'], $user_row['username']);


includes/ucp/ucp_register.php

FIND
$sql = 'SELECT user_id, username, user_email, user_lang, user_jabber, user_notify_type

REPLACE
$sql = 'SELECT user_id, username, email, user_lang, user_jabber, user_notify_type

FIND
$messenger->to($row['user_email'], $row['username']);

REPLACE
$messenger->to($row['email'], $row['username']);


includes/ucp/ucp_profile.php

FIND
$sql = 'SELECT user_id, username, user_email, user_lang, user_jabber, user_notify_type

REPLACE
$sql = 'SELECT user_id, username, email, user_lang, user_jabber, user_notify_type

FIND
$messenger->to($row['user_email'], $row['username']);

REPLACE
$messenger->to($row['email'], $row['username']);


includes/ucp/ucp_pm_viewmesage.php

FIND
$user_row['email'] = ($config['board_email_form'] && $config['email_enable']) ? append_sid("{$phpbb_root_path}memberlist.$phpEx", "mode=email&u=$user_id") : ((($config['board_hide_emails'] && !$auth->acl_get('a_email')) || empty($user_row['user_email'])) ? '' : 'mailto:' . $user_row['user_email']);

REPLACE
$user_row['email'] = ($config['board_email_form'] && $config['email_enable']) ? append_sid("{$phpbb_root_path}memberlist.$phpEx", "mode=email&u=$user_id") : ((($config['board_hide_emails'] && !$auth->acl_get('a_email')) || empty($user_row['email'])) ? '' : 'mailto:' . $user_row['email']);


includes/ucp/ucp_groups.php

FIND
$sql = 'SELECT ug.*, u.username, u.username_clean, u.user_email

REPLACE
$sql = 'SELECT ug.*, u.username, u.username_clean, u.email

FIND
$sql = 'SELECT u.username, u.username_clean, u.user_email, u.user_notify_type, u.user_jabber, u.user_lang

REPLACE
$sql = 'SELECT u.username, u.username_clean, u.email, u.user_notify_type, u.user_jabber, u.user_lang

FIND
$messenger->to($row['user_email'], $row['username']);

REPLACE
$messenger->to($row['email'], $row['username']);


includes/ucp/ucp_activate.php

FIND
$sql = 'SELECT user_id, username, user_type, user_email, user_newpasswd, user_lang, user_notify_type, user_actkey, user_inactive_reason

REPLACE
$sql = 'SELECT user_id, username, user_type, email, user_newpasswd, user_lang, user_notify_type, user_actkey, user_inactive_reason

FIND
$messenger->to($user_row['user_email'], $user_row['username']);

REPLACE
$messenger->to($user_row['email'], $user_row['username']);


includes/mcp/mcp_reports.php

FIND
$sql = 'SELECT r.report_id, r.post_id, r.report_closed, r.user_id, r.user_notify, u.username, u.username_clean, u.user_email, u.user_jabber, u.user_lang, u.user_notify_type

REPLACE
$sql = 'SELECT r.report_id, r.post_id, r.report_closed, r.user_id, r.user_notify, u.username, u.username_clean, u.email, u.user_jabber, u.user_lang, u.user_notify_type


includes/auth/auth_apache.php

FIND
$sql = 'SELECT user_id, username, password, user_passchg, user_email, user_type

REPLACE
$sql = 'SELECT user_id, username, password, user_passchg, email, user_type


includes/auth/auth_db.php

FIND
$sql = 'SELECT user_id, username, password, user_passchg, user_pass_convert, user_email, user_type, user_login_attempts

REPLACE
$sql = 'SELECT user_id, username, password, user_passchg, user_pass_convert, email, user_type, user_login_attempts


includes/auth/auth_ldap.php

FIND
$sql ='SELECT user_id, username, user_password, user_passchg, user_email, user_type

REPLACE
$sql ='SELECT user_id, username, user_password, user_passchg, email, user_type


includes/acp/acp_email.php

FIND
$sql = 'SELECT username, user_email, user_jabber, user_notify_type, user_lang

REPLACE
$sql = 'SELECT username, email, user_jabber, user_notify_type, user_lang

FIND
$sql = 'SELECT u.user_email, u.username, u.username_clean, u.user_lang, u.user_jabber, u.user_notify_type

REPLACE
$sql = 'SELECT u.email, u.username, u.username_clean, u.user_lang, u.user_jabber, u.user_notify_type

FIND
$sql = 'SELECT username, username_clean, user_email, user_jabber, user_notify_type, user_lang

REPLACE
$sql = 'SELECT username, username_clean, email, user_jabber, user_notify_type, user_lang

FIND
if (($row['user_notify_type'] == NOTIFY_EMAIL && $row['user_email']) ||

REPLACE
if (($row['user_notify_type'] == NOTIFY_EMAIL && $row['email']) ||

FIND
($row['user_notify_type'] == NOTIFY_BOTH && $row['user_email'] && $row['user_jabber']))

REPLACE
($row['user_notify_type'] == NOTIFY_BOTH && $row['email'] && $row['user_jabber']))

FIND
$email_list[$j][$i]['email']	= $row['user_email'];

REPLACE
$email_list[$j][$i]['email']	= $row['email'];


includes/acp/acp_inactive.php

FIND
$sql = 'SELECT user_id, username, user_email, user_lang, user_jabber, user_notify_type, user_regdate, user_actkey

REPLACE
$sql = 'SELECT user_id, username, email, user_lang, user_jabber, user_notify_type, user_regdate, user_actkey

FIND
$messenger->to($row['user_email'], $row['username']);

REPLACE
$messenger->to($row['email'], $row['username']);


includes/acp/acp_prune.php

FIND
$sort_by_types = array('username', 'user_email', 'user_posts', 'user_regdate', 'user_lastvisit');

REPLACE
$sort_by_types = array('username', 'email', 'user_posts', 'user_regdate', 'user_lastvisit');

FIND
$where_sql .= ($email) ? " AND user_email LIKE '" . $db->sql_escape(str_replace('*', '%', $email)) . "' " : '';

REPLACE
$where_sql .= ($email) ? " AND email LIKE '" . $db->sql_escape(str_replace('*', '%', $email)) . "' " : '';


includes/acp/acp_users.php

FIND
$ban[] = $user_row['user_email'];

REPLACE
$ban[] = $user_row['email'];

FIND
$messenger->to($user_row['user_email'], $user_row['username']);

REPLACE
$messenger->to($user_row['email'], $user_row['username']);

FIND
'email'				=> strtolower(request_var('user_email', $user_row['user_email'])),

REPLACE
'email'				=> strtolower(request_var('user_email', $user_row['email'])),

FIND
if ($data['email'] != $user_row['user_email'])

REPLACE
if ($data['email'] != $user_row['email'])

FIND
array('email', $user_row['user_email'])

REPLACE
array('email', $user_row['email'])

FIND
if ($data['email'] != $user_row['user_email'] && $data['email_confirm'] != $data['email'])

REPLACE
if ($data['email'] != $user_row['email'] && $data['email_confirm'] != $data['email'])

FIND
$update_email = ($data['email'] != $user_row['user_email']) ? $data['email'] : false;

REPLACE
$update_email = ($data['email'] != $user_row['email']) ? $data['email'] : false;

FIND
add_log('user', $user_id, 'LOG_USER_UPDATE_EMAIL', $user_row['username'], $user_row['user_email'], $update_email);

REPLACE
add_log('user', $user_id, 'LOG_USER_UPDATE_EMAIL', $user_row['username'], $user_row['email'], $update_email);

FIND
'USER_EMAIL'		=> $user_row['user_email'],

REPLACE
'USER_EMAIL'		=> $user_row['email'],


includes/constants.php

FIND
define('USERS_TABLE',				$table_prefix . 'users');

REPLACE
define('USERS_TABLE',								'jos_users');


MYSQL DATABASE CHANGES

Change user_email and user_password to email and password respectively.

RUN
ALTER TABLE `phpbb_users` ADD `id` INT( 11 ) NOT NULL ,
ADD `name` TEXT NOT NULL ,
ADD `usertype` VARCHAR( 75 ) NOT NULL ,
ADD `block` TINYINT( 4 ) NOT NULL ,
ADD `sendEmail` TINYINT( 4 ) NOT NULL ,
ADD `gid` TINYINT( 3 ) NOT NULL ,
ADD `registerDate` DATETIME NOT NULL ,
ADD `lastvisitDate` DATETIME NOT NULL ,
ADD `activation` VARCHAR( 100 ) NOT NULL ,
ADD `params` TEXT NOT NULL ;

RUN
UPDATE phpbb_users SET block = 0, sendEmail = 1, gid = 18

BACK UP
jos_users

DROP
jos_users

RENAME
phpbb_users to jos_users

RUN
UPDATE jos_users SET id = user_id

RUN
ALTER TABLE `jos_users` ADD INDEX ( `id` ) 

RUN
UPDATE jos_users SET usertype = 'Super Administrator' WHERE username = 'SLWX Katscher'

RUN
UPDATE jos_users SET gid = 25 WHERE username = '[YOUR ADMIN'S USERNAME HERE]'

RUN
UPDATE jos_users SET name = username

CLEAR
Your cookies in your browser for the current domain

RUN
SELECT user_id FROM `jos_users` WHERE username = '[YOUR ADMIN'S USERNAME HERE]'
Write down the user_id

RUN
UPDATE `jos_core_acl_aro` SET `value` = '[YOUR ADMIN'S USER_ID HERE]', `name` = '[YOUR ADMIN'S USERNAME HERE]' WHERE `id` =10 LIMIT 1
And then I created this phpscript to handle the ACL permission setup:

Code: Select all

<?php
# Database Name
$db = "";

# Database Host
$host = "";

# MySQL Username
$usr = "";

# MySQL Password
$pwd = "";

# Admin user_id
$admin_id = "[YOUR ADMIN'S USER_ID HERE]";

// DO NOT EDIT BELOW THIS LINE

// MySQL Connect String
mysql_pconnect("$host","$usr","$pwd");

// MySQL Database Select
mysql_select_db("$db");

$result = MYSQL_QUERY("SELECT user_id, name FROM jos_users");
while($r=mysql_fetch_array($result)){    

	$value = $r["user_id"];
	$name = $r["name"];
	
	if($user_id != $admin_id) {
		
		$result2 = MYSQL_QUERY("INSERT INTO jos_core_acl_aro (section_value, value, order_value, name, hidden)".
		"VALUES ('users', '$value', '0', '$name', '0')");
		
		echo "Inserting $name into jos_core_acl_aro<br>";
		
	}else
		$admin_aro_id = $value;

}

if (!$result) { echo("ERROR: " . mysql_error() . "\n");	}
if (!$result2) { echo("ERROR: " . mysql_error() . "\n");	}

$result = MYSQL_QUERY("SELECT id FROM jos_core_acl_aro");
while($r=mysql_fetch_array($result)){    

	$aro_id = $r["id"];
	
	if($aro_id != $admin_aro_id){
	
		$result2 = MYSQL_QUERY("INSERT INTO jos_core_acl_groups_aro_map (group_id, section_value, aro_id)".
		"VALUES ('18', '', '$aro_id')");
		
		echo "Inserting $aro_id into jos_core_acl_groups_aro_map<br>";
		
	}

}

if (!$result) { echo("ERROR: " . mysql_error() . "\n");	}
if (!$result2) { echo("ERROR: " . mysql_error() . "\n");	}

echo "<br><br>If you don't see any errors above, everything went as planned!";
?>
Last edited by theaxiom on Thu May 24, 2007 4:24 am, edited 1 time in total.
Top
theaxiom
Joomla! Fledgling
Joomla! Fledgling
Posts: 4
Joined: Thu May 24, 2007 3:09 am

Re: Joomla 1.5 + phpBB3 User Table Integration. Please help!

Post by theaxiom » Mon May 28, 2007 11:10 pm

Its nice to see there are a lot of people here willing to help newbies.  >:(

I am seriously considering not using Joomla! just because of this. Thanks.
Top
aaldere1
Joomla! Apprentice
Joomla! Apprentice
Posts: 7
Joined: Tue Mar 27, 2007 4:03 pm

Re: Joomla 1.5 + phpBB3 User Table Integration. Please help!

Post by aaldere1 » Wed May 30, 2007 12:06 am

Hi Axiom.  What exactly do these changes do?  Is the forum itself embedded in a JOomla installation?  Do you have a website where your integration is viewable?
Top
mehdi
Joomla! Explorer
Joomla! Explorer
Posts: 437
Joined: Tue Oct 04, 2005 3:56 pm

Re: Joomla 1.5 + phpBB3 User Table Integration. Please help!

Post by mehdi » Tue Jun 05, 2007 3:37 am

hi theaxiom,
I found the changes you made a "bit too aggressive" and not reliable.
Forcing the sharing of the same user table between two different script by hacking a lot is in my opinion a bad practice.
Its nice to see there are a lot of people here willing to help newbies.
Sorry, but considering the level of your changes , I  do not consider you "as a newbie".
If you are tempted by your solution, just perform a local install and see if everything is working fine.
Top
theaxiom
Joomla! Fledgling
Joomla! Fledgling
Posts: 4
Joined: Thu May 24, 2007 3:09 am

Re: Joomla 1.5 + phpBB3 User Table Integration. Please help!

Post by theaxiom » Tue Jun 05, 2007 3:42 am

mehdi wrote: hi theaxiom,
I found the changes you made a "bit too aggressive" and not reliable.
Forcing the sharing of the same user table between two different script by hacking a lot is in my opinion a bad practice.
Its nice to see there are a lot of people here willing to help newbies.
Sorry, but considering the level of your changes , I  do not consider you "as a newbie".
If you are tempted by your solution, just perform a local install and see if everything is working fine.



Thank you very much for your kind response.  :laugh:

I apologize, I was a bit frustrated as I had received no feedback. I actually found a better solution for what I am doing, and that is called cakePHP, you can find it at http://www.cakephp.org - it is a PHP rapid application development framework, and it is working wonderfully.

Take care.
Top
Locked

Return to “phpbb - Joomla! Integration”