Can you split phpBB's Database?

Hello,

I'm sorry if this isn't the place to ask, but the main phpBB support forums aren't very helpful.. at least not for me.
I've been googling around trying to find out how to do this and stumbled across a link to your site from phpbbhacks.

Basically, I wish to know if it's possible at all to force phpBB to use two databases at once.

What I want to do is merge ExpressionEngine and phpBB's user tables together, so that they both share the same username/password information, while everything else is on their own DB. I'm trying to do this so people don't need to register twice. I managed to force a test-install of phpBB to use SHA1 for password encryption to be compatible with EE's encryption, but I haven't the foggiest idea on how to proceed with the next step..

Any help would be duly appreciated, thanks in advance.

It can be done. The demo board I have on this site actually uses two databases, though it doesn't share user information. You have to make sure the database user account that phpBB uses (listed as $dbuser in config.php) has rights to both databases.

The next step for you would be to decide which registration form you want to use - ExpressionEngine's or phpBB's. Then, you'll need to find the registration part of that script and modify it to insert the registration details in the other's database. That is the trickiest part.

Thanks for the reply

It's good to know it can be done, since I've been getting nothing but silence everywhere else - I was about to give up.

In any case, rather than extensively modifying the files, I wanted to point only the user_password and username rows to a second DB. I'm not sure if I can re-route specific rows within a table or if I have to re-route the entire table..
I'm not sure on the exact layout of the tables ExpressionEngine uses, because I was wrestling to get SHA1 working in phpBB.

As far as registration forms go, To simplify it, I wanted to make it so the user can register at either one, the username and password will go to the "shared" database and all other information goes where each program needs it, and I'll just tell them to update their profile in the other.

Also, how can I tell it to access both? I looked in config.php but it says don't change it, and already has info for my main database.. unless I'm supposed to add an extra line with details for the second DB.

Thanks again!

Quote:

Unfortunately, you can't work with specific rows like that without making even more extensive changes to the files. phpBB pulls the username from the database in a lot of places, and you'd probably have to track down and change every one. With that approach, you're talking about edits to viewtopic.php, login.php, page_header.php, forums.html, viewforum.php, search.php, functions.php, sessions.php... well, you get the idea. It's a long list. If you decide to install any big mods for phpBB, you'd have to edit all of those, too.

There are really only two ways you can make a cross-registration system work without driving yourself insane with file edits. The first approach is to make registration on each script do the main work of registration on both. That would mean editing phpBB's usercp_register.php to add whatever info EE needs to EE's database and also editing EE's registration to add the info phpBB needs in it's database. After that, there would be separate profiles for the user in EE and phpBB.

The second approach is to combine the user tables. I had forgotten about this before, because it's been a long time since I did a shared account setup. This won't work in all cases; it depends on how different the layout of ExpressionEngine's tables are from phpBB's. If it is possible here, then it will be the easiest method with the fewest file changes and will allow for mostly linked profiles in the two scripts. There would generally be a lot of alterations needed to one of the database tables. Getting the password systems to work as you have is a great first step in this. But even with this approach, both scripts may have to be changed to do some extra registration tasks.

The thing about phpBB is that it actually creates three table entries when someone registers. One in phpbb_users is their account, and the other two in phpbb_user_groups and phpbb_groups are permissions for the account. When someone registers in ExpressionEngine, EE will also have to create those permissions entries for phpBB at the very least. If EE normaly does anything like that itself, phpBB will have to do what EE typically does, also. That is what is so tricky about these cross-registration setup: the steps needed are different in every script.

Quote:

You can leave config.php as it is, and tell the scripts to access the other database elsewhere in the code. In MySQL there are two ways you can refer to a database table. The first is what phpBB and most scripts use, and that is to just use the table name, like "phpbb_users." The second is a database.table notation, like "mydatabase.phpbb_users," where "mydatabase" is the name of the database holding the phpBB tables. This notation will let you access a second database for a few tables.

If you edit the constants.php file of phpBB, you can tell it to look for tables using the database.table notation. For example, I have the demo board here using the main board's word censor table. In the demo board's constants.php, I've replaced this line:

