In the following tutorial I'll show you how to integrate PhpBB3 and CBSubs in order to grant/deny access to PhpBB3 groups depending on the CBSubs plan a Joomla user is subscribed to.

What you need:

• Joomla, CB and CBSubs of course.
• CBSubs SQL plugin installed and published.
• I personally use JFusion for the bridge part but you could use any other bridge.
• MySQL 5.0 or higher for stored procedures support.
• Access to the mysql console in order to create the necessary procedures.

In my configuration PhpBB3 and Joomla are in separate databases. If you have both on the same database the procedure is only simplified.

Supposing that each one have a database of its own, you need to grant your Joomla database user with the necessary permissions (at least SELECT, INSERT AND DELETE permissions) for making changes in the PhpBB3 database. You can easy do this using phpMyAdmin under the privileges section. This is not necessary if PhpBB3 and Joomla share the same database

We are now going to create the stored procedures. Think of stored procedures as MySQL functions that contain some logic and SQL calls that interact with your databases. You need to gain access to your server terminal and launch the MySQL console (this is done in linux with the mysql -uYourJoomlaDatabaseUser -p command). You'll be asked for your password. Once you are in you should see something like this:

[code:1]macbook-pro-de-arunas-mazeika:bin amazeika$ ./mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.41 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>[/code:1]

The next thing to do is to chose the database where we are going to store the procedures, in our case, this is the Joomla database. For this we execute the following command (make sure to substitute YourJoomlaDatabase with your actual database name):

[code:1]mysql> use YourJoomlaDatabase;[/code:1]

Now we need to change the SQL delimiter to something different, like |. Execute the next command:

[code:1]mysql> delimiter |[/code:1]

We are now ready to create our stored procedures. Copy and paste the following block making sure you substitute the red parts of the code with you databases names and prefixes:

CREATE PROCEDURE `grantPhpbbGroupAccess`(groupId INT, JUserId INT)
BEGIN
DECLARE userId, userInGroup INT DEFAULT 0;
DECLARE userEmail VARCHAR(100);
SELECT email INTO userEmail FROM jos_users WHERE id = JUserId;
SELECT user_id INTO userId FROM YourPhpbb3Database.phpbb_users WHERE user_email = userEmail;
IF userId THEN
SELECT COUNT(*) INTO userInGroup FROM YourPhpbb3Database.phpbb_user_group WHERE user_id = userId AND group_id = groupId;
IF NOT userInGroup THEN
INSERT INTO YourPhpbb3Database.phpbb_user_group VALUES (groupId,userId,0,0);
END IF;
END IF;
END|

hit enter after pasting, you should get a message like this: Query OK, 0 rows affected (0,00 sec)

Execute the following command to revert the delimiter to its default value ( ; ):

[code:1]mysql> delimiter ;[/code:1]

Now execute this in order to check that procedure was successfully stored in your Joomla database:

[code:1]mysql> SHOW CREATE PROCEDURE grantPhpbbGroupAccess;

You should get a response similar to this one:

+

---

+

---

+

---

+
| Procedure | sql_mode | Create Procedure |
+

---

+

---

+

---

+
| grantPhpbbGroupAccess | | CREATE DEFINER=`root`@`localhost` PROCEDURE `grantPhpbbGroupAccess`(groupId INT, JUserId INT)
BEGIN
DECLARE userId, userInGroup INT DEFAULT 0;
DECLARE userEmail VARCHAR(100);
SELECT email INTO userEmail FROM jos_users WHERE id = JUserId;
SELECT user_id INTO userId FROM YourPhpbb3Database.phpbb_users WHERE user_email = userEmail;
IF userId THEN
SELECT COUNT(*) INTO userInGroup FROM YourPhpbb3Database.phpbb_user_group WHERE user_id = userId AND group_id = groupId;
IF NOT userInGroup THEN
INSERT INTO YourPhpbb3Database.phpbb_user_group VALUES (groupId,userId,0,0);
END IF;
END IF;
END |
+

---

+

---

+

---

+
1 row in set (0,00 sec)[/code:1]

So far, so good. We need to repeat this procedure for remaining denyPhpbbGroupAccess stored procedure. As we just did earlier, change the SQL delimiter to |:

[code:1]mysql> delimiter |[/code:1]

Copy and Paste the following code making sure you substitute the red parts of the code with you databases names and prefixes:

CREATE PROCEDURE `denyPhpbbGroupAccess`(groupId INT, JUserId INT)
BEGIN
DECLARE userId, userInGroup INT DEFAULT 0;
DECLARE userEmail VARCHAR(100);
SELECT email INTO userEmail FROM jos_users WHERE id = JUserId;
SELECT user_id INTO userId FROM YourPhpbb3Database.phpbb_users WHERE user_email = userEmail;
IF userId THEN
SELECT COUNT(*) INTO userInGroup FROM YourPhpbb3Database.phpbb_user_group WHERE user_id = userId AND group_id = groupId;
IF userInGroup THEN
DELETE FROM YourPhpbb3Database.phpbb_user_group WHERE user_id = userId AND group_id = groupId;
END IF;
END IF;
END|

hit enter after pasting, you should get a message like this: Query OK, 0 rows affected (0,00 sec)