Code:

With something like:

Code:

That tells the demo phpBB, "Hey! Use the phpbb_words table in database1, instead of the table in database2."

If you go with the combining tables approach, you could edit this line and have it point at EE's user table.

Code:

Wow, this is definately the most details I've been given so far, thank you very much

Let's see.. moving ahead, I installed EE to look at its table structure.
It seems very different
At the very least, the username row is named the same. password is different though, phpBB is "user_password" where ee is simply "password"

In any case, the EE table has a ton more tables in it than phpbb, and contains a lot of different information, so I believe a table merge would be very cumbersome at this point.

I think cross-registration might be the best bet, and might be doable with that database.table notation.. At least in the registration pages, we could tell it to write the password and username and other basic data shared by both (like joindate and etc) to the other's DB via the db.table approach. Although to make it transparent, I would probably also need to hunt down the change password pages as well, so a change in password will be updated in both DBs. This also removes the third DB entirely.

Although admittedly, I am still learning PHP and DB's are new to me, so I'm trying to learn as quickly as I can to implement this. As far as this plan is concerned, how would the edits look phpbb-side? I could try to mirror it over to the EE side once I get it done.

Much thanks again

Glad I can help.

Quote:

I can't be too specific because I don't know how EE's tables are laid out, but it would generally be something like this:

Code:

Thanks!

I will try it out right now and see if I can make it work..
I'm still debating with myself whether to use MD5 or not.. EE has the option of using SHA1 or MD5, SHA1 being defualt. If I use MD5 it would be even fewer code changes, but SHA1 is more secure.. I dunno.

In any case, let me see if I can understand that snippet..
You mean I should find that specific cluster of lines in usercp_register.php and add that segment after it, instead of replacing, right? Seems like a duplicate call into the other database, although I can't tell which fields are optional/should be edited.. There are a lot of semingly empty fields with the quotatinal marks.

Parameters aside, it looks like a simple addition to make this configuration work.. would just have to add a call into the change password thing as well to sync passwords.

Thanks again

Edit:
What are the str_replace() things?
I'll assume they mean string replace, but what is it there for?
I thought it would just use the information provided by the registration script, which should have already been declared with a value from the user..

Yeah, you have the gist of it.

str_replace is indeed string replace. In these cases, it's used to replace any \' sequences in the username and password with a sequence of two '. This is done because MySQL will throw an error if a ' is used in a query without being escaped, and the way to escape a ' for MySQL is to put another ' before it. PHP typically adds a \ before the ' instead of another ', so str_replace is used to fix that for MySQL.

Ahh, that string replace looked very confusing at first, but reconstructing it manually helped

I thought it wasn't necessary, but a glance at the phpBB code reveals it uses the same thing, so I guess it would be in my best interest to copy it.
There's another thing though, I'm trying to keep everything as close as possible, and both phpBB and EE need a regdate in their database.
phpBB seems to use time() for the regdate field, would it display a different regdate if I used that for the EE-database insertion, since it's being called later in the script?

Thanks again! I'm trying to build a query right now.. I'll post it in a bit to see if there are any errors lol

Edit:

Well, I've almost gotten the basic query down.. I think. Just need a few more fields. Unfortunately, those fields are IP, an EE-specific "unique_id" and an authcode, I believe for the email verification. For member_id, I borrowed phpBB's user_id code, I hope that's ok.

I can prolly leave authcode blank since I don't want to send the user 2 emails. For the IP though, EE has this: (I believe is the code for generating it at least)

Code:

And this is the unique_id one

Code:

Quote:

Usually it's not necessary and \' will be good enough, but I've read that some newer versions of MySQL will reject \' in favor of two '. I'm not sure what the cutoff point for that in MySQL is, but better safe than sorry.

Quote:

No, it would be the same date. It might be a few microseconds later, but there won't be a significant difference.

You can try the variable $client_ip for the IP address. That should be available in phpBB during the registration.

The unique id may be a bit harder. If you can find the random() function they use to generate it, we may be able to adapt that to create it here.