Execute the following command to revert the delimiter to its default value (:

[code:1]mysql> delimiter ;[/code:1]

Now execute this in order to check that procedure was successfully stored in your Joomla database:

[code:1]mysql> SHOW CREATE PROCEDURE denyPhpbbGroupAccess;
+

---

+

---

+

---

+
| Procedure | sql_mode | Create Procedure |
+

---

+

---

+

---

+
| denyPhpbbGroupAccess | | CREATE DEFINER=`root`@`localhost` PROCEDURE `denyPhpbbGroupAccess`(groupId INT, JUserId INT)
BEGIN
DECLARE userId, userInGroup INT DEFAULT 0;
DECLARE userEmail VARCHAR(100);
SELECT email INTO userEmail FROM jos_users WHERE id = JUserId;
SELECT user_id INTO userId FROM YourPhpbb3Database.phpbb_users WHERE user_email = userEmail;
IF userId THEN
SELECT COUNT(*) INTO userInGroup FROM YourPhpbb3Database.phpbb_user_group WHERE user_id = userId AND group_id = groupId;
IF userInGroup THEN
DELETE FROM YourPhpbb3Database.phpbb_user_group WHERE user_id = userId AND group_id = groupId;
END IF;
END IF;
END |
+

---

+

---

+

---

+
1 row in set (0,01 sec)[/code:1]

If you are still with me, the worst part is behind us .

Let's now configure the CBSubs plan. Go to your plan integrations tab -> SQL actions and insert the following two queries:

SQL action 1 on ACTIVATION (WARNING: use only integer substitutions): CALL grantPhpbbGroupAccess(phpbb3_group_id,[user_id])

SQL action 1 on DEACTIVATION (WARNING: use only integer substitutions): CALL denyPhpbbGroupAccess(phpbb3_group_id,[user_id])

grantPhpbbGroupAccess(phpbb3_group_id,[user_id]) will add your current subscriber to the PhpBB3 group with id = phpbb3_group_id. IMPORTANT !!! you need to replace phpbb3_group_id with the actual id of the group you want your user to be added. [user_id] should be left as it is since this is the CB field User ID that will be interpreted by CBSubs.

The PhpBB3 group needs to be created in the PhpBB3 ACP (Administration Control Panel) which is the PhpBB3 backend. This can be done in the General tab -> Quick Access -> Manage Group.

Once your group is created we need its id. Open phpMyAdmin and go to your PhpBB3 database. Select your groups table (phpbb_groups if your PhpBB prefix is phpbb_) and display its rows. Search for the group name you just added in the group_name column and you'll get its id. This is the value you have to add instead of phpbb3_group_id in:

SQL action 1 on ACTIVATION (WARNING: use only integer substitutions): CALL grantPhpbbGroupAccess(phpbb3_group_id,[user_id])

SQL action 1 on DEACTIVATION (WARNING: use only integer substitutions): CALL denyPhpbbGroupAccess(phpbb3_group_id,[user_id])

under the SQL actions tab of your CBSubs plan.

We are almost done since there's possibly a last problem that needs to be addressed. I say possibly because I had to face it and perhaps you will as well. PhpBB3 cache mechanism keeps a record of the permissions each user have. Even when you manually add a user to a group you have sometimes to flush the cache in order to make these changes visible.

I was able to isolate the cache file that keeps this information. Its name is data_acl_options.php under the cache directory of PhpBB3.

The solution I came up is to setup a cronjob that will delete this file every five minutes. This means that in the worst case, the new subscriber will not be able to see the PhpBB3 changes within his first 5 minutes as a subscriber, wich is fine by me. For those wondering how can I do this, this is how it's done:

In your web server's unix terminal:

[code:1]crontab -e[/code:1]

Add the following line at the end:

*/5 * * * * /bin/rm /YourPhpBB3AbsolutePath/cache/data_acl_options.php

At this point be very careful, and try to understand what you are doing since bad usage of the rm (delete command) could be catastrophic. Be sure to correctly replace the YourPhpBB3AbsolutePath part with your PhpBB3 absolute path. Then save and exit from the editor.

Now we are done !!!. Of course, you need to assign your PhpBB3 group to some forums/categories but this is out of the scope of this tutorial.

Enjoy and pay some Karma .

Post edited by: amazeika, at: 2009/07/14 19:33

nant wrote:

Thank you for sharing!

My pleasure Nick.

I commend you for contributing a much needed integration solution!

I'm still trying to integrate my cbSubs Plans with kunena forum permissions.

Do you suggest I implement this setup/procedure in order to control which kind of users would require moderator approval when they publish threads on the forum?
(e.g. I want to allow paid subscribers to publish threads without the need for moderator approval. Conversely, free registered users can also publish threads, but they would require moderator approval required.)

I have some questions before I consider installing phpbb3 in order to integrate the two.

Can I execute the SQL stored procedures protocal using the 'MySQL' icon button from my shared hosting's Cpanel?

I'm not sure if my web hosting company would allow me to execute the adjustment for the flushing of the PhpBB3 cache mechanism due to my being on a shared hosting web server/account.

I can live with the permissions or forum threads not publishing right away.
I hope this isn't (I don't think it is) necessary in order to benefit from this integration, is it?

Post edited by: daywalker0028, at: 2010/05/21 16:33

Post edited by: daywalker0028, at: 2010/05/21 16:50

Post edited by: daywalker0028, at: 2010/05/21 16:50

Post edited by: daywalker0028, at: 2010/05/21 16:54

This is a great howto. However I only got half of it to work, and I don't understand why the other half isn't working

The CALL grantPhpbbGroupAccess(8,99); works fine, a user is added to the appropriate group when that procedure is called. But CALL denyPhpbbGroupAccess(8,99); fails with the following error:

ERROR 1142 (42000): SELECT command denied to user 'dbuser'@'localhost' for table 'phpbb_users'

I'm not sure why one works and the other doesn't. I'm running these right from the mysql command line to see the error messages directly.